RE: rule hint ignored?
- From: "McCartney, Bruce" <bruce.mccartney@xxxxxxxxxxxxxxxxx>
- To: <zhuchao@xxxxxxxxx>, <mschmitt@xxxxxxxxxxxx>
- Date: Wed, 8 Feb 2006 10:25:54 -0700
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@xxxxxxxxxxxxxxxxx
From: zhu chao
Sent: Wed 2/8/2006 10:19 AM
To: mschmitt@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@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_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/
Other related posts:
