 |
|
Extracting SQL Source from the Library Cache
Oracle Tips by Burleson Consulting |
This script is substantially similar to the
STATSPACK extraction script except that the SQL is extracted
directly from the v$sqlarea view. This report will rank and display
all SQL that currently resides in the library cache, and it is a
good starting point for investigating SQL when you do not have a
STATSPACK database.
rpt_sql_cache.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 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 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 temp2;
create table temp2 as
select
executions exec,
loads loads,
parse_calls parse,
disk_reads reads,
buffer_gets gets,
rows_processed rows_proc,
sorts sorts,
sql_text
from
v\$sqlarea
order by
$sortkey desc
;
spool off;
select * from temp2 where rownum < 11;
exit
!
The output from this script is exactly the same
as the output from the rpt_sql_STATSPACK.ksh script except
that there is no display for the date. Next, let’s look at a method
for extracting and explaining all of the SQL in your library cache.
Explaining all SQL in the Library Cache
One step that is commonly used to locate SQL
is using a script to extract and explain all of the SQL that
currently reside in the library cache. I will explain this procedure
in detail in Chapter 8.
At first glance, it may be hard to fully
appreciate the value of this technique and the information produced
by the reports. But if your database has a large library cache, you
can get some great insights into the internal behavior of the tables
and indexes.
The output from the access.sql script
has several extremely useful reports. I will address them briefly
here and go into greater detail in Chapter 8.
The Full-Table Scan Report
This is the most valuable report of all. Here
you see all of the SQL statements that performed full-table scans,
and the number of times that a full-table scan was performed. Also
note the C and K columns. The C column indicates if an Oracle7 table
is cached, and the K column indicates whether the Oracle8 table is
assigned to the KEEP pool. As you will recall, small tables with
full-table scans should be placed in the KEEP pool.
Mon
Feb 29 page 1
full table scans and counts
Note that "C" indicates the table is cached.
“K” indicates the table is in the KEEP Pool
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
-------------- -------------------- ------------ - - --------
--------
SYS DUAL N 2
97,237
SYSTEM SQLPLUS_PRODUCT N K 2
16,178
DONALD PAGE 3,450,209 N 932,120
9,999
DONALD RWU_PAGE 434 N 8
7,355
DONALD PAGE_IMAGE 18,067 N 1,104
5,368
DONALD SUBSCRIPTION 476 N K 192
2,087
DONALD PRINT_PAGE_RANGE 10 N K 32
874
ARSD JANET_BOOKS 20 N 8
64
PERFSTAT STATS$TAB_STATS N 65
10
In the preceding report, you see several huge
tables that are performing full-table scans. For tables that have
less than 200 blocks and are doing legitimate full-table scans, we
will want to place these in the KEEP pool. The larger table
full-table scans should also be investigated, and the legitimate
large-table full-table scans should be parallelized with the
alter table parallel degree nn command.
An Oracle database invokes a large-table
full-table scan when it cannot service a query through indexes. If
you can identify large tables that experience excessive full-table
scans, you can take appropriate action to add indexes. This is
especially important when you migrate from Oracle7 to Oracle8,
because Oracle8 offers indexes that have built-in functions. Another
cause of a full-table scan is when the cost-based optimizer decides
that a full-table scan will be faster than an index range scan. This
occurs most commonly with small tables, which are ideal for caching
in Oracle7 or placing in the KEEP pool in Oracle8. This full-table
scan report is critical for two types of SQL tuning:
-
For a small-table full-table scan, in
Oracle7 you can cache the table by using the alter table xxx
cache command, (where xxx = table name), which will put the
table rows at the most recently used end of the data buffer,
thereby reducing disk I/O for the table. In Oracle8, you should
place cached tables in the KEEP pool with the alter table xxx
storage (buffer_pool keep) command.
-
For a large-table full-table scan, you can
investigate the SQL statements to see if the use of indexes would
eliminate the full-table scan. Again, the original source for all
the SQL statements is in the SQLTEMP table. I will talk about the
process of finding and explaining the individual SQL statements in
the next section.
Next, we see the index usage reports. These
index reports are critical for the following areas of Oracle tuning:
Index usage Ensuring that the
application is actually using a new index. Remote DBAs can now obtain
empirical evidence that an index is actually being used after it has
been created.
Row resequencing Finding out which
tables might benefit from row resequencing. Tables that have a large
amount of index range scan activity will benefit from having the
rows re-sequenced into the same order as the index. Resequencing can
result in a tenfold performance improvement, depending on the row
length.
Next, let’s look at the index range scan
report.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
