Oracle数据库的Hash join

来源：pcdog 作者：佚名 出处：巧巧读书 2008-04-30 进入讨论组

• 关 键 词：
• oracle数据库
• cluster
• access
• opera
• 唯一索引

上一页 1 2 3 4 下一页 

    PS:每个分区只有不到10行，这里有一个重要的参数Kept，1在内存中，0在磁盘

    *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

    PS:hash join的第一阶段，但是要观察更多的阶段，需提高trace的level，这里略过

    Revised number of hash buckets (after flushing): 50

    Allocating new hash table.

    *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

    Requested size of hash table: 16

    Actual size of hash table: 16

    Number of buckets: 128

    Match bit vector allocated: FALSE

    kxhfResize(enter): resize to 14 slots (numAlloc=8, max=12)

    kxhfResize(exit): resized to 14 slots (numAlloc=8, max=14)

    freeze work area size to: 2359K (14 slots)

    *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

    Total number of rows (may have changed): 50

    Number of in-memory partitions (may have changed): 8

    Final number of hash buckets: 128

    Size (in bytes) of hash table: 1024

    kxhfIterate(end_iterate): numAlloc=8, 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: 86

    Number of buckets with 1 rows: 37

    Number of buckets with 2 rows: 5

    Number of buckets with 3 rows: 0

    PS:桶里面的行数，最大的桶也只有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

    Nmber 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: 128 Empty buckets: 86 Non-empty buckets: 42

    PS:创建了128个桶，Oracle 7开始的计算公式

    Bucket数=0.8*hash_area_size/(hash_multiblock_io_count*db_block_size)

    但是不准确，估计10g发生了变化。

    Total number of rows: 50

    Maximum number of rows in a bucket: 2

    Average number of rows in non-empty buckets: 1.190476

    小表500行时候的trace分析

    Original hash-area size: 3925453

    Memory for slot table: 2826240

    。。。

    Hash-join fanout: 8

    Number of partitions: 8

    。。。

    ### Partition Distribution ###

    Partition:0 rows:52 clusters:1 slots:1 kept=1

    Partition:1 rows:63 clusters:1 slots:1 kept=1

    Partition:2 rows:55 clusters:1 slots:1 kept=1

    Partition:3 rows:74 clusters:1 slots:1 kept=1

    Partition:4 rows:66 clusters:1 slots:1 kept=1

    Partition:5 rows:66 clusters:1 slots:1 kept=1

    Partition:6 rows:54 clusters:1 slots:1 kept=1

    Partition:7 rows:70 clusters:1 slots:1 kept=1

更多内容请看数据库专栏、数据库处理专题、城域网专题专题，或进入讨论组讨论。

上一页 1 2 3 4 下一页 

【收藏此文】【大 中 小】【打印】【关闭】

较早的文章：最强有力的辅助诊断工具SQL_TRACE

较新的文章：Oracle 10g分区表维护中的两个注意事项