Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Expense of 'over ... partition by'

Re: Expense of 'over ... partition by'

From: <Jared.Still_at_radisys.com>
Date: Fri, 05 Dec 2003 08:29:25 -0800
Message-ID: <F001.005D8F0B.20031205082925@fatcity.com> 





Content-Type: text/plain; charset="us-ascii"



Thanks Jonathan.



Lack of some decent docs and my inexperience with analytics led
me to the (partition by 1).  Someone mentioned that Tom Kyte's book
has a good chapter on them, so I'll go look that up. 



You're right, the group by is unnecessary in that query, it's an artifact
of an earlier incarnation of the query.  Removing it seemed to make
little difference in 



Larry Elkins pointed out that the SQL could be greatly simplified via
the ratio_to_report() function. It appears below.



The standard SQL approach is more appropriate for this, I agree.
I'm just trying to use newer functionality as much as possible so
as to be familiar with it.



select


   low_optimal_size/1024 low_optimal_size_kb

   , (high_optimal_size+1)/1024 high_optimal_size_kb
   , optimal_executions
   , onepass_executions
   , multipasses_executions
   , total_executions
   , optimal_executions / total_executions * 100 pct_optimal_executions
   , onepass_executions / total_executions * 100 pct_onepass_executions
   , multipasses_executions / total_executions * 100 


pct_multipasses_executions


   , ratio_to_report(optimal_executions) over ( ) * 100 
pct_total_optimal_executions


from v$sql_workarea_histogram


where total_executions != 0


order by low_optimal_size_kb



Here is the latest run_stats from it - quite an improvement with 
ratio_to_report()  ( RUN1 )


NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.Consistent RBA                              1          0         -1
LATCH.lgwr LWN SCN                                1          0         -1
LATCH.mostly latch-free SCN                       1          0         -1
LATCH.redo allocation                            19         18         -1
STAT...calls to kcmgcs                            7          6         -1
STAT...consistent gets                            4          5          1
STAT...cursor authentications                     0          1          1
STAT...deferred (CURRENT) block cleanout          4          3         -1


 applications


STAT...redo entries                              17         18          1
STAT...enqueue requests                           1          0         -1
STAT...db block gets                             28         29          1
STAT...consistent gets - examination              0          1          1
STAT...cleanout - number of ktugct calls          0          1          1
STAT...calls to get snapshot scn: kcmgss       1006       1005         -1
STAT...active txn count during cleanout           0          1          1
LATCH.undo global data                            1          0         -1
LATCH.library cache pin                        2012       2010         -2
STAT...session logical reads                     32         34          2
LATCH.redo writing                                2          0         -2
LATCH.cache buffers chains                      102        105          3
STAT...recursive cpu usage                       32         29         -3
LATCH.library cache pin allocation                4          8          4
LATCH.messages                                    6          0         -6
LATCH.shared pool                              1001       1008          7
LATCH.library cache                            2014       2022          8
STAT...redo size                              27084      27496        412
LATCH.SQL memory manager workarea list l          0       2000       2000


atch




27 rows selected.




Jared








"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: ml-errors_at_fatcity.com


 12/05/2003 01:14 AM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Expense of 'over ... partition by'







Jared,



I think what you've discovered is just a repeat
of the fact that different functionality is appropriate
in different circumstances.



Imagine replacing your v$sql_workarea_histogram
with a chunky SQL statement that crunched through
a massive table producing a small result set.



In those circumstances, your analytic approach would
sort a small set twice having done one big crunch. With
the group by approach, you would have to crunch the big
data set twice.  I know which option would be cheaper.
(You then have to wonder whether you could produce
the small result set using subquery factoring 'with subquery'
as another possible optimisation strategy).



BTW - did you notice how Oracle didn't do a sort for
the order by in the GROUP BY example, because
the optimizer could infer that the data had already been
ordered by the GROUP BY ?  That's the reason why
your GROUP BY example did less sorting.  (I'm not
sure you need the GROUP BY, though I may be missing
something).



BTW-2:  in the analytic clause, the (partition by 1) is not
necessary, you can write:


>                 , sum(optimal_executions) over ( )





Regards



Jonathan Lewis


http://www.jlcomp.demon.co.uk



  The educated person is not the person


  who can answer the questions, but the


  person who can question the answers -- T. Schick Jr




One-day tutorials:


http://www.jlcomp.demon.co.uk/tutorial.html




Three-day seminar:


see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November




The Co-operative Oracle Users' FAQ


http://www.jlcomp.demon.co.uk/faq/ind_faq.html






> While working on some scripts to monitor PGA usage on 9i, I came across
> something interesting while experimenting with different forms of SQL.
>
> I have recently been forcing myself to make use of 'OVER..PARTITION BY' 


in


> SQL so as to be more comfortable in using it.  Can't add new tools to 
the


> box until I


> know how to use them.  :)  Yes, I know I should have been using them 
long

> ago.
>
> Anyway, I thought it might be interesting to compare the forms of SQL 


with

> and
> without the use of OVER...PARTITION BY.
>
> This SQL can be run on any instance that has PGA_AGGREGATE_TARGET set.
>
> Here is the SQL using OVER:
>
> select
>         low_optimal_size_kb
>         , high_optimal_size_kb
>         , optimal_executions
>         , onepass_executions
>         , multipasses_executions
>         , total_executions
>         , optimal_executions / sum_optimal_executions * 100
> pct_optimal_executions
> from (
>         select
>                 low_optimal_size/1024 low_optimal_size_kb


>                 , (high_optimal_size+1)/1024 high_optimal_size_kb

>                 , optimal_executions

>                 , onepass_executions

>                 , multipasses_executions

>                 , total_executions

>                 , sum(optimal_executions) over ( partition by 1 )

> sum_optimal_executions
>         from v$sql_workarea_histogram
>         where total_executions != 0
> ) a
> order by low_optimal_size_kb
> /
>
> and here is the SQL using good old GROUP BY
>
> select
>         low_optimal_size_kb
>         , high_optimal_size_kb
>         , optimal_executions
>         , onepass_executions
>         , multipasses_executions
>         , total_executions
>         , optimal_executions / sum_optimal_executions * 100
> pct_optimal_executions
> from (
>         select
>                 h.low_optimal_size/1024 low_optimal_size_kb


>                 , (h.high_optimal_size+1)/1024 high_optimal_size_kb

>                 , h.optimal_executions

>                 , h.onepass_executions

>                 , h.multipasses_executions

>                 , h.total_executions

>                 , hs.sum_optimal_executions

>         from v$sql_workarea_histogram h,
>                 (
>                         select sum(optimal_executions)
> sum_optimal_executions
>                         from v$sql_workarea_histogram
>                 ) hs
>         where h.total_executions != 0
>         group by h.low_optimal_size/1024
>                 ,(h.high_optimal_size+1)/1024


>                 , h.optimal_executions

>                 , h.onepass_executions

>                 , h.multipasses_executions

>                 , h.total_executions

>                 , hs.sum_optimal_executions

> ) a
> order by low_optimal_size_kb
> /
>
>
> The new version is significantly simpler.
>
> It then seemed that it might be interesting to compare the performance 
and
> scalability of the two methods.
>
> This is where it gets interesting.
>
>
> 16:10:47 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> @run_stats
>
> NAME                                           RUN1       RUN2 DIFF
> ---------------------------------------- ---------- ---------- 
----------
> LATCH.lgwr LWN SCN                                1          0 -1
> LATCH.mostly latch-free SCN                       1          0 -1
> LATCH.undo global data                            1          0 -1
> STAT...active txn count during cleanout           1          0 -1
> STAT...consistent gets                            5          4 -1
> STAT...db block gets                             28         29 1
> STAT...enqueue requests                           1          0 -1
> STAT...redo entries                              17         18 1
> STAT...deferred (CURRENT) block cleanout          4          3 -1
>  applications
>
> STAT...consistent gets - examination              1          0 -1
> STAT...cleanout - number of ktugct calls          1          0 -1
> STAT...calls to kcmgcs                            7          6 -1
> STAT...calls to get snapshot scn: kcmgss       1006       1005 -1
> LATCH.Consistent RBA                              2          0 -2
> STAT...recursive cpu usage                       29         31 2
> LATCH.redo allocation                            20         18 -2
> LATCH.cache buffers chains                      102        105 3
> LATCH.redo writing                                4          0 -4
> LATCH.library cache                            2014       2008 -6
> LATCH.library cache pin                        2012       2006 -6
> LATCH.messages                                    8          0 -8
> STAT...redo size                              27096      27508 412
> STAT...sorts (memory)                          1004       2004 1000
> LATCH.SQL memory manager workarea list l          0       2000 2000
> atch
>
> STAT...workarea executions - optimal           2008       4008 2000
> STAT...sorts (rows)                            6112      10112 4000
>
> 26 rows selected.
>
> RUN1 is the the GROUP BY SQL
> RUN2 is the OVER...PARTITION BY SQL
>
> The OVER version of the SQL is significantly more expensive in terms of
> sorting and latching.
>
> Has anyone else noticed this?




