Improving the Oracle Optimizer - Oracle Database 11gR2 Performance Tuning - page 305

How to do it...

The following steps will demonstrate stored outlines, their creation, and management:

1. Connect to SH schema:

CONNECT sh@TESTDB/sh

2. Execute a query on the CUSTOMERS and COUNTRIES tables, analyzing the

execution plan:
SET AUTOT TRACE EXP
SELECT C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
C.CUST_STREET_ADDRESS, C.CUST_POSTAL_CODE, C.CUST_CITY,
C.CUST_STATE_PROVINCE, CN.COUNTRY_NAME
FROM sh.CUSTOMERS C, sh.COUNTRIES CN
WHERE C.COUNTRY_ID = CN.COUNTRY_ID;

3. Execute the same query using the primary key index COUNTRIES_PK to analyze the

execution plan:
SELECT /*+ INDEX (CN COUNTRIES_PK) */
C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
C.CUST_STREET_ADDRESS, C.CUST_POSTAL_CODE, C.CUST_CITY,
C.CUST_STATE_PROVINCE, CN.COUNTRY_NAME
FROM sh.CUSTOMERS C, sh.COUNTRIES CN
WHERE C.COUNTRY_ID = CN.COUNTRY_ID;
SET AUTOT OFF

4. Create an outline, named CUST_LIST_OUTLINE , to store the execution plan,

obtained in step 3, in the APP_LISTS category:
CREATE OR REPLACE OUTLINE CUST_LIST_OUTLINE FOR CATEGORY
APP_LISTS ON
SELECT /*+ INDEX (CN COUNTRIES_PK) */
C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
C.CUST_STREET_ADDRESS, C.CUST_POSTAL_CODE, C.CUST_CITY,
C.CUST_STATE_PROVINCE, CN.COUNTRY_NAME
FROM sh.CUSTOMERS C, sh.COUNTRIES CN
WHERE C.COUNTRY_ID = CN.COUNTRY_ID;

5. Alter the session to use the stored outlines of the APP_LISTS category:

ALTER SESSION SET USE_STORED_OUTLINES = APP_LISTS;