Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

SQL Execution Plans


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

When an SQL statement is passed to Oracle, the cost based optimizer (CBO) uses database statistics to create an execution plan which it uses to retrieve the data in the most efficient matter. 

The previous section explained how the tkprof utility was able to produce execution plans for traced SQL statements in addition to formatting the trace output.  An alternative approach is to use the explain plan statement that produces the likely execution plan without having to execute the statement.  This is especially useful for long-running queries in which executing the statement may result in an unacceptable delay to the tuning process.  Also, the execution plans displayed in the tkprof output are actually derived using this method.

The following examples query tables within the SCOTT schema.  If it is not already present, it can be created using the utlsampl.sql script found in the $ORACLE_HOME/rdbms/admin directory.

Before proceeding, the plan_table table must be created to hold the execution plan data.

plan_table

The execution plan information is stored in a table. For any of the following examples to work, this table must be created using the utlxplan.sql script.  This script can be created in the current schema or in a shared schema.  The following example shows how it is created in the SYS schema and made available to all users.

SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> GRANT ALL ON sys.plan_table TO public;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

Synonym created.

With the plan table present, execution plans using the autotrace feature of SQL*Plus can then be generated.

autotrace

The simplest way to get an execution plan is to use the autotrace feature of SQL*Plus.  By switching on this feature, an execution plan is displayed for all suitable statement runes by SQL*Plus, as shown below.

SQL> SET AUTOTRACE ON
SQL> SELECT *
  2  FROM   emp e, dept d
  3  WHERE  e.deptno = d.deptno
  4  AND    e.ename  = 'SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20         20 RESEARCH       DALLAS

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics
----------------------------------------------------------
         81  recursive calls
          4  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This is a relatively easy way to get the execution plan, but it requires that the statement must run to completion.  If the query is particularly inefficient or returns many rows, this may take considerable time.  A better option is to run the explain plan statement manually, which is shown in the next section.

Explain Plan

The explain plan statement generates the execution plan for a query  without executing the query itself, allowing the execution plan for poorly performing queries to be displayed without impacting the database.  The following example shows how the explain plan statement is used to generate an execution plan.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

If multiple people are accessing the same plan table, or a history of the execution plans is to be saved, the statement_id clause of the explain plan statement should be used.  This associates a user specified identifier with each plan, which can be used when retrieving the data.  The following example shows how the statement_id is set using the explain plan statement.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

At this point the execution plan data is present in the plan_table, which can be queried using several methods to display the execution plan.

utlxpls.sql

Prior to Oracle 9i Release 2, the utlxpls.sql script or the utlxplp.sql script for parallel queries was used to query the plan_table and display execution plan.  The example below shows the expected output from the script.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

Plan Table
-------------------------------------------------------------------------------
| Operation                 |  Name   |  Rows | Bytes|  Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
| SELECT STATEMENT          |         |       |      |        |       |       |
|  NESTED LOOPS             |         |       |      |        |       |       |
|   TABLE ACCESS FULL       |EMP      |       |      |        |       |       |
|   TABLE ACCESS BY INDEX RO|DEPT     |       |      |        |       |       |
|    INDEX UNIQUE SCAN      |PK_DEPT  |       |      |        |       |       |
-------------------------------------------------------------------------------

8 rows selected.

SQL>

By default, the Oracle scripts do not accept a statement_id parameter, but they can be modified to create a personal script, like the explain.sql script shown below.

explain.sql

-- *****************************************************************
-- Parameters:
--   1) Statement ID
-- *****************************************************************

SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF

COLUMN plan             FORMAT A50
COLUMN object_name      FORMAT A30
COLUMN object_type      FORMAT A15
COLUMN bytes            FORMAT 9999999999
COLUMN cost             FORMAT 9999999
COLUMN partition_start  FORMAT A20
COLUMN partition_stop   FORMAT A20

SELECT LPAD(' ', 2 * (level - 1)) ||
       DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
       INITCAP(pt.operation) ||
       DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
       pt.object_name,
       pt.object_type,
       pt.bytes,
       pt.cost,
       pt.partition_start,
       pt.partition_stop
FROM   plan_table pt
START WITH pt.id = 0
  AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
  AND pt.statement_id = '&1';

The following example shows the output from the explain.sql script.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> @explain.sql TIM

PLAN                                               OBJECT_NAME                    OBJECT_TYPE           BYTES     COST PARTITION_START      PARTITION_STOP
-------------------------------------------------- ------------------------------ --------------- ----------- ----Select Statement                                                                                           57        4

  1.1 Nested Loops                                                      
57        4
    2.1 Table Access (Full)                        EMP                           
TABLE                    37        3
    2.2 Table Access (By Index Rowid)              DEPT                          
TABLE                    20        1
      3.1 Index (Unique Scan)                      PK_DEPT                       
INDEX (UNIQUE)                     0

5 rows selected.

The utlxpls.sql script is still present in later versions of Oracle, but it now displays the execution plan using the dbms_xplan package.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 
��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.