リアルタイム統計は、一部の DML (INSERT, UPDATE, MERGE) 実行時にオプティマイザ統計を収集する機能です。頻繁に変更される表においては、DBMS_STATSジョブ間で統計が実態と乖離する可能性がありますが、リアルタイム統計はオプティマイザがより最適な実行計画を生成するのに役立ちます。
リアルタイム統計は OracleDB 19c からの新機能ですが、Exadata のみで動きます。
Autonomous AI Database の基盤は Exadata を使用しているので、ADB-S で挙動を確認してみます。
津島博士のパフォーマンス講座 第76回 オプティマイザ統計の運用について（３）
SQL Tuning Guide | Real-Time Statistics
リアルタイム統計はデフォルト無効となっているため、optimizer_real_time_statistics初期化パラメータを変更して有効にします。
SQL> sho parameter real_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_real_time_statistics boolean FALSE
SQL> alter system set optimizer_real_time_statistics=true;
System altered.
SQL>
SQL> sho parameter real_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_real_time_statistics boolean TRUE
SQL>
OPTIMIZER_REAL_TIME_STATISTICS
適当な表 (ここでは testtab) を作成し、データを 1件 INSERT します。
SQL> CREATE TABLE testtab (
2 col1 TIMESTAMP,
3 col2 NUMBER,
4 col3 VARCHAR2(10)
5 );
Table created.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS('ADMIN', 'TESTTAB');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> INSERT INTO testtab (col1, col2, col3)
2 VALUES (
3 SYSTIMESTAMP,
4 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
5 DBMS_RANDOM.STRING('A', 10)
6 );
1 row created.
リアルタイム統計が取得されているか確認するには USER_TAB_STATISTICE, USER_TAB_COL_STATISTICS を参照しますが、
反映までに少しタイムラグがあるため、FLUSH_DATABASE_MONITORING_INFOを実行して強制的に反映させてから参照します。
リアルタイム統計が取得されている場合、NOTES列に "STATS_ON_CONVENTIONAL_DML" と記載されますが記載されていません。
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
----------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
last_analyzed,
notes
6 7 8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
リアルタイム統計が使用された場合、実行計画の Note に "dynamic statistics used: statistics for conventional DML" と記載されるので、適当な SQL で確認してみましたが、(案の定) 当該の記載はありません。
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
27 recursive calls
13 db block gets
20 consistent gets
0 physical reads
1092 redo size
366 bytes sent via SQL*Net to client
348 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SET AUTOTRACE OFF;
データを 10件 INSERT してみますが変わりません。
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
);
END LOOP;
8 9 10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
--------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 1 | 33 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
488 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE OFF;
データを 100件 INSERT して初めて、リアルタイム統計が取得されたことを確認できました。
SQL> BEGIN
2 FOR i IN 1..100 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
--------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
TESTTAB 100 29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
FROM user_tab_col_statistics
ORDER BY last_analyzed, column_name;
8 9
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
TESTTAB
COL1
787E011D09270D16340560
787E011D09270D1695F798
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL3
624F6166446273434672
624F6166446273434672
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 5 | 170 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 5 | 170 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 5 | 170 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- dynamic statistics used: statistics for conventional DML ★
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
785 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SET AUTOTRACE OFF;
以降、1,000件 INSERT → 100件 INSERT → 10件 INSERT → 1件 INSERT と変化させながら試したところ、都度リアルタイム統計が取得されていることを確認できました。
件数によって取得される・されないわけではなく、何らかのトリガーがあり、そのトリガーが引かれてからは統計が取得される仕様のようです。
1,000件
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
TESTTAB 1100 29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
TESTTAB
COL1
787E011D09270D16340560
787E011D0927381C09FE68
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL2
C105
C163
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL3
426D7777616A4A6E6373
75575441454B76676E51
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
6 rows selected.
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
130 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 1265 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 55 | 1265 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 55 | 1265 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 55 | 1265 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- dynamic statistics used: statistics for conventional DML ★
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
4782 bytes sent via SQL*Net to client
447 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
130 rows processed
SQL> SET AUTOTRACE OFF;
100件
SQL> BEGIN
2 FOR i IN 1..100 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
--------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
TESTTAB 1200 29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
TESTTAB
COL1
787E011D09270D16340560
787E011D0927381C09FE68
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL2
C105
C163
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL3
426D7777616A4A6E6373
75575441454B76676E51
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
6 rows selected.
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
144 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 1380 | 3 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 60 | 1380 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 60 | 1380 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 60 | 1380 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- dynamic statistics used: statistics for conventional DML ★
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
5248 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
144 rows processed
SQL> SET AUTOTRACE OFF;
10件
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
---------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
TESTTAB 1210 29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
TESTTAB
COL1
787E011D09270D16340560
787E011D0927381C09FE68
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL2
C105
C163
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL3
426D7777616A4A6E6373
75575441454B76676E51
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
6 rows selected.
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
144 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 1403 | 3 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- dynamic statistics used: statistics for conventional DML ★
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
5245 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
144 rows processed
SQL> SET AUTOTRACE OFF;
1件
SQL> INSERT INTO testtab (col1, col2, col3)
2 VALUES (
3 SYSTIMESTAMP,
4 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
5 DBMS_RANDOM.STRING('A', 10)
6 );
1 row created.
SQL> SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
------------- ---------- --------- -------------------------
TESTTAB 0 29-JAN-26
TESTTAB 1211 29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOW_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ANAL NOTES
--------- ---------------------------------------------------------------------------------------------------
TESTTAB
COL1
29-JAN-26 HYPERLOGLOG
TESTTAB
COL2
29-JAN-26 HYPERLOGLOG
TESTTAB
COL3
29-JAN-26 HYPERLOGLOG
TESTTAB
COL1
787E011D09270D16340560
787E011D0927381C09FE68
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL2
C105
C163
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
TESTTAB
COL3
426D7777616A4A6E6373
75575441454B76676E51
29-JAN-26 STATS_ON_CONVENTIONAL_DML ★
6 rows selected.
SQL> SET AUTOTRACE TRACE;
SELECT * FROM testtab WHERE col2 < 10;
SQL>
144 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3666148979
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 1403 | 3 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TESTTAB | 61 | 1403 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL2"<10)
Note
-----
- dynamic statistics used: statistics for conventional DML ★
- automatic DOP: Computed Degree of Parallelism is 4
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
5245 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
144 rows processed
SQL> SET AUTOTRACE OFF;
なお、最初にDBMS_STATS.GATHER_TABLE_STATSで統計を取得していないと、先ほどと同様の流れを実施してもリアルタイム統計は取得されませんでした。
リアルタイム統計を取得する DML は一部 (INSERT, UPDATE, MERGE)、取得される統計は最低限 (件数、最大、最小) のため、あくまで 自動 (手動) 統計取得の補完機能として活用しましょう。
1件
SQL> drop table testtab;
Table dropped.
SQL> CREATE TABLE testtab (
2 col1 TIMESTAMP,
3 col2 NUMBER,
4 col3 VARCHAR2(10)
5 );
Table created.
SQL> INSERT INTO testtab (col1, col2, col3)
2 VALUES (
3 SYSTIMESTAMP,
4 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
5 DBMS_RANDOM.STRING('A', 10)
6 );
1 row created.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
-------------- ---------- --------- -------------------------
TESTTAB
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
no rows selected
10件
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
/
11
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
--------------- ---------- --------- -------------------------
TESTTAB
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
FROM user_tab_col_statistics
ORDER BY last_analyzed, column_name;
8 9
no rows selected
100件
SQL> BEGIN
2 FOR i IN 1..100 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
------------- ---------- --------- -------------------------
TESTTAB
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
no rows selected
1,000件
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO testtab (col1, col2, col3)
4 VALUES (
5 SYSTIMESTAMP,
6 TRUNC(DBMS_RANDOM.VALUE(0, 100)),
7 DBMS_RANDOM.STRING('A', 10)
8 );
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT
2 table_name,
3 num_rows,
4 last_analyzed,
5 notes
6 FROM user_tab_statistics;
TABLE_NAME NUM_ROWS LAST_ANAL NOTES
--------------- ---------- --------- -------------------------
TESTTAB
SQL> SELECT
2 table_name,
3 column_name,
4 low_value,
5 high_value,
6 last_analyzed,
7 notes
8 FROM user_tab_col_statistics
9 ORDER BY last_analyzed, column_name;
no rows selected