oracle实验记录 （计算hash join cost）

计算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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-608413/