Thanks for the question, Eric.
Asked: July 07, 2016 - 5:51 pm UTC
Last updated: July 09, 2016 - 2:05 am UTC
Version: 11.2.0.3.0 and 12.1.0.2.0
Viewed 10K+ times! This question is
You Asked
Hi Tom,
So we are looking to be refreshing stats when the stats become stale while a lot of DML is happening on a table, and were testing how to handle this. FLUSH_DATABASE_MONITORING_INFO is the right procedure as it updates the dictionary. We need this as the *_TAB_MODIFICATIONS or *_TAB_STATISTICS tables are not updated in real time. We aren't able to execute this directly though.
When we tested this in 11g *_TAB_STATISTICS for the stale table was updated when we gathered statistics on a dummy table using DBMS_STATS.GATHER_TABLE_STATS (we were under the impression that this procedure updates *_TAB_STATISTICS). When we did the same test in 12c this did not occur. The statistics were still not showing as stale.
Is this the expected behavior in 12C?
Here is our test output. TEST_FLUSH is the stale table, TEST_STATS is the dummy table.
**********11G TEST****************
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
24 35597 NO
1 row selected.
SQL> INSERT INTO TEST_FLUSH SELECT * FROM TEST_FLUSH;
192 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'XXXX', tabname => 'TEST_STATS' , ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => 4);
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
24 35597 YES
1 row selected
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'XXXX', tabname => 'TEST_FLUSH');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
216 35597 NO
**********12C TEST****************
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
24 35597 NO
1 row selected.
SQL> INSERT INTO TEST_FLUSH SELECT * FROM TEST_FLUSH;
192 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'XXXX', tabname => 'TEST_STATS' , ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => 4);
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
24 35597 NO
1 row selected
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'XXXX', tabname => 'TEST_FLUSH');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
NUM_ROWS BLOCKS STA
---------- ---------- ---
216 35597 NO
and Connor said...
I've reproduced what you are seeing on my databases here:
11g
==================================
SQL> create table test_flush as
2 select * from dba_objects
3 where rownum <= 200;
Table created.
SQL> create table ANY_OTHER_TABLE as
2 select * from dba_objects
3 where rownum <= 200;
Table created.
SQL> exec dbms_stats.gather_table_stats('','ANY_OTHER_TABLE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','TEST_FLUSH');
PL/SQL procedure successfully completed.
SQL> select stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
STA
---
NO
SQL> INSERT INTO TEST_FLUSH SELECT * FROM TEST_FLUSH;
200 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => user, tabname => 'ANY_OTHER_TABLE' , ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> select stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
STA
---
YES
12c
=================================
SQL> create table test_flush as
2 select * from dba_objects
3 where rownum <= 200;
Table created.
SQL> create table ANY_OTHER_TABLE as
2 select * from dba_objects
3 where rownum <= 200;
Table created.
--
-- these two redundant for 12c, but included anyway
--
SQL> exec dbms_stats.gather_table_stats('','ANY_OTHER_TABLE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','TEST_FLUSH');
PL/SQL procedure successfully completed.
SQL> select stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
STA
---
NO
SQL> INSERT INTO TEST_FLUSH SELECT * FROM TEST_FLUSH;
200 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => user, tabname => 'ANY_OTHER_TABLE' , ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> select stale_Stats from ALL_TAB_STATISTICS WHERE TABLE_NAME='TEST_FLUSH';
STA
---
NO
According to the docs, you don't need to call FLUSH_DATABASE_MONITORING_INFO. Running the stats procedures does this for you:
"Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics."
Now the question becomes - does this mean that when you gather stats on object X, should it flush the monitoring info for object Y ?
(I'm hypothesizing here) but it may well be the case that we've optimized things in 12c to only flush what's required.
I'm liaising with the Optimizer product manager to get more info.
If the new behaviour causes you problems, as a workaround, you could create a wrapper procedure around dbms_stats.flush.... and grant access on that to the programs/people that need it.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment