Re: rule hint ignored?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 08 Feb 2006 10:20:54 -0700
Message-Id: <6.2.3.4.2.20060208101708.0503e2b8@pop.centrexcc.com>

At 09:05 AM 2/8/2006, Mike Schmitt wrote:

>We have the same query using the /*+ rule */ hint in DEV, TST, and
>PRD. In our TST environment in looks like the plan is being
>generated by the cost based optimizer instead.

What do you base that assertion (TST uses cbo) on? Check v$sql what optimizer IS being used for the query.

>The DEV and TST boxes were both created from copies of production a
>couple of months ago, and the data is kept partly in sync. The data
>differs in each instance by at most 5%, and stats are all up to
>date. Databases at 9206 and all three have pretty much the same
>init.ora (except open_cursors, sga, buffers etc)
>
>Since this is the same exact query (cut an pasted), does anyone have
>any suggestions on what might be causing the TST system to ignore
>the rule hint? (BTW, the RBO does give the better results, just not
>sure why rule is ignored in one instance and used in the other two)

Validate that the table and index structures for all tables used in the plan are the same, e.g. that ALL index still exist and are defined on the same columns in the same order.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

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

Received on Wed Feb 08 2006 - 11:20:54 CST