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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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

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'

       (Cost=1 Card=14 Bytes=140)

 

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.