RE: Long Parse Time
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxx>
- Date: Sat, 09 May 2009 22:41:30 +0200
Tanel,
> You forgot to bring out one important fact from that thread ;) The thread
> was about doing 50-table joins!!! I think the original poster had an 8-table
> join :)
Apologies if it looked like it was my intention to say that, may be I wasn't
clear enough. Of course I agree that a simple 8-table join shouldn't take
seconds to parse, but you said "Parsing should not take that long, only
fractions of seconds" which sounded like a general statement to me and I just
wanted to point out that there are actually cases where parsing of queries can
take that long even without hitting any obvious bugs.
> > By the way, the parsing issue can be become more significant
> > when using the FIRST_ROWS_N optimizer modes, because in this
> > case the CBO first works out a significant part of the
> > ALL_ROWS optimization to determine the "proration factor" and
> > alternative ALL_ROWS plans and only then switches to the
> > FIRST_ROWS_N mode to basically do it all again.
>
> Hmm, I had never thought about that... Do you have any docs/references about
> how this works?
I don't think there is much documentation available yet about the specifics of
the FIRST_ROWS_N optimizer mode. I hope I get a chance to change that :-) I've
submitted a paper for the OOW 2009 called "Everything You Always Wanted to Know
About FIRST_ROWS_N But Were Afraid to Ask". Should it be selected, I'm going to
present my findings there.
One point is that the FIRST_ROWS_N optimization first has to work out the final
ALL_ROWS cardinality to determine the estimated fraction to process when
running in FIRST_ROWS_N mode, therefore a part of the ALL_ROWS optimization is
always done first. Furthermore depending on the options available (e.g.
avoiding sorts by using index-access paths) the ALL_ROWS alternatives are also
evaluated to a certain extent.
Combine this with the different cost-based transformations applied in 10g and
later you might end up with a significant overhead in parsing compared to the
ALL_ROWS plan, at least for simple queries. I haven't evaluated yet in detail
if this is still significant if the number of tables joined is high. My tests
conducted so far focus on the basics, and this finding of possible parsing
overhead is just a side note so far.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
