|
Replies:
18
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Jun 28, 2009 5:42 PM
Last Post By: jinyu
|
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
how to troubleshooting high sys cpu usage ???
Posted:
Jun 27, 2009 11:23 PM
|
|
|
|
how to troubleshooting high sys cpu usage ???
|
|
|
Posts:
1,268
Registered:
10/27/06
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 27, 2009 11:33 PM
in response to: jinyu
|
|
|
Little more details won't hurt i guess  . Things like OS, version and other details. |
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 27, 2009 11:37 PM
in response to: Amardeep Sidhu
|
|
|
|
Thanks for your reply,I just want to get some idea about how to diagnose high sys cpu usage.
|
|
|
Posts:
265
Registered:
06/10/09
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 1:06 AM
in response to: jinyu
|
|
|
The best thing is to run AWR / Perfstat to see what is going on.
You can allsow do :
First Identifiy the process number that consunes the most CPU (using TOP in linux or task manager in windows).
Then Run the following query againt the database to identifiy the SQL that the process is running, that way you can Identifiy the SQL that consuming the most CPU is your system:
select sql_fulltext from v$session s,v$sql q, v$process p
where s.paddr=p.addr and
s.sql_id=q.sql_id and
p.spid=<Insert process id from top>
Oded
http://www.dbsnaps.com
http://www.orbiumsoftware.com |
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 3:02 AM
in response to: Oded Raz
|
|
|
|
Thanks for your reply !
I don't say how to troubleshooting high user cpu,I don't think high sys cpu is related to sql which consume high buffer gets
|
|
|
Posts:
265
Registered:
06/10/09
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 4:05 AM
in response to: jinyu
|
|
|
|
O.K, first send us the processes who consumes the most CPU, and we will take it from there
|
|
|
Posts:
173
Registered:
07/06/98
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 6:51 AM
in response to: jinyu
|
|
|
High SYS mode CPU usage troubleshooting is highly OS specific.
So let us know exactly on which version of OS and platform you are and also knowing what's the exact version of Oracle wouldn't hurt.
Also, let us know what exactly do you mean by HIGH. Like is it 5% or 95% of total CPU capacity.
--
Tanel Poder
http://blog.tanelpoder.com |
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 7:31 AM
in response to: Tanel Poder
|
|
|
os: aix 5.3
sys% : 40%
oracle : 10.2.0.4.0
The system has only one double-core cpu ,the runable process about 40,there is no page in and page out.
before tuning :
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 1224 14-May-09 14:52:21 117 2.4
End Snap: 1225 14-May-09 14:55:18 117 1.9
Elapsed: 2.96 (mins)
DB Time: 36.20 (mins)
Per Second Per Transaction
Redo size: 9,393.74 128,529.54
Logical reads: 24,445.00 334,467.77
Block changes: 32.41 443.38
Physical reads: 20.30 277.69
Physical writes: 0.30 4.15
User calls: 6,592.79 90,205.54
Parses: 1,242.47 17,000.00
Hard parses: 0.13 1.77
Sorts: 125.10 1,711.62
Logons: 0.02 0.23
Executes: 1,244.06 17,021.85
Transactions: 0.07
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
latch: cache buffers chains 1,449 251 173 11.5 Concurrency
CPU time 156 7.2
latch free 1,031 71 69 3.3 Other
SQL*Net more data to client 1,007,758 23 0 1.1 Network
direct path read 3,575 12 3 .6 User I/O
after tuning :
DB Name DB Id Instance Inst num Release RAC Host
TESTDB 2465204434 testdb 1 10.2.0.4.0 NO testserver
Snap Id Snap Time Sessions Cursors/Session
Begin Snap:1403 21-May-09 18:24:32 109 2.0
End Snap: 1404 21-May-09 18:37:26 99 1.6
Elapsed: 12.90 (mins)
DB Time: 15.89 (mins)
Load Profile
Per Second Per Transaction
Redo size: 1,068.64 91,871.56
Logical reads: 9,996.25 859,381.00
Block changes: 3.82 328.33
Physical reads: 43.00 3,696.78
Physical writes: 0.39 33.78
User calls: 13,463.10 1,157,427.56
Parses: 2,367.31 203,518.11
Hard parses: 0.06 5.22
Sorts: 216.08 18,576.78
Logons: 0.05 4.00
Executes: 2,368.39 203,611.67
Transactions: 0.01
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 876 91.8
SQL*Net more data to client 9,192,435 187 0 19.6 Network
latch free 9,055 109 12 11.5 Other
SQL*Net more data from client 3,530,492 65 0 6.9 Network
direct path read 33,304 44 1 4.6 User I/O
after tuning,the reponse time has reduced and thuoughput has increased ,but the sys% cpu is still 40% |
|
|
Posts:
265
Registered:
06/10/09
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 7:40 AM
in response to: jinyu
|
|
|
|
Post the entire SQL Sections of the AWR, and the top 5 event of that awr
Edited by: Oded Raz on Jun 28, 2009 5:40 PM
|
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 7:50 AM
in response to: Oded Raz
|
|
|
Hi,I have post the top 5 event above.
before tuning:
SQL ordered by Gets
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 4,348,081
• Captured SQL account for 73.3% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,768,970 2,593 682.21 40.68 32.47 1375.28 a2vm9mf99ma3p
select
712,504 2,883 247.14 16.39 13.14 83.85 66vrg3gz2stvf
select
130,928 7,901 16.57 3.01 7.83 10.49 ctnn0bpfd3v5k
select
92,800 2,902 31.98 2.13 2.73 6.40 2985xzq8x547u
select
81,498 27,159 3.00 1.87 3.12 3.98 1prkhshjp25sc
select
60,513 2,631 23.00 1.39 2.29 5.64 2d8w9016z0kug
select
55,416 15,835 3.50 1.27 2.12 2.39 8tk22zsappg9m
select
44,879 15,838 2.83 1.03 1.74 1.89 c6sjzyfh9jmz0
select
31,677 10,558 3.00 0.73 1.15 1.24 5yr7z98xmcpqz
select
26,346 13,172 2.00 0.61 1.31 1.47 36shs0f8kxp5z
select
after tuning:
SQL ordered by Gets
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 7,734,429
• Captured SQL account for 99.4% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,728,723 99,759 17.33 22.35 113.87 119.45 309a6ak09rud2
select
996,920 332,437 3.00 12.89 41.57 44.03 1prkhshjp25sc
select
964,093 199,469 4.83 12.46 32.19 33.28 8tk22zsappg9m
select
531,776 33,241 16.00 6.88 34.38 36.36 2985xzq8x547u
select
432,140 199,473 2.17 5.59 20.86 21.57 c6sjzyfh9jmz0
select
332,406 166,226 2.00 4.30 17.28 17.84 36shs0f8kxp5z
select
265,872 33,234 8.00 3.44 7.74 8.09 4a34fa9tt90wx
select *
232,617 33,243 7.00 3.01 7.54 7.78 53rvrd0ug5gwm
select
232,603 33,237 7.00 3.01 9.24 9.83 9254c5ag3z9qd
select
232,589 33,239 7.00 3.01 8.11 8.40 gum17w9qgrw8p
select co
232,547 33,246 6.99 3.01 12.56 13.21 cx8yttzjh9ftz
select
232,484 33,244 6.99 3.01 10.08 11.07 gbx7hs3ykpjqs
select
199,446 99,753 2.00 2.58 11.27 11.89 2hpja5k312kwa
select
199,428 33,241 6.00 2.58 9.15 9.56 2d8w9016z0kug
select
199,389 199,469 1.00 2.58 19.59 20.25 b9j0pb2d5cn69
select
133,004 33,253 4.00 1.72 17.56 18.67 a2vm9mf99ma3p
select
99,756 33,255 3.00 1.29 3.77 3.92 5yr7z98xmcpqz
select
99,747 33,258 3.00 1.29 4.58 4.70 1fnshqnqxjtqz
select
|
|
|
Posts:
173
Registered:
07/06/98
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 8:21 AM
in response to: Oded Raz
|
|
|
You say you have a single dual-core CPU (are both cores in use?)
Having runnable processes at 40 with 2 CPUs only isn't too normal condition for Oracle, I think this may be the reason for your high sys mode CPU utilization.
From the AWR report you sent I checked following things that are potential SYS CPU eaters:
1) lots of physical IOs (especially on buffered filesystems) - you didn't have too many physical IOs
2) lots of logons (new process startups which have to attach to SHM segments) - you didn't have too much of these either
So, leaving the possibility of OS bugs out, the above things aren't likely your problem. When you have a large runqueue like you mentioned - then things like high CPU usage and some latch contention are probably symptoms of the CPU stravation, so I would look into getting the CPU runqueues to normal levels first - and troubleshoot SYS usage only if it still is there.
So, I'd either throttle the workload to prevent CPU trashing or try to tune the SQLs to use even less CPU time so that the runqueues would drop.
If you really want to see on what exactly the SYS time is spent, you may be able to get some info with truss -fp <PID> on a busy process (be careful to test this in test env first) or go even deeper with trace/trcfmt commands. But the latter one impacts your system performance more during tracing so you probably dont want to go that way.
Throttle your workload or tune the SQLs and the SYS CPU will likely disappear as a consequence..
--
Tanel Poder
http://blog.tanelpoder.com |
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 8:29 AM
in response to: Tanel Poder
|
|
|
|
Thanks for your reply.
Having runnable processes at 40 with 2 CPUs only isn't too normal condition for Oracle, I think this may be the reason for your high sys mode CPU utilization.
I also think this is the reason for high sys cpu .
|
|
|
Posts:
199
Registered:
08/18/08
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 8:34 AM
in response to: Tanel Poder
|
|
|
|
The sql is very hard to tune,because the avg buffer gets per sql is very low,most are below 10 buffer gets per sql, the high buffer gets is during to high executions per second
|
|
|
Posts:
173
Registered:
07/06/98
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 8:42 AM
in response to: jinyu
|
|
|
|
Yep, in this case the hardware is just undersized for given workload... The "buy more hardware" end point in tuning process is a perfectly valid in some cases....
|
|
|
Posts:
614
Registered:
01/15/09
|
|
|
|
Re: how to troubleshooting high sys cpu usage ???
Posted:
Jun 28, 2009 9:49 AM
in response to: jinyu
|
|
|
The sql is very hard to tune,because the avg buffer gets per sql is very low,most are below 10 buffer gets per sql, the high buffer gets is during to high executions per second
Are such high execution rates really needed? Suppose you have a suboptimal procedure(s), which run row-by-row processing - then it is reasonable to rewrite it to a single SQL. |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
