Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 7 May 2012

Script: Database Heavy Load Information

To Analyze the DISK I/O's 
prompt SESSIONS PERFORMING HIGH I/O > 50000
select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) "Last SQL"
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and t.disk_reads > 50000
order by t.disk_reads desc;
To check INACTIVE sessions with HIGH DISK IO 
Select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and t.disk_reads > 50000
and s.status='INACTIVE'
--and s.process='1234'
order by S.PROGRAM;
Note: You can increase or decrease the disk read size according to your application load. Here in the above example we are considering > 50000 disk read is higher.
Select  substr(to_char(s.pct, '99.00'), 2) || '%'  load,  s.executions  executes,  p.sql_text
from ( select  address,  buffer_gets, executions, pct, rank() over (order by buffer_gets desc)  ranking
from ( select  address,  buffer_gets, executions, 100 * ratio_to_report(buffer_gets) over ()  pct
from sys.v_$sql
    where  command_type != 47
      )
    where
      buffer_gets > 50 * executions
     )
       s,  sys.v_$sqltext  p
   where
        s.ranking <= 5 and  p.address = s.address
        order by 1, s.address, p.piece ;
From the above script you can find the actual load information for particular SQL statements.

0 comments:

Post a Comment