Dump file D:\oracle\admin\d817\udump\ORA00932.TRC
Mon Jan 17 23:15:28 2005
ORACLE V8.1.7.4.1 - Production vsnsta=0
vsnsql=f vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: d817

Redo thread mounted by this instance: 1

Oracle process number: 11

Windows thread id: 932, image: ORACLE.EXE


*** 2005-01-17 23:15:28.031
*** SESSION ID:(8.961) 2005-01-17 23:15:28.021
QUERY
select
	/*+ ordered full(bu) full(pb) use_hash(pb) */
	bu.build_vc,
	bu.build_padding,
	pb.probe_vc,
	pb.probe_padding
from
	build_tab	bu,
	probe_tab	pb
where
	bu.id between 1 and 2000
and	pb.id = bu.id_probe
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = All_Rows
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 524288
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_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 = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 8
SORT_AREA_SIZE = 524288
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: PROBE_TAB   Alias: PB
  TOTAL ::  CDN: 10000  NBLKS:  1429  TABLE_SCAN_CST: 218  AVG_ROW_LEN:  1030
-- Index stats
  INDEX#: 32412  COL#: 1 
    TOTAL ::  LVLS: 1   #LB: 21  #DK: 10000  LB/K: 1  DB/K: 1  CLUF: 1429
***********************
Table stats    Table: BUILD_TAB   Alias: BU
  TOTAL ::  CDN: 10000  NBLKS:  1429  TABLE_SCAN_CST: 218  AVG_ROW_LEN:  1030
-- Index stats
  INDEX#: 32415  COL#: 2 
    TOTAL ::  LVLS: 1   #LB: 23  #DK: 4327  LB/K: 1  DB/K: 2  CLUF: 9990
  INDEX#: 32414  COL#: 1 
    TOTAL ::  LVLS: 1   #LB: 20  #DK: 10000  LB/K: 1  DB/K: 1  CLUF: 1429
***************************************
SINGLE TABLE ACCESS PATH
Column:         ID  Col#: 1      Table: BUILD_TAB   Alias: BU
    NDV: 10000     NULLS: 0         DENS: 1.0000e-004 LO:  1  HI: 10000
  TABLE: BUILD_TAB     ORIG CDN: 10000  CMPTD CDN: 2001
  Access path: tsc  Resc:  218  Resp:  218
  BEST_CST: 218.00  PATH: 2  Degree:  1
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: PROBE_TAB     ORIG CDN: 10000  CMPTD CDN: 10000
  Access path: tsc  Resc:  218  Resp:  218
  BEST_CST: 218.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***********************
Join order[1]: BUILD_TAB [BU] PROBE_TAB [PB] 
Now joining: PROBE_TAB [PB] *******
NL Join
  Outer table: cost: 218  cdn: 2001  rcz: 1030  resp:  218
  Inner table: PROBE_TAB
    Access path: tsc  Resc: 218
    Join resc:  436436  Resp:  436436
Join cardinality:  2001 = outer (2001) * inner (10000) * sel (1.0000e-004)  [flag=0]
  Best NL cost: 436436  resp: 436436
HA Join
  Outer table: 
    resc: 218  cdn: 2001  rcz: 1030  deg: 1  resp: 218
  Inner table: PROBE_TAB
    resc: 218  cdn: 10000  rcz: 1027  deg:  1  resp: 218

  Hash join one ptn:  1581   Deg:  1
      hash_area:  64   buildfrag:  255   probefrag:   1269               ppasses:    4
  Hash join   Resc: 2017   Resp: 2017
Join result: cost: 2017  cdn: 2001  rcz: 2057
Best so far: TABLE#: 0  CST:        218  CDN:       2001  BYTES:    2061030
Best so far: TABLE#: 1  CST:       2017  CDN:       2001  BYTES:    4116057
Final:
  CST: 2017  CDN: 2001  RSC: 2017  RSP: 2017  BYTES: 4116057
qerhjAllocate: enr 2001.000000 enb 2061030.000000 erl_qerhjs 1050 ens_qerhjs 65
*** HASH JOIN STATISTICS (INITIALIZATION) ***
Original memory: 524288
Calculated length of build rows: 1050
Memory after hash table overhead: 490598
Calculated overhead for partitions and row/slot managers: 1332
Number of partitions: 8
Number of slots: 14
Cluster (slot) size: 32768
Block size: 8192
Minimum number of bytes per block: 8160
Multiblock IO: 4
Bit vector memory allocation: 26214
Per partition bit vector length: 2048
Maximum possible row length: 1081
Estimated Cardinality: 1962
Estimated Row Length (includes overhead): 1050
Estimated Input Size: 2061030
# Immutable Flags:
*** HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of partitions: 8
Number of partitions that fit in memory: 0
Total number of rows in in-memory partitions: 0
   (used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 876
### Partition Distribution ###
Partition    0 rows:        247 clusters:      8 in-memory slots      1
Partition    1 rows:        244 clusters:      8 in-memory slots      1
Partition    2 rows:        208 clusters:      7 in-memory slots      1
Partition    3 rows:        260 clusters:      9 in-memory slots      1
Partition    4 rows:        260 clusters:      9 in-memory slots      1
Partition    5 rows:        243 clusters:      8 in-memory slots      1
Partition    6 rows:        282 clusters:      9 in-memory slots      1
Partition    7 rows:        256 clusters:      9 in-memory slots      6
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Revised number of hash buckets (after flushing): 282
Allocating new hash table.
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Requested size of hash table: 4096
Actual size of hash table: 4096
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of rows (may have changed): 0
Number of in-memory partitions (may have changed): 0
Final number of hash buckets: 512
Size of hash table: 4096
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 256
 PROBE PARTION number of rows: 220
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 220
Number of rows iterated over this function call: 220
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        344
Number of buckets with   1 rows:        121
Number of buckets with   2 rows:         42
Number of buckets with   3 rows:          5
Number of buckets with   4 rows:          0
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 344 Non-empty buckets: 168
Total number of rows: 220
Maximum number of rows in a bucket: 3
Average number of rows in non-empty buckets: 1.309524
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 282
 PROBE PARTION number of rows: 248
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 248
Number of rows iterated over this function call: 248
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        329
Number of buckets with   1 rows:        142
Number of buckets with   2 rows:         25
Number of buckets with   3 rows:         13
Number of buckets with   4 rows:          1
Number of buckets with   5 rows:          1
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          1
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 329 Non-empty buckets: 183
Total number of rows: 248
Maximum number of rows in a bucket: 8
Average number of rows in non-empty buckets: 1.355191
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 243
 PROBE PARTION number of rows: 219
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 219
Number of rows iterated over this function call: 219
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        346
Number of buckets with   1 rows:        121
Number of buckets with   2 rows:         39
Number of buckets with   3 rows:          4
Number of buckets with   4 rows:          2
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 346 Non-empty buckets: 166
Total number of rows: 219
Maximum number of rows in a bucket: 4
Average number of rows in non-empty buckets: 1.319277
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 260
 PROBE PARTION number of rows: 229
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 229
Number of rows iterated over this function call: 229
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        329
Number of buckets with   1 rows:        147
Number of buckets with   2 rows:         28
Number of buckets with   3 rows:          6
Number of buckets with   4 rows:          2
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 329 Non-empty buckets: 183
Total number of rows: 229
Maximum number of rows in a bucket: 4
Average number of rows in non-empty buckets: 1.251366
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 260
 PROBE PARTION number of rows: 236
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 236
Number of rows iterated over this function call: 236
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        331
Number of buckets with   1 rows:        139
Number of buckets with   2 rows:         32
Number of buckets with   3 rows:          7
Number of buckets with   4 rows:          3
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 331 Non-empty buckets: 181
Total number of rows: 236
Maximum number of rows in a bucket: 4
Average number of rows in non-empty buckets: 1.303867
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 208
 PROBE PARTION number of rows: 183
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 183
Number of rows iterated over this function call: 183
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        363
Number of buckets with   1 rows:        117
Number of buckets with   2 rows:         30
Number of buckets with   3 rows:          2
Number of buckets with   4 rows:          0
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 363 Non-empty buckets: 149
Total number of rows: 183
Maximum number of rows in a bucket: 3
Average number of rows in non-empty buckets: 1.228188
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 244
 PROBE PARTION number of rows: 213
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 213
Number of rows iterated over this function call: 213
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        349
Number of buckets with   1 rows:        123
Number of buckets with   2 rows:         32
Number of buckets with   3 rows:          6
Number of buckets with   4 rows:          2
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 349 Non-empty buckets: 163
Total number of rows: 213
Maximum number of rows in a bucket: 4
Average number of rows in non-empty buckets: 1.306748
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION number of rows: 247
 PROBE PARTION number of rows: 220
   ROLE REVERSAL OCCURRED
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 36
Number of rows left to be iterated over (start of function): 220
Number of rows iterated over this function call: 220
Number of rows left to be iterated over (end of function): 0
### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        349
Number of buckets with   1 rows:        117
Number of buckets with   2 rows:         36
Number of buckets with   3 rows:          9
Number of buckets with   4 rows:          1
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:          0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 349 Non-empty buckets: 163
Total number of rows: 220
Maximum number of rows in a bucket: 4
Average number of rows in non-empty buckets: 1.349693
QUERY
alter session set events '10053 trace name context off'
