Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> The optimizer is ignoring my hints

The optimizer is ignoring my hints

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 24 May 2004 11:37:19 +0100
Message-ID: <7765c89704052403376868e6e3@mail.gmail.com> 





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_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Mon May 24 2004 - 05:34:26 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US