Oracle 統計情報はロックされていても失効する
Oracleが保持する統計情報は、デフォルトでテーブルの10%以上のデータが変更されると失効されます。失効された統計情報は利用されないので再収集対象となります。
運用上、統計情報をロックする場合が（稀に）ありますが、この場合も統計情報が失効します。
※ちなみに最初はロックしているから失効しないと思いながら確認していました。
失効するといっても、失効した統計情報が使用されないわけではないのでほぼ問題ない（はず)です。
今回は統計情報が失効する様子を確認してみます。
また、統計情報をロックした状態で失効した場合に問題となるケースも確認してみます。
当たり前のことかもしれませんが、探しても情報が見つからなかったので一応メモとして残しておきます。
なお、環境はOracle19cを使用します。
テーブルをロックせず、統計情報が失効することを確認する
まずは、テーブルをロックせず、統計情報が失効されることを確認してみます。
準備として、test01テーブルを作成し、1万レコードを挿入しています。
create table test01 (
id number,
status char(1),
val varchar2(100),
constraint pk_test01 primary key(id)
);
insert into test01 select rownum, to_char(trunc(dbms_random.value(1,10))), 'TEST DATA' || rownum from dual connect by level <=10000;
commit;
対象スキーマの統計情報を取得します。
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');
PL/SQL procedure successfully completed.
次にtest01テーブルの統計情報を確認してみます。
stale_statsが"YES"だと統計情報が失効したことを示します。今回は統計を取得した直後のため、統計は有効（"NO"）になっています。
SQL> column table_name format a20
SQL> column index_name format a20
SQL> select table_name, num_rows, blocks, avg_row_len, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked, stale_stats from user_tab_statistics where table_name = 'TEST01';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ STATT STALE_S
-------------------- ---------- ---------- ----------- ------------------- ----- -------
TEST01 10000 35 20 2021/01/09 01:06:50 NO
SQL> select table_name, index_name, num_rows, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked, stale_stats from user_ind_statistics where table_name = 'TEST01';
TABLE_NAME INDEX_NAME NUM_ROWS TO_CHAR(LAST_ANALYZ STATT STA
-------------------- -------------------- ---------- ------------------- ----- ---
TEST01 PK_TEST01 10000 2021/01/09 01:06:50 NO
デフォルトでは10%以上のレコードが変更されると統計が失効されるので、1001レコードを挿入します。（1000レコードでは失効しなかった）
insert into test01 select 10000 + rownum, to_char(trunc(dbms_random.value(1,10))), 'TEST DATA' || rownum from dual connect by level <=1001;
commit;
注意点としては、変更された情報がすぐに反映されるわけではないため、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOプロシージャで強制的に反映させます。
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
再度、統計情報を確認してみます。
stale_statsが"YES"となっており、統計情報が失効していることが確認できます。
SQL> select table_name, num_rows, blocks, avg_row_len, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), statty
pe_locked, stale_stats from user_tab_statistics where table_name = 'TEST01';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ STATT STALE_S
-------------------- ---------- ---------- ----------- ------------------- ----- -------
TEST01 10000 35 20 2021/01/09 01:06:50 YES
SQL> select table_name, index_name, num_rows, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked
, stale_stats from user_ind_statistics where table_name = 'TEST01';
TABLE_NAME INDEX_NAME NUM_ROWS TO_CHAR(LAST_ANALYZ STATT STA
-------------------- -------------------- ---------- ------------------- ----- ---
TEST01 PK_TEST01 10000 2021/01/09 01:06:50 YES
テーブルがロックされていても統計情報が失効されることを確認する
次に、前述の操作をテーブルをロックした上で実行し、同様に統計情報が失効されることを確認します。
準備として、test02テーブルを作成し、1万レコード挿入しています。
create table test02 (
id number,
status char(1),
val varchar2(100),
constraint pk_test02 primary key(id)
);
insert into test02 select rownum, to_char(trunc(dbms_random.value(1,10))), 'TEST DATA' || rownum from dual connect by level <=10000;
commit;
統計情報を取得し、その後にtest02テーブルの統計をロックします。
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.LOCK_TABLE_STATS(ownname => 'HR',tabname => 'TEST02');
PL/SQL procedure successfully completed.
次にtest02テーブルの統計情報を確認してみます。
stattype_lockedが"ALL"になっており、統計情報がロックされていることが確認できます。
SQL> column table_name format a20
SQL> column index_name format a20
SQL> select table_name, num_rows, blocks, avg_row_len, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked, stale_stats from user_tab_statistics where table_name = 'TEST02';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ STATT STALE_S
-------------------- ---------- ---------- ----------- ------------------- ----- -------
TEST02 10000 35 20 2021/01/09 01:11:45 ALL NO
SQL> select table_name, index_name, num_rows, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked, stale_stats from user_ind_statistics where table_name = 'TEST02';
TABLE_NAME INDEX_NAME NUM_ROWS TO_CHAR(LAST_ANALYZ STATT STA
-------------------- -------------------- ---------- ------------------- ----- ---
TEST02 PK_TEST02 10000 2021/01/09 01:11:45 ALL NO
先ほどと同様に統計が失効させるため、1001レコードを挿入します。
insert into test02 select 10000 + rownum, to_char(trunc(dbms_random.value(1,10))), 'TEST DATA' || rownum from dual connect by level <=1001;
commit;
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
再度、統計情報を確認してみます。
stale_statsが"YES"となっており、統計情報が失効していることが確認できます。
SQL> select table_name, num_rows, blocks, avg_row_len, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked, stale_stats from user_tab_statistics where table_name = 'TEST02';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ STATT STALE_S
-------------------- ---------- ---------- ----------- ------------------- ----- -------
TEST02 10000 35 20 2021/01/09 01:11:45 ALL YES
SQL> select table_name, index_name, num_rows, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS'), stattype_locked
, stale_stats from user_ind_statistics where table_name = 'TEST02';
TABLE_NAME INDEX_NAME NUM_ROWS TO_CHAR(LAST_ANALYZ STATT STA
-------------------- -------------------- ---------- ------------------- ----- ---
TEST02 PK_TEST02 10000 2021/01/09 01:11:45 ALL YES
この状態でSQLを実行し、実行計画を確認すると以下のようになります。
統計情報がない場合は動的統計（動的サンプリング）機能で、動的にサンプリングされて統計として利用されるのですが、特にそのような情報がないので既存の統計が使用されていると思われます。
つまり、統計が失効されても、ロックされていれば基本的には実行計画が変わらないと言えます。（厳密には色々なオプティマイザの機能を無効化しないと実行計画が変わる恐れはあります）
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));
SQL_ID g53k920ygm8f5, child number 0
-------------------------------------
select * from test02 where id < 10000
Plan hash value: 3294238222
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | TABLE ACCESS FULL| TEST02 | 9999 | 195K| 11 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST02@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<10000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TEST02"."STATUS"[CHARACTER,1],
"TEST02"."VAL"[VARCHAR2,100]
29 rows selected.
統計情報をロックした状態で失効した場合に発生する問題
動的サンプリング（動的統計）機能を制御する初期化パラメータであるOPTIMIZER_DYNAMIC_SAMPLINGを"11"を設定した場合、ロック＆失効された統計は使用されないようです。そのため、せっかく統計をロックしたのに実行計画が変わることがありえます。
動的サンプリング（動的統計）は統計情報が存在しない、失効している場合などに、動的にサンプリングして統計情報として利用する機能です。
OPTIMIZER_DYNAMIC_SAMPLINGを"11"を設定した場合は、統計が失効している場合にも動的にサンプリングして失効した統計情報を使用しないようです。
それを確認してみます。
まず、現在のセッションでOPTIMIZER_DYNAMIC_SAMPLINGを"11"に設定します。
SQL> alter session set optimizer_dynamic_sampling=11;
先ほどと同じSQLの実行計画を確認すると以下のようになります。
最後の「Note」部に以下の文章が出力されていることが確認できます。
これは動的サンプリング（動的統計）が使用されたということなので、失効された統計情報は使用されなかったということになります。
- dynamic statistics used: dynamic sampling (level=AUTO)」
SQL> select * from test02 where id < 10000;
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));
SQL_ID g53k920ygm8f5, child number 1
-------------------------------------
select * from test02 where id < 10000
Plan hash value: 3294238222
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | TABLE ACCESS FULL| TEST02 | 9999 | 195K| 11 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST02@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<10000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TEST02"."STATUS"[CHARACTER,1],
"TEST02"."VAL"[VARCHAR2,100]
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)★ここに注目
33 rows selected.