Re: rule hint ignored?

  • From: Kevin Lidh <kevin.lidh@xxxxxxxxx>
  • To: mschmitt@xxxxxxxxxxxx
  • Date: Wed, 8 Feb 2006 09:44:36 -0700

I think if you've gathered stats, the optimizer will go the cost route.  I'm
guessing in production and dev, you don't have stats on the tables in the
query.

On 2/8/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
>
>
>
>
>
>
>
>

Other related posts: