You Asked
Hi Tom , Greetings..
We are using a Oracle 12.2.0.1.0 Standard edition 2 database in RAC mode.
it has limitation of using 16 thread at any point of time from CPU.
We were normally seeing "Resmgr:Cpu Quantum" wait in general but from last few weeks , this occurrence of this wait event has increased 10 times.
There is no specific load added on database , just some normal deployments from application side adding 5-10 queries in last 2 releases.
Our concern is that even those queries which normally complete in 5 second are taking infinite time.
Can you please guide or share any document that can help us to trace the actual problem query with the limitation of standard edition.
** Statspack of sample time window having issue when database was running very slow showing this wait event in almost every session.**
//
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 162209 07-Jul-22 10:00:33 1,369 .9
End Snap: 162210 07-Jul-22 11:00:10 1,527 1.0
Elapsed: 59.62 (mins) Av Act Sess: 194.4
DB time: 11,590.00 (mins) DB CPU: 763.21 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 38,016M Std Block Size: 8K
Shared Pool: 6,144M Log Buffer: 98,712K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 194.4 5.5 0.04 0.03
DB CPU(s): 12.8 0.4 0.00 0.00
Redo size: 863,558.9 24,390.2
Logical reads: 529,736.7 14,961.8
Block changes: 3,775.6 106.6
Physical reads: 21,201.0 598.8
Physical writes: 228.3 6.5
User calls: 6,584.2 186.0
Parses: 4,176.1 118.0
Hard parses: 4.0 0.1
W/A MB processed: 148.0 4.2
Logons: 200.9 5.7
Executes: 4,487.1 126.7
Rollbacks: 3.0 0.1
Transactions: 35.4
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.69 87.34
% SQL with executions>1: 70.01 73.80
% Memory for SQL w/exec>1: 87.47 87.39
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
resmgr:cpu quantum 3,837,800 568,268 148 79.6
db file sequential read 21,358,172 35,812 2 5.0
CPU time 32,801 4.6
library cache lock 297,350 19,228 65 2.7
read by other session 1,405,021 13,808 10 1.9
//
**Statspack report of sample time window when database was running fine even observing the same wait but with less frequency.**
//
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 162257 07-Jul-22 17:00:29 1,278 1.2
End Snap: 162258 07-Jul-22 18:00:05 1,082 1.2
Elapsed: 59.60 (mins) Av Act Sess: 100.2
DB time: 5,974.36 (mins) DB CPU: 681.80 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 38,016M Std Block Size: 8K
Shared Pool: 6,144M Log Buffer: 98,712K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 100.2 3.1 0.03 0.01
DB CPU(s): 11.4 0.4 0.00 0.00
Redo size: 564,012.1 17,583.9
Logical reads: 494,596.7 15,419.8
Block changes: 2,208.6 68.9
Physical reads: 19,313.7 602.1
Physical writes: 181.3 5.7
User calls: 6,873.3 214.3
Parses: 3,708.3 115.6
Hard parses: 3.9 0.1
W/A MB processed: 87.9 2.7
Logons: 189.2 5.9
Executes: 3,866.1 120.5
Rollbacks: 2.3 0.1
Transactions: 32.1
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.95 Redo NoWait %: 100.00
Buffer Hit %: 96.99 Optimal W/A Exec %: 100.00
Library Hit %: 99.87 Soft Parse %: 99.90
Execute to Parse %: 4.08 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 55.16 % Non-Parse CPU: 98.93
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 86.63 86.67
% SQL with executions>1: 69.27 71.03
% Memory for SQL w/exec>1: 82.13 83.79
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
resmgr:cpu quantum 2,757,460 257,708 93 67.6
db file sequential read 18,477,682 35,203 2 9.2
CPU time 28,745 7.5
library cache lock 457,044 9,352 20 2.5
LMS CR slave timer 679,868 7,064 10 1.9
//
Regards
and Connor said...
Well, you've got 760 minutes of CPU used in the hour, which means you were *averaging* about 13 threads being fully utilised for the entire hour, so I'd say it would be unsurprising to expect some periods *during* the hours to be (well) in excess of 16 threads. Combine that with DB time of 11,590 mins which is 190 sessions on average being active during the hour, you can see why resource manager has come in and put the brakes on.
Unless you've got a fairly niche application usage, almost always the issue lies with expensive SQL, which means threads are consistently bottlenecked running expensive queries.
Check the SQL section of your statspack report, or even as a simple first step,
select sql_text, buffer_gets, executions, sql_id, elapsed_time
from v$sqlstats
where buffer_gets > 1000000
or disk_reads > 10000
or executions > 10000
and alter the thresholds as necessary to get a good sized starting list of candiddates.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment