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 -----------------------------------------------------------------