Skip to Main Content
  • Questions
  • GATHER_TABLE_STATS difference between 11g and 12c (DBMS_STATS.flush_database_monitoring_info)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

Connor - Thanks for your prompt response

Krishna, July 08, 2016 - 4:56 pm UTC

Hi Connor,

We have gone through your response

We were under an assumption that, DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO will be invoked whenever GATHER_*_STATS procedure gets called.

So, lets say that I have a table TEST in my database for which there will be heavy DML changes at times. So, before I start querying this table, we want to check if the table has STALE Statistics and if so, we would want to gather stats on the table. If not, we thought of not gathering stats. The reason behind this is we dont want to have additional overhead on the database by gathering stats on a table when it is not required to gather stats So, we thought that if we gather stats on a DUMMY table using GATHER_TABLE_STATS, it should invoke DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO and the monitoring info would be flushed into *_TAB_STATISTICS and also that there wont be much overhead on the database when statistics are gathered on a dummy table. So, we tested the same approach in 11G and 12C both. For our surprise, it worked in 11G but not in 12C.

So, for your question "Now the question becomes - does this mean that when you gather stats on object X, should it flush the monitoring info for object Y ? "
=> Yes - We are seeing that this behaviour is happening in 11G but not in 12C
So, we wanted to know if Oracle has changed the way how it flushes monitoring info in 12C

We tried to find if this behaviour change is documented. But, couldn't find it. Can you please let us know if you find anything from Optimizer product manager

Also, we tried one more test in 12C =>
Instead of gathering stats on a dummy table to make sure that monitoring info of TEST table getting flushed into *_TAB_STATISTICS, we invoked GATHER_SCHEMA_STATS (ownname=>'XXXXXX', options=>'LIST STALE') just before checking for staleness of TEST table. And it looks like it has flushed the info into both *_TAB_STATISTICS and also *_TAB_MODIFICATIONS

So, we are guessing that this is surely a behaviour change in 12C when compared with 11G

Connor McDonald
July 09, 2016 - 2:05 am UTC

I got some feedback from the Optimizer PM.

In 11g, to decide on stale, we only looked at the dictionary tables, so we had to flush out monitoring info *before* deciding if something was stale.

In 12c, thats been improved - we also look at the memory structures that hold the "pending" information (ie, that data that *would* be flushed), so if someone asks us to gather stale, we dont need to flush out the info.

So in your case, you should be able to just do a "gather stale" which will be a no-op if appropriate.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library