| General Information |
| 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 |
V$SQL |
|
| Exceptions |
| Number |
Description |
| ORA-209649 |
Outline not found |
|
| Security Model |
Execute is granted to DBA, OUTLN, and EXECUTE_CATALOG_ROLE roles |
| Subprograms |
| CLEAR_USED |
DROP_EXTRAS |
EXACT_TEXT_SIGNATURES |
| CREATE_OUTLINE |
DROP_EXTRAS_EXPACT |
REFRESH_OUTLINE_CACHE |
| DROP_BY_CAT |
DROP_UNREFD_HINTS |
REFRESH_OUTLINE_CACHE_EXPACT |
| DROP_COLLISION |
DROP_UNREFD_HINTS_EXPACT |
UPDATE_BY_CAT |
| DROP_COLLISION_EXPACT |
DROP_UNUSED |
UPDATE_SIGNATURES |
|
| 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 category |
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; |
