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

 

 


 

 

 

 

 

 

 

Understanding Oracle SQL Utilities

Oracle Tips by Burleson Consulting

This chapter is dedicated to looking at the tools that are used to extract the execution details for a SQL statement. While almost a dozen third-party tools can be used to deliver SQL execution information, Oracle provides a wealth of free tools and utilities for this purpose. The topics in this chapter will include:

  • The explain plan utility

  • Running a fast SQL trace

  • Running TKPROF to get a SQL trace report

  • Oracle’s Center of Expertise (COE) SQL execution report

  • Reporting on all SQL in the library cache

These reports provide a common basis for all SQL tuning activities, since they are all free of charge and readily available. Let’s begin with the most basic tool of SQL tuning, the explain plan utility.

Explaining a SQL Statement

As I briefly mentioned in Chapter 7, to see the explain plan for a SQL statement, you must first create a plan table in your schema. 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. The syntax for the explain plan utility is as follows:

explain plan
   set statement_id = ‘<your ID>’
   Into table <table name>
for
   <SQL statement>
;

To run an explain plan, we start by lifting a SQL statement from the stats$sql_summary table. I will show you the details for extracting the SQL in the next section. Here is the statement that we suspect is not optimized because it takes more than 11 minutes to execute. It is not important that we understand the purpose of this SQL, only that we note the basic structure of the statement.

SELECT B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,
B.GLOSSARY_NBR,B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,
B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_RATING,S.START_VISUAL_PAGE_NBR,
S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(S.END_DATE,'DD-MON-YYYYHH24:MI:SS'),
S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,
TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYYHH24:MI:SS'),
S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,
S.TRIAL_SUBSC_FLAG  
FROM
   BOOK B,
   SUBSCRIPTION S,
   PAGE P 
WHERE
(S.USER_UNIQUE_ID = :b1  AND S.ISBN = B.ISBN  AND S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID  AND S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR )
ORDER BY B.BOOK_TITLE;

It is always a good idea to get a visual “pattern” for the SQL statement before you get the execution plan. The preceding statement can be simplified into the following structure:

select
   stuff
from
   book,
   subscription,
   page
where
   user = :var
and
   subscription isbn = book isbn
and
   subscription book_id – page book_id
and
   subscription last_page_nbr_viewed = page page_nbr

Here we see a simple three-way table join where the result set is limited for a single user. Now that you understand the basic structure of the query, we can get the execution plan for this SQL statement by inserting the SQL into the following snippet:

delete from plan_table where statement_id = 'test1';

explain plan set statement_id = 'test1'
for
SELECT B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,
B.GLOSSARY_NBR,B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,
B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_RATING,S.START_VISUAL_PAGE_NBR,
S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(S.END_DATE,'DD-MON-YYYYHH24:MI:SS'),
S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,
TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYYH24:MI:SS'),
S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,S.TRIAL_SUBSC_FLAG  
FROM
   BOOK B,
   SUBSCRIPTION S,
   PAGE P 

WHERE
(S.USER_UNIQUE_ID = :b1  AND S.ISBN = B.ISBN  AND S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID  AND S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR )
ORDER BY B.BOOK_TITLE;

When you execute this code, you instruct Oracle to display the execution plan inside the plan table. To display the data inside the plan table, you can use the following script.

plan.sql

SET PAGES 9999;
SELECT  lpad(' ',2*(level-1))||operation operation,
        options,
        object_name,
        position
FROM plan_table
START WITH id=0
AND
statement_id = 'test1'
CONNECT BY prior id = parent_id
AND
statement_id = 'test1';

Here is the output from plan.sql. This display is known as the execution plan for the SQL statement. It describes in detail all of the access steps that are used to retrieve the requested rows.

OPTIONS                        OBJECT_NAME                  POSITION
------------------------------ -------------------------- ----------
SELECT STATEMENT
  SORT
ORDER BY                                                           1

    NESTED LOOPS
                                                                   1
      NESTED LOOPS
                                                                   1
        TABLE ACCESS
