Re: More explain plan cost can improve performance ?
Date: Tue, 20 Apr 2010 02:15:48 -0700 (PDT)
Message-ID: <d83317b2-fd6c-4340-9401-01847fabfa7d_at_l37g2000vbd.googlegroups.com>
On Apr 19, 12:12 pm, "aman.oracle.dba" <aman.oracle...._at_gmail.com> wrote:
> Same tables, same data and same output; cost of first query is shown
> as 6822 but only 255 in second; But 2nd query is taking 19 minutes and
> 1st query is taking only 3 seconds. Why oracle is behaving this this
> way. Please suggest.
First of all - costs can be compared across different queries. Cost has always been a time estimate and therefore in theory is comparable - of course the estimates of the optimizer can be bad and the estimated cost/time may have nothing to do with the actual execution time. After all, it's an estimate that might be good or bad.
For more details, see e.g. http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
Regarding the particular case: The vast difference in cost is primarily caused by the fact that the second form of the query enables the FIRST_ROWS(n) mode due to the ROWNUM < n predicate, whereas the first one uses a predicate on an analytic function output which does NOT automatically enable the FIRST_ROWS(n) mode.
So the first query is optimized using the default ALL_ROWS mode (assuming that this is your default instance / session mode) and the second one using FIRST_ROWS(300), which results in an optimization mode that is roughly based on the assumption that only a fraction of the total rows will be processed.
It would be helpful if you posted the "Predicate Information" section below the plan output so that we could see what predicates where applied in what manner to the INDEX RANGE SCAN and the TABLE ACCESS BY ROWID. Still it is rather questionable why the FIRST_ROWS(300) optimization favors an INDEX FULL SCAN instead of an INDEX RANGE SCAN of the same index if I interpret the posted output correctly.
You have partitioning involved - that might be something that leads to further complications with the FIRST_ROWS(N) mode. Also the optimization will be based on your global statistics.
Please post also your 4-digit version.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Apr 20 2010 - 04:15:48 CDT