| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statspack Report Help, Bursts of Activity, Amaze Your Friends ...
Thanks John, in a way you are making me feel better. I just want to be sure
this is not a DB issue and I don't think it is. All the things you point
out are issues, but the mystery is why I see the box go idle so frequently
without getting any type of IO or enqueue waits, something I might expect to
see. The only time I have seen this type of behavior is when a box with a
NIC set to auto-negotiate is on a switch set to 100-Full Duplex. It creates
a huge network issue and you see the SQL*Net From Client as the only
significant wait. Since this is usually categorized as an idle wait it is
frequently missed.
I am more and more convinced this is some issue with either the network or the midtier processes.
Thanks for the help!
-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com]
Sent: Friday, February 13, 2004 7:23 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Statspack Report Help, Bursts of Activity, Amaze Your
Friends ...
Ethan,
Interesting Statspack - that one! A preliminary glance shows up the following:
Statistic Total per Second perTrans
recursive calls 24,383,652 13,554.0 1,670.8 recursive cpu usage 209,166 116.314.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)
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!
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Sat Feb 14 2004 - 07:14:40 CST
-----------------------------------------------------------------
 |
 |