Where to use Stored Outlines ?
The SQL statements in a hybrid system need to have an execution plan that is appropriately tailored for the type of system configuration you are using. When running the statements during the day in the OLTP configuration they need to use a different execution plan than they would use when running at night in the DSS configuration. This can be achieved by creating two different stored outlines for each SQL statement. The outlines are grouped by categories. You can name one category OLTP and the other DSS. Each category contains the different execution paths.
How to create the Stored Outline ?
To ensure that the same tuning execution plan is used every time a particular query is recompiled, you must create a stored outline for it. The stored outline is created with the CREATE OUTLINE command. When creating an individual stored outline for your SQL statement using the CREATE OUTLINE command you give the outline a name which is later used to identify which outline to use during the statements execution.
CREATE OR REPLACE OUTLINE emp_hiredate
FOR CATEGORY dss ON
SELECT empno,ename,TO_CHAR(hiredate,'DD.MM.YYYY') hiredate
FROM (SELECT empno,ename,hiredate
ORDER BY hiredate DESC)
WHERE ROWNUM < 5
How to use Stored Outlines ?
The statement in the stored outline must match the text of the SQL statement being executed. This is done by using the method for comparing cursors in the shared pool.
Stored outlines rely on hints used by the optimizer for its execution plans. Stored outlines use the cost-based optimizer. Their query text must match exactly the text of the stored outline SQL statement. The USE_STORED_OUTLINES parameter may be set at the instance or session levels, but will be of no use if the stored outline has not yet been created. The parameter identifies a stored outline to be used when executing a SQL statement. If the parameter is set to TRUE, DEFAULT category outlines are used. If the parameter is set to a category name, that categories outlines are used. This parameter is set using the ALTER SESSION or ALTER SYSTEM command. The ALTER OUTLINE SQL command can be used to change the category of an outline, rename an outline, or rebuild an outline.
ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
Where can I find the created Stored Outlines ?
All stored outlines are stored in the OUTLN schema and may be transferred easily using the Export/Import utility. The OUTLN schema is automatically created with the database.
The OL$ OUTLN schema table contains the outline name, statement text, category, and the creation date. The OL$HINTS OUTLN schema table contains the hints for the outlines in the OL$ table. This information can also be viewed in the DBA_OUTLINES and DBA_OUTLINE_HINTS data dictionary views.
SELECT name,used FROM USER_OUTLINES;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60585/，如需转载，请注明出处，否则将追究法律责任。