RE: Long Parse Time

> 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.

Yep, there's always the reality factor you need to account for. If I can't access the OS (nor have DBA access) then of course I won't tell the client that "I'm done, your problem is unsolvable" but I just come up with next best approach under the circumstances. But if I DO have OS/DBA access then I won't bother with the guess-based approaches much, but will go and see what's going on myself.

>
> 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

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 :)

I appreciate that there are extremes, but I still stand with that if parsing takes tens of seconds of minutes of CPU time to complete, then it's either a bug or there are incorrect optimizer settings in use. One example is the cost based query transformation. It's a great thing but when implemented then apparently the CBO developers didn't foresee all corner cases and because of that sometimes parsing takes way too long. But this shortcoming is a software design or implementation bug and should be fixed in a future release.

> 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/SiebInstUNI
X/SiebInstCOM_RDBMS16.html), or > "_optimizer_cost_based_transformation" etc. to have a
> supported configuration.
>

Yeah, I remember one Oracle Apps 10.7 -> 11.5 upgrade where we moved from Oracle 7.3 to 9.0 in the process and I forgot to set the optimizer_max_permutations from 80000 to 2000 (or 1000 whichever the requirement was). We ended up with 30 sec parse times for frequently used selects with 12-way joins. That's an example where we didn't have an optimizer bug but just had wrong values for an optimizer parameter.

I'm sure that even more CBO optimizations need to be disabled should Siebel come up with 200-table joins in their next release ;-)

> 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?

Thanks,

--
Regards,
Tanel Poder
http://blog.tanelpoder.com

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