Ranking SQL Statements Oracle Tips by Burleson Consulting

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 7-1). After you have tuned the frequently executed SQL and moved 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 1: The diminishing marginal returns for SQL tuning

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.

• Library cache The library cache within the SGA will store the SQL source code and also provide statistics about the number of executions. Most SQL tuning professionals will use the rpt_sql_cache.ksh file and the access.sql script 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.

Let’s take a look at some tools that are used to identify high-impact SQL statements.

Using STATSPACK to Identify High-Impact SQL

Here is an easy-to-use Korn shell script that can be run against the STATSPACK tables to identify high-use SQL statements.

rpt_sql_STATSPACK.ksh

#!/bin/ksh
 
# First, we must set the environment . . . .
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|\

   grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

echo "How many days back to search?"
read days_back

echo executions
echo loads
echo parse_calls
echo disk_reads
echo buffer_gets
echo rows_processed
echo sorts
echo
echo "Enter sort key:"
read sortkey
 
$ORACLE_HOME/bin/sqlplus perfstat/perfstat<<!

 
set array 1;
set lines 80;
set wrap on;
set pages 999;
set echo off;
set feedback off;

column mydate      format a8
column exec        format 9,999,999
column loads       format 999,999
column parse       format 999,999
column reads       format 9,999,999
column gets        format 9,999,999
column rows_proc   format 9,999,999
column sorts       format 999,999

drop table temp1;
create table temp1 as
   select min(snap_id) min_snap
   from stats\$snapshot where snap_time > sysdate-$days_back;

drop table temp2;

create table temp2 as
select
   to_char(snap_time,'dd Mon HH24:mi:ss') mydate,
   executions                             exec,
   loads                                  loads,
   parse_calls                            parse,
   disk_reads                             reads,
   buffer_gets                            gets,
   rows_processed                         rows_proc,
   sorts                                  sorts,
   sql_text
from
   perfstat.stats\$sql_summary sql,
   perfstat.stats\$snapshot     sn
where
   sql.snap_id >
   (select min_snap from temp1)
and
   sql.snap_id = sn.snap_id
order by $sortkey desc
;
spool off;

select * from temp2 where rownum < 11;

exit
!

Here is the listing from running this valuable script. Note that the Remote DBA is prompted as to how many days back to search, and for the sort key for extracting the SQL.

rpt_sql_STATSPACK.ksh Execution Listing

It is interesting to note in the preceding output that we see the STATSPACK insert statement for the stats$sql_summary table.

Next, let’s look at a technique that is probably the most valuable script in this book.

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