oracle 记录 计算,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