RE: Statspack Report Help, Bursts of Activity, Amaze Your Friends ...

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 13 Feb 2004 17:22:53 -0800
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D92FE@ussccem13.hds.com>

Ethan,

Interesting Statspack - that one! A preliminary glance shows up the following:

Very high rate of execution (i.e SQL calls) - 1,751.09 per sec in that 30 minute period, as well as recursive calls (i.e. bith for SYS as well as PL/SQL recursion). You might want to see what prompts this and why... Parsing consumes CPU, and lots of it...

Statistic                                      Total     per Second    per

Trans
--------------------------------- ------------------- --------------

recursive calls                           24,383,652       13,554.0
1,670.8
recursive cpu usage                          209,166          116.3

14.3

2. The problem with Statspack (and Cary and many others including myself have mentioned this many times) is that it captures SQL information that is not within a Time and action scope, so relying on this would be incorrect... Having said that, I would still look at these:

                                                CPU per    Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ----------------- ----------- ----------

1,013,753 1,016,408 1.0 0.00 0.00 1817891629
SELECT user from sys.dual

1,002,400 1,003,675 1.0 0.00 1048.96 2390715309
Module: JDBC Thin Client
SELECT HSD_SEQ_BATCH_MESSAGE_ID.NEXTVAL FROM DUAL 3. I think the latter also shows up as:

                                                        Avg Wt         Wait
Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)     Time

(s)
-- ------------ ------------ ----------- ------------ -------------

SQ 6,609 6,609 0 5,173 18.77 97

You might want to see if the cache size of these sequences are adequate....

4. Without repeating the caveat of (2), I would then also investigate the following, as LIOs cost CPU as well...
I would look not only for tuning opportunuties, but larger batching (and possible offloading of batch reports/processes to non-prime time.

                                                     CPU      Elapsd

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

50,356,857 1,154 43,636.8 75.8 2236.12 3541.02 1143193881
Module: JDBC Thin Client
BEGIN :1 := hsd_batch.post_transaction(:2,:3,:4); END;

27,601,105 371,649 74.3 41.6 340.65 ######### 3554013190
Module: JDBC Thin Client
SELECT * FROM HSD_BENEFIT_RULE_SELECT WHERE COLUMN_NAME = 'me d_def_filter' AND RULE_ID = :b1

14,753,988 1,237 11,927.2 22.2 679.64 930.81 3401631907
Module: JDBC Thin Client
BEGIN :1 := hsd_batch.validate_transaction(:2,:3,:4); END;

9,532,698 180,715 52.7 14.4 119.41 ######### 1463021224
Module: JDBC Thin Client
SELECT * FROM HSD_BENEFIT_RULE_SELECT WHERE COLUMN_NAME = 'ot her_med_def_code' AND RULE_ID = :b1

9,079,235 2,283 3,976.9 13.7 399.22 510.27 1153765395
Module: JDBC Thin Client
SELECT seq_prov_id FROM hsd_prov_master WHERE UPPER (pr ovider_id) = UPPER (:b1)

5. Finally, I would look at sar output for the period mentioned:

Sar -u (default) should show CPU usage, sar -q will show CPU queue depths/queueing...

So what are you using to load the system? If these are synthetic load generators, surely you have access to what they do... Please let us know what you find!

Take care, Bro!
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org 
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan
>Sent: Friday, February 13, 2004 3:44 PM
>To: oracle-l_at_freelists.org
>Subject: Statspack Report Help, Bursts of Activity, Amaze Your 
>Friends...
>
>
>http://www.geocities.com/epost1/sp_1805_1808.txt
>
>
>I am stumped here.  I am benchmarking some software and this 
>is what I am
>seeing.  When running nmon on AIX 24 CPU box, all CPU's are 
>idle, then we
>see bursts of activity on all CPU's to 100%.  In the database I see 40
>sessions coming in via JDBC Thin Client processing 
>transactions, all idle,
>then all have serious bursts of activity.  As you can see the most
>significant wait it CPU, but CPU's are Idle!!
>
>Stumpppeddd...
>
>nmon also shows that the disks are all idle, then experience bursts of
>activity.  The disk system is an EMC, older model with not 
>enough channels
>or controllers (not a disk guy), but I don't see any IO waits 
>and I don't
>see anything in nmon.
>
>If you can spot something obvious in my Statspack report that 
>I am missing I
>would appreciate it.
>
>Event 10046 trace on a session also doesn't show it having any 
>significant
>waits.
>
>My fear is we have a disk, OS or hardware configuration issue. 
> I don't have
>a lot of access to the admins of this box.
>
>Thanks!
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to:  oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>


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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Fri Feb 13 2004 - 19:22:53 CST