グローバル統計／パーティション統計の収集 で GRANULARITY => 'GLOBAL AND PARTITION' と 'APPROX_GLOBAL AND PARTITION' の負荷の違いを計測してみる。

やってみる。まずはデータ作成。

-- データ作成
DROP TABLE TBL_A;
CREATE TABLE TBL_A (
    C1 NUMBER
  , C2 DATE
  , C3 NUMBER
  , C4 VARCHAR2(100)
)
PARTITION BY RANGE (C2)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
SUBPARTITION BY HASH (C1)
SUBPARTITIONS 16
(
  PARTITION P1 VALUES LESS THAN(TO_DATE('20160428', 'YYYYMMDD'))
);

INSERT /*+ APPEND */ INTO TBL_A
SELECT LEVEL                                                AS C1
     , TO_DATE('2016/04/27', 'YYYY/MM/DD')+(LEVEL/24/60/60) AS C2
     , MOD(LEVEL, 26)                                       AS C3
     , CHR(ASCII('A') + MOD(LEVEL, 26))                     AS C4
  FROM DUAL
CONNECT BY LEVEL <= 1000000;

COMMIT;

グローバル統計／パーティション統計の収集コマンドは下記の２種類。

-- 統計情報収集(CASE1. GLOBAL AND PARTITION) 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'CASE1';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
      'AYSHIBAT'
    , 'TBL_A'
    , GRANULARITY => 'GLOBAL AND PARTITION'
    , METHOD_OPT  => 'FOR ALL COLUMNS SIZE AUTO '
                  || 'FOR COLUMNS SIZE 254 C1 '
                  || 'FOR COLUMNS SIZE 254 (C3, C4)'
  );
END;
/
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

-- 統計情報収集(CASE2. APPROX_GLOBAL AND PARTITION) 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'CASE2';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
      'AYSHIBAT'
    , 'TBL_A'
    , GRANULARITY => 'APPROX_GLOBAL AND PARTITION'
    , METHOD_OPT  => 'FOR ALL COLUMNS SIZE AUTO '
                  || 'FOR COLUMNS SIZE 254 C1 '
                  || 'FOR COLUMNS SIZE 254 (C3, C4)'
  );
END;
/
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

結果をtkprof後のトレースで比較。

--CASE1(GLOBAL AND PARTITION)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.40       0.57          7       3237       1445           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.40       0.58          7       3237       1445           3
：
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1062      0.19       0.26         20        299         52           0
Execute   3363      1.35       2.16        118      21075      33144       82499
Fetch     4900      2.58       2.83        343      16668          0       10983
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9325      4.13       5.26        481      38042      33196       93482

--CASE2(APPROX_GLOBAL AND PARTITION)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.36       0.36          0       1171       1449           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.37       0.37          0       1171       1449           3
：
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      406      0.03       0.03          0          0          0           0
Execute    478      0.18       0.21          0      11706        906       71751
Fetch      448      2.52       2.55        333       8076          0        3712
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1332      2.74       2.80        333      19782        906       75463

グローバル統計をパーティション統計の集計で求める'APPROX_GLOBAL AND PARTITION'の方が、
負荷(query値／current値)は低いぜ！(｀･ω･)Ъ

マニュアルは下記。

155 DBMS_STATS
GATHER_TABLE_STATSプロシージャ
http://docs.oracle.com/cd/E57425_01/121/ARPLS/d_stats.htm#i1036461