-
statspack wait events
i Guy,
My database is 10g and and my database is running in auto tuneing,i.e I kept in sga_target,
and my application is hanging ,can any body help me in finding why cpu is high and
what is parse cpu to parsed in statspack.
Code:
STATSPACK report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
q xxxxxx q 1 10.1.0.4.0 NO q
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1580 20-Jun-06 17:42:03 109 384.0
End Snap: 1582 20-Jun-06 18:02:05 97 436.7
Elapsed: 20.03 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,472M Std Block Size: 8K
Shared Pool Size: 416M Log Buffer: 256K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,074.00 15,160.82
Logical reads: 74.22 276.19
Block changes: 13.37 49.74
Physical reads: 0.68 2.53
Physical writes: 0.97 3.61
User calls: 39.07 145.41
Parses: 9.41 35.01
Hard parses: 0.04 0.17
Sorts: 1.81 6.73
Logons: 0.12 0.43
Executes: 14.69 54.67
Transactions: 0.27
% Blocks changed per Read: 18.01 Recursive Call %: 31.96
Rollback per transaction %: 36.53 Rows per Sort: 62.95
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.08 In-memory Sort %: 100.00
Library Hit %: 99.68 Soft Parse %: 99.52
Execute to Parse %: 35.96 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 86.49 % Non-Parse CPU: 97.00
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.61 93.66
% SQL with executions>1: 35.62 35.76
% Memory for SQL w/exec>1: 61.91 61.94
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 21 83.14
db file sequential read 724 2 9.42
process startup 12 1 1.95
log file parallel write 336 0 1.82
control file parallel write 400 0 1.32
-------------------------------------------------------------
Wait Events DB/Inst: q/q Snaps: 1580-1582
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 724 0 2 3 2.2
process startup 12 0 1 42 0.0
log file parallel write 336 0 0 1 1.0
control file parallel write 400 0 0 1 1.2
SQL*Net more data to client 1,635 0 0 0 5.1
log buffer space 2 0 0 62 0.0
log file sync 115 0 0 1 0.4
db file parallel read 11 0 0 8 0.0
change tracking file synchro 19 0 0 1 0.1
control file sequential read 2,020 0 0 0 6.3
latch: library cache 68 0 0 0 0.2
SQL*Net break/reset to clien 6 0 0 0 0.0
rdbms ipc reply 4 0 0 1 0.0
db file parallel write 712 0 0 0 2.2
change tracking file synchro 19 0 0 0 0.1
buffer busy waits 1 0 0 0 0.0
db file scattered read 1 0 0 0 0.0
latch: shared pool 2 0 0 0 0.0
latch: redo allocation 1 0 0 0 0.0
virtual circuit status 47,614 37 2,292 48 147.4
SQL*Net message from client 49,039 0 1,584 32 151.8
Queue Monitor Wait 40 40 1,149 28726 0.1
jobq slave wait 375 368 1,087 2898 1.2
wakeup time manager 4 0 915 ###### 0.0
SQL*Net message to client 49,039 0 0 0 151.8
SQL*Net more data from clien 22 0 0 0 0.1
-------------------------------------------------------------
Background Wait Events DB/Inst: q/q Snaps: 1580-1582
Last edited by davey23uk; 06-20-2006 at 07:28 AM.
-
Have you ever heard of bind variables?
Jeff Hunter
-
Short of rewriting app to utilize bind variables: (Bind variables would be the best)
Review the following parameters:
Cursor_sharing
Cursor_space_for_time
Last edited by ixion; 06-20-2006 at 11:06 AM.
-
Those are bandaids to the symtoms. Fix the problem.
Jeff Hunter
-
Indeed... Short term bandaid. Long term solution.
-
I don't know if I'm being stupid here, but have you looked at the STATSPACK report that the poster attached? All the SQL seems to use bind variables and % Non-Parse CPU = 99.88. I don't think bind variables are the issue here, although I could be wrong?
Last edited by hacketta1; 06-21-2006 at 07:56 AM.
Reason: Bad Grammar!
-
First the report in the .txt file spans 2 hours - you should try to target a bad period of 10-15 mins to simplify things.
Secondly what is happening here?
Code:
Instance Activity Stats DB/Inst: KTPRO/ktpro Snaps: 1552-1564
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
bytes sent via SQL*Net to client 292,477,608,057 40,599,334.8 ############
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
292Gb in 2 hours! wow, 40Mb a second
-
. . . also your two reports are very different: the "in-line" one shows 21 seconds CPU in 20 minutes, the .txt file shows 8'798 second in 120 minutes and only one CPU ! ! ! (probably an effect of a long transaction finishing in the time slot).
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Time Model System Stats DB/Inst: KTPRO/ktpro Snaps: 1552-1564
-> Total Time in Database calls 9967.6s (or 9967640228us)
Statistic Time (s) % of DB Time
----------------------------------- -------------------- ------------
DB CPU 6,858.6 68.8
DB time 9,967.6
PL/SQL compilation elapsed time 4.9 .0
PL/SQL execution elapsed time 9,218.9 92.5
background cpu time 15.0 .2
background elapsed time 126.6 1.3
connection management call elapsed 11.4 .1
failed parse elapsed time 0.2 .0
hard parse (bind mismatch) elapsed 0.0 .0
hard parse (sharing criteria) elaps 0.1 .0
hard parse elapsed time 3.8 .0
inbound PL/SQL rpc elapsed time 9,244.5 92.7
parse time elapsed 17.4 .2
sequence load elapsed time 0.3 .0
sql execute elapsed time 1,796.7 18.0
-------------------------------------------------------------
From the posted file 2 hour window. It looks like your app is transmitting most/ if not all of the PL/SQL to be executed into the server. Hence the high sql net activity.
What type of system and How many CPU's?
Can you modify the application to utilize named procedures/packages instead of anonymous procedures? Then use compiled PL/SQL and ping the packages.
BAD SQL:
SELECT ABS(:b1),ABS(:b2) FROM DUAL ----> Rewrite this, you don't need the DB to do this math work.
There seems to be a lot of that type thing. Each one by itself is not a lot, but it adds up. If you don't have to select the data, don't!
Instead:
var2 = abs(var1) --- If this is embedded in the app. Then no round trip to the DB is required... 100 x faster.
or if in PL/SQL
var2 := abs(:var1) ; (this is a function call and requires less overhead)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|