LoginSignup
2

More than 5 years have passed since last update.

増分統計(INCREMENTALプリファレンス)を有効にした状態で統計を採取して、シノプシス(synopsis)が使われる様子をSQLトレースで確認する。

Last updated at Posted at 2018-11-28

表題の通り、増分統計(INCREMENTALプリファレンス=TRUE)を採取してみて、
内部動作をSQLトレースで確認してみるやで彡(゚)(゚)

Oracle Database SQLチューニング・ガイド 18c
13.2.8.2 DBMS_STATSによるパーティション表のグローバル統計の導出方法
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-6ECF96F3-72DD-4B09-974D-70D3FABE6F47
増分統計メンテナンスが有効な場合、DBMS_STATSでは、変更されたパーティションのみの統計を収集し、シノプシスを作成します。また、データベースは、パーティション・レベルのシノプシスをグローバル・シノプシスに自動的にマージし、パーティション・レベルの統計およびグローバル・シノプシスからグローバル統計を導出します。

1. オブジェクト作成と増分統計の有効化

まずはオブジェクトの作成と増分統計の有効化を実行彡(゚)(゚)

CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy
-- テーブル作成
DROP TABLE TBL_A PURGE;
CREATE TABLE TBL_A (
    C1 NUMBER
  , C2 VARCHAR2(10)
  , C3 DATE
)
PARTITION BY RANGE (C1) INTERVAL(10000)
(PARTITION VALUES LESS THAN (10001));

-- 索引作成
CREATE INDEX TBL_A_I1 ON TBL_A(C1, C3) LOCAL;
CREATE INDEX TBL_A_I2 ON TBL_A(C1) GLOBAL;

-- INCREMENTALプリファレンス有効化と確認
COLUMN PREFERENCE FORMAT A30;
EXEC DBMS_STATS.SET_TABLE_PREFS('AYSHIBAT', 'TBL_A', 'INCREMENTAL', 'TRUE');
SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'AYSHIBAT', 'TBL_A') AS PREFERENCE FROM DUAL;

Table dropped.

Table created.

Index created.

Index created.

PL/SQL procedure successfully completed.

PREFERENCE
------------------------------
TRUE

2. テストデータの作成

次にテストデータを投入しますやで彡(゚)(゚)

-- テストデータ作成
 INSERT INTO TBL_A
 SELECT LEVEL, 'C2_' || LEVEL, SYSDATE + (LEVEL/24/60)
   FROM DUAL
CONNECT BY LEVEL <= 50000;
COMMIT;
SELECT COUNT(*) FROM TBL_A;

50000 rows created.

Commit complete.

  COUNT(*)
----------
     50000

3. SQLトレースを取得しながら統計を採取

SQLトレースを取得しながらトレースを採取彡(゚)(゚)

-- 統計採取およびSQLトレース取得
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A');
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

-- オプティマイザ統計確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN OWNER FORMAT             A20;
COLUMN TABLE_NAME FORMAT        A20;
COLUMN INDEX_NAME FORMAT        A20;
COLUMN PARTITION_NAME FORMAT    A20;
COLUMN SUBPARTITION_NAME FORMAT A20;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;
SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;

Connected.

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

OWNER                TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                                                          2018/11/28 02:09:12      50000 NO
AYSHIBAT             TBL_A                SYS_P4443                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4444                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4445                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4446                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4447                                 2018/11/28 02:09:11      10000 NO

OWNER                TABLE_NAME           INDEX_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                TBL_A_I1                                                       2018/11/28 02:09:13      50000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4443                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4444                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4445                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4446                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4447                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I2                                                       2018/11/28 02:09:13      50000 NO

7 rows selected.

4. SQLトレース(整形後)の確認

SQLトレースを確認しますやで。tkprofの整形後のトレースです彡(゚)(゚)
シノプシス表(wri\$_optstat_synopsis_head\$)にデータをINSERTしてますね!

TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:11:48 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_4417_AYSHIBAT.trc
Sort options: default
:
:
SQL ID: 808a0gzdt26kc Plan Hash: 0

insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1,
  spare2)
values
 (:1, :2, :3, :4, :5) ★シノプシス表へのINSERT文


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       15      0.00       0.00          0          0          0           0
Execute     15      0.00       0.00          0         25        135          15
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       30      0.00       0.00          0         25        135          15
:

5. 追加データ投入と統計失効確認

追加データを投入します。DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOで
失効状態をフラッシュした後に、統計が失効したことを確認彡(゚)(゚)

CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy
-- 追加データ投入
 INSERT INTO TBL_A
 SELECT LEVEL+50000, 'C2_' || (LEVEL+50000), SYSDATE + (LEVEL/24/60)
   FROM DUAL
CONNECT BY LEVEL <= 10000;
COMMIT;

-- 統計情報の失効を即座に反映
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-- オプティマイザ統計確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN OWNER FORMAT             A20;
COLUMN TABLE_NAME FORMAT        A20;
COLUMN INDEX_NAME FORMAT        A20;
COLUMN PARTITION_NAME FORMAT    A20;
COLUMN SUBPARTITION_NAME FORMAT A20;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;
SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;

Connected.

10000 rows created.

Commit complete.

PL/SQL procedure successfully completed.

Session altered.

OWNER                TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                                                          2018/11/28 02:09:12      50000 YES ★統計が失効
AYSHIBAT             TBL_A                SYS_P4443                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4444                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4445                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4446                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4447                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4448

7 rows selected.

OWNER                TABLE_NAME           INDEX_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                TBL_A_I1                                                       2018/11/28 02:09:13      50000 YES ★統計が失効
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4443                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4444                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4445                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4446                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4447                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4448
AYSHIBAT             TBL_A                TBL_A_I2                                                       2018/11/28 02:09:13      50000 YES ★統計が失効

