RE: Long Parse Time

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Sat, 09 May 2009 22:41:30 +0200
Message-Id: <624189488_at_web.de>



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
--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 09 2009 - 15:41:30 CDT

Original text of this message