Re: Intrepretation of 10053 trace

From: Pk <pbhandari2050_at_yahoo.com>
Date: 22 Sep 2004 16:06:14 -0700
Message-ID: <fa93fdc3.0409221506.4d28ffb2@posting.google.com>

Thanks Christian, here is the full trace file.

Prakash

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

QUERY
explain plan for SELECT * FROM kmk.OUT_CLIENT_LOAD_GRP_ASGN_VIEW

---

PARAMETERS USED BY THE OPTIMIZER

---

OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101

HASH_AREA_SIZE = 81920
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 2097152

OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE

STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE

PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0

OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32

QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = FALSE
_NEW_INITIAL_JOIN_ORDERS = TRUE

ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE

QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE

DB_FILE_MULTIBLOCK_READ_COUNT = 8
_NEW_SORT_COST_ESTIMATE = TRUE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE

DB_FILE_MULTIBLOCK_READ_COUNT = 8
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE

---

BASE STATISTICAL INFORMATION

---

Table stats Table: CUSTOMER Alias: CUSTOMER   TOTAL :: CDN: 155939 NBLKS: 3743 AVG_ROW_LEN: 180

Column: CUST_STORE  Col#: 2      Table: CUSTOMER   Alias: CUSTOMER
    NDV: 144906    NULLS: 0         DENS: 3.2507e-05
    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76
Column:    CUST_ID  Col#: 1      Table: CUSTOMER   Alias: CUSTOMER
    NDV: 115732    NULLS: 0         DENS: 2.6984e-04

    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 75 -- Index stats
  INDEX NAME: CUSTOMER_AK_1 COL#: 3
    TOTAL :: LVLS: 1 #LB: 360 #DK: 2 LB/K: 180 DB/K: 3438 CLUF: 6876   INDEX NAME: CUSTOMER_AK_5 COL#: 32
    TOTAL :: LVLS: 1 #LB: 302 #DK: 7 LB/K: 43 DB/K: 1782 CLUF: 12479   INDEX NAME: CUSTOMER_NAME_AK COL#: 8 6 7     TOTAL :: LVLS: 1 #LB: 141 #DK: 17439 LB/K: 1 DB/K: 1 CLUF: 24974   INDEX NAME: CUSTOMER_PK COL#: 1 2
    TOTAL :: LVLS: 2 #LB: 619 #DK: 153290 LB/K: 1 DB/K: 1 CLUF: 42498

---

Table stats Table: OUT_ORD_STAT Alias: OUT_ORD_STAT   TOTAL :: CDN: 19 NBLKS: 1 AVG_ROW_LEN: 18

Column:  STATUS_CD  Col#: 1      Table: OUT_ORD_STAT   Alias: OUT_ORD_STAT
    NDV: 19        NULLS: 0         DENS: 5.2632e-02 LO:  1  HI: 99

    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: OUT_ORD_STAT_PK COL#: 1
    TOTAL :: LVLS: 0 #LB: 1 #DK: 19 LB/K: 1 DB/K: 1 CLUF: 1

---

Table stats Table: OUT_ORD_HDR Alias: OUT_ORD_HDR   TOTAL :: CDN: 284368 NBLKS: 20302 AVG_ROW_LEN: 464

Column:  STATUS_CD  Col#: 4      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 9         NULLS: 0         DENS: 1.7583e-06
    FREQUENCY HISTOGRAM: #BKT: 284368 #VAL: 9
Column:    CUST_ID  Col#: 7      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 16829     NULLS: 353       DENS: 6.9387e-04
    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76
Column: CUST_STORE  Col#: 8      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 19994     NULLS: 353       DENS: 6.5538e-04
    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76
Column:  STATUS_CD  Col#: 4      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 9         NULLS: 0         DENS: 1.7583e-06
    FREQUENCY HISTOGRAM: #BKT: 284368 #VAL: 9
Column:  STATUS_CD  Col#: 4      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 9         NULLS: 0         DENS: 1.7583e-06
    FREQUENCY HISTOGRAM: #BKT: 284368 #VAL: 9
Column: CUST_STORE  Col#: 8      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 19994     NULLS: 353       DENS: 6.5538e-04
    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76
Column:    CUST_ID  Col#: 7      Table: OUT_ORD_HDR   Alias: OUT_ORD_HDR
    NDV: 16829     NULLS: 353       DENS: 6.9387e-04

    HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76 -- Index stats
  INDEX NAME: OUT_ORD_HDR_AK_10 COL#: 104     TOTAL :: LVLS: 1 #LB: 31 #DK: 1 LB/K: 31 DB/K: 6669 CLUF: 6669

  INDEX NAME: OUT_ORD_HDR_AK_11  COL#: 20
    TOTAL ::  LVLS: 2   #LB: 657  #DK: 11  LB/K: 59  DB/K: 3854  CLUF: 42397
  INDEX NAME: OUT_ORD_HDR_AK_13  COL#: 56 55
    TOTAL ::  LVLS: 2   #LB: 859  #DK: 47  LB/K: 18  DB/K: 4195  CLUF: 197193
  INDEX NAME: OUT_ORD_HDR_AK_14  COL#: 27
    TOTAL ::  LVLS: 2   #LB: 591  #DK: 4  LB/K: 147  DB/K: 6532  CLUF: 26131
  INDEX NAME: OUT_ORD_HDR_AK_15  COL#: 133
    TOTAL ::  LVLS: 2   #LB: 701  #DK: 4  LB/K: 175  DB/K: 6418  CLUF: 25675
  INDEX NAME: OUT_ORD_HDR_AK_3  COL#: 7 8

    TOTAL :: LVLS: 2 #LB: 1345 #DK: 22690 LB/K: 1 DB/K: 11 CLUF: 261074   INDEX NAME: OUT_ORD_HDR_AK_5 COL#: 5
    TOTAL :: LVLS: 2 #LB: 755 #DK: 6972 LB/K: 1 DB/K: 5 CLUF: 35403   INDEX NAME: OUT_ORD_HDR_AK_6 COL#: 6
    TOTAL :: LVLS: 2 #LB: 730 #DK: 8221 LB/K: 1 DB/K: 25 CLUF: 205681   INDEX NAME: OUT_ORD_HDR_AK_7 COL#: 2
    TOTAL :: LVLS: 2 #LB: 688 #DK: 4 LB/K: 172 DB/K: 6496 CLUF: 25984   INDEX NAME: OUT_ORD_HDR_AK_8 COL#: 4
    TOTAL :: LVLS: 2 #LB: 680 #DK: 6 LB/K: 113 DB/K: 4017 CLUF: 24107   INDEX NAME: OUT_ORD_HDR_PK COL#: 1 2 3     TOTAL :: LVLS: 2 #LB: 1553 #DK: 275882 LB/K: 1 DB/K: 1 CLUF: 186721   INDEX NAME: OUT_ORD_HDR_PRIORITY_AK COL#: 105     TOTAL :: LVLS: 2 #LB: 639 #DK: 1 LB/K: 639 DB/K: 20012 CLUF: 20012   INDEX NAME: OUT_ORD_HDR_SHIP_DT_AK COL#: 47     TOTAL :: LVLS: 0 #LB: 0 #DK: 0 LB/K: 0 DB/K: 0 CLUF: 0
_OPTIMIZER_PERCENT_PARALLEL = 0

---

SINGLE TABLE ACCESS PATH
  TABLE: OUT_ORD_HDR ORIG CDN: 284368 ROUNDED CDN: 2393 CMPTD CDN: 2393   Access path: tsc Resc: 3083 Resp: 3083   Access path: index (scan)

      Index: OUT_ORD_HDR_AK_8
  TABLE: OUT_ORD_HDR
      RSC_CPU: 0 RSC_IO: 211
  IX_SEL: 8.4152e-03 TB_SEL: 8.4152e-03   BEST_CST: 211.00 PATH: 4 Degree: 1

---

SINGLE TABLE ACCESS PATH
  TABLE: OUT_ORD_STAT ORIG CDN: 19 ROUNDED CDN: 11 CMPTD CDN: 11   Access path: tsc Resc: 2 Resp: 2
  Access path: index (scan)

      Index: OUT_ORD_STAT_PK
  TABLE: OUT_ORD_STAT
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 6.0365e-01 TB_SEL: 6.0365e-01   BEST_CST: 2.00 PATH: 4 Degree: 1

---

SINGLE TABLE ACCESS PATH
  TABLE: CUSTOMER ORIG CDN: 155939 ROUNDED CDN: 155939 CMPTD CDN: 155939   Access path: tsc Resc: 570 Resp: 570   BEST_CST: 570.00 PATH: 2 Degree: 1
