Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Tuning question

Re: SQL Tuning question

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 17 Jan 2003 10:52:16 -0800
Message-ID: <336da121.0301171052.4b60453b@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US