The optimizer is ignoring my hints

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 24 May 2004 11:37:19 +0100

Yes I know we did this to death a little while back - I believe that I
may have said words to the effect that the CBO never *ignores* valid
hints but that you may not get the execution plan you expect if you
don't specify enough.

Consider the following 10053 extracts using the same old scott/tiger demo

First we have this one demonstrating the optimizer ignoring the hint

QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  fro(0): flg=0 objn=55850 hint_alias="D"@"SEL$1"
  fro(1): flg=0 objn=55849 hint_alias="E"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DEPT   Alias:  D
  TOTAL ::  CDN: 4  NBLKS:  5  AVG_ROW_LEN:  100
  COLUMN:     DEPTNO(NUMBER)  Col#: 1      Table: DEPT   Alias: D
    NO STATISTICS  (using defaults)
    Size: 22  NDV: 1  Nulls: 0  Density: 1.0000e+000
    No Histogram: #BKT: 0
        (0 uncompressed buckets and 0 endpoint values)
***********************
Table stats    Table: EMP   Alias:  E
  TOTAL ::  CDN: 14  NBLKS:  5  AVG_ROW_LEN:  100
  COLUMN:     DEPTNO(NUMBER)  Col#: 8      Table: EMP   Alias: E
    NO STATISTICS  (using defaults)
    Size: 22  NDV: 1  Nulls: 0  Density: 1.0000e+000
    No Histogram: #BKT: 0
        (0 uncompressed buckets and 0 endpoint values)
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: EMP  Alias: E     
    Original Card: 14  Rounded Card: 14  Computed Card: 14.00
  Access Path: table-scan  Resc:  3  Resp:  3
  BEST_CST: 3.00  PATH: 2  Degree:  1
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DEPT  Alias: D     
    Original Card: 4  Rounded Card: 4  Computed Card: 4.00
  Access Path: table-scan  Resc:  3  Resp:  3
  BEST_CST: 3.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DEPT[D]#0  EMP[E]#1
Now joining: EMP[E]#1 *******
NL Join
  Outer table: cost: 3  cdn: 4  rcz: 22  resp:  3
  Inner table: EMP  Alias: E
    Access Path: table-scan  Resc: 2
    Join:  Resc:  9  Resp:  9
  Best NL cost: 9  resp: 9
Join cardinality:  14 = outer (4) * inner (14) * sel (2.5000e-001)  [flag=0]
SM Join
  Outer table: 
    resc: 3  cdn: 4  rcz: 22  deg: 1  resp: 3
  Inner table: EMP  Alias: E
    resc: 3  cdn: 14  rcz: 20  deg:  1  resp: 3
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:          20 Area size:      131072 Max Area size:     3878912
      Degree:               1
      Blocks to Sort:       1 Row size:           35 Total Rows:              4
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11591376
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:          20 Area size:      131072 Max Area size:     3878912
      Degree:               1
      Blocks to Sort:       1 Row size:           32 Total Rows:             14
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11593418
      Total Temp space used: 0
  Merge join  Cost:  8  Resp:  8
HA Join
  Outer table: 
    resc: 3  cdn: 4  rcz: 22  deg: 1  resp: 3
  Inner table: EMP  Alias: E
    resc: 3  cdn: 14  rcz: 20  deg:  1  resp: 3
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 1   Deg: 1
    hash_area: 124 (max=947)  buildfrag: 1  probefrag: 1 ppasses: 1
  Hash join   Resc: 7   Resp: 7
Join result: cost: 7  cdn: 14  rcz: 42
Best so far: TABLE#: 0  CST:          3  CDN:          4  BYTES:         88
Best so far: TABLE#: 1  CST:          7  CDN:         14  BYTES:        588
***********************
Join order[2]:  EMP[E]#1  DEPT[D]#0
Now joining: DEPT[D]#0 *******
NL Join
  Outer table: cost: 3  cdn: 14  rcz: 20  resp:  3
  Inner table: DEPT  Alias: D
    Access Path: table-scan  Resc: 1
    Join:  Resc:  20  Resp:  20
  Best NL cost: 20  resp: 20
