DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Top 10 worst sql statements in the sga query.. works in 8 but not 7...

  1. #1

    Question

    Hi guys,
    I have a query that returns the top 10 sql statements ordered by disk reads

    SELECT sql_text,
    executions,
    buffer_gets,
    disk_reads,
    parse_calls,
    rows_processed
    FROM (
    SELECT sql_text,
    executions,
    buffer_gets,
    disk_reads,
    parse_calls,
    rows_processed
    FROM v$sql
    ORDER BY disk_reads DESC
    )
    WHERE ROWNUM < 11
    /

    Works fine in 8, but not in 7. The reason being that the ORDER BY in the inline-view is not allowed until 8. Any ideas how I can modify this to work with 7 (without resorting to PLSQL!).

    -Bob

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    The first thought that occurs is to take the inline view out of the query, and create a standard view to replace it.

    HTH

    David.

  3. #3

    an idea

    That would work but it would mean creating database objects (the view) to run the audit report. If possible I want the report to work with any database without any 'preparatory' steps.

    I can order the inner query by unioning it with select '',0,0,0,0 from dual as this causes a sort behind the scenes. This gets me part of the way but it means I can get the "bottom 10" not the "top 10".. I will keep at it :-) although I have a feeling im going to end up doing an anonymous plsql block with a cursor :-(

    -Bob

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The use of external view will be of no help here, as you can't use ORDER BY inside a view if you can't use it inside inline view.

    As you have figured it out, you can sometimes fool Oracle to sort the output without explicitelly using ORDER BY. But the problem is that implicit sorting is always performed in ASCending order, which is suitable for BOTTOM-N querries, not for TOP-N.

    Here are two of the workarounds to get your TOP-10 query done in pre-8i, using only SQL. The first one might be very slow on large tables. On the second one you'd better use hint to force rule based optimizer, as in this mode outer join is performed with MERGE JOIN, which implicitly use sorting.

    1.) With using corelated subquery, can be very slow on large tables:

    SELECT sql_text, executions, buffer_gets, disk_reads, parse_calls, rows_processed
    FROM v$sql st
    WHERE 10 >= (SELECT COUNT(*) FROM v$sql WHERE disk_reads >= st.disk_reads)
    ORDER BY disk_reads DESC;

    2.) With the use of a "dummy" outer join to take advantage of sorting inside a merge-join:

    SELECT /*+ RULE */ sql_text, executions, buffer_gets, disk_reads, parse_calls, rows_processed
    FROM v$sql st, dual
    WHERE -1*st.disk_reads = DECODE(dual.dummy(+), 'X', NULL, NULL)
    AND rownum <= 10
    ORDER BY st.disk_reads DESC;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5

    cheers

    thanks for that - I thought there would be a way around it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width