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

 

 


 

 

 

 

 
 

The Standard STATSPACK Report

Oracle Tips by Burleson Consulting

Oracle STATSPACK provides only a single report to show activity between snapshots. You are prompted for a beginning and an ending snapshot; the report then shows the differences between those snapshots.

Note: You must ensure that you have not stopped and re-started the database during the time between the starting and the ending snapshot. Remember, a STATSPACK snapshot is nothing more than a reflection of the v$ views at the time that the snapshot was taken, and the v$ views are in-memory accumulators of database activity.

Because of the large size of the STATSPACK report, we will divide it into small sections and explain each section individually. Also, remember that the report was originally designed for the exclusive use of Oracle Technical Support.  Hence, many parameters and values are completely undocumented and therefore useless to those without the Oracle internal DSECT manuals.

Introductory Section

The first section of the STATSPACK report describes the report environment.  The “environment” includes the database name, database version, and other standard information about the size of the data blocks and the SGA component sizes.

STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DIA2          1710902234 dia2                1 9.0.1.0.0   NO      oracle_db

            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- -------------------
Begin Snap:       1 08-Dec-01 20:22:49        7       6.7
  End Snap:       2 08-Dec-01 20:35:17        8       7.3
   Elapsed:               12.47 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:        64M      Std Block Size:         8K
           Shared Pool Size:       112M          Log Buffer:       512K

 

Load Profile Section

The Load Profile section provides an overall metric of the load on the database during the snapshot period.  The most important statistics in this section are the total number of physical reads and writes, because those numbers give you a good idea of the amount of activity on the database.

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:                426.92            159,668.00
              Logical reads:                 48.77             18,240.00
              Block changes:                  0.82                306.50
             Physical reads:                  0.04                 14.00
            Physical writes:                  0.18                 66.00
                 User calls:                  0.04                 14.50
                     Parses:                  0.21                 78.00
                Hard parses:                  0.02                  8.00
                      Sorts:                  0.22                 82.00
                     Logons:                  0.00                  0.50
                   Executes:                  0.41                154.50
               Transactions:                  0.00

  % Blocks changed per Read:    1.68    Recursive Call %:    99.19
 Rollback per transaction %:    0.00       Rows per Sort:    41.32

Instance Efficiency Section

The Instance Efficiency section provides metrics about the general health of the SGA RAM region during the snapshot period.  Let’s take a closer look at some of the information.

  • Buffer hit ratio This is the percentage of time that a requested data block resided inside a RAM data buffer. You should always check the data buffer hit ratio; you should also review the data buffer hit ratios for the buffer_pool_keep and buffer_pool_recycle.

  • Library hit ratio This is the percentage that an SQL statement was found cached in the RAM of the library cache.  If this metric is too low, additional memory can be added to the library cache by increasing the shared_pool_size parameter.

  • Buffer nowait ratio This ratio is the percentage of time that a buffer was immediately available.  This statistic is for all of the data buffers in the SGA, as well as for the library cache buffers.  If this number falls too low, further investigation is required: look at specific buffer hit ratios to determine the buffer that needs more RAM storage.

  • Redo nowait ratio This ratio is the percentage of times that redo space is immediately available.  A shortage in this metric may indicate that the redo log file sizes are too small and that log switches are occurring too frequently.

Instance Efficiency percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.92    In-memory Sort %:  100.00
            Library Hit   %:   88.76        Soft Parse %:   89.74
         Execute to Parse %:   49.51         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   79.31     % Non-Parse CPU:   99.84

Shared Pool Activity Section

The Shared Pool Activity section shows RAM memory usage within the shared pool.  The most important area of the shared pool is the library cache, and the % SQL with executions>1 shows the number of SQL statements that were successfully cached in the library cache.  The % Memory for SQL w/exec>1 shows the amount of RAM required for the SQL cursor, normally for sorting and hash joins.  This memory is allocated from the pga_aggregate_target region in Oracle9i.

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   16.89   17.58
    % SQL with executions>1:   47.22   52.28
  % Memory for SQL w/exec>1:   54.52   69.61

