CBO/10053 help, please

From: <DEEDSD_at_Nationwide.com>
Date: Fri, 16 Jul 2004 15:56:27 -0400
Message-ID: <OF6B6766E4.9ECF5241-ON85256ED3.006BB334@lnotes-gw.ent.nwie.net>

I have a query that runs on two different databases, production and a copy of production. The init.ora parameters are the same. I'm not sure whether the tables were analyzed or dbms_statted. Production runs very nicely with two full scans of the ppay_sections table and a hash join. Development takes forever and does index scan of the primary key and then full scans the ppay_sections table and then filters.

I've noticed the density is dramatically different between the two 10053 traces. What I don't understand is why the development CBO is picking BEST_CST at 575.00 when the tsc is 24, especially since it is joining the same table to itself and it chooses an FTS later in the plan. The production trace also has column statistics, where the development trace does not. Oracle 9.2.0.4 EE on Solaris. Any ideas? I'm stumped.

The production trace:

2004-07-16 14:21:06.527 QUERY Select case_seq_id from ppay_sections where section_id = 'PP' and case_seq_id not in (select case_seq_id from ppay_sections where section_id = 'PS')
BASE STATISTICAL INFORMATION
Table stats Table: PPAY_SECTIONS Alias: PPAY_SECTIONS TOTAL :: CDN: 197613 NBLKS: 436 AVG_ROW_LEN: 13 Column: CASE_SEQ_I Col#: 1 Table: PPAY_SECTIONS Alias: PPAY_SECTIONS NDV: 37673 NULLS: 0 DENS: 2.6544e-05 LO: 119 HI: 725070406 NO HISTOGRAM: #BKT: 1 #VAL: 2 Column: CASE_SEQ_I Col#: 1 Table: PPAY_SECTIONS Alias: PPAY_SECTIONS NDV: 37673 NULLS: 0 DENS: 2.6544e-05 LO: 119 HI: 725070406 NO HISTOGRAM: #BKT: 1 #VAL: 2 -- Index stats INDEX NAME: PK_PPAY_SECTIONS COL#: 1 2 TOTAL :: LVLS: 2 #LB: 638 #DK: 245630 LB/K: 1 DB/K: 1 CLUF: 51678

SINGLE TABLE ACCESS PATH
Column: SECTION_ID Col#: 2 Table: PPAY_SECTIONS Alias: PPAY_SECTIONS
    NDV: 11 NULLS: 0 DENS: 9.0909e-02     NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PPAY_SECTIONS ORIG CDN: 197613 ROUNDED CDN: 17965 CMPTD CDN: 1796
5
  Access path: tsc Resc: 28 Resp: 28   Access path: index (iff)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 40
  IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00   Access path: iff Resc: 40 Resp: 40   Skip scan: ss-sel 0 andv 37673
    ss cost 37673
    table io scan cost 28
  Access path: index (no sta/stp keys)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 640
  IX_SEL: 1.0000e+00 TB_SEL: 9.0909e-02   BEST_CST: 28.00 PATH: 2 Degree: 1

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: PPAY_SECTIONS [PPAY_SECTIONS] PPAY_SECTIONS [PPAY_SECTIONS] Now joining: PPAY_SECTIONS [PPAY_SECTIONS] ******* NL Join
  Outer table: cost: 28 cdn: 17965 rcz: 8 resp: 28   Inner table: PPAY_SECTIONS
    Access path: tsc Resc: 28
    Join: Resc: 503048 Resp: 503048
  Access path: index (iff)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 40
  IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00   Inner table: PPAY_SECTIONS
    Access path: iff Resc: 40
    Join: Resc: 718628 Resp: 718628
  Access path: index (unique)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 5.5664e-05 TB_SEL: 5.5664e-05     Join: resc: 17993 resp: 17993
  Access path: index (eq-unique)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 17993 resp: 17993
Anti-join cardinality: 0 = outer (17965) * (1 - sel (1.0000e+00)) [flag=5]   Best NL cost: 17993 resp: 17993
SM Join
  Outer table:
    resc: 28 cdn: 17965 rcz: 8 deg: 1 resp: 28   Inner table: PPAY_SECTIONS
    resc: 28 cdn: 17965 rcz: 8 deg: 1 resp: 28     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           29 Area size:      712704 Max Area size:

712704

