BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Managing Stored Outlines

Oracle Tips by Burleson Consulting

Because the stored outline feature is new with Oracle8i, there are only a few rudimentary views and procedures to aid in the management of SQL stored outlines. As Oracle continues to enhance the functionality of optimizer plan stability, these tools will grow more robust.

Oracle provides two dictionary structures to aid in stored outline management, the Remote DBA_OUTLINES view and the ol$hints table. Oracle also provides the outline package to aid in the categorization and management of stored outlines.

Using the Dictionary Views and Tables for Stored Outlines

Oracle provides several views to help display stored outlines, most notably the Remote DBA_OUTLINES view and the ol$hints table. Together, these structures will tell you everything you need to know about the status of stored outlines in your database. Here are the columns in the Remote DBA_OUTLINES view:

SQL> desc Remote DBA_outlines;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 USED                                               VARCHAR2(9)
 TIMESTAMP                                          DATE
 VERSION                                            VARCHAR2(64)
 SQL_TEXT                                           LONG

We also have a Remote DBA_OUTLINE_HINTS view, but it does not contain as much useful information as ol$hints, and it is seldom used because it lacks detail about the sequence of the stored outline steps.

SQL> desc Remote DBA_outline_hints;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 NODE                                               NUMBER
 STAGE                                              NUMBER
 JOIN_POS                                           NUMBER
 HINT                                               VARCHAR2(512)

To see the stored execution plans for a SQL statement, you must reference the outln.ol$hints view. Note that we created a public synonym for ol$hints to make it easily accessible by all users:

SQL> create public synonym ol$hints for outln.ol$hints;
Synonym created.

SQL> desc ol$hints;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 OL_NAME                                            VARCHAR2(30)
 HINT#                                              NUMBER
 CATEGORY                                           VARCHAR2(30)
 HINT_TYPE                                          NUMBER
 HINT_TEXT                                          VARCHAR2(512)
 STAGE#                                             NUMBER
 NODE#                                              NUMBER
 TABLE_NAME                                         VARCHAR2(30)
 TABLE_TIN                                          NUMBER
 TABLE_POS                                          NUMBER

Once a stored outline is used, the used column in Remote DBA_OUTLINES will change. Here we execute the original query and check to see if the stored outline was used:

SQL> select * from Remote DBA_outlines where name = ‘CBO_SQL’;

NAME                           OWNER
------------------------------ ------------------------------
CATEGORY                       USED      TIMESTAMP
------------------------------ --------- ---------
VERSION
----------------------------------------------------------------
SQL_TEXT
---------------------------------------------------------------
CBO_SQL                        OPS$ORACLE
DEFAULT                        USED      14-APR-01
8.1.6.1.0
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+)

Here we can run a query to see our automatically generated outlines, as evidenced by their names in the for SYS_OUTLINE-nnn.

SQL> select * from Remote DBA_outlines
  2  where name like ‘SYS_OUTLINE%’;

NAME                           OWNER
------------------------------ ------------------------------
CATEGORY                       USED      TIMESTAMP
------------------------------ --------- ---------
VERSION
----------------------------------------------------------------
SQL_TEXT
---------------------------------------------------------------------
SYS_OUTLINE_0104142013050001   READER
DEFAULT                        UNUSED    14-APR-01
8.1.6.1.0
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P

SYS_OUTLINE_0104142013050002   READER
DEFAULT                        UNUSED    14-APR-01
8.1.6.1.0
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE


SYS_OUTLINE_0104142013050003   READER
DEFAULT                        UNUSED    14-APR-01
8.1.6.1.0
SELECT DECODE('A','A','1','2') FROM DUAL

SYS_OUTLINE_0104142013270004   READER
DEFAULT                        USED      14-APR-01
8.1.6.1.0
select nbr_pages from book

Next, let’s look at how the outline package is used to help us manage our stored outlines.

Using the Outline Package

The outline package contains several procedures that can help us manage our stored outlines. These procedures allow us to drop, categorize, and manage all stored outlines. The outline package contains the following functions and stored procedures.

  • Procedures drop_collision, drop_extras, drop_unrefd_hints, drop_unused, update_by_cat

  • Functions drop_collision_expact, drop_extras_expact, drop_unrefd_hints_expact

Let's begin by looking at a procedure to identify and drop unused stored outlines.

Identify and Drop Unused Stored Outlines

One of the problems with systems that generate SQL with literal values is that there will be a huge number of nonreusable SQL statements. In these cases, cursor_sharing=force should be set to rewrite the SQL to replace the literals with host variables, thereby making the SQL reusable.

For example, cursor_sharing will transform this statement, removing the literal and replacing it with a host variable:

select * from customer where cust_name = ‘JONES’;

into a reusable form:

select * from customer where cust_name = :var1;

Oracle provides a procedure for dropping stored outlines that are not reused. Let’s begin by running a query to see if our database has SQL statements that have never been reused.

SQL> set long 1000
SQL> select * from Remote DBA_outlines where used='UNUSED';

NAME         OWNER  CATEGORY USED   TIMESTAMP VERSION    SQL_TEXT
------------ ------ ---------- --------- ---------- -------------
TEST_OUTLINE SYSTEM TEST UNUSED 08-MAY-99 8.1.3.0.0 
                                             select a.table_name,
                                               b.tablespace_name,
                                                 c.file_name from
                                                    Remote DBA_tables a,
                                               Remote DBA_tablespaces b,
                                                 Remote DBA_data_files c
                                                  where
                                              a.tablespace_name =
                                                b.tablespace_name
                                            and b.tablespace_name
                                              = c.tablespace_name
                                                  and c.file_id =
                                                        (select
                                              min(d.file_id) from
                                                 Remote DBA_data_files d
                                                         where
                                              c.tablespace_name =
                                               d.tablespace_name)

Now, we are ready to drop any stored outlines that have not been used. We do this by running the drop_unused procedure in the outline package.

SQL> execute outline_pkg.drop_unused;

PL/SQL procedure successfully completed.

SQL> select * from Remote DBA_outlines where used='UNUSED';

no rows selected

Remember, there is no recovery for this procedure, so you should always make sure that you no longer want the unused stored outlines before running drop_unused.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter