RE: query for top 10 sql?
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2004 10:39:05 -0500
Obviously in this case (second out of 3) it doesn't make any difference.
But, if asked for top 10 out of 1000:
- Without optimization: all 1000 would have been sorted;
- With optimization: find top 10 and stop, no need to sort the rest 990.
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Thursday, March 11, 2004 9:16 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: query for top 10 sql?
Second out of 3? How do you sort them?
On 03/11/2004 08:58:44 AM, Igor Neyman wrote:
> T.Kyte explains this optimization very well in his (second) book.
>
> Sorting stops as soon as top-N (where N specified by ROWNUM) elements
of
> "sorted" array are filled.
>
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
> Sent: Wednesday, March 10, 2004 11:04 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: query for top 10 sql?
>
> A slight correction, below...
>
> on 3/10/04 7:07 PM, Mark Richard at mrichard@xxxxxxxxxxxxxxxxx wrote:
>
> >
> > Originally I thought the same but then noticed it was "query for" &
> "top
> > ten sql" - which I interpreted as a query about SQL (similar to the
> > Statspack type results).
> >
> > As far as the SQL Server "TOP" equivalent - I have not seen such a
> graceful
> > solution in Oracle. One approach I have seen includes using an
ORDER
> BY in
> > a subquery and applying "where rownum <= 10" in the outer query - if
> the
> > inner query returns a large result set then a big sort is required.
>
> Oracle has an optimization on this operation that halts the sort after
> the
> first N rows (where "N" is the number specified in "rownum <= N") are
> retrieved from the inner query.
>
> The following test case may help illustrate:
>
> SQL> select count(*)
> 2 from dba_objects;
>
> COUNT(*)
> ----------
> 4905
> SQL>
> SQL> select count(distinct object_type)
> 2 from (select object_id, object_type, object_name
> 3 from dba_objects
> 4 order by 1, 2, 3)
> 5 where rownum <= 1000;
>
> COUNT(DISTINCTOBJECT_TYPE)
> --------------------------
> 10
> SQL>
> SQL> select s.value - &&V_VALUE sort_rows, s.value
> 2 from v$mystat s, v$statname n
> 3 where s.statistic# = n.statistic# and n.name = 'sorts
(rows)';
>
> SORT_ROWS
> ----------
> 6185
> SQL>
> SQL> select count(distinct object_type)
> 2 from (select object_id, object_type, object_name
> 3 from dba_objects
> 4 order by 1, 2, 3)
> 5 where rownum <= 100;
>
> COUNT(DISTINCTOBJECT_TYPE)
> --------------------------
> 3
> SQL>
> SQL> select s.value - &&V_VALUE sort_rows, s.value
> 2 from v$mystat s, v$statname n
> 3 where s.statistic# = n.statistic# and n.name = 'sorts
(rows)';
>
> SORT_ROWS
> ----------
> 5285
> SQL>
> SQL> select count(distinct object_type)
> 2 from (select object_id, object_type, object_name
> 3 from dba_objects
> 4 order by 1, 2, 3)
> 5 where rownum <= 10;
>
> COUNT(DISTINCTOBJECT_TYPE)
> --------------------------
> 3
> SQL>
> SQL> select s.value - &&V_VALUE sort_rows, s.value
> 2 from v$mystat s, v$statname n
> 3 where s.statistic# = n.statistic# and n.name = 'sorts
(rows)';
>
> SORT_ROWS
> ----------
> 5195
>
> Notice that when the query returned 1,000 rows, the total number of
rows
> sorted by the operation was 6,185. When the number of rows returned
by
> the
> query was reduced by 900 to 100, then the total number of rows sorted
> reduced correspondingly by 900 to 5,285. When the number of rows
> returned
> by the query was further reduced by 90 to 10, then the total number of
> rows
> sorted also reduced by 90 to 5,195.
>
> Mind you, I didn't see the number of logical I/Os change for any
> operation,
> so the initial scan of the inner query certainly did not change (which
> is to
> be expected).
>
> But the secondary operation of sorting appears to have been reduced.
A
> small optimization in this small test case, but if this was a huge
sort
> in
> which significant I/O is performed from disk in the temporary
> tablespace,
> then perhaps this optimization would show more pronounced
improvement...
>
> Well, you can't expect "top N" (or "bottom N") operations without a
full
> scan of the problem-set as well at least one sort operation on the
> results.
> So, the optimization isn't as trivial as it might seem.
>
>
> > second approach might be to use an analytical function like RANK()
or
> > ROW_NUM() - but again a large sort is often required. A third
> approach
> > relies on using index ordering (with a hint typically for force
index
> > traversal) and "where rownum <= 10" applied on the original query -
> this
> > depends on a suitable index being available and is quite risky (for
> > example, if the index is dropped the query won't fail but will
return
> a
> > very incorrect result without warning).
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
