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

 

 

   
   
 

Tuning SQL
Oracle Tips by Burleson
 

DML statements (DELETE, INSERT, SELECT, and UPDATE) are the most common cause of performance problems in stored PL/SQL objects. There are a number of potential reasons why a given DML statement could perform poorly, including:

  • Failure to use the proper indexes in a SELECT, UPDATE, or DELETE statement.

  • Number and/or types of indexes degrading the performance of an INSERT, UPDATE, or DELETE statement.

  • Statistics for the tables haven’t been updated recently (if using the cost-based optimizer).

  • Lack of indexes usable by the SELECT or UPDATE statement (i.e., the index should not be used by the DML statement).

  • The DML statement attempts to modify the value of an indexed column for a WHERE clause comparison.

Of course, there are other situations that can cause performance problems, but these are the most common reasons for DML statements to perform poorly.

In many instances, resolving a performance problem first requires that the performance bottleneck be identified. The EXPLAIN PLAN statement is an excellent tool for identifying SQL statements that perform poorly.

Using the EXPLAIN PLAN Statement

The EXPLAIN PLAN SQL statement is used to illustrate the steps that Oracle goes through to execute a specific DML statement. The use of the EXPLAIN PLAN statement is illustrated in Listing 10.1.

Listing 10.1 Using the EXPLAIN PLAN SQL statement.

EXPLAIN PLAN
SET statement_id = <statement_name>
INTO <plan_table>
FOR <SQL_statement>;

In this example, statement_name is a unique identifier for the SQL statement, plan_table is the name (possibly prefaced with a schema reference) of the table that holds the results (typically PLAN_TABLE), and SQL_statement is the SQL statement for which the EXPLAIN PLAN is being generated.

On a Unix system, the PLAN_TABLE table can be created by running the utlxplan.sql file from the $ORACLE_HOME/rdbms/admin directory.

This is the structure of the PLAN_TABLE table:

statement_id         varchar2 (30)
timestamp            date
remarks              varchar2 (80)
operation            varchar2 (30)
options              varchar2 (30)
object_node          varchar2 (30)
object_owner         varchar2 (30)
object_name          varchar2 (30)
object_instance      varchar2 (30)
object_type          varchar2 (30)
search_columns       number
id                   number
parent_id            number
position             number
other                long

The results of the EXPLAIN PLAN statement are written to this table and can be retrieved using the query in Listing 10.2.

Listing 10.2 Getting an EXPLAIN PLAN from the PLAN_TABLE table.

SELECT lpad (' ', 2 * (level - 1)) ||
       operation   || ' ' ||
       options     || ' ' ||
       object_name || ' ' ||
       decode (id, 0, 'Cost = ' || position) "EXPLAIN PLAN";
FROM   PLAN_TABLE
START WITH id = 0
AND   statement_id  = <statement_name>
CONNECT BY PRIOR id = parent_id
AND   statement_id  = <statement_name>;

To use the code in this example, replace statement_name with the same value that was used for statement_name when generating the EXPLAIN PLAN. The query produces output that looks like this:

EXPLAIN PLAN
------------------------------------------------------------------
SELECT STATEMENT   Cost = 13
  MERGE JOIN
    TABLE ACCESS FULL STUDENTS
    TABLE ACCESS BY ROWID STUDENTS
      INDEX UNIQUE SCAN STUDENTS_SSN

This output shows the series of operations performed by Oracle to resolve the statement and the total cost of those operations. High cost values are extremely undesirable.


TIP:  Using EXPLAIN PLAN With The Rule-Based Optimizer
Running an EXPLAIN
PLAN on a statement that uses the rule-based optimizer will always show a cost of zero. However, you can still use the statement to identify poorly performing SQL statements by examining the operations that Oracle performs to resolve the query.


While using EXPLAIN PLAN alone can isolate performance bottlenecks, using TKPROF and EXPLAIN PLAN together will provide even more insights into how SQL statements perform.

Using TKPROF

TKPROF is a utility provided by Oracle that provides detailed statistics about the execution of a DML statement. The first step involved with running TKPROF is setting up a trace file.

  
               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

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. 

Hit Counter