Join cardinality:  14 = outer (14) * inner (4) * sel (2.5000e-001)  [flag=0]
SM Join
  Outer table: 
    resc: 3  cdn: 14  rcz: 20  deg: 1  resp: 3
  Inner table: DEPT  Alias: D
    resc: 3  cdn: 4  rcz: 22  deg:  1  resp: 3
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:          20 Area size:      131072 Max Area size:     3878912
      Degree:               1
      Blocks to Sort:       1 Row size:           32 Total Rows:             14
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11593418
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:          20 Area size:      131072 Max Area size:     3878912
      Degree:               1
      Blocks to Sort:       1 Row size:           35 Total Rows:              4
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11591376
      Total Temp space used: 0
  Merge join  Cost:  8  Resp:  8
HA Join
  Outer table: 
    resc: 3  cdn: 14  rcz: 20  deg: 1  resp: 3
  Inner table: DEPT  Alias: D
    resc: 3  cdn: 4  rcz: 22  deg:  1  resp: 3
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 1   Deg: 1
    hash_area: 124 (max=947)  buildfrag: 1  probefrag: 1 ppasses: 1
  Hash join   Resc: 7   Resp: 7
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
(newjo-save)    [0 1 ]
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 7  CDN: 14  RSC: 7  RSP: 7  BYTES: 588
  IO-RSC: 6  IO-RSP: 6  CPU-RSC: 5871062  CPU-RSP: 5871062
QUERY
select /*+ ordered use_nl(e d) */ ename,dname
from emp e,dept d
where e.deptno=d.deptno
=====================


and then we have this one behaving as one would expect and only
considering the nested loops path.

QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  fro(0): flg=0 objn=55850 hint_alias="D"@"SEL$1"
  fro(1): flg=0 objn=55849 hint_alias="E"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DEPT   Alias:  D
  TOTAL ::  CDN: 4  NBLKS:  5  AVG_ROW_LEN:  100
  COLUMN:     DEPTNO(NUMBER)  Col#: 1      Table: DEPT   Alias: D
    NO STATISTICS  (using defaults)
    Size: 22  NDV: 1  Nulls: 0  Density: 1.0000e+000
    No Histogram: #BKT: 0
        (0 uncompressed buckets and 0 endpoint values)
***********************
Table stats    Table: EMP   Alias:  E
  TOTAL ::  CDN: 14  NBLKS:  5  AVG_ROW_LEN:  100
  COLUMN:     DEPTNO(NUMBER)  Col#: 8      Table: EMP   Alias: E
    NO STATISTICS  (using defaults)
    Size: 22  NDV: 1  Nulls: 0  Density: 1.0000e+000
    No Histogram: #BKT: 0
        (0 uncompressed buckets and 0 endpoint values)
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: EMP  Alias: E     
    Original Card: 14  Rounded Card: 14  Computed Card: 14.00
  Access Path: table-scan  Resc:  3  Resp:  3
  BEST_CST: 3.00  PATH: 2  Degree:  1
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DEPT  Alias: D     
    Original Card: 4  Rounded Card: 4  Computed Card: 4.00
  Access Path: table-scan  Resc:  3  Resp:  3
  BEST_CST: 3.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***********************
Join order[1]:  EMP[E]#0  DEPT[D]#1
Now joining: DEPT[D]#1 *******
NL Join
  Outer table: cost: 3  cdn: 14  rcz: 20  resp:  3
  Inner table: DEPT  Alias: D
    Access Path: table-scan  Resc: 1
    Join:  Resc:  20  Resp:  20
  Best NL cost: 20  resp: 20
Join cardinality:  14 = outer (14) * inner (4) * sel (2.5000e-001)  [flag=0]
Join result: cost: 20  cdn: 14  rcz: 42
Best so far: TABLE#: 0  CST:          3  CDN:         14  BYTES:        280
Best so far: TABLE#: 1  CST:         20  CDN:         14  BYTES:        588
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 20  CDN: 14  RSC: 20  RSP: 20  BYTES: 588
  IO-RSC: 20  IO-RSP: 20  CPU-RSC: 545448  CPU-RSP: 545448
QUERY
select /*+ ordered use_nl(e d) */ ename,dname
from emp e,dept d
where e.deptno=d.deptno
=====================


The difference between them - a new parameter for 10g

  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
<snip>
  _optimizer_ignore_hints             = true

So it would seem that having just about settled the whole the
optimizer is ignoring my hint debate - now it can.

I do like the changes to the parameter listing in 10053 trace, the
output seems a lot easier to read now.

Niall Litchfield
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: