Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Tuning question
rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0301170500.148f1cdc_at_posting.google.com>...
> First off, my explanation is going to be a bit vague since I dont have
> alot of details. However, I hope its enough to get an opinion.
>
> I was talking to a couple of DBA's and on their project they stated
> that they often have to use a hint to force the RBO. They said that
> this happens when querying off of layered views. My best guess on why
> this is so, is that the analyzer will gather statistics off the
> tables, these statistics may not be entirely valid for views made on
> top of views, so when the CBO is used, it is using poor statistics.
>
> I have seen in some books that if this happens you should consider
> re-writing your queries so that they directly access the tables. This
> could be very time consuming.
>
> Just looking for some general comments on this?
This is happening because CBO, in most cases, just can't find good execution plan for a query which access many tables. In my experience, the threshold is 7 tables. Check optimizer_max_permutations in init.ora first, it should be as big as possible (80000 for 8.1.7). If it doesn't help, you'll have to tune views manually. If RULE hint helps, great, you solved the problem. Received on Fri Jan 17 2003 - 12:52:16 CST