RE: Plan stability

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Fri, 05 Dec 2003 13:34:26 -0800
Message-ID: <F001.005D8F46.20031205133426@fatcity.com>

Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.

This generates a trace file with the following info in it. interesting bits bold and red

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-----
mailto:ml-errors_at_fatcity.com] On Behalf Of Justin Cave Sent: 05 December 2003 20:10
To: Multiple recipients of list ORACLE-L

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:1951
680913800>
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:19516 80913800

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

------=_NextPart_000_0001_01C3BB77.439D23E0 Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR></HEAD>
<BODY>
I could not get Outlook to prefix your message =
properly -=20
grrr. Comments are at the top which may make reading them hard. I = have=20
tried to setup a simple demo that hints are not 'suggestions' sorry if = this=20
becomes long - this is all 9.2 but should apply to 8i and later versions = as=20
well. The sql I issue comes first SQL> create user niall = identified by=20
niall; User created. SQL> alter user niall default = tablespace=20
users   2  temporary tablespace temp   3  = quota=20
unlimited on users; User altered. SQL> grant create=20 session,   2  alter session,   3  create=20 table,   4  select_catalog_role to niall; Grant=20 succeeded. **** create an unimaginatively named=20 user SQL> conn =
niall/niall Connected. SQL>=20 create table t1 as select * from dba_segments; Table=20 created. SQL> create table t2 as select * from=20 dba_tablespaces; Table created. SQL> create index i1 on =

t1(tablespace_name); Index created. SQL> create index = i2 on=20
t2(tablespace_name); Index created. SQL> conn=20 system Connected. SQL> exec=20
dbms_stats.gather_schema_stats('NIALL'); PL/SQL procedure = successfully=20
completed. **** create some tables and gather some=20 stats  SQL> alter session set events '10053 trace = name=20
context forever, level 1';
Session altered.
SQL> select /*+ index(t1 i1) */=20
t1.segment_name,t2.tablespace_name,t2.contents   2  from=20 t1,t2   3  where =
t1.tablespace_name=3Dt2.tablespace_name  =20 4  and t2.tablespace_name=3D'USERS';
***** Issue my query and tell Oracle to use index =
i1 for=20
table t1
<FONT=20

size=3D2>SEGMENT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=

bsp;         =20
 ---------------------------------------------------------------------=
----------- TABLESPACE_NAME       &= nbsp;       =20

CONTENTS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=

nbsp;    =20
 ------------------------------=20

---------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=

=20
 <snip boring results>
20 rows selected.
SQL> alter session set events '10053 trace name =
context=20
off';
Session altered.
<DIV> </DIV>
<DIV>This generates a trace file with the following info =
in it.=20
interesting bits bold and red</DIV>
<DIV> </DIV>
*** 2003-12-05 21:02:09.000 *** SESSION ID:(9.15) =
2003-12-05=20
21:02:09.000 QUERY select /*+ index(t1 i1) */=20

t1.segment_name,t2.tablespace_name,t2.contents<BR>from t1,t2<BR>where=20
t1.tablespace_name=3Dt2.tablespace_name<BR>and=20
t2.tablespace_name=3D'USERS'<BR>***************************************<B=

R>PARAMETERS=20
USED BY THE=20

OPTIMIZER<BR>********************************<BR>OPTIMIZER_FEATURES_ENABL=

E =3D=20
9.2.0 OPTIMIZER_MODE/GOAL =3D Choose _OPTIMIZER_PERCENT_PARALLEL = =3D=20
101 <snip parameter list>
*************************************** BASE =
STATISTICAL=20

INFORMATION<BR>***********************<BR>Table stats&nbsp;&nbsp;&nbsp; =

Table:=20
T2   Alias: T2   TOTAL ::  CDN: 15  = NBLKS: =20
1  AVG_ROW_LEN:  88 -- Index stats   INDEX NAME: = I2 =20
COL#: 1     TOTAL ::  LVLS: 1   #LB: = 25 =20
#DK: 100  LB/K: 1  DB/K: 1  CLUF:=20