>


> Or perhaps my use of OVER..PARTITION BY needs some optimization, which 
is

> clearly
> in the realm of possibility.  :)
>
> Jared
>
>
>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



--=_alternative 005A7D0F88256DF3_=
Content-Type: text/html; charset="us-ascii"


<br><font size=2 face="sans-serif">Thanks Jonathan.</font>
<br>
<br><font size=2 face="sans-serif">Lack of some decent docs and my inexperience with analytics led</font>
<br><font size=2 face="sans-serif">me to the (partition by 1). &nbsp;Someone mentioned that Tom Kyte's book</font>
<br><font size=2 face="sans-serif">has a good chapter on them, so I'll go look that up. </font>
<br>
<br><font size=2 face="sans-serif">You're right, the group by is unnecessary in that query, it's an artifact</font>
<br><font size=2 face="sans-serif">of an earlier incarnation of the query. &nbsp;Removing it seemed to make</font>
<br><font size=2 face="sans-serif">little difference in </font>
<br>
<br><font size=2 face="sans-serif">Larry Elkins pointed out that the SQL could be greatly simplified via</font>
<br><font size=2 face="sans-serif">the ratio_to_report() function. It appears below.</font>
<br>
<br><font size=2 face="sans-serif">The standard SQL approach is more appropriate for this, I agree.</font>
<br><font size=2 face="sans-serif">I'm just trying to use newer functionality as much as possible so</font>
<br><font size=2 face="sans-serif">as to be familiar with it.</font>
<br>
<br><font size=2 face="sans-serif">select</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;low_optimal_size/1024 low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, (high_optimal_size+1)/1024 high_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, optimal_executions / total_executions * 100 pct_optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, onepass_executions / total_executions * 100 pct_onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, multipasses_executions / total_executions * 100 pct_multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp;, ratio_to_report(optimal_executions) over ( ) * 100 pct_total_optimal_executions</font>
<br><font size=2 face="sans-serif">from v$sql_workarea_histogram</font>
<br><font size=2 face="sans-serif">where total_executions != 0</font>
<br><font size=2 face="sans-serif">order by low_optimal_size_kb</font>
<br>
<br><font size=2 face="sans-serif">Here is the latest run_stats from it - quite an improvement with ratio_to_report() &nbsp;( RUN1 )</font>
<br>
<br><font size=2 face="Courier">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RUN1 &nbsp; &nbsp; &nbsp; RUN2 &nbsp; &nbsp; &nbsp; DIFF</font>
<br><font size=2 face="Courier">---------------------------------------- ---------- ---------- ----------</font>
<br><font size=2 face="Courier">LATCH.Consistent RBA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.lgwr LWN SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.mostly latch-free SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.redo allocation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...calls to kcmgcs &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...consistent gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...cursor authentications &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...deferred (CURRENT) block cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">&nbsp;applications</font>
<br>
<br><font size=2 face="Courier">STAT...redo entries &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...enqueue requests &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...db block gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28 &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...consistent gets - examination &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...cleanout - number of ktugct calls &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...calls to get snapshot scn: kcmgss &nbsp; &nbsp; &nbsp; 1006 &nbsp; &nbsp; &nbsp; 1005 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...active txn count during cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">LATCH.undo global data &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.library cache pin &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2012 &nbsp; &nbsp; &nbsp; 2010 &nbsp; &nbsp; &nbsp; &nbsp; -2</font>
<br><font size=2 face="Courier">STAT...session logical reads &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32 &nbsp; &nbsp; &nbsp; &nbsp; 34 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2</font>
<br><font size=2 face="Courier">LATCH.redo writing &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -2</font>
<br><font size=2 face="Courier">LATCH.cache buffers chains &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp; &nbsp; &nbsp; &nbsp;105 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</font>
<br><font size=2 face="Courier">STAT...recursive cpu usage &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32 &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; -3</font>
<br><font size=2 face="Courier">LATCH.library cache pin allocation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4</font>
<br><font size=2 face="Courier">LATCH.messages &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -6</font>
<br><font size=2 face="Courier">LATCH.shared pool &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1001 &nbsp; &nbsp; &nbsp; 1008 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7</font>
<br><font size=2 face="Courier">LATCH.library cache &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014 &nbsp; &nbsp; &nbsp; 2022 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8</font>
<br><font size=2 face="Courier">STAT...redo size &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27084 &nbsp; &nbsp; &nbsp;27496 &nbsp; &nbsp; &nbsp; &nbsp;412</font>
<br><font size=2 face="Courier">LATCH.SQL memory manager workarea list l &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; 2000 &nbsp; &nbsp; &nbsp; 2000</font>
<br><font size=2 face="Courier">atch</font>
<br>
<br>
<br><font size=2 face="Courier">27 rows selected.</font>
<br>
<br>
<br><font size=2 face="Courier">Jared</font>
<br><font size=2 face="sans-serif"><br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>&quot;Jonathan Lewis&quot; &lt;jonathan_at_jlcomp.demon.co.uk&gt;</b></font>
<br><font size=1 face="sans-serif">Sent by: ml-errors_at_fatcity.com</font>
<p><font size=1 face="sans-serif">&nbsp;12/05/2003 01:14 AM</font>
<br><font size=2 face="sans-serif">&nbsp;</font><font size=1 face="sans-serif">Please respond to ORACLE-L</font>
<br>
<td><font size=1 face="Arial">&nbsp; &nbsp; &nbsp; &nbsp; </font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; To: &nbsp; &nbsp; &nbsp; &nbsp;Multiple recipients of list ORACLE-L &lt;ORACLE-L_at_fatcity.com&gt;</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp; &nbsp;</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; Subject: &nbsp; &nbsp; &nbsp; &nbsp;Re: Expense of 'over ... partition by'</font></table>
<br>
<br>
<br><font size=2 face="Courier New"><br>
Jared,<br>
<br>
I think what you've discovered is just a repeat<br>
of the fact that different functionality is appropriate<br>
in different circumstances.<br>
<br>
Imagine replacing your v$sql_workarea_histogram<br>
with a chunky SQL statement that crunched through<br>
a massive table producing a small result set.<br>
<br>
In those circumstances, your analytic approach would<br>
sort a small set twice having done one big crunch. With<br>
the group by approach, you would have to crunch the big<br>
data set twice. &nbsp;I know which option would be cheaper.<br>
(You then have to wonder whether you could produce<br>
the small result set using subquery factoring 'with subquery'<br>
as another possible optimisation strategy).<br>
<br>
BTW - did you notice how Oracle didn't do a sort for<br>
the order by in the GROUP BY example, because<br>
the optimizer could infer that the data had already been<br>
ordered by the GROUP BY ? &nbsp;That's the reason why<br>
your GROUP BY example did less sorting. &nbsp;(I'm not<br>
sure you need the GROUP BY, though I may be missing<br>
something).<br>
<br>
BTW-2: &nbsp;in the analytic clause, the (partition by 1) is not<br>
necessary, you can write:<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , sum(optimal_executions) over ( )<br>
<br>
<br>
Regards<br>
<br>
Jonathan Lewis<br>
http://www.jlcomp.demon.co.uk<br>
<br>
 &nbsp;The educated person is not the person<br>
 &nbsp;who can answer the questions, but the<br>
 &nbsp;person who can question the answers -- T. Schick Jr<br>
