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


 

Using DML and Packages to Edit Outlines
 

This is an example of editing the outlines manually. The steps are as follows:

  1. Create a new public stored outline

  2. Create a private outline

  3. Edit the private outline, test the result

  4. Publicize the edits by creating a public stored outline from your private outline.

A Detailed Example
 

Let’s look at a more detailed example.

  1. Create a new public stored outline

a. First, ensure that the required privileges have been granted.
 

  SQL> connect system as sysdba

  Enter password:

  Connected.

 

  SQL> grant create any outline to scott;

  

  Grant succeeded.

 

  SQL> grant execute on dbms_outln to scott;

  

  Grant succeeded.

  

  SQL> grant execute on dbms_outln_edit to scott;

  

  Grant succeeded.  
 

b. Connect and set your environment.  In this example, you will set hash_join_enabled=false and you will still be able to enforce a hash join by editing your stored outline.
 

  SQL> connect scott

  Enter password:

  Connected.

 

  SQL> set pages 1000

 

  SQL> alter session set optimizer_goal=all_rows;

 

  Session altered.

 

  SQL> alter session set hash_join_enabled=false;

 

  Session altered.  
 

  c. Create standard demo tables in Scott's schema.
 

  SQL> @$ORACLE_HOME/sqlplus/demo/demobld.sql

  Building demonstration tables.  Please wait.

  Demonstration table build is complete.

 d. Analyze the tables for Oracle cost-based optimizer.
 

  SQL> analyze table emp compute statistics;

 

  Table analyzed.

 

  SQL> analyze table dept compute statistics;

 

  Table analyzed.  
 

 e. Check the current execution plan for the query involved.
 

  SQL> set autotrace on explain

 

  SQL> select e.ename from emp e, dept d where e.deptno=d.deptno;

 

  ENAME

  ----------

  SMITH

  ALLEN

  WARD

  JONES

  MARTIN

  BLAKE

  CLARK

  SCOTT

  KING

  TURNER

  ADAMS

  JAMES

  FORD

  MILLER

 

  14 rows selected.

 

  Execution Plan

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

     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=126 Card=41 Bytes=

            1353)

     1    0   NESTED LOOPS (Cost=126 Card=41 Bytes=1353)

     2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=41 Bytes=820)

     3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=41 Bytes=533)

 

  SQL> set autotrace off

f. Create a public outline
 

  SQL> create or replace outline outln1

    2  on select e.ename from emp e, dept d where e.deptno=d.deptno;

 

  outline created.

2. Create a private outline

a. Create the Edit Tables in your schema, to store information about your  private outlines.  The tables created in this step must be there for the next step, otherwise you will get error: 
 

ORA-18009 "one or more outline system tables do not exist".

 

  SQL> execute dbms_outln_edit.create_edit_tables;

 

  PL/SQL procedure successfully completed.

b. Create your private outline by cloning it from the Public outline.
 

 SQL> create private outline priv_outln1 from outln1;

 

  outline created.

3. Edit the private outline, test the result

a. After you create the private outline, new entries appear in the table ol$hints.  This table is created in your schema by running the procedure create_edit_tables.  Let’s examine the contents relevant to the newly created private outline.
 

  SQL> column hint# format 999999

  SQL> column hint_text format a28

  SQL> column user_table_name format a16

 

  SQL> select hint#, hint_text, user_table_name from ol$hints

    2  where ol_name = 'PRIV_OUTLN1';

 

    HINT# HINT_TEXT                    USER_TABLE_NAME

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

        1 NOREWRITE

        2 NOREWRITE

        3 NO_EXPAND

        4 PQ_DISTRIBUTE(D NONE NONE)   SCOTT.DEPT

        5 USE_NL(D)                    SCOTT.DEPT

        6 ORDERED

        7 NO_FACT(D)                   SCOTT.DEPT

        8 NO_FACT(E)                   SCOTT.EMP

        9 FULL(E)                      SCOTT.EMP

       10 FULL(D)                      SCOTT.DEPT

 

  10 rows selected.
 

b. Edit the outline by performing DML against the appropriate hint records in the ol$hints table.  In this example, you will change the hint USE_NL to  USE_HASH.
 

  SQL> update ol$hints set hint_text='USE_HASH(D)'

    2  where hint# = 5;

 

  1 row updated.

 

  SQL> commit;

 

  Commit complete.
 

Query the table ol$hints to see the changes.
 

  SQL> select hint#, hint_text, user_table_name from ol$hints

    2  where ol_name = 'PRIV_OUTLN1';

 

    HINT# HINT_TEXT                    USER_TABLE_NAME

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

        1 NOREWRITE

        2 NOREWRITE

        3 NO_EXPAND

        4 PQ_DISTRIBUTE(D NONE NONE)   SCOTT.DEPT

        5 USE_HASH(D)                  SCOTT.DEPT

        6 ORDERED

        7 NO_FACT(D)                   SCOTT.DEPT

        8 NO_FACT(E)                   SCOTT.EMP

        9 FULL(E)                      SCOTT.EMP

       10 FULL(D)                      SCOTT.DEPT

 

  10 rows selected.

c. After manually editing the outline, re-synchronize the stored outline definition using the following procedure:
 

  SQL> execute dbms_outln_edit.refresh_private_outline('PRIV_OUTLN1');

 

  PL/SQL procedure successfully completed.

Note that the private outline name must match in case to ol$hints.ol_name 
 

Alternatively, you can use:
 

   SQL> alter system flush shared_pool or 

or

 SQL> create private outline priv_outln1 from private priv_outln1;
 

d. Set the parameter use_private_outlines, otherwise the query will still  use nested loops instead of not hash join.
 

  SQL> alter session set use_private_outlines=true;

 

  Session altered.
 

e. Test the current execution plan for the query, to confirm that Oracle  optimizer is now using hash join method for this query.

  SQL> set autotrace on explain

 

  SQL> select e.ename from emp e, dept d where e.deptno=d.deptno;

 

  ENAME

  ----------

  MILLER

  KING

  CLARK

  FORD

  ADAMS

  SCOTT

  JONES

  SMITH

  JAMES

  TURNER

  BLAKE

  MARTIN

  WARD

  ALLEN

   

  14 rows selected.

    

  Execution Plan

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

     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=41 Bytes=1

          353) 

     1    0   HASH JOIN (Cost=10 Card=41 Bytes=1353)

     2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=41 Bytes=820)

     3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=41 Bytes=533)

4. Publicize the edits by creating a public stored outline from your private outline.
 

If you want to preserve your edits for public use, then publicize the edits with the following statement:


 
SQL> create or replace outline outln2 from private priv_outln1;

 

Outline created.


The above text is an excerpt from:

Using Oracle SQL Stored Outlines & Optimizer Plan Stability
ISBN 0-9740716-8-4

by Mike Ault
 

http://www.rampant-books.com/ebook_plan_stability.htm


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.