Wait Event Section

The Wait Event section is important because it shows the types of operations that experienced wait conditions within Oracle.  As we know, wait conditions within Oracle may be caused by locks, latches, waits on disk I/O, or waits on buffer blocks.

Top 5 Event Wait Section

The Top 5 Event Wait section is one of the most important in the report because it shows how long Oracle is waiting for resources. This section is the starting point for looking at tuning the Oracle database. The Top 5 lists the highest-ranking events waited for during the snapshot period.  Here are the most important sources of waits:

  •  db file scattered read This indicates full-table scan processing where the sequential pre-fetch (set by the db_file_multiblock_read_count parameter) is reading sequential disk blocks.  In an OLTP database, you should minimize full-table scans, and tune the SQL to access the tables via indexes.

  • db file sequential read This indicates requests for individual data blocks, such as the type of I/O that would occur when a table is being accessed via an index.

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                            Wait     % Total
Event                                               Waits  Time (s)   Wt Time
-------------------------------------------- ------------ ----------- -------
control file parallel write                           243           1   76.44
log file parallel write                                11           0    8.79
db file sequential read                                28           0    8.72
log file sync                                           3           0    3.04
rdbms ipc reply                                         2           0    1.35
          -------------------------------------------------------------

Wait Detail Section

The Wait Detail section shows details of wait conditions.  It is critical to understanding the nature of any data waits within the snapshot period.

The most common sources of waits are waits on disk I/O and waits on network communication.  Special attention should be paid to SQL*Net message entries and db file entries.  Waits on db file entries can indicate disk I/O contention, and SQL*Net message waits may indicate a network bottleneck.

Wait Events for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write           243          0          1      3    121.5
log file parallel write                11          0          0      6      5.5
db file sequential read                28          0          0      3     14.0
log file sync                           3          0          0      8      1.5
single-task message                     2          0          0      5      1.0
control file sequential read          108          0          0      0     54.0
db file parallel write                 10         10          0      0      5.0
SQL*Net message from client            25          0      1,064  42565     12.5
virtual circuit status                 25         25        733  29307     12.5
SQL*Net message to client              26          0          0      0     13.0
          -------------------------------------------------------------

Background Wait Events Section

The Background Wait Events section lists all waits incurred by the Oracle background processes.  This section is rarely helpful when tuning a database because the behavior of the background processes cannot be adjusted.

Background Wait Events for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write           243          0          1      3    121.5
log file parallel write                11          0          0      6      5.5
rdbms ipc reply                         2          0          0      5      1.0
control file sequential read           72          0          0      0     36.0
db file sequential read                 1          0          0      0      0.5
db file parallel write                 10         10          0      0      5.0
rdbms ipc message                     740        729      2,185   2953    370.0
pmon timer                            249        249        729   2928    124.5
smon timer                              2          2        600 ######      1.0
          -------------------------------------------------------------

SQL Section

The SQL section is arguably the most important in the STATSPACK report, because inefficient SQL statements are the primary cause of poor performance.  As we know, STATSPACK gathers SQL information inside the stats$sql_summary table based upon the STATSPACK snapshot thresholds.

Once the SQL statements are gathered, the section displays them according to the major SQL metrics, including buffer gets, executions, and disk reads.  For details on tuning individual SQL statements, see Chapter 11.

SQL ordered by Gets for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         33,747            1       33,747.0   92.5     0.29      0.33  671327864
select --        substr(seg.tablespace_name,1,10) c1,         su
bstr(tab.owner,1,10)           c2,         substr(tab.table_name
,1,30)      c3, --        seg.bytes/1024                   c4, -
-        seg.extents                      c5 --        tab.max_e
xtents                  c6, --        tab.initial_extent/1024

             60            3           20.0    0.2     0.00      0.00 1819073277
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh
ere d_obj#=:1 and p_obj#=obj#(+) order by order#

             60           11            5.5    0.2     0.00      0.00 1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1

             46           16            2.9    0.1     0.01      0.00 1705880752


SQL ordered by Reads for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> End Disk Reads Threshold:    1000

                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
             16            1           16.0   57.1     0.29      0.33  671327864
