Interpreting statspack report

From: Pascal Oegerli <pascal.oegerli_at_edorex.ch>
Date: Wed, 23 Nov 2005 18:57:44 +0100
Message-ID: <dm2aj5$sl6$1@news.hispeed.ch>

Hello,

Is anybody out there who can verify my reflections on the following Statspack Report?

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ PMCSHG 3476772697 PMCSHG 1 9.2.0.6.0 NO ms009005

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:      7213 22-Nov-05 09:42:04       81  28,061.7 ~
  End Snap:      7214 22-Nov-05 10:42:07       95  24,116.8 ~
   Elapsed:               60.05 (mins)

Cache Sizes (end)

---

               Buffer Cache:       576M      Std Block Size:          8K
           Shared Pool Size:       528M          Log Buffer:      4,000K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             34,445.58             17,287.56
              Logical reads:             15,711.60              7,885.35
              Block changes:                 55.43                 27.82
             Physical reads:                685.85                344.22
            Physical writes:                  6.99                  3.51
                 User calls:                113.94                 57.19
                     Parses:                 60.26                 30.24
                Hard parses:                  6.41                  3.22
                      Sorts:                 11.53                  5.79
                     Logons:                  0.34                  0.17
                   Executes:                278.88                139.97
               Transactions:                  1.99



  % Blocks changed per Read:    0.35    Recursive Call %:     82.67

 Rollback per transaction %:    0.01       Rows per Sort:    303.44

Instance Efficiency Percentages (Target 100%)

---

            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:   96.08    In-memory Sort %:     99.93
            Library Hit   %:   97.68        Soft Parse %:     89.36
         Execute to Parse %:   78.39         Latch Hit %:     99.94
Parse CPU to Parse Elapsd %:   53.89     % Non-Parse CPU:     85.36

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   88.53   88.59

    % SQL with executions>1: 45.10 31.29
% Memory for SQL w/exec>1: 39.58 17.86

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     % 
Total
Event                                               Waits    Time (s) Ela 

Time

-------------------------------------------- ------------ ----------- --------
CPU time                                     ~                  3,805 
84.92
db file scattered read                            343,220         210 
4.70
SQL*Net more data to client                       436,462         183 
4.07
db file sequential read                            41,423          96 
2.14
log file parallel write                             8,207          68 

1.51

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

85% of the whole Time is CPU time. So I have to look closer at this, agree?

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

parse time cpu                                55,695           15.5 
7.8
recursive cpu usage                          258,990           71.9 

36.1

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Recursive CPU is 2589 seconds of 3'805 total CPU time. That means, I have: either a lot of PL/SQL
or very expensive buffer handling in the buffer cache So let's have a look at these SQL Statments:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

                                                     CPU      Elapsd

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

     16,668,489 103 161,830.0 29.4 166.73 219.96 2693357960
Module: w3wp.exe
begin "PA_ANTENNA"."P_CUD"(p_action=>:p_action, p_rec_status=>:p _rec_status, p_azimut=>:p_azimut, p_mod_user=>:p_mod_user, p_con n_cells=>:p_conn_cells, p_antenna_id=>:p_antenna_id, p_antennamo del_id=>:p_antennamodel_id, p_ret_id=>:p_ret_id, p_antenna_set_i d=>:p_antenna_set_id, p_preamplifier_id=>:p_preamplifier_id, p_e

      5,936,120 48,747 121.8 10.5 1114.58 1570.03 3004224063
Module: w3wp.exe
SELECT A.CAPTION, A.LANG_ID FROM X_MLTEXT A WHERE ML_ID=:B1 ORDE R BY LANG_ID DESC       2,794,360 19,353 144.4 4.9 524.16 847.55 1434437237
Module: w3wp.exe
SELECT A.CAPTION, A.LANG_ID FROM X_MLTEXT A, X_ML B WHERE B.ML_I D_CODE = LOWER(:B1 ) AND A.ML_ID=B.ML_ID ORDER BY LANG_ID DESC       1,924,208 10,934 176.0 3.4 371.70 615.85 838048417
Module: w3wp.exe
select pa_ml.f_get(1,'msg_reallydel') from dual

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Would you agree that theses statments are very critical ones (especially the second one)
due to their Buffer gets and CPU times?
Is this the right approach that I'm going?

Thank you very much for your advice.

Pascal Received on Wed Nov 23 2005 - 11:57:44 CST