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

 

 


 

 

 

 

 

 

 

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.

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).

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

How many days back to search?
7
executions
loads
parse_calls
disk_reads
buffer_gets
rows_processed
sorts

Enter sort key:
disk_reads

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Dec 14 09:14:46 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production

MYDATE         EXEC    LOADS    PARSE      READS       GETS  ROWS_PROC    SORTS
-------- ---------- -------- -------- ---------- ---------- ---------- -----SQL_TEXT
----------------------------------------------------------------------------11 Dec 1        866        1      866    246,877  2,795,211        865        4:00:09
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

11 Dec 1        863        1      863    245,768  2,784,834        862        1:00:29

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELS
E :b := 0; END IF; END;

11 Dec 1        866        1      866    245,325    597,647    129,993      866
4:00:09
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID,DBID,INSTANCE_NUMBER,SQL_TEXT,SHARABLE_M
EM,SORTS,MODULE,LOADED_VERSIONS,EXECUTIONS,LOADS,INVALIDATIONS,PARSE_CALLS,DISK_
READS,BUFFER_GETS,ROWS_PROCESSED,ADDRESS,HASH_VALUE,VERSION_COUNT )  SELECT MIN(
:b1),MIN(:b2),MIN(:b3),MIN(SQL_TEXT),SUM(SHARABLE_MEM),SUM(SORTS),MIN(MODULE),SU
M(LOADED_VERSIONS),SUM(EXECUTIONS),SUM(LOADS),SUM(INVALIDATIONS),SUM(PARSE_CALLS
),SUM(DISK_READS),SUM(BUFFER_GETS),SUM(ROWS_PROCESSED),ADDRESS,HASH_VALUE,COUNT(
1)   FROM V$SQL  GROUP BY ADDRESS,HASH_VALUE  HAVING (SUM(BUFFER_GETS) > :b4  OR
 SUM(DISK_READS) > :b5  OR SUM(PARSE_CALLS) > :b6  OR SUM(EXECUTIONS) > :b7 )

11 Dec 0        861        1      861    245,029  2,778,052        860        0
9:00:24
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE
E :b := 0; END IF; END;

11 Dec 1        864        1      864    244,587    595,861    129,605      864
2:00:02
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID,DBID,INSTANCE_NUMBER,SQL_TEXT,SHARABLE_M

EM,SORTS,MODULE,LOADED_VERSIONS,EXECUTIONS,LOADS,INVALIDATIONS,PARSE_CALLS,DISK_
READS,BUFFER_GETS,ROWS_PROCESSED,ADDRESS,HASH_VALUE,VERSION_COUNT )  SELECT MIN(
:b1),MIN(:b2),MIN(:b3),MIN(SQL_TEXT),SUM(SHARABLE_MEM),SUM(SORTS),MIN(MODULE),SU
M(LOADED_VERSIONS),SUM(EXECUTIONS),SUM(LOADS),SUM(INVALIDATIONS),SUM(PARSE_CALLS
),SUM(DISK_READS),SUM(BUFFER_GETS),SUM(ROWS_PROCESSED),ADDRESS,HASH_VALUE,COUNT(
1)   FROM V$SQL  GROUP BY ADDRESS,HASH_VALUE  HAVING (SUM(BUFFER_GETS) > :b4  OR
 SUM(DISK_READS) > :b5  OR SUM(PARSE_CALLS) > :b6  OR SUM(EXECUTIONS) > :b7 )

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.


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