select --        substr(seg.tablespace_name,1,10) c1,         su
bstr(tab.owner,1,10)           c2,         substr(tab.table_name
,1,30)      c3, --        seg.bytes/1024                   c4, -
-        seg.extents                      c5 --        tab.max_e
xtents                  c6, --        tab.initial_extent/1024

              1            2            0.5    3.6     0.00      0.01  365454555
select cols,audit$,textlength,intcols,property,flags,rowid from
view$ where obj#=:1

              1            5            0.2    3.6     0.00      0.00  931956286
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#

              1            9            0.1    3.6     0.00      0.01 2591785020


SQL ordered by Executions for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> End Executions Threshold:     100

                                                CPU per    Elap per
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
          16              16              1.0       0.00        0.00 1705880752
select file# from file$ where ts#=:1

          11              11              1.0       0.00        0.00  189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1

           0            2     0.00  114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1

           0            0     0.00  181436173
select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version)   from id
l_sb4$ where obj#=:1 and version<=:2 and   (part=0 or part=2) an
d piece#=0

           0            0     0.00  315090940
update con$ set con#=:3 where owner#=:1 and name=:2

           0            0     0.00  411033441
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)

           0            0     0.00  449029010
delete from dependency$ where d_obj#=:1

           0            0     0.00  636388251
insert into ccol$(con#,obj#,intcol#,pos#,col#) values(:1,:2,:3,d
ecode(:4,0,null,:4),:5)

SQL ordered by Parse Calls for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> End Parse Calls Threshold:      1000

                           % Total
 Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------

           0            0     0.00  641766606
