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