表題の通り、増分統計(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#(+)