Degree: 1

      Blocks to Sort:       42 Row size:           19 Rows:      17965
      Initial runs:          1 Merge passes:        1 IO Cost / pass:
46
      Total IO sort cost: 44
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           29 Area size:      712704 Max Area size:

712704

Degree: 1

      Blocks to Sort:       42 Row size:           19 Rows:      17965
      Initial runs:          1 Merge passes:        1 IO Cost / pass:
46
      Total IO sort cost: 44
      Total CPU sort cost: 0
      Total Temp space used: 0

  Merge join Cost: 144 Resp: 144
HA Join
  Outer table:
    resc: 28 cdn: 17965 rcz: 8 deg: 1 resp: 28   Inner table: PPAY_SECTIONS
    resc: 28 cdn: 17965 rcz: 8 deg: 1 resp: 28     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 7 Deg: 1

      hash_area:  256 (max=256)  buildfrag:  257                probefrag:

44
ppasses: 2
Hash join Resc: 63 Resp: 63

Join result: cost: 63  cdn: 1  rcz: 16
Best so far: TABLE#: 0  CST:         28  CDN:      17965  BYTES:     143720
Best so far: TABLE#: 1  CST:         63  CDN:          1  BYTES:         16

Final:
CST: 63 CDN: 1 RSC: 63 RSP: 63 BYTES: 16 IO-RSC: 63 IO-RSP: 63 CPU-RSC: 0 CPU-RSP: 0 Production execution plan:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=1 Bytes=16) 1 0 HASH JOIN (ANTI) (Cost=63 Card=1 Bytes=16)

   2    1     TABLE ACCESS (FULL) OF 'PPAY_SECTIONS' (Cost=28 Card=179
          65 Bytes=143720)

   3    1     TABLE ACCESS (FULL) OF 'PPAY_SECTIONS' (Cost=28 Card=179
          65 Bytes=143720)

The development trace:

BASE STATISTICAL INFORMATION

Table stats Table: PPAY_SECTIONS Alias: PPAY_SECTIONS TOTAL :: CDN: 177671 NBLKS: 373 AVG_ROW_LEN: 13 -- Index stats
INDEX NAME: PK_PPAY_SECTIONS COL#: 1 2 TOTAL :: LVLS: 1 #LB: 574 #DK: 152875 LB/K: 1 DB/K: 1 CLUF: 45920
_OPTIMIZER_PERCENT_PARALLEL = 0

SINGLE TABLE ACCESS PATH
Column: SECTION_ID Col#: 2 Table: PPAY_SECTIONS Alias: PPAY_SECTIONS
    NDV: 10 NULLS: 0 DENS: 1.0000e-01     NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PPAY_SECTIONS ORIG CDN: 177671 ROUNDED CDN: 888 CMPTD CDN: 888
  Access path: tsc Resc: 24 Resp: 24   Access path: index (iff)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 37
  IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00   Access path: iff Resc: 37 Resp: 37   Skip scan: ss-sel 0 andv 28198
    ss cost 28198
    table io scan cost 24
  Access path: index (no sta/stp keys)

      Index: PK_PPAY_SECTIONS
  TABLE: PPAY_SECTIONS
      RSC_CPU: 0 RSC_IO: 575
  IX_SEL: 1.0000e+00 TB_SEL: 5.0000e-03   BEST_CST: 575.00 PATH: 4 Degree: 1

OPTIMIZER STATISTICS AND COMPUTATIONS

GENERAL PLANS

Join order[1]: PPAY_SECTIONS [PPAY_SECTIONS] Best so far: TABLE#: 0 CST: 575 CDN: 888 BYTES: 7104 prefetching is on for PK_PPAY_SECTIONS
Final:
CST: 575 CDN: 888 RSC: 575 RSP: 575 BYTES: 7104 IO-RSC: 575 IO-RSP: 575 CPU-RSC: 0 CPU-RSP: 0 First Rows Plan

Development execution plan:

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=510624 Card=888 Byte
          s=7104)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'PPAY_SECTIONS' (Cost=24 Card=888
           Bytes=7104)

   3    1     INDEX (FULL SCAN) OF 'PK_PPAY_SECTIONS' (UNIQUE) (Cost=5
          75 Card=888 Bytes=7104)

----------------------------------------------------------------

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 Fri Jul 16 2004 - 14:56:16 CDT