oracle實驗記錄 （計算hash join cost）

fufuh2o發表於2009-07-06
Oracle


計算hash join cost

hash_area_size                       integer     1048576
workarea_size_policy                 string      MANUAL
db_file_multiblock_read_count        integer     16
db_block_size                        integer     8192

 


SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

Session altered.

SQL> select
  2     /*+ ordered full(bu) full(pb) use_hash(pb) */
  3     bu.build_vc,
  4     bu.build_padding,
  5     pb.probe_vc,
  6     pb.probe_padding
  7  from
  8     build_tab       bu,
  9     probe_tab       pb
 10  where
 11     bu.id between 1 and 2000
 12  and        pb.id = bu.id_probe
 13  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1206 Card=2000 Byt
          es=4114000)

   1    0   HASH JOIN (Cost=1206 Card=2000 Bytes=4114000)
   2    1     TABLE ACCESS (FULL) OF 'BUILD_TAB' (TABLE) (Cost=421 Car~~~~~~~~~~~~~~~~~~~2M
          d=2000 Bytes=2060000)

   3    1     TABLE ACCESS (FULL) OF 'PROBE_TAB' (TABLE) (Cost=421 Car~~~~~~~~~~~~~~~10M
          d=10000 Bytes=10270000)

 


SQL> alter session set events '10053 trace name context off';
HA Join
  Outer table:
    resc: 421  cdn: 2000  rcz: 1030  deg: 1  resp: 421~~~~~~~~~~~~~~~cost
  Inner table: PROBE_TAB  Alias: PB
    resc: 421  cdn: 10000  rcz: 1027  deg:  1  resp: 421~~~~~~cost
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 364   Deg: 1
    hash_area: 128 (max=128)  buildfrag: 255  probefrag: 1269 ppasses: 1~~~~~~~~~~~探察遍數
  Hash join   Resc: 1206   Resp: 1206
Join result: cost: 1206  cdn: 2000  rcz: 2057

 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~10104 trace
Original hash-area size: 1028839
Memory for slot table: 983040
Calculated overhead for partitions and row/slot managers: 45799
Hash-join fanout: 8
Number of partitions: 8
Number of slots: 15
Multiblock IO: 8
Block size(KB): 8
Cluster (slot) size(KB): 64
Minimum number of bytes per block: 8160
Bit vector memory allocation(KB): 32
Per partition bit vector length(KB): 4
Maximum possible row length: 1194
Estimated build size (KB): 2
Estimated Build Row Length (includes overhead): 613


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~計算 h

 2個表掃描成本為 421 1206-842=364
下面計算 364 的 cost (onepass情況)build table  大無法讀入記憶體全部,多出來的I/O,     optimal情況2個表cost相加就是了~~


SQL> select 2060000/8192,10270000/8192 from dual;

2060000/8192 10270000/8192
------------ -------------
  251.464844    1253.66211              `~~~~~~~~每個表佔多少個塊
~~~~公式  (探察遍數+1)*舍入(大表塊數/i/o量取值)+舍入(小表塊/i/o量取值)
SQL> select 2*ceil(1253/8)+ceil(252/8) from dual;

2*CEIL(1253/8)+CEIL(252/8)
--------------------------
                       346~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有些誤差~~~~~i/0量 取值 從哪看 是從 aux_stats$ mbrc?還是 看 10104 trace中? 我還不清楚 還要繼續實驗

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                       1430.318
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                               10
MREADTIM                               20
CPUSPEED                              500
MBRC                                    8

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-608413/，如需轉載，請註明出處，否則將追究法律責任。

相關文章