I'm running oracle 8.1.5 on Solaris 2.6 box.
Right now, top command shows there is 0% CPU idle on the machine and all the processes shown in the output of the top command are oracle process.
What should I do to identify the cause of the CPU hike?
See when u view top u get the PID of the process.once u get the PID of the process, run the following query t o get the information about the Oracle user
select s.sid, p.spid,
s.serial#, s.status, s.user#, s.username, s.command, s.lockwait, s.process, s.machine, s.
terminal, s.program, s.module
from v$session s, v$process p
where s.paddr= p.addr
and p.spid = '&pid'
Once u enter get the sid and serial# enter the following sql to get what the users are executing
select sql_text from v$sqltext_with_newlines
where (hash_value,address) in(
select sql_hash_value,sql_address from v$session
where sid=&sid and serial#=&Serial)
order by address,piece
U can then findout what users are executing and if u think that the users are running adhoc queries which is consuming a lot of CPU time u can kill that session at oracle level and also at os level.
Inc ase of any help please be free to ask me at email@example.com
Rohit Nirkhe,Oracle DBA,OCP 8i
Click Here to Expand Forum to Full Width