Tuesday, January 31, 2012

Top 5 Session consuming buffers in oracle database

prompt
prompt **Top 5 by Buffer Gets:
prompt

set linesize 2000
set pagesize 100
set TIMI ON
select d.sql_text sql,'''', x.sid,'''',x.serial#, '''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value  from (SELECT * FROM
(SELECT s.sid,s.serial#, s.username,s.machine, a.hash_value, a.address, s.module, s.program, a.buffer_gets  FROM
V$SQLAREA a, v$session s
WHERE a.buffer_gets > 10000 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY buffer_gets DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.buffer_gets desc, x.hash_value, d.piece;

2 comments:

  1. I really enjoy the blog.Much thanks again. Really Great.
    Very informative article post. Really looking forward to read more. Will read on…


    sap online training
    software online training
    sap sd online training
    hadoop online training
    sap-crm-online-training

    ReplyDelete
  2. I appreciate you sharing this article. Really thank you! Much obliged.
    This is one awesome blog article. Much thanks again.


    oracle online training
    sap fico online training
    dotnet online training
    qa-qtp-software-testing-training-tutorial

    ReplyDelete