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

表題の通り、増分統計(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. オブジェクト作成と増分統計の有効化
まずはオブジェクトの作成と増分統計の有効化を実行彡(ﾟ)(ﾟ)

```sql
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. テストデータの作成
次にテストデータを投入しますやで彡(ﾟ)(ﾟ)

```sql
-- テストデータ作成
 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
-- 統計採取および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してますね！

```shell
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で
失効状態をフラッシュした後に、統計が失効したことを確認彡(ﾟ)(ﾟ)

```sql
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
-- 増分統計採取および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トレースは下記の通り。
でもこれだけだと増分統計の効果が判らないので…彡(ﾟ)(ﾟ)

```shell
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トレースと同じ条件です。

```shell
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

```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#(+)
```