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

 

 


 

 

 

 

 

 

 

Step 2: Extract and Explain the SQL Statement

Oracle Tips by Burleson Consulting

As each SQL statement is identified, it will be “explained” to determine its existing execution plan and then tuned to see if the execution plan can be improved.

Explaining a SQL Statement

To see the output of an explain plan, you must first create a plan table in your schema. While we will review this in detail in Chapter 8, let’s take a quick tour. Oracle provides the syntax to create a plan table in $ORACLE_HOME/rdbms/admin/utlxplan.sql. The listing that follows executes utlxplan.sql to create a plan table and then creates a public synonym for the plan table.

sql> @$ORACLE_HOME/rdbms/admin/utlxplan
Table created.

sql> create public synonym plan_table for sys.plan_table;
Synonym created.

Once the plan table is created, you are ready to populate the plan table with the execution plan for SQL statements.

We start by lifting a SQL statement from the stats$sql_summary table or from the library cache. I will show you the details for extracting the SQL in the next section. Here is a sample SQL statement that we have changed to add the explain plan statement:

delete from plan_table;

select ename from emp
where
reverse(ename) like 'GNI%';

explain plan
   set statement_id = 'test3'
for
select ename from emp
where
reverse(ename) like 'GNI%';

@plan

Note that we use the plan.sql script to display the execution plan.

plan.sql

rem plan.sql - displays contents of the explain plan table
set pages 9999;
select  lpad(' ',2*(level-1))||operation operation,
                options,
                object_name,
                position,
                other_tag
from plan_table
start with id=0
and
statement_id = 'test3'
connect by prior id = parent_id
and
statement_id = 'test3';

Now, let’s see what happens when we execute this listing:

SQL> @exp
2 rows deleted.


ENAME
----------
KING


Explained.

  1  explain plan
  2     set statement_id = 'test3'
  3  for
  4  select ename from emp
  5  where
  6* reverse(ename) like 'GNI%'

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     1

  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1

    INDEX
RANGE SCAN                     ENAME_REVERSE_IDX                     1

Now that you see how the execution plan will change, let’s turn our attention to the process of tuning a SQL statement.

Step 3: Tune the SQL Statement

For those SQL statements that possess a suboptimal execution plan, the SQL will be tuned by one of the following methods:

  • Hints  Adding SQL hints will modify the execution plan.

  • Index  Adding B-tree indexes can remove full-table scans.

  • Re-write  Rewriting the SQL can change the execution plan, especially when changing the table order in the from clause with the RBO.

  • Bitmap Indexes  Adding bitmapped indexes allows you to index all low-cardinality columns that are mentioned in the WHERE clause of the query.

  • PL/SQL  Rewriting the SQL in PL/SQL can often improve performance. For certain queries, this can result in more than a twenty-fold performance improvement. The SQL would be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.

By far the most common approach is to add indexes and hints to the query. While we can instantly see the execution plan change as we add indexes and change hints, it is not always immediately evident which execution plan will result in the best performance.

Hence, the Remote DBA will normally take the three most promising execution plans and actually execute the statement in SQL*Plus, noting the total elapsed time for the query by using the SQL*Plus set timing on command.

The details of all of the SQL hints are way beyond the scope of this book, but you can get details on all of the hints in the forthcoming Oracle Press book Oracle High-Performance SQL Tuning (October 2001), by Don Burleson.


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