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

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

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.

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

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,

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