RE: Long Parse Time

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Sat, 09 May 2009 17:47:44 +0200
Message-Id: <624022031_at_web.de>

> Again, if parsing takes minutes or tens of seconds of CPU time then you're
> hitting a bug or someone has fiddled with undocumented parameters! Parsing
> should not take that long!

Tanel,

I absolutely agree that the systematic approach is the way to go if you want to be sure about the root cause of the issue.

But having SQL-level access only to a locked-down production box and before jumping through all the hoops to get OS-level access to run the actual process trace I think the "low hanging fruits" as you've called them are definitely worth a try, since one quite quickly can work out if some of the "usual suspects" (Cost Based Query Transformations, View Merging, Btree to bitmaps etc.) apply.

Having said that, there are definitely real-world cases (see e.g. http://forums.oracle.com/forums/thread.jspa?messageID=3166431&#3166431) where Oracle requires several seconds of parsing even with the most simple and straightforward normalized primary/foreign key relationships, and probably due to that some vendors at least in some versions of their products actually require you to fiddle with the optimizer related (undocumented) parameters like "(_)optimizer_max_permutations" (see e.g. http://download.oracle.com/docs/cd/E05553_01/books/SiebInstUNIX/SiebInstCOM_RDBMS16.html), or "_optimizer_cost_based_transformation" etc. to have a supported configuration.

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.

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/

---

Verschicken Sie SMS direkt vom Postfach aus - in alle deutschen und viele ausländische Netze zum gleichen Preis!
https://produkte.web.de/webde_sms/sms

--
http://www.freelists.org/webpage/oracle-l

Received on Sat May 09 2009 - 10:47:44 CDT