Enable and Disable Real time Statistics in Oracle

Enable and Disable Real time Statistics in Oracle

Oracle Database 19c has new feature real-time statistics which extend online support to conventional DML statements.
It automatically gather real-time statistics during conventional DML operations.
Conventional DML operation like CTAS , insert into table_name as select * from table.

Example of showing optimizer statistics gather during load table conventinal in second step.

---------------------------------------------------------------------------------------
|Id| Operation                        | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop|
---------------------------------------------------------------------------------------
| 0| INSERT STATEMENT                 |     |    |     |1110 (100)|        |     |     |
| 1|  LOAD TABLE CONVENTIONAL         |TRAN |    |     |          |        |     |     |
| 2|   OPTIMIZER STATISTICS GATHERING |     |718K|  35M|1110   (2)|00:00:01|     |     |
| 3|    PARTITION RANGE ALL           |     |718K|  35M|1110   (2)|00:00:01|   1 |  48 |
| 4|     TABLE ACCESS FULL            |TRAN |718K|  35M|1110   (2)|00:00:01|   1 |  48 |
---------------------------------------------------------------------------------------

Enable the Real time Statistics

--- optimizer online stats gathering for conventional DML
ALTER SYSTEM SET "_optimizer_gather_stats_on_conventional_dml"= TRUE;
--- use optimizer statistics gathered for conventional DML
ALTER SYSTEM SET "_optimizer_use_stats_on_conventional_dml"= TRUE;

Disable the Real time Statistics

--- optimizer online stats gathering for conventional DML
ALTER SYSTEM SET "_optimizer_gather_stats_on_conventional_dml"= FALSE;
--- use optimizer statistics gathered for conventional DML
ALTER SYSTEM SET "_optimizer_use_stats_on_conventional_dml"= FALSE;

Use of Hint with Real time Statistics
NO_GATHER_OPTIMIZER_STATISTICS: hint prevents the collection of real-time statistics.
GATHER_OPTIMIZER_STATISTICS: hint may be cause collection of real time statistics.

insert /*+NO_GATHER_OPTIMIZER_STATISTICS*/ into t
as select * from table_name;

Check the real time statistics is gathered
Note: The view DBA_TAB_COL_STATISTICS and DBA_TAB_STATISTICS will contain STATS_ON_CONVENTIONAL_DML in the NOTES column and SHARED in the SCOPE column.

SET PAGESIZE 5000
SET LINESIZE 200
COL COLUMN_NAME FORMAT a13
COL LOW_VALUE FORMAT a10
COL HIGH_VALUE FORMAT a10
COL NOTES FORMAT a25
COL PARTITION_NAME FORMAT a13

SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES
FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'TRAN';

COLUMN_NAME   LOW_VALUE  HIGH_VALUE SAMPLE_SIZE NOTES
------------- ---------- ---------- ----------- -------------------------
ITEMS_QANTITY 10111      224422     7854        STATS_ON_CONVENTIONAL_DML
ITEMS_QANTITY 10111      2125349    4568

SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES
FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'TRAN';

PARTITION_NAM   NUM_ROWS     BLOCKS NOTES
------------- ---------- ---------- -------------------------
GLOBAL           2331253       4500 STATS_ON_CONVENTIONAL_DML
GLOBAL            415456       4500

Note:
Force the Oracle database to write optimizer statistics to the data dictionary with following command:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.