Home » RDBMS Server » Performance Tuning » cpu utilizaton
cpu utilizaton [message #574254] Tue, 08 January 2013 04:34 Go to next message
maninderkrb
Messages: 41
Registered: August 2012
Location: India
Member
how to troubleshoot when cpu utilizaton is high because of oracle?
Re: cpu utilizaton [message #574261 is a reply to message #574254] Tue, 08 January 2013 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Like with any other program.

Regards
Michel
Re: cpu utilizaton [message #574262 is a reply to message #574261] Tue, 08 January 2013 05:04 Go to previous messageGo to next message
maninderkrb
Messages: 41
Registered: August 2012
Location: India
Member
i mean sir...

what are step by step procedure when oracle takes 100 % cpu...
how to get the queries that takes cpu?
how can be awr usefull at that time?
what else we can check?
Re: cpu utilizaton [message #574263 is a reply to message #574262] Tue, 08 January 2013 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$sesstat for "%CPU%" statistics, wait a while, query again and see which sessions are taking much CPU.

Regards
Michel
Re: cpu utilizaton [message #574265 is a reply to message #574263] Tue, 08 January 2013 05:58 Go to previous messageGo to next message
maninderkrb
Messages: 41
Registered: August 2012
Location: India
Member
i used to trouble shoot as following steps

1. run the following script to find the top queries
########################################################
set pages 1000
set lines 300
set trim on
set trims on
select /*+ hash(v$session) */ sw.event,
sw.seq#,
sw.p1,
sw.p2,
sw.p3,
sw.sid,
s.serial#,
s.osuser,
s.username,
s.logon_time,
sw.state,
sw.wait_time,
sw.seconds_in_wait sec,
s.program,
s.process,
sa.hash_value,
sa.sql_text sql,
sa.buffer_gets * (8192/1024/1024),
sa.executions
from
v$session_wait sw,
v$session s,
v$sqlarea sa
where
sw.sid = s.sid (+)
and s.sql_address = sa.address
and sw.event not in ('rdbms ipc message','smon timer','pmon timer','lock manager wait for remote message',
'pipe get','null event','SQL*Net message from client', 'PX Idle Wait','single-task message', 'wakeup time manager')
order by sa.buffer_gets * (8192/1024/1024)
/
########################################################################

2> check for locking with followning
################################################
SELECT DECODE(request,0,'Holder: ',' Waiter: ')||sid sess,id1,id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
/
###########################################
3> take out the awr report of last hour
and study it for top cpu consumption.

###################################

are the queries only responsible for cpu utilization??
what other things do we need to check and modify for optimizing cpu utilization

###################################

i used this as said by you ..
but we cant kill that session.
what to do?

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;




Re: cpu utilizaton [message #574267 is a reply to message #574265] Tue, 08 January 2013 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: cpu utilizaton [message #574273 is a reply to message #574267] Tue, 08 January 2013 08:41 Go to previous message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
checks can also be done at the OS level; but since you refuse to follow Posting Guideline we don't know OS name or version.
Previous Topic: num_rows on user_indexes vs table_indexes
Next Topic: AWR Baseline in RAC
Goto Forum:
  


Current Time: Sat Dec 20 19:20:31 CST 2014

Total time taken to generate the page: 0.08549 seconds