Re: More explain plan cost can improve performance ?
Date: Tue, 20 Apr 2010 09:56:02 -0700 (PDT)
Message-ID: <b98d7179-af60-449c-81cc-10d8eec07477_at_h16g2000prf.googlegroups.com>
On Apr 20, 6:43 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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/Exper......
>
> "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.
I don't think any of us disagree here, it's just a semantics argument. The theory diverges from the observation likely because of what Ronald pointed out about first_rows - yes, you can compare costs with a sufficient degree of information about how the optimizer works and sufficient skepticism about wrong or inappropriate costing. If you don't know those details and what Ronald supplied, then no, you can't compare costs (and see Jonathan's glossary definition of costs). I would encourage everyone to learn what Ronald and Jonathan (and Tom and Carlos) have to say about it, but I wouldn't expect someone posting the level of detail in the OP to know all that. Aman is asking for help, so we should both help him with the specific issue and help him improve posting details. This can benefit many more than just Aman.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2102678500346648193
jg
-- _at_home.com is bogus. http://www.presstelegram.com/news/ci_14915388Received on Tue Apr 20 2010 - 11:56:02 CDT