CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle OUTLN_PKG
Version 11.1
 
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;
 
Related Topics
Outlines
OUTLN_EDIT_PKG
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [227 users online]    © 2010 psoug.org