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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Ongoing Ranking of SQL Statements
Oracle Tips by Burleson
 

Once you have established your baseline optimizer_mode and tuned the SQL, there is an ongoing process of rechecking your database for new SQL statements. The process of ongoing SQL extraction depends upon the method you used to make the tuning changes permanent. If you made your tuning changes permanent by adding hints to the source code, you need to check for SQL that does not possess hints. If you used stored outlines to make the tuning changes permanent, then you need to check for SQL that does not exist in the DBA_OUTLINES view.

Let’s look at two methods for identifying untuned SQL.

Finding New SQL Without Hints

If you are using hints to make your execution plan persistent, you can query the v$sqlarea view or stats$sql_summary table to extract SQL that does not have hints.

rpt_sql_nohint.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
and
   sql.sql_text NOT LIKE '%/*+%'
order by $sortkey desc
;
spool off;

select * from temp2 where rownum < 11;

exit
!

Here is the output from this script. Simply put, it scans for SQL that does not have the /*+ string that delimits a hint. Of course, you should always use the /*+ syntax for hints and not the ––+ method for denoting hints.

root>rpt_sql_nohint.ksh  
How many days back to search?
55
executions
loads
parse_calls
disk_reads
buffer_gets
rows_processed
sorts

Enter sort key:
executions

SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 2 13:51:50 2001

(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
---------------------------------------------------------------------
07 Feb 11:00:38  97,244        9   14,220     0    224,685     97,244
       0
SELECT BOOK_UNIQUE_ID   FROM BOOK  WHERE (ISBN = :b1 )

07 Feb 09:00:42  96,368        9   14,217     0    222,933     96,368
       0
SELECT BOOK_UNIQUE_ID   FROM BOOK  WHERE (ISBN = :b1 )

Finding New SQL When Using Optimizer Plan Stability

The optimizer plan stability features of Oracle8i use stored outlines to alleviate the need to reparse a SQL statement each time it is called. While we will cover the use of optimizer plan stability in detail in Chapter 13, for now you need to know that Oracle will store the execution plan for any SQL statement in the sql_text column of the DBA_OUTLINES view.

Conclusion

This chapter has been concerned with the process of setting the SQL baseline parameters, finding significant SQL statements to tune, and periodically rechecking for new and untuned SQL statements. The major points in this chapter include:

  • The baseline setting for optimizer_mode can make a huge positive impact of the performance of all SQL.

  • The most benefit in tuning individual SQL statements is by locating those queries that are executed most frequently and those that consume the most resources.

Next, let’s take a look the evaluation and tuning of full-table scans. Full-table scans are very important to SQL tuning, and the Oracle SQL tuning professional must be able to evaluate and tune any query that invokes a full-table scan.


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