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