I could not get Outlook to prefix your message properly -
grrr. Comments are at the top which may make reading them hard.
I have
tried to setup a simple demo that hints are not 'suggestions' sorry if this
becomes long - this is all 9.2 but should apply to 8i and later versions as
well. The sql I issue comes first
SQL> create user niall identified by
niall;
User created.
SQL> alter user niall default tablespace
users
2 temporary tablespace temp
3 quota
unlimited on users;
User altered.
SQL> grant create
session,
2 alter session,
3 create
table,
4 select_catalog_role to niall;
Grant
succeeded.
**** create an unimaginatively named
user
SQL> conn niall/niall
Connected.
SQL>
create table t1 as select * from dba_segments;
Table
created.
SQL> create table t2 as select * from
dba_tablespaces;
Table created.
SQL> create index i1 on
t1(tablespace_name);
Index created.
SQL> create index i2 on
t2(tablespace_name);
Index created.
SQL> conn
system
Connected.
SQL> exec
dbms_stats.gather_schema_stats('NIALL');
PL/SQL procedure successfully
completed.
**** create some tables and gather some
stats
SQL> alter session set events '10053 trace name
context forever, level 1';
Session altered.
SQL> select /*+ index(t1 i1) */
t1.segment_name,t2.tablespace_name,t2.contents
2 from
t1,t2
3 where t1.tablespace_name=t2.tablespace_name
4 and t2.tablespace_name='USERS';
***** Issue my query and tell Oracle to use index i1 for table t1
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
CONTENTS
------------------------------
---------
<snip boring results>
20 rows selected.
SQL> alter session set events '10053 trace name context off';
Session altered.
*** 2003-12-05 21:02:09.000
*** SESSION ID:(9.15) 2003-12-05
21:02:09.000
QUERY
select /*+ index(t1 i1) */
t1.segment_name,t2.tablespace_name,t2.contents
from t1,t2
where
t1.tablespace_name=t2.tablespace_name
and
t2.tablespace_name='USERS'
***************************************
PARAMETERS
USED BY THE
OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE =
9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL =
101
<snip parameter list>
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table stats Table:
T2 Alias: T2
TOTAL :: CDN: 15 NBLKS:
1 AVG_ROW_LEN: 88
-- Index stats
INDEX NAME: I2
COL#: 1
TOTAL :: LVLS: 1 #LB: 25
#DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
***********************
Table stats Table:
T1 Alias: T1
TOTAL :: CDN: 1789 NBLKS:
25 AVG_ROW_LEN: 95
-- Index stats
INDEX NAME: I1
COL#: 5
TOTAL :: LVLS: 1 #LB: 25
#DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
_OPTIMIZER_PERCENT_PARALLEL =
0
***************************************
SINGLE TABLE ACCESS
PATH
Column: TABLESPACE Col#: 5 Table:
T1 Alias: T1
NDV:
9 NULLS:
0 DENS:
1.1111e-001
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: T1 ORIG CDN:
1789 ROUNDED CDN: 199 CMPTD CDN: 199
Access path: index
(equal)
Index:
I1
TABLE: T1
RSC_CPU: 0 RSC_IO: 92
IX_SEL: 0.0000e+000
TB_SEL: 1.1111e-001
BEST_CST: 92.00 PATH: 4
Degree: 1
***************************************
SINGLE TABLE
ACCESS PATH
Column: TABLESPACE Col#: 1
Table: T2 Alias: T2
NDV:
15 NULLS:
0 DENS:
6.6667e-002
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE:
T2 ORIG CDN: 15 ROUNDED CDN: 1 CMPTD CDN:
1
Access path: tsc Resc: 2 Resp: 2
Access path: index (equal)
Index:
I2
TABLE: T2
RSC_CPU: 0 RSC_IO: 56
IX_SEL: 0.0000e+000
TB_SEL: 6.6667e-002
BEST_CST: 2.00 PATH: 2
Degree: 1
***************************************
OPTIMIZER
STATISTICS AND
COMPUTATIONS
***************************************
GENERAL
PLANS
***********************
Join order[1]: T2 [T2] T1 [T1]
Now
joining: T1 [T1] *******
NL Join
Outer table: cost: 2 cdn: 1 rcz: 17 resp: 2
Access path: index (join stp)
Index:
I1
TABLE: T1
RSC_CPU: 0 RSC_IO: 92
IX_SEL: 0.0000e+000
TB_SEL: 1.1111e-001
Join: resc: 94 resp:
94
Join cardinality: 199 = outer (1) * inner (199) * sel
(1.0000e+000) [flag=0]
Best NL cost: 94 resp: 94
Join
result: cost: 94 cdn: 199 rcz: 42
Best so far: TABLE#: 0
CST: 2
CDN: 1
BYTES: 17
Best so far:
TABLE#: 1 CST: 94
CDN: 199
BYTES:
8358
***********************
Join order[2]: T1 [T1] T2 [T2]
Now
joining: T2 [T2] *******
NL Join
Outer table: cost: 92 cdn:
199 rcz: 25 resp: 92
Inner table: T2
Access path:
tsc Resc: 2
Join: Resc: 490
Resp: 490
Access path: index (join
stp)
Index: I2
TABLE: T2
RSC_CPU: 0 RSC_IO:
56
IX_SEL: 0.0000e+000 TB_SEL:
6.6667e-002
Join: resc: 11236 resp:
11236
Join cardinality: 199 = outer (199) * inner (1) * sel
(1.0000e+000) [flag=0]
Best NL cost: 490 resp:
490
Final:
CST: 94 CDN: 199 RSC: 94 RSP: 94
BYTES: 8358
IO-RSC: 94 IO-RSP: 94 CPU-RSC: 0
CPU-RSP: 0
*** 2003-12-05 21:02:30.000
QUERY
alter session set events
'10053 trace name context off'
You will see that for table t1
Oracle only ever considers an index access path using index i1. This is what we
told it to. By comparison for table t2 it always considers both the index access
and a tablescan (tsc in the trace file). you can repeat this experiment with
other hints and you will see that hints constrain the available options for the
CBO. This doesn't mean that by themselves they constrain the execution plan,
just the set of available execution plans. In my example Oracle considered 2
different join orders - an ordered hint would have eliminated that option as
well.
so plan stability works (in my understanding) by generating a set of hints that limit the available execution plans to 1 (the one you want), and ensuring that when the query you want plan stability is issued query rewrite kicks in and sends the cbo the hinted query.
The first part of this - hints are directives I am sure of 10053 trace would seem to prove it, the 2nd part I am only fairly sure of - you can see the hints in outln's tables, I guess a 10046 trace might prove it as well but this email is long enough and has enough trace in it already
Cheers
Niall
(who is usually wrong when he is sure of something :( ).
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Justin Cave
Sent: 05 December 2003 20:10
To:
Multiple recipients of list ORACLE-L
Subject: RE: Plan
stability
At 01:14 PM 12/4/2003, you wrote:
Hi
Justin
Didn't know you were on the list
I'm usually about a week
behind, so I don't get to participate very often...
> A
properly formed hint will cause the CBO to consider the
> hinted path to
be
> less costly than it would otherwise consider it, but hints do
>
not force a
> query to use that particular plan.
>
> If
you want to force Oracle to use a particular plan, plan
> stability
is
> orders of magnitude easier!
Umm, but if you look at plan
stability you will see that it is
implemented as hints (and query rewrite) -
typically loads of them. A
hint *does* force you to do what it says, if it is
being 'ignored' then
likely you haven't excluded alternative access paths. Of
course for any
sufficiently complex query (in my case that means 3 or more
joins) then
manually specifying an access path with hints becomes a too
difficult
problem.
I've never looked under the covers on plan
stability, so now I'm get confused. My understanding was that plan
stability forced a query to follow a particular execution plan. My
understanding of hints, however, was that they were only suggestions that the
CBO could ignore. Tom Kyte writes (second or third response
down):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1951680913800
1) yes -- IF it accepts the hint, hints are just
that --
hints. They are NOT directives, they are suggestions. It
took
the suggestion in this case.
If plan stability is just Oracle
applying a bunch of hints, and hints are
only suggestions, does that imply
that the CBO can ignore plan
stability?
Justin Cave