800<BR>***********************<BR>Table stats&nbsp;&nbsp;&nbsp; Table:=20

T1   Alias: T1   TOTAL ::  CDN: 1789  = NBLKS: =20
25  AVG_ROW_LEN:  95 -- Index stats   INDEX NAME: = I1 =20
COL#: 5     TOTAL ::  LVLS: 1   #LB: = 25 =20
#DK: 100  LB/K: 1  DB/K: 1  CLUF:=20 800 _OPTIMIZER_PERCENT_PARALLEL =3D=20

0<BR>***************************************<BR>SINGLE TABLE ACCESS=20

PATH Column: TABLESPACE  Col#: 5      = Table:=20
T1   Alias: T1     NDV:=20 9         NULLS:=20 0         DENS:=20 1.1111e-001     NO HISTOGRAM: #BKT: 1 #VAL: = 2 <FONT=20
size=3D3>  TABLE: T1     = ORIG CDN:=20
1789  ROUNDED CDN: 199  CMPTD CDN: 199   Access path: = index=20
(equal)       Index:=20 I1   TABLE: = T1      =20
RSC_CPU: 0   RSC_IO: 92   IX_SEL:  = 0.0000e+000 =20
TB_SEL:  1.1111e-001   BEST_CST: 92.00  PATH: 4 =20

Degree:&nbsp; 1<BR>***************************************<BR>SINGLE =

TABLE=20
ACCESS PATH Column: TABLESPACE  Col#: = 1     =20
Table: T2   Alias: T2     NDV:=20 15        NULLS:=20 0         DENS:=20 6.6667e-002     NO HISTOGRAM: #BKT: 1 #VAL: 2 <FONT =

color=3D#ff0000>  TABLE:=20
T2     ORIG CDN: 15  ROUNDED CDN: 1  CMPTD = CDN:=20
1   Access path: tsc  Resc:  2  Resp:  = 2  =20
Access path: index (equal)       Index:=20 I2   TABLE: = T2      =20
RSC_CPU: 0   RSC_IO: 56   IX_SEL:  = 0.0000e+000 =20
TB_SEL:  6.6667e-002   BEST_CST: 2.00  PATH: 2 =20

Degree:&nbsp; 1<BR>***************************************<BR>OPTIMIZER=20

STATISTICS AND=20

COMPUTATIONS<BR>***************************************<BR>GENERAL=20
PLANS<BR>***********************<BR>Join order[1]: T2 [T2] T1 [T1] =

 Now=20

joining: T1 [T1] ******* NL Join  =20
Outer table: cost: 2  cdn: 1  rcz: 17  resp:  = 2  =20
Access path: index (join stp)       Index:=20 I1   TABLE: T1       =

RSC_CPU: 0   RSC_IO: 92   IX_SEL:  = 0.0000e+000 =20
TB_SEL:  1.1111e-001     Join:  resc: = 94  resp:=20
94 Join cardinality:  199 =3D outer (1) * inner (199) * sel=20 (1.0000e+000)  [flag=3D0]   Best NL cost: 94  resp: = 94 Join=20
result: cost: 94  cdn: 199  rcz: 42 Best so far: TABLE#: = 0 =20
CST:          2 =20 CDN:          1 =20 BYTES:         17 Best so = far:=20
TABLE#: 1  CST:         = 94 =20
CDN:        199 =20 BYTES:      =20

8358<BR>***********************<BR>Join order[2]: T1 [T1] T2 [T2] =

 Now=20

joining: T2 [T2] ******* NL Join   Outer table: cost: = 92  cdn:=20
199  rcz: 25  resp:  92   Inner table: T2     Access = path:=20
tsc  Resc: 2     Join:  Resc:  = 490 =20
Resp:  490   Access path: index (join=20 stp)       Index: = I2  =20
TABLE: T2       RSC_CPU: 0   = RSC_IO:=20
56   IX_SEL:  0.0000e+000  TB_SEL: =20 6.6667e-002     Join:  resc: 11236  resp:=20 11236 Join cardinality:  199 =3D outer (199) * inner (1) * sel=20 (1.0000e+000)  [flag=3D0]   Best NL cost: 490  resp:=20 490 Final:   CST: 94  CDN: 199  RSC: 94  RSP: = 94 =20
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=20
'10053 trace name context off' 
 You will see that =
for table t1=20
Oracle only ever considers an index access path using index i1. This is = what we=20
told it to. By comparison for table t2 it always considers both the = index access=20
and a tablescan (tsc in the trace file). you can repeat this experiment = with=20
other hints and you will see that hints constrain the available options = for the=20
CBO. This doesn't mean that by themselves they constrain the execution = plan,=20
just the set of available execution plans. In my example Oracle = considered 2=20
different join orders - an ordered hint would have eliminated that = option as=20
well. 
so plan stability works =
(in my=20
understanding) by generating a set of hints that limit the = available=20
execution plans to 1 (the one you want), and ensuring that when the = query you=20
want plan stability is issued query rewrite kicks in and sends the cbo = the=20
hinted query. 
 
The first part of this - =
hints are=20
directives I am sure of 10053 trace would seem to prove it, the 2nd part = I am=20
only fairly sure of - you can see the hints in outln's tables, I guess a = 10046=20
trace might prove it as well but this email is long enough and has = enough trace=20
in it already
 
Cheers
Niall
(who is usually wrong =
when he is sure=20
of something :( ). 
 -----Original Message----- From: = ml-errors_at_fatcity.com=20
[<A href=3D"mailto:ml-errors_at_fatcity.com">mailto:ml-errors_at_fatcity.com</A>] On Behalf Of Justin Cave Sent: 05 December 2003 = 20:10 To:=20
Multiple recipients of list ORACLE-L Subject: RE: Plan=20 stability At 01:14 PM 12/4/2003, you wrote: Hi=20 Justin Didn't know you were on the list I'm usually about = a week=20
behind, so I don't get to participate very often... > = A=20
properly formed hint will cause the CBO to consider the > hinted = path to=20
be > less costly than it would otherwise consider it, but hints = do >=20
not force a > query to use that particular = plan.  > > If=20
you want to force Oracle to use a particular plan, plan > = stability=20
is > orders of magnitude easier! Umm, but if you look at = plan=20
stability you will see that it is implemented as hints (and query = rewrite) -=20
typically loads of them. A hint *does* force you to do what it says, = if it is=20
being 'ignored' then likely you haven't excluded alternative access = paths. Of=20
course for any sufficiently complex query (in my case that means 3 or = more=20
joins) then manually specifying an access path with hints becomes a = too=20
difficult problem. I've never looked under the covers on plan=20 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=20
CBO could ignore.  Tom Kyte writes (second or third response=20 down): <A=20
href=3D"http://asktom.oracle.com/pls/ask/f?p=3D4950:8:::::F4950_P8_DISPLA= YID:1951680913800"><FONT=20
face=3DArial color=3D#0000ff=20
size=3D2>http://asktom.oracle.com/pls/ask/f?p=3D4950:8:::::F4950_P8_DISPL= AYID:1951680913800</A> <FONT=20 face=3DArial color=3D#0000ff size=3D2>1) yes -- IF it accepts the hint, = hints are just=20
that -- hints.  They are NOT directives, they are = suggestions.  It=20
took the suggestion in this case. If plan stability is just = Oracle=20
applying a bunch of hints, and hints are only suggestions, does that = imply=20
that the CBO can ignore plan stability? Justin Cave=20
</BODY></HTML>

------=_NextPart_000_0001_01C3BB77.439D23E0--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Dec 05 2003 - 15:34:26 CST