RE: rule hint ignored?

From: McCartney, Bruce <bruce.mccartney_at_dbinfosystems.com>
Date: Wed, 8 Feb 2006 10:25:54 -0700
Message-ID: <C0FA7AA7-3611-4F24-8D5D-834AA532687C@mimectl>

also, if the underlying object(s) have a nonzero parallel degree; the rule hint can be ignored...

Bruce McCartney |DBIS |É403 615 3350 | bruce.mccartney_at_dbinfosystems.com

From: zhu chao
Sent: Wed 2/8/2006 10:19 AM
To: mschmitt_at_uchicago.edu
Cc: oracle-l_at_freelists.org
Subject: Re: rule hint ignored?

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_at_uchicago.edu> 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_number and 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
http://www.cnoug.org/

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 08 2006 - 11:25:54 CST