8 rows selected.

6. SQLトレースを取得しながら増分統計を採取

グローバル統計が失効した状態で、統計を採取します彡(゚)(゚)

-- 増分統計採取およびSQLトレース取得
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A');
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

-- オプティマイザ統計確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN OWNER FORMAT             A20;
COLUMN TABLE_NAME FORMAT        A20;
COLUMN INDEX_NAME FORMAT        A20;
COLUMN PARTITION_NAME FORMAT    A20;
COLUMN SUBPARTITION_NAME FORMAT A20;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;
SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST;

Connected.

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

OWNER                TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                                                          2018/11/28 02:16:03      60000 NO
AYSHIBAT             TBL_A                SYS_P4443                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4444                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4445                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4446                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4447                                 2018/11/28 02:09:11      10000 NO
AYSHIBAT             TBL_A                SYS_P4448                                 2018/11/28 02:16:03      10000 NO

7 rows selected.

OWNER                TABLE_NAME           INDEX_NAME           PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED         NUM_ROWS STA
-------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- ---
AYSHIBAT             TBL_A                TBL_A_I1                                                       2018/11/28 02:16:04      60000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4443                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4444                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4445                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4446                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4447                                 2018/11/28 02:09:13      10000 NO
AYSHIBAT             TBL_A                TBL_A_I1             SYS_P4448                                 2018/11/28 02:16:04      10000 NO
AYSHIBAT             TBL_A                TBL_A_I2                                                       2018/11/28 02:16:04      60000 NO

8 rows selected.

7. 増分統計採取時のSQLトレース(整形後)確認

増分統計によるSQLトレースは下記の通り。
でもこれだけだと増分統計の効果が判らないので…彡(゚)(゚)

TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:17:18 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_4496_AYSHIBAT.trc
Sort options: default
:
:
insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1,
  spare2)
values
 (:1, :2, :3, :4, :5)
:
:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      143      0.01       0.01          0          0          0           0
Execute    455      0.04       0.04          3        136        292          50
Fetch      382      0.65       0.65          0       3009          0         318
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      980      0.71       0.71          3       3145        292         368
:

8. 参考:増分統計を有効化しなかった場合のSQLトレース(※7.のトレースと同じ条件)

増分統計を有効化しなかった場合のSQLトレースも見てみます。
増分統計を有効化していない以外は7.のSQLトレースと同じ条件です。

TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:36:53 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_4648_AYSHIBAT.trc
Sort options: default
:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      199      0.02       0.02          0          0          0           0
Execute    833      0.10       0.11          0        273        631         117
Fetch      670      1.22       1.23          0       4419          0         638
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1702      1.35       1.37          0       4692        631         755
:

増分統計の方が query, current, rows と云った負荷が
少ない事が解りますやね!彡(^)(^)

9. マニュアル

マニュアルも読んでおくんやで彡(゚)(゚)

13.2.8.3.2 SET_TABLE_PREFSを使用した増分統計の有効化
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-75AD8460-A5F6-43B1-ADE0-4AD77AF72274
変更されたパーティションのみをスキャンしてグローバル統計を増分的に更新するデータベースでは、次の条件を満たす必要があります。
・パーティション表のPUBLISH値がtrueです。
・パーティション表のINCREMENTAL値がtrueです。
・統計収集プロシージャでは、ESTIMATE_PERCENTにAUTO_SAMPLE_SIZE、GRANULARITYにAUTOを指定する必要があります。

Oracle Database PL/SQL Packages and Types Reference 18c
162 DBMS_STATS 
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_STATS.html#GUID-01FAB8ED-E4A3-4C3E-8FE2-88717DCDDA06

10. 追記:シノプシス(synopsis)を集計してるっぽい内部SQL

select /*+ parallel(1) OPT_PARAM('_parallel_syspls_obey_force' 'false') */  
       b.intcol#,
       b.nnv,
       b.nmin,
       b.nmax,
       b.minval,
       b.maxval,
       b.acl,
       nvl(n.ndv, 0) sndv
  from ( -- all basic stats except ndv
         select /*+ no_merge */ 
                intcol#, 
                greatest(0, :total_rows - sum(h.null_cnt))  nnv, 
                sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) 
                acl,                                          -- if total_rows is 0, avgcln is 0
                min(nvl2(h.lowval, h.minimum, null))  nmin,   -- normalized min
                max(nvl2(h.hival, h.maximum, null))   nmax,   -- normalized max
                min(h.lowval)   minval,                       -- raw value
                max(h.hival)    maxval 
           from sys.tabpart$ t, 
                sys."_HIST_HEAD_DEC" h
          where t.bo# = :tab_num and
                t.obj# = h.obj#
                group by h.intcol#
          union all
         select intcol#, 
                greatest(0, :total_rows - sum(h.null_cnt)) nnv,
                sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) avgcln,
                min(nvl2(h.lowval, h.minimum, null))  nmin, -- normalized min
                max(nvl2(h.hival, h.maximum, null))   nmax, -- normalized max
                min(h.lowval)   minval,                     -- raw value 
                max(h.hival)    maxval 
          from sys.tabcompart$ t, 
               sys."_HIST_HEAD_DEC" h
         where t.bo# = :tab_num and
               t.obj# = h.obj#
         group by h.intcol#
       ) b,  
       (
         select intcol#, 
                to_approx_count_distinct(approx_count_distinct_agg(spare2)) ndv
           from wri$_optstat_synopsis_head$ 
          where bo# = :tab_num 
          group by intcol#
       ) n
 where b.intcol# = n.intcol#(+)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2