<br>
<br>
One-day tutorials:<br>
http://www.jlcomp.demon.co.uk/tutorial.html<br>
<br>
<br>
Three-day seminar:<br>
see http://www.jlcomp.demon.co.uk/seminar.html<br>
____UK___November<br>
<br>
<br>
The Co-operative Oracle Users' FAQ<br>
http://www.jlcomp.demon.co.uk/faq/ind_faq.html<br>
<br>
<br>
----- Original Message ----- <br>
To: &quot;Multiple recipients of list ORACLE-L&quot; &lt;ORACLE-L_at_fatcity.com&gt;<br>
Sent: Friday, December 05, 2003 12:24 AM<br>
<br>
<br>
&gt; While working on some scripts to monitor PGA usage on 9i, I came across<br>
&gt; something interesting while experimenting with different forms of SQL.<br>
&gt;<br>
&gt; I have recently been forcing myself to make use of 'OVER..PARTITION BY' in<br>
&gt; SQL so as to be more comfortable in using it. &nbsp;Can't add new tools to the<br>
&gt; box until I<br>
&gt; know how to use them. &nbsp;:) &nbsp;Yes, I know I should have been using them long<br>
&gt; ago.<br>
&gt;<br>
&gt; Anyway, I thought it might be interesting to compare the forms of SQL with<br>
&gt; and<br>
&gt; without the use of OVER...PARTITION BY.<br>
&gt;<br>
&gt; This SQL can be run on any instance that has PGA_AGGREGATE_TARGET set.<br>
&gt;<br>
&gt; Here is the SQL using OVER:<br>
&gt;<br>
&gt; select<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , high_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions</font>
<br><font size=2 face="Courier New">&gt; &nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , total_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions / sum_optimal_executions * 100<br>
&gt; pct_optimal_executions<br>
&gt; from (<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; select<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size/1024 low_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , (high_optimal_size+1)/1024 high_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , total_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , sum(optimal_executions) over ( partition by 1 )<br>
&gt; sum_optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; where total_executions != 0<br>
&gt; ) a<br>
&gt; order by low_optimal_size_kb<br>
&gt; /<br>
&gt;<br>
&gt; and here is the SQL using good old GROUP BY<br>
&gt;<br>
&gt; select<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , high_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , total_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions / sum_optimal_executions * 100<br>
&gt; pct_optimal_executions<br>
&gt; from (<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; select<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; h.low_optimal_size/1024 low_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , (h.high_optimal_size+1)/1024 high_optimal_size_kb<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.onepass_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.multipasses_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.total_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , hs.sum_optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram h,<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select sum(optimal_executions)<br>
&gt; sum_optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) hs<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; where h.total_executions != 0<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; group by h.low_optimal_size/1024<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,(h.high_optimal_size+1)/1024<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.optimal_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.onepass_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.multipasses_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.total_executions<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , hs.sum_optimal_executions<br>
&gt; ) a<br>
&gt; order by low_optimal_size_kb<br>
&gt; /<br>
&gt;<br>
&gt;<br>
&gt; The new version is significantly simpler.<br>
&gt;<br>
&gt; It then seemed that it might be interesting to compare the performance and<br>
&gt; scalability of the two methods.<br>
&gt;<br>
&gt; This is where it gets interesting.<br>
&gt;<br>
&gt;<br>
&gt; 16:10:47 rsysdevdb.radisys.com - jkstill_at_dv03 SQL&gt; @run_stats<br>
&gt;<br>
&gt; NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RUN1 &nbsp; &nbsp; &nbsp; RUN2 &nbsp; &nbsp; &nbsp; DIFF<br>
&gt; ---------------------------------------- ---------- ---------- ----------<br>
&gt; LATCH.lgwr LWN SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; LATCH.mostly latch-free SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; LATCH.undo global data &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...active txn count during cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...consistent gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...db block gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28 &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1<br>
&gt; STAT...enqueue requests &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...redo entries &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1<br>
&gt; STAT...deferred (CURRENT) block cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; &nbsp;applications<br>
&gt;<br>
&gt; STAT...consistent gets - examination &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...cleanout - number of ktugct calls &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...calls to kcmgcs &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; STAT...calls to get snapshot scn: kcmgss &nbsp; &nbsp; &nbsp; 1006 &nbsp; &nbsp; &nbsp; 1005 &nbsp; &nbsp; &nbsp; &nbsp; -1<br>
&gt; LATCH.Consistent RBA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -2<br>
&gt; STAT...recursive cpu usage &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; 31 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2<br>
&gt; LATCH.redo allocation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; -2<br>
&gt; LATCH.cache buffers chains &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp; &nbsp; &nbsp; &nbsp;105 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3<br>
&gt; LATCH.redo writing &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -4<br>
&gt; LATCH.library cache &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014 &nbsp; &nbsp; &nbsp; 2008 &nbsp; &nbsp; &nbsp; &nbsp; -6<br>
&gt; LATCH.library cache pin &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2012 &nbsp; &nbsp; &nbsp; 2006 &nbsp; &nbsp; &nbsp; &nbsp; -6<br>
&gt; LATCH.messages &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -8<br>
&gt; STAT...redo size &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27096 &nbsp; &nbsp; &nbsp;27508 &nbsp; &nbsp; &nbsp; &nbsp;412<br>
&gt; STAT...sorts (memory) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1004 &nbsp; &nbsp; &nbsp; 2004 &nbsp; &nbsp; &nbsp; 1000<br>
&gt; LATCH.SQL memory manager workarea list l &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; 2000 &nbsp; &nbsp; &nbsp; 2000<br>
&gt; atch<br>
&gt;<br>
&gt; STAT...workarea executions - optimal &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008 &nbsp; &nbsp; &nbsp; 4008 &nbsp; &nbsp; &nbsp; 2000<br>
&gt; STAT...sorts (rows) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6112 &nbsp; &nbsp; &nbsp;10112 &nbsp; &nbsp; &nbsp; 4000<br>
&gt;<br>
&gt; 26 rows selected.<br>
&gt;<br>
&gt; RUN1 is the the GROUP BY SQL<br>
&gt; RUN2 is the OVER...PARTITION BY SQL<br>
&gt;<br>
&gt; The OVER version of the SQL is significantly more expensive in terms of<br>
&gt; sorting and latching.</font>
<br><font size=2 face="Courier New">&gt;<br>
&gt; Has anyone else noticed this?<br>
&gt;<br>
&gt; Or perhaps my use of OVER..PARTITION BY needs some optimization, which is<br>
&gt; clearly<br>
&gt; in the realm of possibility. &nbsp;:)<br>
&gt;<br>
&gt; Jared<br>
&gt;<br>
&gt;<br>
&gt;<br>
<br>
-- <br>
Please see the official ORACLE-L FAQ: http://www.orafaq.net<br>
-- <br>
Author: Jonathan Lewis<br>
 &nbsp;INET: jonathan_at_jlcomp.demon.co.uk<br>
<br>
Fat City Network Services &nbsp; &nbsp;-- 858-538-5051 http://www.fatcity.com<br>
San Diego, California &nbsp; &nbsp; &nbsp; &nbsp;-- Mailing list and web hosting services<br>
---------------------------------------------------------------------<br>
To REMOVE yourself from this mailing list, send an E-Mail message<br>
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<br>
the message BODY, include a line containing: UNSUB ORACLE-L<br>
(or the name of mailing list you want to be removed from). &nbsp;You may<br>
also send the HELP command for other information (like subscribing).<br>
</font>
<br>
<br>
--=_alternative 005A7D0F88256DF3_=--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Fri Dec 05 2003 - 10:29:25 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US