BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter