Home » RDBMS Server » Performance Tuning » cpu utilizaton
|
|
|
|
|
|
|
|
| Re: cpu utilizaton [message #574265 is a reply to message #574263] |
Tue, 08 January 2013 05:58   |
 |
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 #574273 is a reply to message #574267] |
Tue, 08 January 2013 08:41 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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.
|
|
|
|
Goto Forum:
Current Time: Sat Jan 18 00:47:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
