 |
|
A STATSPACK Reactive Report
Oracle Tips by Burleson Consulting
|
Sometimes, the Remote DBA is faced with an imminent
performance problem. In cases when the end users are complaining about
current poor performance, you can use STATSPACK to quickly see what is
going on.
The quick.ksh script is great for using
STATSPACK to see what is happening right now in your database. Here
are the steps in quick.ksh:
1.
Issue a starting snapshot.
2.
Wait for the specified amount of time.
3.
Issue an ending snapshot.
4.
Run rpt_last.sql to compare the beginning and ending
values.
Here is the script. Take a minute to see how it
produces the report.
quick.ksh
#!/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':'`
#ORACLE_HOME=`cat /var/opt/oracle/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 perfstat/perfstat<<!
execute statspack.snap;
exit
!
sleep $elapsed
$ORACLE_HOME/bin/sqlplus 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
The rpr_last.sql Script
The rpt_last.sql script provides the
same information as the statspack_alert report, but it compares only
the last two STATSPACK snapshots.
The rpt_last.sql script is great for
performing stress tests on an Oracle database. You bounce the
database, issue a statspack.snap, perform the stress test, run another
statspack.snap, and then complete the process with rpt_last.sql.
You thereby get a complete picture of all of the activity within the
database during the stress test.
Here is an example of the output from
rpt_last.sql:
>cat rpt_last.lst
TO_CHAR(SNAP_ READS
WRITES
------------- ------------
------------
2001-02-01 17 4,881,080
395
***********************************************************
This will identify any single file with a read I/O
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
---------------- ----------------------------------------
------------
2001-02-01 17 /u01/oradata/testb1/bookd01.dbf
916,989
1 row selected.
***********************************************************
This will identify any single file with a write I/O
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
---------------- ------------
----------------
01 Feb 17:33:47 654
20
Yr Mo Dy Hr NAMESPACE HIT_RATIO PIN_HIT_RATIO
RELOADS
---------------- --------------- --------- -------------
------------
2001-02-01 17 SQL AREA .66 .78
13
TABLE/PROCEDURE 1.00 .88
80
SQL AREA .97 .95
8
TABLE/PROCEDURE .74 .30
0
BODY .00 .00
0
CLUSTER 1.00 1.00
0
SQL AREA 1.00 1.00
8
TABLE/PROCEDURE 1.00 1.00
0
BODY 1.00 1.00
0
SQL AREA .96 1.00
10
TABLE/PROCEDURE .84 1.00
0
BODY .99 .98
0
CLUSTER 1.00 1.00
0
SQL AREA .96 1.00
10
TABLE/PROCEDURE .84 1.00
0
BODY .99 .99
0
CLUSTER 1.00 1.00
0
SQL AREA .97 1.00
8
TABLE/PROCEDURE .85 1.00
0
BODY .99 .98
0
CLUSTER 1.00 1.00
0
21 rows selected.
***********************************************************
When there are high disk sorts, you should investigate
increasing sort_area_size, or adding indexes to force index_full
scans
***********************************************************
Yr Mo Dy Hr SORTS_MEMORY SORTS_DISK
RATIO
---------------- ------------ ------------
---------------
01 Feb 17:33:47 168 0
.0000000000000
***********************************************************
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
***********************************************************
Yr Mo Dy Hr FILENAME
WAITS
---------------- ----------------------------------------
----------
01 Feb 17:33:47 /u01/oradata/testb1/bookd01.dbf
61342
01 Feb 17:33:47 /u01/oradata/testb1/bookx01.dbf
9
2 rows selected.
***********************************************************
Buffer Bury Waits may signal a high update table with too
few freelists. Find the offending table and add more freelists.
***********************************************************
Yr Mo Dy Hr BUFFER_BUSY_WAIT
----------------
----------------
01 Feb 17:33:47
61,358
***********************************************************
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.
***********************************************************
Yr Mo Dy Hr TABLE_FETCH_CONTINUED_ROW
----------------
-------------------------
01 Feb 17:33:47
178
***********************************************************
Enqueue Deadlocks indicate contention within the Oracle
shared pool.
Investigate increasing shared_pool_size
***********************************************************
Yr Mo Dy Hr ENQUEUE_DEADLOCKS
---------------- -----------------
01 Feb 17:33: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 200 issue for details
***********************************************************
Yr Mo Dy Hr FTS
----------------
------------
01 Feb 17:33:47 0
Now that we see the general reports, let’s look
at how they are scheduled in a UNIX environment.
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. |
 |
|
