Re: Rule Based Optimization
From: Terry Coccoli <request_at_ifneeded.com>
Date: Mon, 05 Apr 2004 22:09:59 GMT
Message-ID: <XOkcc.12498199$Of.2086398_at_news.easynews.com>
>
>
> If you throw any hint to a query (except "RULE"), cost-based
> optimizing is triggered. In that case, since you probably don't have
> any stats for your tables, Oracle will try to make up these stats by
> itself, using criteria such as number of extents used by a table (to
> estimate its size), ... Oracle usually does a decent job at making up
> these stats (from what I've seen), but of course it would be much
> better to have real up-to-date stats if you intend to use CBO for some
> queries. If you decide to go that route (use hints and gather the
> stats), make sure that OPTIMIZER_MODE (for the instance running
> Siebel) is RULE (and not CHOOSE), or otherwise CBO will be used for
> all the queries (including the ones from the Siebel application)
> running against the database, and your Siebel TAM will slap your
> fingers.
>
> Daniel Roy
> IBM
> Siebel/Oracle Consultant
Date: Mon, 05 Apr 2004 22:09:59 GMT
Message-ID: <XOkcc.12498199$Of.2086398_at_news.easynews.com>
Daniel Roy wrote:
>>What happens if you throw in a hint like FIRST_ROWS? Would the >>optimizer accept the hint ?
>
>
> If you throw any hint to a query (except "RULE"), cost-based
> optimizing is triggered. In that case, since you probably don't have
> any stats for your tables, Oracle will try to make up these stats by
> itself, using criteria such as number of extents used by a table (to
> estimate its size), ... Oracle usually does a decent job at making up
> these stats (from what I've seen), but of course it would be much
> better to have real up-to-date stats if you intend to use CBO for some
> queries. If you decide to go that route (use hints and gather the
> stats), make sure that OPTIMIZER_MODE (for the instance running
> Siebel) is RULE (and not CHOOSE), or otherwise CBO will be used for
> all the queries (including the ones from the Siebel application)
> running against the database, and your Siebel TAM will slap your
> fingers.
>
> Daniel Roy
> IBM
> Siebel/Oracle Consultant
Thanks for the headsup, Roy. I think you've given me a couple of questions that I need to discuss with the DBA. Received on Tue Apr 06 2004 - 00:09:59 CEST