insert into col$(obj#,name,intcol#,segcol#,type#,length,precisio

          -------------------------------------------------------------

Instance Activity Section

The Instance Activity section shows the major instance activity during the snapshot period.  Many of the metrics in this section are internal and undocumented, and valuable only to people from Oracle technical support who possess the highly-secret DSECT manual that show internal operations.

The major areas of importance include the CPU usage metric, the SQL*Net messages, the chained row fetches, and PGA usage.  Let’s look at each one.

  •  Table Scans (short tables)This is the number of full-table scans performed on small tables. On short tables, it is optimal to perform full-table scans (rather than to use indexes) and to place those small tables in the KEEP buffer pool. Note that Table Scans (long tables) plus Table Scans (short tables) is equal to the number of full-table scans.

  • Table Scans (long tables)This is the total number of full-table scans performed on large tables. This metric should be carefully evaluated to see if the full-table scan can be removed by adding an index, or if the query speed might be improved by invoking Oracle parallel query (OPQ).

  • Table Fetch by ROWIDThis is usually the number of rows that were accessed using an index, normally with nested loop joins.

  • Table Fetch by Continued RowThis is the number of rows that are chained to another block. However, there are several anomalies in this metric, and a high value here may not necessarily indicate chained rows. Of course, the value will also be high if tables contain large objects with LOB, BLOB, or CLOB datatypes, because those rows commonly exceed the database block size, thereby forcing the row to chain onto multiple blocks.

Instance Activity Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session                         147            0.2         73.5
CPU used when call started                       117            0.2         58.5
CR blocks created                                  2            0.0          1.0
DBWR checkpoint buffers written                  131            0.2         65.5
DBWR checkpoints                                   0            0.0          0.0
DBWR revisited being-written buff                  0            0.0          0.0
DBWR transaction table writes                      3            0.0          1.5
DBWR undo block writes                            26            0.0         13.0
SQL*Net roundtrips to/from client                 24            0.0         12.0
background timeouts                              729            1.0        364.5
buffer is not pinned count                    21,448           28.7     10,724.0
buffer is pinned count                           393            0.5        196.5
bytes received via SQL*Net from c              4,794            6.4      2,397.0
bytes sent via SQL*Net to client               5,423            7.3      2,711.5
calls to get snapshot scn: kcmgss                367            0.5        183.5
calls to kcmgas                                   18            0.0          9.0
calls to kcmgcs                                  258            0.3        129.0
change write time                                  1            0.0          0.5
cleanouts only - consistent read                   0            0.0          0.0
cluster key scan block gets                   11,257           15.1      5,628.5
cluster key scans                              6,380            8.5      3,190.0
commit cleanout failures: buffer                   0            0.0          0.0
commit cleanout failures: callbac                  4            0.0          2.0
commit cleanouts                                  70            0.1         35.0
commit cleanouts successfully com                 66            0.1         33.0
consistent changes                                 3            0.0          1.5
consistent gets                               35,667           47.7     17,833.5
consistent gets - examination                 17,955           24.0      8,977.5
cursor authentications                             4            0.0          2.0
data blocks consistent reads - un                  3            0.0          1.5
db block changes                                 613            0.8        306.5
db block gets                                    813            1.1        406.5
deferred (CURRENT) block cleanout                 23            0.0         11.5
enqueue releases                                 563            0.8        281.5
enqueue requests                                 563            0.8        281.5
enqueue timeouts                                   0            0.0          0.0
execute count                                    309            0.4        154.5
free buffer requested                             73            0.1         36.5
immediate (CR) block cleanout app                  0            0.0          0.0
immediate (CURRENT) block cleanou                 17            0.0          8.5
index fast full scans (full)                       2            0.0          1.0
leaf node splits                                   6            0.0          3.0
logons cumulative                                  1            0.0          0.5
messages received                                 16            0.0          8.0
messages sent                                     16            0.0          8.0
no buffer to keep pinned count                15,385           20.6      7,692.5
no work - consistent read gets                14,788           19.8      7,394.0
opened cursors cumulative                        154            0.2         77.0
parse count (hard)                                16            0.0          8.0
parse count (total)                              156            0.2         78.0
parse time cpu                                    23            0.0         11.5
parse time elapsed                                29            0.0         14.5
physical reads                                    28            0.0         14.0
physical reads direct                              0            0.0          0.0
physical writes                                  132            0.2         66.0
physical writes direct                             0            0.0          0.0
physical writes non checkpoint                    82            0.1         41.0
prefetched blocks                                  0            0.0          0.0
process last non-idle time             1,007,861,359    1,347,408.2 ############
recovery blocks read                               0            0.0          0.0
recursive calls                                3,546            4.7      1,773.0
recursive cpu usage                               56            0.1         28.0
redo blocks written                              615            0.8        307.5
redo entries                                     354            0.5        177.0
redo ordering marks                                0            0.0          0.0
redo size                                    319,336          426.9    159,668.0
redo synch time                                    2            0.0          1.0
redo synch writes                                  2            0.0          1.0
redo wastage                                   3,792            5.1      1,896.0
redo write time                                    0            0.0          0.0
redo writes                                       11            0.0          5.5
rollback changes - undo records a                  0            0.0          0.0
rollbacks only - consistent read                   2            0.0          1.0
rows fetched via callback                      3,276            4.4      1,638.0
session connect time                   1,007,861,359    1,347,408.2 ############
session logical reads                         36,480           48.8     18,240.0
session pga memory                         1,446,820        1,934.3    723,410.0
session pga memory max                     1,437,404        1,921.7    718,702.0
session uga memory                           132,520          177.2     66,260.0
session uga memory max                       711,284          950.9    355,642.0
shared hash latch upgrades - no w              2,865            3.8      1,432.5
sorts (memory)                                   164            0.2         82.0
sorts (rows)                                   6,777            9.1      3,388.5
summed dirty queue length                          0            0.0          0.0
switch current to new buffer                       1            0.0          0.5
table fetch by rowid                           3,580            4.8      1,790.0
table fetch continued row                          0            0.0          0.0
table scan blocks gotten                       1,560            2.1        780.0
table scan rows gotten                         3,323            4.4      1,661.5
table scans (long tables)                          4            0.0          2.0
table scans (short tables)                        20            0.0         10.0
transaction rollbacks                              0            0.0          0.0
user calls                                        29            0.0         14.5
user commits                                       2            0.0          1.0
write clones created in foregroun                  0            0.0          0.0
          -------------------------------------------------------------


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