# 10104 Trace Sample

### EXEM Knowledge Base

Dump file c:\oracle\admin\ukja10\udump\ukja10_ora_2900.trc Mon May 12 18:58:35 2008 ORACLE V10.2.0.1.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Windows XP Version V5.1 Service Pack 2 CPU : 2 - type 586 Process Affinity : 0x00000000 Memory (Avail/Total): Ph:518M/2046M, Ph+PgF:2263M/3942M, VA:1641M/2047M Instance name: ukja10 Redo thread mounted by this instance: 1 Oracle process number: 17 Windows thread id: 2900, image: ORACLE.EXE (SHAD) *** 2008-05-12 18:58:35.671 *** ACTION NAME:() 2008-05-12 18:58:35.656 *** MODULE NAME:(SQL*Plus) 2008-05-12 18:58:35.656 *** SERVICE NAME:(UKJA10) 2008-05-12 18:58:35.656 *** SESSION ID:(144.1278) 2008-05-12 18:58:35.656 kxhfInit(): enter kxhfInit(): exit *** RowSrcId: 2 HASH JOIN STATISTICS (INITIALIZATION) *** Join Type: INNER join Original hash-area size: 504269 Memory for slot table: 458752 Calculated overhead for partitions and row/slot managers: 45517 Hash-join fanout: 8 Number of partitions: 8 Number of slots: 14 Multiblock IO: 4 Block size(KB): 8 Cluster (slot) size(KB): 32 Minimum number of bytes per block: 8160 Bit vector memory allocation(KB): 16 Per partition bit vector length(KB): 2 Maximum possible row length: 237 Estimated build size (KB): 1142 Estimated Build Row Length (includes overhead): 117 # Immutable Flags: Not BUFFER(execution) output of the join for PQ Evaluate Left Input Row Vector Evaluate Right Input Row Vector # Mutable Flags: IO sync kxhfSetPhase: phase=BUILD kxhfAddChunk: add chunk 0 (sz=14) to slot table kxhfAddChunk: chunk 0 (lbs=08745340, slotTab=08745298) successfuly added kxhfWrite: hash-join is spilling to disk kxhfSetPhase: phase=PROBE_1 qerhjFetch: max build row length (mbl=115) *** RowSrcId: 2 HASH JOIN BUILD HASH TABLE (PHASE 1) *** Total number of partitions: 8 Number of partitions which could fit in memory: 1 Number of partitions left in memory: 1 Total number of slots in in-memory partitions: 5 Total number of rows in in-memory partitions: 1238 (used as preliminary number of buckets in hash table) Estimated max # of build rows that can fit in avail memory: 7952 ### Partition Distribution ### Partition:0 rows:1275 clusters:5 slots:1 kept=0 Partition:1 rows:1276 clusters:5 slots:1 kept=0 Partition:2 rows:1231 clusters:5 slots:1 kept=0 Partition:3 rows:1266 clusters:5 slots:1 kept=0 Partition:4 rows:1196 clusters:5 slots:1 kept=0 Partition:5 rows:1278 clusters:5 slots:1 kept=0 Partition:6 rows:1240 clusters:5 slots:2 kept=0 Partition:7 rows:1238 clusters:5 slots:5 kept=1 *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) *** Revised number of hash buckets (after flushing): 1278 Allocating new hash table. *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) *** Requested size of hash table: 256 Actual size of hash table: 256 Number of buckets: 2048 Match bit vector allocated: FALSE *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) *** Total number of rows (may have changed): 1238 Number of in-memory partitions (may have changed): 1 Final number of hash buckets: 2048 Size (in bytes) of hash table: 8192 kxhfIterate(end_iterate): numAlloc=14, maxSlots=14 *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) *** ### 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: 1110 Number of buckets with 1 rows: 689 Number of buckets with 2 rows: 200 Number of buckets with 3 rows: 47 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: 2048 Empty buckets: 1110 Non-empty buckets: 938 Total number of rows: 1238 Maximum number of rows in a bucket: 4 Average number of rows in non-empty buckets: 1.319829 Disabled bitmap filtering: filtered rows=0 minimum required=50 out of=1000 qerhjFetch: max probe row length (mpl=119) kxhfSetPhase: phase=PROBE_2 qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1275 size=(5 slots, 160K) PROBE PARTION: nrows:1275 size=(5 slots, 160K) *** RowSrcId: 2 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): 1275 Number of rows iterated over this function call: 1275 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: 1120 Number of buckets with 1 rows: 654 Number of buckets with 2 rows: 213 Number of buckets with 3 rows: 52 Number of buckets with 4 rows: 7 Number of buckets with 5 rows: 1 Number of buckets with 6 rows: 1 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: 2048 Empty buckets: 1120 Non-empty buckets: 928 Total number of rows: 1275 Maximum number of rows in a bucket: 6 Average number of rows in non-empty buckets: 1.373922 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1240 size=(5 slots, 160K) PROBE PARTION: nrows:1240 size=(5 slots, 160K) *** RowSrcId: 2 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): 1240 Number of rows iterated over this function call: 1240 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: 1114 Number of buckets with 1 rows: 674 Number of buckets with 2 rows: 220 Number of buckets with 3 rows: 34 Number of buckets with 4 rows: 6 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: 2048 Empty buckets: 1114 Non-empty buckets: 934 Total number of rows: 1240 Maximum number of rows in a bucket: 4 Average number of rows in non-empty buckets: 1.327623 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1278 size=(5 slots, 160K) PROBE PARTION: nrows:1278 size=(5 slots, 160K) *** RowSrcId: 2 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): 1278 Number of rows iterated over this function call: 1278 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: 1110 Number of buckets with 1 rows: 660 Number of buckets with 2 rows: 229 Number of buckets with 3 rows: 37 Number of buckets with 4 rows: 11 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: 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: 2048 Empty buckets: 1110 Non-empty buckets: 938 Total number of rows: 1278 Maximum number of rows in a bucket: 5 Average number of rows in non-empty buckets: 1.362473 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1196 size=(5 slots, 160K) PROBE PARTION: nrows:1196 size=(5 slots, 160K) *** RowSrcId: 2 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): 1196 Number of rows iterated over this function call: 1196 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: 1128 Number of buckets with 1 rows: 693 Number of buckets with 2 rows: 182 Number of buckets with 3 rows: 41 Number of buckets with 4 rows: 4 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: 2048 Empty buckets: 1128 Non-empty buckets: 920 Total number of rows: 1196 Maximum number of rows in a bucket: 4 Average number of rows in non-empty buckets: 1.300000 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1266 size=(5 slots, 160K) PROBE PARTION: nrows:1266 size=(5 slots, 160K) *** RowSrcId: 2 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): 1266 Number of rows iterated over this function call: 1266 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: 1096 Number of buckets with 1 rows: 701 Number of buckets with 2 rows: 204 Number of buckets with 3 rows: 35 Number of buckets with 4 rows: 8 Number of buckets with 5 rows: 4 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: 2048 Empty buckets: 1096 Non-empty buckets: 952 Total number of rows: 1266 Maximum number of rows in a bucket: 5 Average number of rows in non-empty buckets: 1.329832 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1231 size=(5 slots, 160K) PROBE PARTION: nrows:1231 size=(5 slots, 160K) *** RowSrcId: 2 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): 1231 Number of rows iterated over this function call: 1231 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: 1116 Number of buckets with 1 rows: 691 Number of buckets with 2 rows: 193 Number of buckets with 3 rows: 39 Number of buckets with 4 rows: 8 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: 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: 2048 Empty buckets: 1116 Non-empty buckets: 932 Total number of rows: 1231 Maximum number of rows in a bucket: 5 Average number of rows in non-empty buckets: 1.320815 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) *** Getting a pair of flushed partions. BUILD PARTION: nrows:1276 size=(5 slots, 160K) PROBE PARTION: nrows:1276 size=(5 slots, 160K) *** RowSrcId: 2 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): 1276 Number of rows iterated over this function call: 1276 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: 1130 Number of buckets with 1 rows: 627 Number of buckets with 2 rows: 229 Number of buckets with 3 rows: 58 Number of buckets with 4 rows: 3 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: 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: 2048 Empty buckets: 1130 Non-empty buckets: 918 Total number of rows: 1276 Maximum number of rows in a bucket: 5 Average number of rows in non-empty buckets: 1.389978 kxhfResetIter(08643F1C) qerhjFetchPhase2(): building a hash table *** RowSrcId: 2, qerhjFreeSpace(): free hash-join memory kxhfRemoveChunk: remove chunk 0 from slot table