oracle實驗記錄 （PGA manual or auto 與hash join）

fufuh2o發表於2009-07-07
Oracle

上一個實驗中 記錄了 多遍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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章