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

 

 


 

 

 

 

 

 

 

How to Create and Modify a Stored Outline

Oracle Tips by Burleson Consulting

The best way to show how to use stored outlines to change the execution plan for a SQL statement is to illustrate the procedure with a simple example. Letís start with a simple query to display the sum of all salaries for each department.

select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

Here is the output from this query:

DNAME          LOC             SUM(SAL)
-------------- ------------- ----------
ACCOUNTING     NEW YORK            8750

Find the Fastest Execution Plan

Now letís take a look at the execution plan for this query. Note that it uses all_rows optimization and does a full-table scan even though there is an index on the emp table:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     5
  SORT
GROUP BY                                                             1
    NESTED LOOPS
OUTER                                                                1
      TABLE ACCESS
BY INDEX ROWID                 DEPT                                  1
        INDEX
RANGE SCAN                     DEPT_DEPT                             1
      TABLE ACCESS
FULL                           EMP                                   2

Of course, this query will run faster if we direct the SQL to use the emp index to access the emp rows. When we reexecute the query with a rule hint, we see the full-table scan disappear.

select /*+ rule */
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

Letís assume that we have done a timing of this query, and it is faster with rule-based optimization because it uses the dept_dept index instead of a full-table scan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
  SORT
GROUP BY                                                             1
    NESTED LOOPS
OUTER                                                                1
      TABLE ACCESS
BY INDEX ROWID                 DEPT                                  1
        INDEX
RANGE SCAN                     DEPT_DEPT                             1
      TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
        INDEX
RANGE SCAN                     DEPT_EMP                              1

If we assume that the emp and dept tables are very large, then changing the SQL to use the index will improve the performance of the query. Of course, you should always verify your hint by reexecuting the SQL with the SQL*Plus set timing on command to ensure that the hint improves performance. Now letís create our first stored outline.

Create the Stored Outline for the Original Query

Outlines are created using the CREATE OUTLINE command. The syntax for this command is:

CREATE [OR REPLACE] OUTLINE
   outline_name
[FOR CATEGORY category_name]
ON
   sql_statement;

Where:

  • outline_name This is a unique name for the outline. Automatic outlines are stored as SYS_OUTLINE-nnn, where nnn is a large unique number.

  • [FOR CATEGORY category_name] This optional clause allows more than one outline to be associated with a single query by specifying multiple categories, each named uniquely.

  • ON sql_statement This specifies the SQL statement for which the outline is prepared.

To illustrate the syntax, here we create a stored outline for the original SQL.

create or replace outline
   cbo_sql
on
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

The query to display stored outline plans is very different in format than the explain plan utility. Here is the code to display the execution plan inside the ol$hints table.

outline.sql

set echo off;
set verify off;
set feedback off;

column hint format a40;

select
   sql_text
from
   Remote DBA_outlines
where
   name = upper('&1');

select distinct
   lpad(' ',2*(level-1))||hint_text hint,
   hint#,
   table_tin,

   stage#
from
   outln.ol$hints
start with
   hint#=1
connect by prior
   hint# = hint#-1
and
   ol_name = upper('&1')
order by
   stage#,
   hint#
;

Now when we query the stored outline, we see a very different form of the execution plan:

SQL> @outline cbo_sql

SQL_TEXT
----------------------------------------------------------------
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+)


HINT                                     HINT#  TABLE_TIN     STAGE#
----------------------------------- ---------- ---------- ----------
                  NOREWRITE                 10          0          1
                NOREWRITE                    9          0          2

NO_EXPAND                                    1          0          3
  PQ_DISTRIBUTE(EMP NONE NONE)               2          1          3
    USE_NL(EMP)                              3          1          3
      ORDERED                                4          0          3
        NO_FACT(EMP)                         5          1          3
          NO_FACT(DEPT)                      6          2          3
            FULL(EMP)                        7          1          3
              FULL(DEPT)                     8          2          3


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