Table: OUT_ORD_HDR Multi-column join key card: 0.000000

---

OPTIMIZER STATISTICS AND COMPUTATIONS

---

GENERAL PLANS

---

Join order[1]: OUT_ORD_STAT [OUT_ORD_STAT] OUT_ORD_HDR [OUT_ORD_HDR] CUSTOMER [C USTOMER]
Now joining: OUT_ORD_HDR [OUT_ORD_HDR] ******* NL Join
  Outer table: cost: 2 cdn: 11 rcz: 11 resp: 2   Inner table: OUT_ORD_HDR
    Access path: tsc Resc: 3083
    Join: Resc: 33915 Resp: 33915
  Access path: index (join index)

      Index: OUT_ORD_HDR_AK_8
  TABLE: OUT_ORD_HDR
      RSC_CPU: 0 RSC_IO: 4131
  IX_SEL: 0.0000e+00 TB_SEL: 1.1111e-01     Join (ordered NL): resc: 45443 resp: 45443 Join cardinality: 2287 = outer (11) * inner (2393) * sel (8.3333e-02) [flag=0]   Best NL cost: 33915 resp: 33915
SM Join
  Outer table:
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2   Inner table: OUT_ORD_HDR
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:      121 Row size:          412 Rows:       2393
      Initial runs:          1 Merge passes:        1 IO Cost / pass:        148
      Total IO sort cost: 134
      Total CPU sort cost: 0
      Total Temp space used: 0

  Merge join Cost: 348 Resp: 348
HA Join
  Outer table:
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2   Inner table: OUT_ORD_HDR
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 21 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  11                probefrag:   111 pp

asses: 2
  Hash join Resc: 234 Resp: 234
Join result: cost: 234 cdn: 2287 rcz: 376 Now joining: CUSTOMER [CUSTOMER] ******* NL Join
  Outer table: cost: 234 cdn: 2287 rcz: 376 resp: 234   Inner table: CUSTOMER
    Access path: tsc Resc: 570
    Join: Resc: 1303824 Resp: 1303824   Access path: index (unique)

      Index: CUSTOMER_PK
  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 6.4128e-06 TB_SEL: 6.4128e-06     Join: resc: 4808 resp: 4808
  Access path: index (eq-unique)

      Index: CUSTOMER_PK
  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 4808 resp: 4808
