Oracle SQL Tuning and CBO Internal Stored Outlines

Call (800) 766-1884 for Oracle support & training

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Chapter 2: Stored Outlines

Another 8i feature requiring the cost-based optimizer is stored outlines. To force Oracle to execute SQL statements the same way no matter what occurs at the operating system level, database level, etc., create a stored outline and invoke its category. The system privilege, QUERY REWRITE, must be granted to the schema that plans to use stored outlines and materialized views. The following command is entered from the SYS schema.

SQL> GRANT QUERY REWRITE TO SYSTEM;

Grant succeeded.

SQL> CONNECT SYSTEM/MANAGER

The following command creates a stored outline named EMPLOYEES with a category of SALARY.

SQL> CREATE OR REPLACE OUTLINE EMPLOYEES

2 FOR CATEGORY SALARY ON

3 SELECT ENAME, SAL, LOC

4 FROM EMP, DEPT

5 WHERE EMP.DEPTNO = DEPT.DEPTNO;

Outline created.

Several data dictionary views get updated when you create stored outlines. The view user_outline_hints is actually looking at outln.ol$_hints. The following query shows that Oracle stores ten hints for the SQL statement joining the EMP and DEPT tables in category SALARY.

SQL> SELECT HINT

2 FROM USER_OUTLINE_HINTS

3 WHERE NAME = 'EMPLOYEES'

4* ORDER BY HINT;

HINT

------------------------------

FULL(DEPT)

FULL(EMP)

NOREWRITE

NO_EXPAND

NO_FACT(DEPT)

NO_FACT(EMP)

ORDERED

PQ_DISTRIBUTE(EMP NONE NONE)

USE_HASH(EMP)

10 rows selected.

Another data dictionary view, user_outlines, is actually looking at outln.ol$. This view is updated when you create a stored outline. Notice that the outline has not been used.

SQL> SELECT NAME,

2 CATEGORY,

3 USED

4* FROM USER_OUTLINES;

NAME CATEGORY USED

------------ ------------ ---------

EMPLOYEES SALARY UNUSED

One method of insuring that Oracle uses the cost-based optimizer is to analyze one or more tables used in a query. The following two statements analyze the EMP and DEPT tables.

SQL> ANALYZE TABLE EMP COMPUTE STATISTICS;

Table analyzed.

SQL> ANALYZE TABLE DEPT COMPUTE STATISTICS;

Table analyzed.

If query_rewrite_enabledis not set to true, Oracle will not use a stored outline or materialized view.

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Session altered.

To cause the cost-based optimizer to rewrite your query and use the stored outline for CATEGORY SALARY, enter the following command.

SQL> ALTER SESSION SET

USE_STORED_OUT LINES = SALARY;

Session altered.

Now, the moment of truth we have all been waiting for. Write the query stored in the outline EMPLOYEES category SALARY, and query the data dictionary to see if Oracle used your outline to execute the query. Because the used column shows “USED,” Oracle did use your stored outline.

SQL> SELECT ENAME, SAL, LOC

2 FROM EMP, DEPT

3* WHERE EMP.DEPTNO = DEPT.DEPTNO;

...... Output Omitted Here ...

SQL> SELECT NAME,

2 CATEGORY,

3 USED

4* FROM USER_OUTLINES;

NAME CATEGORY USED

--------- -------- -----

EMPLOYEES SALARY USED

To see the EXPLAIN PLAN output for this query, use AUTOTRACE. Notice that Oracle uses a HASH JOINand a full table scan on each of the two tables, which are hints in user_outline_hints.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT ENAME, SAL, LOC

2 FROM EMP, DEPT

3* WHERE EMP.DEPTNO = DEPT.DEPTNO;

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=3 Card=14 Bytes=266)

1 0 HASH JOIN (Cost=3 Card=14 Bytes=266)

2 1 TABLE ACCESS (FULL) OF 'DEPT'

(Cost=1 Card=4 Bytes=36)

3 1 TABLE ACCESS (FULL) OF 'EMP'