OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed
« How to find the most frequently executed SQL?

AWR Restriction? »

How to find the top SQL statements that have caused most block buffer reads?

January 16th, 2006 By kirtandesai

Select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 10000
order by buffer_gets desc;

I had to bump up the number of buffer_gets because the above statement returned a LOT of rows.

Filed under Administration, SQL, Tuning. Follow any responses to this entry through this RSS 2.0 feed.  

2 Responses to “How to find the top SQL statements that have caused most block buffer reads?”

  1. nlitchfield Says:
    January 17th, 2006 at 10:37 am

    How about this for a top 10.

    select sql_text,buffer_gets
from(
Select buffer_gets, sql_text, rownum rn
from v$sqlarea
where buffer_gets > 10000
order by buffer_gets desc)
where rn < 11
  2. kirtandesai Says:
    January 17th, 2006 at 11:07 am

    yep
    whatever fits your requirements!!!!!

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question
« How to find the most frequently executed SQL?

AWR Restriction? »