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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Techniques for Extracting Significant SQL for Tuning
Oracle Tips by Burleson

Once we have tuned the initialization parameters and established a baseline optimizer_mode, we are ready to look into tuning individual SQL statements. Depending on our environment, we have choices about where to find this SQL, and once it is located, we have several options for observing the performance of the SQL inside the shared pool.

Identifying High-Use SQL Statements

There are many ways to locate SQL statements. At the highest level, you have the choice of locating the SQL source code in several places:

  • Application programs Some Oracle professionals know the location of their SQL source code and interrogate the source code libraries to extract the SQL source code.

  • The library cache The library cache within the SGA will store the SQL source code, and it also provides statistics about the number of executions. Most SQL tuning professionals will use the rpt_sql_cache.ksh and access.sql scripts for this purpose.

  • The stats$sql_summary table The STATSPACK stats$sql_summary table stores the source for all SQL statements that exceed the threshold values as defined in the stats$statspack_parameter table. The STATSPACK tables are useful because they keep a historical record of all of the important SQL. Most SQL tuning professionals will use the SQL top-10 script rpt_sql_STATSPACK.ksh for this purpose.

The details about executing these scripts were covered in Chapter 7. Since the goal is to locate high-use SQL statements, the library cache and the stats$sql_summary table are excellent places to begin your quest for offensive SQL. For details on using STATSPACK for SQL tuning, see Donald Keith Burleson, Oracle High-Performance Tuning with STATSPACK (McGraw-Hill Professional Publishing, 2001). For details on using ranking tools for extracting SQL, see Chapter 7.

Extraction Scripts for SQL Statements

There are several techniques for extracting and ranking SQL. Many people use third-party tools, but there are several SQL*Plus scripts that can be used to locate important SQL statements. Here are some of the common techniques:

  • Extract SQL from stats$sql_summary with the STATSPACK SQL top-10 report (rpt_sql_STATSPACK.ksh).

  • Extract SQL from the v$sqlarea view using rpt_sql_cache.sql.

  • Extract and analyze SQL from the library cache using access.sql.

Please note that any of these techniques can be used with either the historical STATSPACK stats$sql_summary table or the v$sqlarea view. The columns in v$sqlarea and stats$sql_summary are identical, so it is very easy to write scripts that extract SQL from both locations.

When ranking SQL statements, we recognize that it is more important to tune a frequently executed SQL statement for a small performance gain than it is to tune a seldom-executed SQL statement for a huge performance gain. In many cases, the Oracle professional does not have the time to locate and tune all of the SQL statements. It is also important to get an immediate measurable result, so that management will continue to fund the tuning effort. Hence, you must quickly locate those statements that are frequently executed and tune these statements first.

At some point you will encounter diminishing marginal returns for your tuning effort (Figure 9-3). After you have tuned the frequently executed SQL and move on to the less frequently executed SQL, you may find that the time and effort for tuning will not result in a cost-effective benefit.

Figure 3: The diminishing marginal returns for SQL tuning

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


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