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


 

Moving OUTLINES from One DB to Another
 

A user may want to copy OUTLINEs from one database to another, for example, to copy the outlines of an application from a test database to a production database. This can easily be done utilizing Oracle’s export and import routines.
 

Scenario
 

Once the optimization for an application is achieved in a test database, you can move the outlines created and stored for the application in a production database. Instead of recreating these outlines in the production database using the CREATE OUTLINE ...  FOR CATEGORY command for each optimized query of the application, export the outlines for the specified category from the test database, and then import these in the production database.

Technique

  1. The outlines for all categories are inserted in the OL$ and OL$HINTS tables owned by OUTLN schema.
     

  2. The OUTLN schema, OL$ and OL$HINTS tables are created during the database creation while sql.bsq is executed:

   create user outln identified by outln

   /

   grant connect to outln

   /

   grant resource to outln

   /

   grant execute any procedure to outln

   /

   create table outln.ol$

   (

    ol_name            varchar2(30),                /* named is potentially generated */

    sql_text          long,                     /* the SQL stmt being outlined */

    textlen           number,                                          /* length of SQL stmt */

    signature           raw(16),                                    /* signature of sql_text */

    hash_value        number,                         /* KGL's calculated hash value */

    category          varchar2(30),                              /* category name */

    version           varchar2(64),        /* db version @ outline creation */

    creator           varchar2(30),       /* user from whom outline created */

    timestamp         date,                                 /* time of creation */

    flags             number,             /* e.g. everUsed, bindVars, dynSql */

    hintcount           number                       /* number of hints on the outline */

   )

   /

   create table outln.ol$hints

   (

    ol_name            varchar2(30),                              /* outline name */

    hint#             number,                          /* which hint for a given outline */

    category            varchar2(30),          /* collection/grouping name */

    hint_type         number,                                                  /* type of hint */

    hint_text         varchar2(512),        /* hint specific information  */

    stage#              number,               /* stage of hint generation/applic'n */

    node#                           number,                                     /* QBC node id */

    table_name        varchar2(30),                         /* for ORDERED hint */

    table_tin            number,                                   /* table instance number */

    table_pos         number                                         /* for ORDERED hint  */

   )

   /

   create unique index outln.ol$name on outln.ol$(ol_name)

   /

   create unique index outln.ol$signature on outln.ol$(signature,category)

   /

   create unique index outln.ol$hnt_num on outln.ol$hints(ol_name, hint#) 

   /

 

  1. Export the data from the OUTLN.OL$ and OUTLN.OL$HINTS from the test database, selecting the appropriate category for the required  application with the new 8i QUERY export parameter.

  2. In the production database, it is recommended to store all stored outlines in a separate tablespace. If this is not set yet, create a new tablespace, and set this tablespace as the default one for the OUTLN schema user.  Drop the OL$ and OL$HINTS tables so that the import  recreates them in the appropriate tablespace.

  3. Import the OUTLN.OL$ and OUTLN.OL$HINTS tables and/or rows only depending on the status of the previous step in the production database.


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.