Re: More explain plan cost can improve performance ?
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 20 Apr 2010 06:43:24 -0700 (PDT)
Message-ID: <8d52c5fc-695d-490e-8f1f-dfd3e069e3fc_at_r28g2000vbi.googlegroups.com>
On Apr 20, 11:15 am, Randolf Geist <mah..._at_web.de> wrote:
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
Date: Tue, 20 Apr 2010 06:43:24 -0700 (PDT)
Message-ID: <8d52c5fc-695d-490e-8f1f-dfd3e069e3fc_at_r28g2000vbi.googlegroups.com>
On Apr 20, 11:15 am, Randolf Geist <mah..._at_web.de> wrote:
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
"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. "
But this is only 'in theory'.
I agree that 'cost'='time'.
IMHO Costs are related to PLANS and only indirectly related to queries. Thus 'in theory' you could compare the cost for a plan for a query to another cost for another plan for another query.
Cheers.
Carlos. Received on Tue Apr 20 2010 - 08:43:24 CDT