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 DBMS_OUTLN_EDIT.GENERATE_SIGNATURE
 

The GENERATE_SIGNATURE procedure is mistakenly attributed to the OUTLN_PKG in the Oracle documentation set, in actuality it is located in the OUTLN_EDIT_PKG which is synonymed to be DBMS_OUTLN_EDIT. Passing the GENERATE_SIGNATURE procedure a SQL statement will return a RAW signature of the SQL which can then be used to probe the outline tables to see if that SQL has an existing OUTLINE.
 

When using GENERATE_SIGNATURE, it is probably easiest to create an anonymous PL/SQL script to return the signature or the SQL and OUTLINE name to you. The signature is a RAW, so in the example, shown below, we return the outline name.

 

SQL> set serveroutput on

 

SQL> declare

2  sql_text varchar2(200);

3  ret_sql_text varchar2(200);

4  ret_outline_name varchar2(32);

5  gen_sig raw(16);

6  begin

7   sql_text := 'select owner, table_name, tablespace_name

8     from

9     dba_tables

10    where owner not in '

11  ('||chr(39)||'SYS'||chr(39)||','||chr(39)||'SYSTEM'||chr(39)||')';

12   dbms_outln_edit.generate_signature(sql_text,gen_sig);

13   select a.sql_text, a.name into ret_sql_text, ret_outline_name

14   from dba_outlines a where signature=gen_sig;

15   dbms_output.put_line('Sql text for '||name||': '||ret_sql_text);

16  end;

17* /

 

Sql text for TEST1: select owner,table_name,tablespace_name

from dba_tables

where

owner not in ('SYS','SYSTEM')                                                  

 

PL/SQL procedure successfully completed.
 

Once you have identified the SQL statement for which you want to edit the hint structure, you use the DBMS_OUTLN_EDIT procedure to perform the edits as shown above.


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.