Re: Interpreting statspack report

Pascal Oegerli wrote:
> 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

Do we have a problem or are we tuning for the sake of tuning?

Maybe I'm missing something, but I'd think most people would be very happy with 85% of their time in the CPU. Most people are trying to wring out their wait events.

First thing I'd do with ANY statspack report is feed it to www.oraperf.com. You'll get a back a pretty good analysis and recommendations of where to get the most bang for the buck. Received on Wed Nov 23 2005 - 14:15:14 CST