Hello,
I'm trying to u
se this new feature and not work as expected, at least in my env.
My soft info is:
Oracle Database 19c 19.0.0.0.0 with this RU applied:
Patch description: "Database Release Update : 19.3.0.0.190416 (29517242)".
On Oracle Linux, non-Exadata, non-Cloud.
To see if this feature work, I've created a copy of a typical EMP with 2,100,014 table , then insert 100.000 rows.
SQL> select INSERTS,UPDATES,DELETES from DBA_TAB_MODIFICATIONS where table_name='EMP';
INSERTS UPDATES DELETES
---------- ---------- ----------
100000 0
However, when I do a simple query, the optimizer dosen't detect stale statistics.
SQL> select count(*) from emp;
COUNT(*)
----------
2200014
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3303 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 2100K| 3303 (1)| 00:00:01 |
-------------------------------------------------------------------
You can see the divergence between the rows expected and the real rows (2 200 014, versus 2 100 000 )
14 rows selected.
Also, dba_tab_statistics info is not update with the stale .
select NOTES,NUM_ROWS,STALE_STATS from dba_tab_statistics where table_name='EMP';
NOTES NUM_ROWS STALE_S
------------------------- ---------- -------
2100014 NO
The MOS note:
19c New Feature:Real-Time Statistics ( Doc ID 2552657.1 )
Don't say nothing special about any requeriment to turn on this feature.
Do any of you have experience with this functionality? Should we activate something in the database?
Many Thanks.
Arturo