CPU Usage

[binjovi]

Hello everybody .
Thanks for all the help, I have come up to a situation when i run my some sql statement the CPU USAGE is going 100% and stays for some time. What can be the possible reasons behing it or is their any way that i can audit or tune or can come to know which sql is taking the most cpu usage rather is their any Dictionary views by which we can find the sql statement based cpu usage. due to all this reasons the application is taking a long time to execute.

Please anybody can help me in this

Best Regards
bins

 

[jimbopalmer]

The key is to reduce disk utilization (to a lesser degree RAM utilization, but RAMM is 100,000 times faster than disk)

this sql should give you a snapshot of what has been being bad lately, it looks in the library cache to find recuring trouble statements, and returns an ID to the line.

rem * Filename : sstmt1.sql - Version 1.3
rem * Author : Craig A. Shallahamer, John Strother
rem * Original : 02-MAY-95
rem * Last Modified : 25-OCT-95 Virag Saksena
rem * Descriptnion : SQL statement activity
rem * Usage : start sstmt1.sql <min disk rds> <min buff rds>

def min_dr=&&1
def min_bg=&&2

col mod heading 'Stmt Ident' format 99999999999
col dr heading 'Disk Rds' format 999,999,999
col bg heading 'Buff Gets' format 999,999,999
col sr heading 'Sorts' format 999,999
col exe heading 'Runs' format 999,999
col loads heading 'Body Loads' format 999,999
col load heading 'Load Factor' format 999,999,999

select a.hash_value mod,
a.disk_reads dr,
a.buffer_gets bg,
a.sorts sr,
a.executions exe,
a.loads loads,
(a.disk_reads*100+a.buffer_gets)/1000 load
from v$sqlarea a
where a.disk_reads > &min_dr
and a.buffer_gets > &min_bg
order by a.disk_reads*100+a.buffer_gets desc

/


you can insert the ID into this sql to find the text causing trouble, then use explain plan on it
rem * Filename : sstmt2.sql - Version 1.3
rem * Author : Craig A. Shallahamer, Oracle USA
rem * Original : 02-MAY-95
rem * Last Modified : 04-OCT-96 Virag Saksena
rem * Descriptnion : SQL statement activity
rem * Usage : start sstmt2.sql <stmt ident>

col hv noprint
col ln heading 'Line' format 9,999
col ss heading 'SQL Statement' format A65

select hash_value hv,
piece ln,
sql_text ss
from v$sqltext
where hash_value = &stmt_ident
order by 1,2
/

in an ideal world you will be able to improve the execution by adding indexes, adding hints, or by rethinking the code. I tried to remain child-like, all I acheived was childish.