上一个实验中 记录了 多遍HJ 的COST 计算,这次看下pga auto 下optimal join(hash join) 启用CUP COSTING 计算时候的成本(默认) ,与manual时候的差别
大部分情况都是 PGA 为AUTO 但这会导致cost变大
SQL> select * from v$version;
BANNER
-------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - P
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL>
~~~~~~先看 manual时候的
_optimizer_cost_model choose(计算CPUCOSTING,也可以设置成 'CPU')
SQL> show parameter pga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 5G
workarea_size_policy string MANUAL
hash_area_size integer 1048576
由于实验的BULIT TAB 大小为2M 所以要加大hash_area_size 让其为 optimal join全部在内存中完成
SQL> alter system set hash_area_size=3145728 scope=spfile;
System altered.
SQL> startup force
hash_area_size integer 3145728
开启 10053 trace
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=842 Card=2000 Byte
s=4114000)
1 0 HASH JOIN (Cost=842 Card=2000 Bytes=4114000)
2 1 TABLE ACCESS (FULL) OF 'BUILD_TAB' (TABLE) (Cost=421 Car
d=2000 Bytes=2060000)
3 1 TABLE ACCESS (FULL) OF 'PROBE_TAB' (TABLE) (Cost=421 Car
d=10000 Bytes=10270000)
SQL> set autotrace off
SQL> alter session set events '10053 trace name context forever';
~~~~~~~~~~~~~~~~~~~~~~~~~trace 中内容
HA Join
Outer table:
resc: 421 cdn: 2000 rcz: 1030 deg: 1 resp: 421
Inner table: PROBE_TAB Alias: PB
resc: 421 cdn: 10000 rcz: 1027 deg: 1 resp: 421
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 384 (max=384) buildfrag: 255 probefrag: 1269 ppasses: 1
Hash join Resc: 842 Resp: 842
****max=384 384个BLOCKS
SQL> select 384*8192 from dual;
384*8192
----------
3145728~~~~~~~~~~~~~~~~~~~~~~~~~~与设置的一样 ,可用HASH JOIN 内存大小
SQL>
~~~~~~~~~~~~~~~~~~~`
改为 PGA AUTO
SQL> alter system set workarea_size_policy='AUTO';
System altered.
SQL> alter system set pga_aggregate_target=100M;
SQL> select
2 /*+ ordered full(bu) full(pb) use_hash(pb) traced */
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=1364 Card=2000 Byt
es=4114000)
1 0 HASH JOIN (Cost=1364 Card=2000 Bytes=4114000)
2 1 TABLE ACCESS (FULL) OF 'BUILD_TAB' (TABLE) (Cost=421 Car
d=2000 Bytes=2060000)
3 1 TABLE ACCESS (FULL) OF 'PROBE_TAB' (TABLE) (Cost=421 Car
d=10000 Bytes=10270000)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~COST 加大了许多
HA Join
Outer table:
resc: 421 cdn: 2000 rcz: 1030 deg: 1 resp: 421
Inner table: PROBE_TAB Alias: PB
resc: 421 cdn: 10000 rcz: 1027 deg: 1 resp: 421
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 522 Deg: 1
hash_area: 124 (max=1280) buildfrag: 255 probefrag: 1269 ppasses: 1
Hash join Resc: 1364 Resp: 1364
******* hash_area: 124 (max=1280) ******
SQL> select 1280*8129/1024/1024 MB from dual;
MB
----------
9.9230957
最大HASH JOIN 可用量 不是按5% PGA_AGGREGATE_TARGET 来的,是按 0.1*PGA_AGGREGATE_TARGET
hash_area: 124 ~~~~在AUTO时候 最小内存可用量 由_smm_min_size 控制
_smm_min_size 128
minimum work area size in auto mode
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-608490/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-608490/