Oracle数据库的Hash join

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

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

上一页 1 2 3 4 下一页 

    测试SQL

    SELECT * FROM vendition a,customer b WHERE a.customerid = b.customerid;

    SELECT * FROM vendition a,customer_small b WHERE a.customerid = b.customerid;

    SELECT * FROM vendition a,customer_pity b WHERE a.customerid = b.customerid;

    小表50行时候的trace分析：

    *** 2008-03-23 18:17:49.467

    *** SESSION ID:(773.23969) 2008-03-23 18:17:49.467

    kxhfInit(): enter

    kxhfInit(): exit

    *** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***

    Join Type: INNER join

    Original hash-area size: 3883510

    PS:hash area的大小，大约380k，本例中最大的表也不过250块左右，所以内存完全可以完全装载

    Memory for slot table: 2826240

    Calculated overhead for partitions and row/slot managers: 1057270

    Hash-join fanout: 8

    Number of partitions: 8

    PS:hash 表数据连一个块都没装满，Oracle仍然对数据进行了分区，这里和以前在一些文档上看到的，当内存不足时才会对数据分区的说法，发生了变化。

    Number of slots: 23

    Multiblock IO: 15

    Block size(KB): 8

    Cluster (slot) size(KB): 120

    PS:分区中全部行占有的cluster的size

    Minimum number of bytes per block: 8160

    Bit vector memory allocation(KB): 128

    Per partition bit vector length(KB): 16

    Maximum possible row length: 270

    Estimated build size (KB): 0

    Estimated Build Row Length (includes overhead): 45

    # 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=32) to slot table

    kxhfAddChunk: chunk 0 (lbs=0x2a97825c38, slotTab=0x2a97825e00) successfuly added

    kxhfSetPhase: phase=PROBE_1

    qerhjFetch: max build row length (mbl=44)

    *** RowSrcId: 1 END OF HASH JOIN BUILD (PHASE 1) ***

    Revised row length: 45

    Revised build size: 2KB

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

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

    Slot table resized: old=23 wanted=12 got=12 unload=0

    *** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***

    Total number of partitions: 8

    Number of partitions which could fit in memory: 8

    Number of partitions left in memory: 8

    Total number of slots in in-memory partitions: 8

    Total number of rows in in-memory partitions: 50

    (used as preliminary number of buckets in hash table)

    Estimated max # of build rows that can fit in avail memory: 66960

    ### Partition Distribution ###

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

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

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

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

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

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

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

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

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

上一页 1 2 3 4 下一页 

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

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

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