Real-time Stats in Action
The last 2 articles I wrote covered the online stats vs real-time stats at a higher level, and then I went into details on online stats. Today is the last part of the series, and we will dive into examples with Real-time Statistics.
Real-time stats were introduced in 19c, as a bonus to the online stats, for conventional DML statements, conventional meaning regular insert, update and delete statements. Why would you need real time stats in the database? Well, the existing stats could become stale between the times you gather statistics. With real time stats this problem is solved. Oracle introduced real time stats to help the optimizer create better plans.
Initially the real-time stats are not available right away in the data dictionary, after they are gathered. You would have to use dbms_stats.flush_database_monitoring_info the get the stats from memory into the data dictionary.
What do you think the purpose of real-time statistics is? You guessed it: the purpose is to improve performance!
Real-time stats, as of this writing, is available for Enterprise Edition on Engineered Systems like Exadata or Exadata Cloud Services .
But… wait, don’t get discouraged. Most of us don’t work with Exadata, so there is a workaround we can use for testing purposes only on our own laptops, to see how this feature works. Promise me, that you never ever ever ever will be doing this in a test/prod system at work, as it could break your license agreement. That being said, I trust you, that you will only do this on your laptop, and at your own risk.
You can set the _exadata_feature_on=true parameter, to mimic the Exadata environment on any database. So here it is, setting up my database on my laptop to test the feature out.
sqlplus / as sysdba
--setup environment to mimic Exadata
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
--create a table and populate it with data:
create table hr.test_stats (
id number(5),
name varchar2(30));
exec dbms_stats.gather_table_stats('HR','TEST_STATS');
insert into hr.test_stats (id, name)
select level, 'Comment for ' || level || ' level'
from dual
connect by level <= 5000;
commit;
There are two main things to notice here. First, for the real-time stats to be gathered, there should be actual statistics gathered already on the table. Notice that right after I created the table, I also gathered stats on it with dbms_stats. If there are no table stats, then there are no real time stats either. During the actual DML, ie. insert statement, real time stats will be gathered, but there will be nothing flushed from memory to the data dictionary. The second important thing to note here is the fact that the table has to be created in a non-sys schema. In my initial testing, out of convenience I created the table under SYS, and I couldn’t get this feature working, no real stats were gathered on the table. As soon as I tested the process in the HR schema, it worked. So when you test it out, make sure you do not create the table under SYS.
Let’s check the execution plan of the insert statement above:
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID d4xp5vmn0dp8v, child number 0
-------------------------------------
insert into hr.test_stats (id, name) select level, 'Comment for ' ||
level || ' level' from dual connect by level <= 5000
Plan hash value: 1236776825
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 2 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | TEST_STATS | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 2 (0)| 00:00:01 |
| 3 | CONNECT BY WITHOUT FILTERING | | | | |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Notice line 2 of the execution plan: OPTIMIZER STATISTICS GATHERING. That line shows us that real-time stats were gathered on the table.
If I check right away the statistics on the table, the real-time stats will not show up yet, and that is because the stats have to be flushed to the data dictionary first. The real-time stats should be visible in the NOTES column of the dba_tab_statistics view.
select table_name, num_rows, notes
from dba_tab_statistics
where table_name='TEST_STATS'
and owner='HR';
TABLE_NAME NUM_ROWS NOTES
-------------------- ---------- -------------------------
TEST_STATS 0
EXEC DBMS_STATS.flush_database_monitoring_info;
select table_name, num_rows, notes
from dba_tab_statistics
where table_name='TEST_STATS'
and owner='HR';
TABLE_NAME NUM_ROWS NOTES
-------------------- ---------- -------------------------
TEST_STATS 0
TEST_STATS 5000 STATS_ON_CONVENTIONAL_DML
Let’s run a SQL statement and see if the real-time stats are used or not:
SQL> select max(id) from hr.test_stats;
MAX(ID)
----------
5000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID b88stjuwvgq78, child number 0
-------------------------------------
select max(id) from hr.test_stats
Plan hash value: 1525674154
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| TEST_STATS | 5000 | 126K| 15 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: statistics for conventional DML
Notice in the notes section: “dynamic statistics used: statistics for conventional DML”
A few other interesting things to note is that NUM_ROWS column is not getting updated by real-time stats gathering. This column is only updated by the dbms_stats package. Also most likely single row inserts will not trigger real-time stats gathering.
This feature can be disabled by setting _optimizer_gather_stats_on_conventional_dml=FALSE, or by using the /*+ NO_GATHER_OPTIMIZER_STATISTICS */ hint during the DML statement.
I have not tested deletes and updates how they work with real-time stats. That might be for another post, or you might want to test it out yourself!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD available until the end of the month!
