Statspack Help Please

From: <art_at_chicagorsvp.com>
Date: 20 Oct 2005 10:25:53 -0700
Message-ID: <1129829153.630056.316930@z14g2000cwz.googlegroups.com>

Hi,

We are all pretty new to Oracle here. We are running 9.2.0.5 on AIX and have a statspack report. I'd like to post some of it and see if anyone can tell me if there is an issue:

Cache Sizes (end)

       Buffer Cache: 112M  Std Block Size:    8K
   Shared Pool Size: 112M      Log Buffer:  512K

Load Profile
~~~~~~~~~~~~            Per Second Per Transaction
                   --------------- ---------------
        Redo size:          909.01        3,559.24
    Logical reads:        4,000.48       15,663.84
    Block changes:            3.42           13.38
   Physical reads:            0.15            0.60
  Physical writes:            0.05            0.20
       User calls:            6.31           24.70
           Parses:            5.86           22.95
      Hard parses:            0.12            0.45
            Sorts:            0.73            2.84
           Logons:            0.02            0.07
         Executes:           34.42          134.75
     Transactions:            0.26

  % Blocks changed per Read:    0.09    Recursive Call %:    90.91
 Rollback per transaction %:   73.94       Rows per Sort:   151.04

Should the Logical Reads be so high? Also, what would cause the rollback to be at 73%?

Instance Efficiency Percentages (Target 100%)

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:  100.00
            Library Hit   %:   98.75        Soft Parse %:   98.02
         Execute to Parse %:   82.97         Latch Hit %:   99.50
Parse CPU to Parse Elapsd %:   56.14     % Non-Parse CPU:   98.75

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   95.84   95.64

% SQL with executions>1: 45.31 46.91 % Memory for SQL w/exec>1: 42.98 45.48

Why is the memory usage so high? Should it be at 95%???

SQL ordered by Parse Calls for DB: SUG Instance: SUG Snaps: 11 -12
-> End Parse Calls Threshold: 1000

                           % Total

Parse Calls Executions Parses Hash Value ------------ ------------ -------- ----------

4,366 4,366 66.98 2545474735 Module: JDBC Thin Client
SELECT /*+ ORDERED */'X' FROM CTXSYS.DR$INDEX,SYS.USER$ U W HERE IDX_OWNER# = U.USER# AND U.NAME = :b1 AND IDX_NAME = :b2

104 104 1.60 2095543314 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

101 102 1.55 3404108640 Module: JDBC Thin Client
ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED 81 81 1.24 1307778841 select condition from cdef$ where rowid=:1

48 128 0.74 3787621475 Module: EPTS.exe
Select EPTS_DISCHARGES.rowid, EPTS_DISCHARGES.* from EPTS_DISCHA RGES where EPTS_DISCHARGES.ROWID=:V1

36 36 0.55 633914867

Notice that the Parse & Executions are mostly the same. What does this mean?

Instance Activity Stats for DB: SUG Instance: SUG Snaps: 11 -12

Statistic                      Total     per Second    per Trans
----------------------- ------------ -------------- ------------
buffer is not pinned count   219,445          197.3        772.7
buffer is pinned count     8,276,689        7,443.1     29,143.3
consistent gets            4,445,008        3,997.3     15,651.4

process last non-idle time 22,596,439,061 20,320,538.7 ############

Are these high numbers ok?

If I can provide anymore info let me know. We just would like to know what those high numbers mean, especially why the CPU is at 95% and the rollback is at 73%.

Thanks as always. Received on Thu Oct 20 2005 - 12:25:53 CDT