General |
Note: Contains functional interface for procedures and functions associated with management of stored outlines |
Source |
{ORACLE_HOME}/rdbms/admin/dbmsol.sql |
First Available |
8.1.5 |
Dependencies |
DBA_OUTLINES |
DBMS_OUTLN_LIB |
OL$ |
OUTLINE |
DBMS_ASSERT |
DBMS_SQL |
OL$HINTS |
USER_OUTLINES |
|
Exceptions |
Number |
Description |
-209649 |
Outline not found |
|
Security Model |
Execute is granted to DBA, OUTLN, and
EXECUTE_CATALOG_ROLE |
Synonym |
DBMS_OUTLN |
|
CLEAR_USED |
Clears the outline
'used' flag |
outln_pkg.clear_used(name IN VARCHAR2); |
exec outln_pkg.clear_used('UW_OUTLINES'); |
|
CREATE_OUTLINE |
Generate an outline from the shared cursor identified by hash value and child number. |
outln_pkg.create_outline(
hash_value IN NUMBER,
child_number IN NUMBER,
category IN VARCHAR2 DEFAULT 'DEFAULT'); |
SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
SELECT hash_value, sql_id, child_number
FROM gv$sql
WHERE sql_text LIKE '%XPLAN_CURSOR%';
SELECT name, owner, category, enabled, format
FROM dba_outlines;
exec outln_pkg.create_outline(291933262, 0);
-- Note: In 11.1.0.6 this caused an ORA-03113 after
outline creation
SELECT name, owner, category, enabled, format
FROM dba_outlines;
SELECT name, hint
FROM dba_outline_hints;
DROP OUTLINE SYS_OUTLINE_08033010584585901; |
|
DROP_BY_CAT |
Drop outlines belonging to the named category |
outln_pkg.drop_by_cat(cat IN VARCHAR2); |
exec outln_pkg.drop_by_cat('DEFAULT'); |
|
DROP_COLLISION |
Drop outlines with an ol$.hintcount that does not match the number of hints in ol$hints |
outln_pkg.drop_collision; |
exec outln_pkg.drop_collision; |
|
DROP_COLLISION_EXPACT |
Generates a string which serves as an invocation of the drop_collision stored procedure |
outln_pkg.drop_collision_expact RETURN
VARCHAR2; |
SELECT outln_pkg.drop_collision_expact
FROM dual; |
|
DROP_EXTRAS |
Drop hint tuples not accounted for by hintcount |
outln_pkg.drop_extras; |
exec outln_pkg.drop_extras; |
|
DROP_EXTRAS_EXPACT |
Generates a string which serves as an invocation of the drop_extras stored procedure |
outln_pkg.drop_extras_expact RETURN VARCHAR2; |
SELECT
outln_pkg.drop_extras_expact
FROM dual; |
|
DROP_UNREFD_HINTS |
Drop hint tuples having no corresponding outline in the OL$ table |
outln_pkg.drop_unrefd_hints; |
exec outln_pkg.drop_unrefd_hints; |
|
DROP_UNREFD_HINTS_EXPACT |
Generates a string which serves as an invocation of the drop_unrefd_hints stored procedure |
outln_pkg.drop_unrefd_hints_expact RETURN VARCHAR2; |
SELECT
outln_pkg.drop_unrefd_hints_expact
FROM dual; |
|
DROP_UNUSED |
Drop outlines that have never been applied |
outln_pkg.drop_unused; |
exec outln_pkg.drop_unused; |
|
EXACT_TEXT_SIGNATURES |
Updates outline
signatures to those that compute based on exact text matching |
outln_pkg.exact_text_signatures; |
exec outln_pkg.exact_text_signatures; |
|
REFRESH_OUTLINE_CACHE |
Re-populates the cache with the current set of outlines |
outln_pkg.refresh_outline_cache; |
exec outln_pkg.refresh_outline_cache; |
|
REFRESH_OUTLINE_CACHE_EXPACT |
Generates a string which serves as an invocation of the refresh_outline_cache procedure |
outln_pkg.refresh_outline_cache_expact
RETURN VARCHAR2; |
SELECT outln_pkg.refresh_outline_cache_expact
FROM dual; |
|
UPDATE_BY_CAT |
Change the category of all outlines in one category to another |
outln_pkg.update_by_cat(
old_cat IN VARCHAR2 DEFAULT 'DEFAULT',
new_cat IN VARCHAR2 DEFAULT 'DEFAULT'); |
exec outln_pkg.update_by_cat('DEFAULT', 'UW_OUTLINES'); |
|
UPDATE_SIGNATURES |
Updates outline
signatures to the current version's signature |
outln_pkg.update_signatures; |
exec outln_pkg.update_signatures; |