 |
|
Handy
STATSPACK Shell Scripts
Oracle Tips by Burleson Consulting
|
(1)Handy STATSPACK Shell Scripts
Once you have become comfortable with the
STATSPACK data collection, you may need some easy scripts that you can
use to quickly get reports from STATSPACK. While the web site
mentioned in the Preface contains dozens of STATSPACK scripts, let's
look at one that is frequently used by the Remote DBA.
(2)A Quick Elapsed-Time STATSPACK Script
This is a great Korn shell script that shows
the Oracle database behavior change between snapshots. This report is
very handy if you get an end-user report of a performance problem and
you want to capture details about what is happening right now. The
script is called
quick.ksh, and it will become one of the most frequently used
of all the STATSPACK scripts.
The
quick.ksh script will take an immediate snapshot, sleep for
the period you specify, and then take another snapshot and produce an
elapsed-time report. This is a very similar procedure to the old
utlbstat/utlestat utilities, but it is much easier to execute and
interpret the results.
(3)quick.ksh
L 3-18
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
echo "Please enter the number of seconds between snapshots."
read elapsed
$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!
execute statspack.snap;
exit
!
sleep $elapsed
$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!
execute statspack.snap;
select
name,
snap_id,
to_char(snap_time,' dd Mon YYYY HH24:mi:ss')
from
stats\$snapshot,
v\$database
where
snap_id > (select max(snap_id)-2 from stats\$snapshot)
;
@rpt_last
Now that we have captured two quick snapshots,
we execute the rpt_last.sql script to give us a report on the
important metrics. Let's take a close look at this SQL*Plus report.
After completing the two snapshots, this script invokes
rpt_last.sql, which is a STATSPACK script that always compares the
last two snapshots in the database. The
rpt_last.sql script is very valuable, and is included in the
Oracle Press web site for these scripts. Here is a sample of the
output from the rpt_last.sql script. This script will identify
almost every possible cause of the performance problem.
(3)rpt_last.sql
L 3-19
***********************************************************
This will identify any single file who's read I/O
is more than 10% of the total read I/O of the database.
The "hot" file should be examined, and the hot table/index
should be identified using STATSPACK.
- The busy file should be placed on a disk device with
"less busy" files to minimize read delay and channel
contention.
- If small file has a hot small table, place the table
in the KEEP pool
- If the file has a large-table full-table scan, place
the table in the RECYCLE pool and turn on parallel query
for the table.
***********************************************************
MYDATE FILE_NAME
READS
---------------- ----------------------------------------
------------
2000-12-20 11 /u03/oradata/PROD/pod01.dbf
1,766
***********************************************************
This will identify any single file who's write I/O
is more than 10% of the total write I/O of the database.
The "hot" file should be examined, and the hot table/index
should be identified using STATSPACK.
- The busy file should be placed on a disk device with
"less busy" files to minimize write delay and channel
contention.
- If small file has a hot small table, place the table
in the KEEP pool
***********************************************************
no rows selected
***********************************************************
When the data buffer hit ratio falls below 90%, you
should consider adding to the db_block_buffer init.ora parameter
***********************************************************
MYDATE phys_writes BUFFER HIT
RATIO
---------------- ------------
----------------
20 Dec 11:23:47 101,888
91
1 row selected.
***********************************************************
When there are high disk sorts, you should investigate
increasing sort_area_size, or adding indexes to force index_full
scans
***********************************************************
MYDATE SORTS_MEMORY SORTS_DISK
RATIO
---------------- ------------ ------------
---------------
20 Dec 11:23:47 109 1
.0091743119266
1 row selected.
***********************************************************
When there is high I/O waits, disk bottlenecks may exist
Run iostats to find the hot disk and shuffle files to
remove the contention
***********************************************************
no rows selected
***********************************************************
Buffer Busy Waits may signal a high update table with too
few freelists. Find the offending table and add more freelists.
***********************************************************
MYDATE BUFFER_BUSY_WAIT
----------------
----------------
20 Dec 11:23:47 20
1 row selected.
***********************************************************
High redo log space requests indicate a need to increase
the log_buffer parameter
***********************************************************
no rows selected
***********************************************************
Table fetch continued row indicates chained rows, or fetches of
long datatypes (long raw, blob)
Investigate increasing db_block_size or reorganizing tables
with chained rows.
***********************************************************
MYDATE TABLE_FETCH_CONTINUED_ROW
----------------
-------------------------
20 Dec 11:23:47 1,551
1 row selected.
***********************************************************
Enqueue Deadlocks indicate contention within the Oracle
shared pool.
Investigate increasing shared_pool_size
***********************************************************
MYDATE ENQUEUE_DEADLOCKS
----------------
-----------------
20 Dec 11:23:47
0
1 row selected.
***********************************************************
Long-table full table scans can indicate a need to:
- Make the offending tables parallel query
(alter table xxx parallel degree yyy;)
- Place the table in the RECYCLE pool
- Build an index on the table to remove the FTS
To locate the table, run access.sql
See Oracle Magazine September 2000 issue for details
***********************************************************
MYDATE FTS
----------------
------------
20 Dec 11:23:47
148
1 row selected.
Note that
quick.ksh requires that you specify the ORACLE_SID, so that you
can run it on a server that contains many Oracle instances. Also, you
may need to change the location of the oratab file from /etc/ to /var/opt/oracle
if you are running Solaris.
Analyzing
STATSPACK tables:
Analyze.sql
spool ao.sql
set lines 150;
set pages 999;
set feedback off;
set heading off;
select
'analyze table perfstat.'||table_name||' estimate statistics
sample 5000 rows;'
from
Remote DBA_tables
where
table_name like 'STATS%'
;
select
'analyze index perfstat.'||index_name||' compute statistics;'
from
Remote DBA_indexes
where
index_name like 'STATS%'
;
spool off;
set feedback on;
@ao
This script provides detailed information about all
statistics from STATSPACK for reporting purposes. However, note that
adding statistics will cause the cost-based SQL optimizer to be
invoked for your STATSPACK queries, and your execution plan may change
after statistics are added to the STATSPACK tables.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
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. |
 |
|
