oracle实验记录 (PGA manual or auto 与hash join)

上一个实验中 记录了 多遍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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值