Oracle 統計情報はロックされていても失効する、が（ほぼ）問題はない

# Oracle 統計情報はロックされていても失効する

Oracleが保持する統計情報は、デフォルトでテーブルの10%以上のデータが変更されると失効されます。失効された統計情報は利用されないので再収集対象となります。

運用上、統計情報をロックする場合が（稀に）ありますが、この場合も統計情報が失効します。
※ちなみに最初はロックしているから失効しないと思いながら確認していました。
失効するといっても、失効した統計情報が使用されないわけではないのでほぼ問題ない（はず)です。

今回は統計情報が失効する様子を確認してみます。
また、統計情報をロックした状態で失効した場合に問題となるケースも確認してみます。
当たり前のことかもしれませんが、探しても情報が見つからなかったので一応メモとして残しておきます。

なお、環境はOracle19cを使用します。


# テーブルをロックせず、統計情報が失効することを確認する

まずは、テーブルをロックせず、統計情報が失効されることを確認してみます。
準備として、test01テーブルを作成し、1万レコードを挿入しています。

```sql
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
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');

PL/SQL procedure successfully completed.
```

次にtest01テーブルの統計情報を確認してみます。
stale_statsが"YES"だと統計情報が失効したことを示します。今回は統計を取得した直後のため、統計は有効（"NO"）になっています。

```sql
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レコードでは失効しなかった）

```sql
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
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
```

再度、統計情報を確認してみます。
stale_statsが"YES"となっており、統計情報が失効していることが確認できます。

```sql
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万レコード挿入しています。

```sql
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
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
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レコードを挿入します。

```sql
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
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
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
SQL> alter session set optimizer_dynamic_sampling=11;
```

先ほどと同じSQLの実行計画を確認すると以下のようになります。
最後の「Note」部に以下の文章が出力されていることが確認できます。
これは動的サンプリング（動的統計）が使用されたということなので、失効された統計情報は使用されなかったということになります。

- dynamic statistics used: dynamic sampling (level=AUTO)」


```sql
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.
```


# 参考

- [12.3 動的統計のオプションの構成](https://docs.oracle.com/cd/F19136_01/tgsql/options-for-optimizer-statistics-gathering.html#GUID-F1C666DC-5450-4F92-83AA-607327DB5FD8)
- [移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.6](https://www.ashisuto.co.jp/tech-note/article/20140530_oracle.html)
- [optimizer_dynamic_sampling=11 - Trying Database ](http://koutashiobara.blogspot.com/2014/12/optimizerdynamicsampling11.html)