FULL                           PAGE                                1
        TABLE ACCESS
BY INDEX ROWID                 SUBSCRIPTION                        2
          INDEX
RANGE SCAN                     SUBSC_ISBN_USER_IDX                 1
      TABLE ACCESS
BY INDEX ROWID                 BOOK                                2
        INDEX

UNIQUE SCAN                    BOOK_ISBN                           1

In this listing, we see the TABLE ACCESS FULL PAGE. This is the dreaded full-table scan that causes excessive overhead for Oracle. The next question is whether this query needs to access all of the rows in the page table. To find out, let’s look at the where clause for the query:

WHERE
   S.USER_UNIQUE_ID = :b1 
AND
   S.ISBN = B.ISBN 
AND
   S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID 
AND
   S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR

Here we see that the only WHERE condition that applies to the page table is:

   S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR

It then follows that Oracle should be able to retrieve the page rows by using an index on the page_seq_nbr column of the page table and there is no need to perform a time-consuming full-table scan.

This statement was extracted from a database where optimizer_mode=RULE, so the first thing we can try is to analyze all of the tables and indexes in the query and reexplain the query with a FIRST_ROWS hint:

Analyze table page estimate statistics sample 5000 rows.
Analyze table book estimate statistics sample 5000 rows.
Analyze table subscription estimate statistics sample 5000 rows.
Analyze index isbn_seq_idx compute statistics;
Analyze index subsc_pub_name_idx compute statistics;

Here is the original explain with the FIRST_ROWS hint:

delete from plan_table where statement_id = 'test1';

explain plan set statement_id = 'test1'
for
SELECT /*+ first_rows */
B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,B.GLOSSARY_NBR,
B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_
RATING,S.START_VISUAL_PAGE_NBR,S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),TO_CHAR(S.END_DATE,'DD-MON-YYYY HH24:MI:SS'),S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYY HH24:MI:SS'),S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,S.TRIAL_SUBSC_FLAG   FROM BOOK B,SUBSCRIPTION S,PAGE P  WHERE (S.USER_UNIQUE_ID = :b1  AND S.ISBN = B.ISBN  AND S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID  AND S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR )ORDER BY B.BOOK_TITLE;

@plan

Now, when we run plan.sql, we see a totally different execution plan without any full-table scans:

OPERATION                                                              
------------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                  POSITION       
------------------------------ -------------------------- ----------       
SELECT STATEMENT                                                               
                                                                  27       
  SORT                                                                          
ORDER BY                                                           1       
    NESTED LOOPS                                                               
                                                                   1       
      NESTED LOOPS                                                             
                                                                   1       
        TABLE ACCESS                                                            
BY INDEX ROWID                 SUBSCRIPTION                        1       
          BITMAP CONVERSION                                                    
TO ROWIDS                                                          1       
            BITMAP INDEX                                                       
FULL SCAN                      SUBSC_PUB_NAME_IDX                  1       
        TABLE ACCESS                                                           

BY INDEX ROWID                 BOOK                                2       
          INDEX                                                            
UNIQUE SCAN                    BOOK_ISBN                           1       
      TABLE ACCESS                                                              
BY INDEX ROWID                 PAGE                                2       
        INDEX                                                                  
UNIQUE SCAN                    ISBN_SEQ_IDX                        1       

When we reexecute the SQL in SQL*Plus with set timing on, the whole query executes in 18 seconds, for a savings of more than 10 minutes! This is just a simple example of the dramatic improvements you can make by tuning your SQL statements.  Also note the use of the bitmap index in this execution plan.

Note: A host of third-party tools in the market show the execution plan for SQL statements. The most common way of determining the execution plan for a SQL statement is by using Oracle’s explain plan utility. By using explain plan, the Oracle Remote DBA can ask Oracle to parse the statement, and display the execution class path without actually executing the SQL statement.

Now that we have covered the extraction and explaining of the SQL statement, let’s go into more detail on getting execution details for SQL statements.


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