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;
Smart way of Technology 