Outer join cardinality: 2327 = max ( outer (2287) , (outer (2287) * inner (1559 39) * sel (6.5236e-06) ) [flag=16]

   Using multi-column join key (card = 153290 sel = 2.7328e-09)   Best NL cost: 4808 resp: 4808
SM Join
  Outer table:
    resc: 234 cdn: 2287 rcz: 376 deg: 1 resp: 234   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:      119 Row size:          424 Rows:       2287
      Initial runs:          1 Merge passes:        1 IO Cost / pass:        146
      Total IO sort cost: 132
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:     1833 Row size:           96 Rows:     155939
      Initial runs:         11 Merge passes:        1 IO Cost / pass:       2121
      Total IO sort cost: 1977
      Total CPU sort cost: 0
      Total Temp space used: 31564000

  Merge join Cost: 2914 Resp: 2914
HA Join
  Outer table:
    resc: 234 cdn: 2287 rcz: 376 deg: 1 resp: 234   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 12334 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  109                probefrag:   1714

ppasses: 11
  Hash join Resc: 13138 Resp: 13138

Join result: cost: 2914  cdn: 2327  rcz: 454
Best so far: TABLE#: 0  CST:          2  CDN:         11  BYTES:        121
Best so far: TABLE#: 1  CST:        234  CDN:       2287  BYTES:     859912
Best so far: TABLE#: 2  CST:       2914  CDN:       2327  BYTES:    1056458
***********************

Join order[2]: OUT_ORD_HDR [OUT_ORD_HDR] OUT_ORD_STAT [OUT_ORD_STAT] CUSTOMER [C USTOMER]
Now joining: OUT_ORD_STAT [OUT_ORD_STAT] ******* NL Join
  Outer table: cost: 211 cdn: 2393 rcz: 365 resp: 211   Inner table: OUT_ORD_STAT
    Access path: tsc Resc: 2
    Join: Resc: 4997 Resp: 4997
  Access path: index (unique)

      Index: OUT_ORD_STAT_PK
  TABLE: OUT_ORD_STAT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 9.0909e-02 TB_SEL: 9.0909e-02     Join (ordered NL): resc: 2604 resp: 2604   Access path: index (eq-unique)

      Index: OUT_ORD_STAT_PK
  TABLE: OUT_ORD_STAT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join (ordered NL): resc: 2604 resp: 2604 Join cardinality: 2287 = outer (2393) * inner (11) * sel (8.3333e-02) [flag=0]   Best NL cost: 2604 resp: 2604
SM Join
  Outer table:
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211   Inner table: OUT_ORD_STAT
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:        1 Row size:           23 Rows:         11
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         10
      Total IO sort cost: 6
      Total CPU sort cost: 0
      Total Temp space used: 0

  Merge join Cost: 218 Resp: 218
HA Join
  Outer table:
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211   Inner table: OUT_ORD_STAT
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 74 Deg: 1 (sides swapped)

      hash_area:  10 (max=10)  buildfrag:  111                probefrag:   1 ppa

sses: 12
  Hash join Resc: 287 Resp: 287
  Outer table:
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2   Inner table: OUT_ORD_HDR
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 21 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  11                probefrag:   111 pp

asses: 2
  Hash join Resc: 234 Resp: 234
Join result: cost: 219 cdn: 2287 rcz: 376 Now joining: CUSTOMER [CUSTOMER] ******* NL Join
  Outer table: cost: 219 cdn: 2287 rcz: 376 resp: 218   Inner table: CUSTOMER
    Access path: tsc Resc: 570
    Join: Resc: 1303808 Resp: 1303808   Access path: index (unique)

      Index: CUSTOMER_PK

  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 6.4128e-06 TB_SEL: 6.4128e-06     Join: resc: 4792 resp: 4792
  Access path: index (eq-unique)

      Index: CUSTOMER_PK
  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 4792 resp: 4792
Outer join cardinality: 2327 = max ( outer (2287) , (outer (2287) * inner (1559 39) * sel (6.5236e-06) ) [flag=16]

   Using multi-column join key (card = 153290 sel = 2.7328e-09)   Best NL cost: 4793 resp: 4792
SM Join
  Outer table:
    resc: 218 cdn: 2287 rcz: 376 deg: 1 resp: 218   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:      119 Row size:          424 Rows:       2287
      Initial runs:          1 Merge passes:        1 IO Cost / pass:        146
      Total IO sort cost: 132
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:     1833 Row size:           96 Rows:     155939
      Initial runs:         11 Merge passes:        1 IO Cost / pass:       2121
      Total IO sort cost: 1977
      Total CPU sort cost: 0
      Total Temp space used: 31564000

  Merge join Cost: 2898 Resp: 2898

HA Join
  Outer table:
    resc: 218 cdn: 2287 rcz: 376 deg: 1 resp: 218   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 12334 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  109                probefrag:   1714

ppasses: 11
  Hash join Resc: 13122 Resp: 13122

Join result: cost: 2898  cdn: 2327  rcz: 454
Best so far: TABLE#: 1  CST:        211  CDN:       2393  BYTES:     873445
Best so far: TABLE#: 0  CST:        219  CDN:       2287  BYTES:     859912
Best so far: TABLE#: 2  CST:       2898  CDN:       2327  BYTES:    1056458
***********************

Join order[3]: OUT_ORD_HDR [OUT_ORD_HDR] CUSTOMER [CUSTOMER] OUT_ORD_STAT [OUT_O RD_STAT]
Now joining: CUSTOMER [CUSTOMER] ******* NL Join
  Outer table: cost: 211 cdn: 2393 rcz: 365 resp: 211   Inner table: CUSTOMER
    Access path: tsc Resc: 570
    Join: Resc: 1364221 Resp: 1364221   Access path: index (unique)

      Index: CUSTOMER_PK
  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 6.4128e-06 TB_SEL: 6.4128e-06     Join: resc: 4997 resp: 4997
  Access path: index (eq-unique)

      Index: CUSTOMER_PK
  TABLE: CUSTOMER
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 4997 resp: 4997
Outer join cardinality: 2434 = max ( outer (2393) , (outer (2393) * inner (1559 39) * sel (6.5236e-06) ) [flag=16]

   Using multi-column join key (card = 153290 sel = 2.7328e-09)

  Best NL cost: 4997 resp: 4997
SM Join
  Outer table:
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:      121 Row size:          412 Rows:       2393
      Initial runs:          1 Merge passes:        1 IO Cost / pass:        148
      Total IO sort cost: 134
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:     1833 Row size:           96 Rows:     155939
      Initial runs:         11 Merge passes:        1 IO Cost / pass:       2121
      Total IO sort cost: 1977
      Total CPU sort cost: 0
      Total Temp space used: 31564000

  Merge join Cost: 2892 Resp: 2892
HA Join
  Outer table:
    resc: 211 cdn: 2393 rcz: 365 deg: 1 resp: 211   Inner table: CUSTOMER
    resc: 570 cdn: 155939 rcz: 78 deg: 1 resp: 570     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 13358 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  111                probefrag:   1714

ppasses: 12
  Hash join Resc: 14139 Resp: 14139
Join result: cost: 2893 cdn: 2434 rcz: 443 Now joining: OUT_ORD_STAT [OUT_ORD_STAT] ******* NL Join

 Outer table: cost: 2893 cdn: 2434 rcz: 443 resp: 2892   Inner table: OUT_ORD_STAT
    Access path: tsc Resc: 2
    Join: Resc: 7760 Resp: 7760
  Access path: index (unique)

      Index: OUT_ORD_STAT_PK
  TABLE: OUT_ORD_STAT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 9.0909e-02 TB_SEL: 9.0909e-02     Join: resc: 5326 resp: 5326
  Access path: index (eq-unique)

      Index: OUT_ORD_STAT_PK
  TABLE: OUT_ORD_STAT
      RSC_CPU: 0 RSC_IO: 1
  IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00     Join: resc: 5326 resp: 5326
Join cardinality: 2327 = outer (2434) * inner (11) * sel (8.3333e-02) [flag=0]   Best NL cost: 5327 resp: 5326
SM Join
  Outer table:
    resc: 2892 cdn: 2434 rcz: 443 deg: 1 resp: 2892   Inner table: OUT_ORD_STAT
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2     using join:1 distribution:2 #groups:1

    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:      149 Row size:          498 Rows:       2434
      Initial runs:          1 Merge passes:        1 IO Cost / pass:        176
      Total IO sort cost: 162
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           58 Area size:     1425408 Max Area size:     1425408
   Degree: 1
      Blocks to Sort:        1 Row size:           23 Rows:         11
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         10

     Total IO sort cost: 6
      Total CPU sort cost: 0
      Total Temp space used: 0

  Merge join Cost: 3062 Resp: 3062
HA Join
  Outer table:
    resc: 2892 cdn: 2434 rcz: 443 deg: 1 resp: 2892   Inner table: OUT_ORD_STAT
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 91 Deg: 1 (sides swapped)

      hash_area:  10 (max=10)  buildfrag:  136                probefrag:   1 ppa

sses: 14
  Hash join Resc: 2986 Resp: 2986
  Outer table:
    resc: 2 cdn: 11 rcz: 11 deg: 1 resp: 2   Inner table: CUSTOMER
    resc: 2892 cdn: 2434 rcz: 443 deg: 1 resp: 2892     using join:8 distribution:2 #groups:1   Hash join one ptn Resc: 25 Deg: 1

      hash_area:  10 (max=10)  buildfrag:  11                probefrag:   136 pp

asses: 2
  Hash join Resc: 2920 Resp: 2920
Final:
  CST: 2898 CDN: 2327 RSC: 2898 RSP: 2898 BYTES: 1056458   IO-RSC: 2898 IO-RSP: 2898 CPU-RSC: 0 CPU-RSP: 0 PLAN
Cost of plan: 2898
Operation...........Object name.....Options.........Id...Pid..

SELECT STATEMENT                                        0
MERGE JOIN                          OUTER               1
SORT                                JOIN                2    1
MERGE JOIN                                              3    2
TABLE ACCESS        OUT_ORD_HDR     BY INDEX ROWID      4    3
INDEX               OUT_ORD_HDR_AK_8RANGE SCAN          5    4
SORT                                JOIN                6    3
TABLE ACCESS        OUT_ORD_STAT    BY INDEX ROWID      7    6
INDEX               OUT_ORD_STAT_PK8RANGE SCAN          8    7
SORT                                JOIN                9    1
TABLE ACCESS        CUSTOMER        FULL               10    9

Received on Wed Sep 22 2004 - 18:06:14 CDT