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

 

 


 

 

 

 

 

 

 

An Actual Case-Study in SQL Tuning

Oracle Tips by Burleson Consulting

The first activity of most SQL tuning sessions is to identify and remove unnecessary full-table scans. This SQL tuning activity can make a huge difference in SQL performance, since unnecessary full-table scans can take 20 times longer than using an index to service the query. Again, here are the basic steps in locating and fixing full-table scans:

1.      Run the full-table scan report to locate SQL statements that produce full-table scans.

2.      Query the v$sqltext or v$sqlarea view to locate the individual SQL statements.

3.      Explain the statement to see the execution plan.

4.      Add indexes or hints to remove the full-table scan.

5.      Change the SQL source or store the outline to make the change permanent.

Let’s quickly step through these activities and see how easy it is to improve the performance of SQL statements.

Get the Full-Table Scan Report

First, we run the access.sql script to extract and explain all of the SQL in the library cache. Here is a sample from an actual report:

Mon Jan 29                                                    page    1

                          full table scans and counts
                   Note that "C" indicates the table is cached.
                 “K” indicates that the table is in the KEEP pool

OWNER          NAME                    NUM_ROWS C K   BLOCKS  NBR_FTS
-------------- ------------------- ------------ - - -------- --------
SYS            DUAL                             N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PR               N  K       2   16,178
DONALD         PAGE                   3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                     434 N          8    7,355
DONALD         PAGE_IMAGE                18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                 476 N   K    192    2,087
DONALD         PRINT_PAGE_RANGE              10 N   K     32      874
ARSD           JANET_BOOKS                   20 N          8       64
PERFSTAT       STATS$TAB_STATS                  N         65       10

Here we see a clear problem with large-table full-table scans against the page_image table. The page_image table has 18,067 rows and consumes 1,104 blocks. The report shows 5,368 full-table scans against this table. Next, we can run a quick query to display the SQL source from v$sqlarea for the page_image table, looking for a SQL statement that has been executed about 5,000 times:

get_sql.sql

set lines 2000;

select
   sql_text,
   disk_reads,
   executions,
   parse_calls
from
   v$sqlarea
where
   lower(sql_text) like '%page_image%'
and
   executions > 5000
order by
   disk_reads desc
;

In the result from this query, we will look for SQL statements whose values for executions (5,201) approximate the value in the full-table scan report (5,368).

From the output, you can clearly see the offensive SQL statement:

SELECT IMAGE_BLOB   FROM PAGE_IMAGE  WHERE (UPPER(BOOK_UNIQUE_ID) = :b1  AND PAGE_SEQ_NBR = :b2  AND IMAGE_KEY = :b3 )

833       5201         148

Now that we have the SQL, we can quickly explain it and verify the full-table scan:

delete from plan_table where statement_id = 'test1';

explain plan set statement_id = 'test1'
for
SELECT IMAGE_BLOB   FROM PAGE_IMAGE 
WHERE
   (UPPER(BOOK_UNIQUE_ID) = :b1 
AND
   PAGE_SEQ_NBR = :b2  AND IMAGE_KEY = :b3 )
;

Here we run the execution plan showing our full-table scan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                   168

  TABLE ACCESS
FULL                           PAGE_IMAGE                            1

Since this is a very simple query against a single table, we can look directly at the where clause to see the problem. The only condition in the where clause references upper(book_unique_id), and the Oracle optimizer has not detected a usable index on this column. Since we are on Oracle8, we can create a function-based index using the upper function:

create unique index book_seq_image_idx
  on page_image
    (upper(book_unique_id),
     page_seq_nbr,
     image_key)
  tablespace bookx
  pctfree 10
  storage (initial 128k next 128k maxextents 2147483645 pctincrease 0);

Now we rerun the execution plan and see that the full-table scan is replaced by an index scan:

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


  TABLE ACCESS
BY INDEX ROWID                 PAGE_IMAGE                            1

    INDEX
UNIQUE SCAN                    BOOK_SEQ_IMAGE_IDX                    1

Problem solved! The query went from an original execution time of 13 minutes to less than 10 seconds.

Now that you've seen the iterative process of locating and tuning SQL statements, let’s look at how third-party GUI tools can speed up the process. This can be very important when the Remote DBA must tune hundreds of SQL statements.

Fast SQL Tuning with Third-Party Tools

Now that you've seen how to tune SQL statements, let’s look at how GUI tools can aid in quickly extracting and tuning SQL statements. In these examples, we will use the Q Diagnostic Center by Precise Software. With Q, the Remote DBA is presented with the list of SQL from v$sqlarea, and the Remote DBA just double-clicks the SQL statement to move the SQL into the Tuning window, as shown in Figure 7-4.

Figure 4: The Q Diagnostic Center Tuning window

From this screen, you can double-click again and see details about the execution plan for the query. This display is especially handy because the screen also shows us the data dictionary details for each table in the query, as shown in Figure 7-5.

Figure 5: The Execution Plan screen

With this screen, the Remote DBA can instantly see everything he or she needs to know about the tables and indexes, including the numbers of rows, blocks, and parallel options for each table in the query.


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