In some cases, rule hint will not work, like partition table/iot etc. Could it be your table has different DDL script in TEST and DEV/Prod? Like in TEST someone changed one of the tables to IOT/Partition etc? On 2/9/06, Mike Schmitt <mschmitt@xxxxxxxxxxxx> wrote: > > > Hi all, > > 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. > > *Part of explain plan from TEST: > > *Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=5207 Card=2031 B > ytes=89364) > 1 0 SORT (UNIQUE) (Cost=5207 Card=2031 Bytes=89364) > 2 1 HASH JOIN (Cost=5190 Card=2031 Bytes=89364) > ....................... > > *Part of explain plan from DEV, PRD > *Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=HINT: RULE > 1 0 SORT (UNIQUE) > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' > 3 2 NESTED LOOPS > ........................ > > > 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) > > QUERY: > select /*+ rule */ distinct ent.id_number from entity ent ,address adr > ,degrees deg where (ent.delete_ind = 'N' and adr.id_number = ent.id_numberand > deg.id_number = ent.id_number ) and ( ent.record_status_code = 'M' and > adr.state_code = 'LA' and deg.school_code = 'JYM' ) > > parallel query and query rewrite are turned off for all three instances > too > > Thanks > > > > > > > > -- Regards Zhu Chao www.cnoug.org