Can Table Stats be Stale even after locking its statistics?

Few day back i was asked by one of my friends, does the stats gets stale even after its locked? To put it in another words, after locking the table stats using LOCK_TABLE_STATS, does oracle mark the stats as STALE after heavy DML is done the particular table?

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table stats_test as select * from dba_objects;

Table created.

SQL> select count(1) from stats_test;

  COUNT(1)
----------
     72773

SQL> exec dbms_stats.gather_table_stats('ANAND','STATS_TEST',estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='DD-MM-YY HH24:MI:SS';

Session altered.

SQL> create index obj_id_indx on stats_test(object_id);

Index created.

SQL> select owner,index_name,last_analyzed,num_rows from dba_indexes where table_name='STATS_TEST';

OWNER                          INDEX_NAME                     LAST_ANALYZED       NUM_ROWS
------------------------------ ------------------------------ ----------------- ----------
ANAND                          OBJ_ID_INDX                    06-05-11 07:02:13      72773

Lets see the explain plan of a simple sql query

SQL> select /*+gather_plan_statistics */ owner,object_name,object_type,status from stats_test where object_id in (100,200,300);

OWNER                          OBJECT_NAME                                                                                                              OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
SYS                            ORA$BASE                                                                                                                 EDITION             VALID
SYS                            I_SQL$TEXT_PKEY                                                                                                          INDEX               VALID
SYS                            RESOURCE_CAPABILITY$                                                                                                     TABLE               VALID

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  7c6qx5t98qyv8, child number 0
-------------------------------------
select /*+gather_plan_statistics */
owner,object_name,object_type,status from stats_test where
object_id in (100,200,300)

Plan hash value: 3422349770
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |      3 |00:00:00.01 |       9 |      1 |
|   1 |  INLIST ITERATOR             |             |      1 |        |      3 |00:00:00.01 |       9 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| STATS_TEST  |      3 |      3 |      3 |00:00:00.01 |       9 |      1 |
|*  3 |    INDEX RANGE SCAN          | OBJ_ID_INDX |      3 |      3 |      3 |00:00:00.01 |       6 |      1 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OBJECT_ID"=100 OR "OBJECT_ID"=200 OR "OBJECT_ID"=300))

The above explain plan shows that Oracle estimated correct number of rows (E-rows), simliar to Actual rows (A-rows), value =3, as the table is having the latest stats gathered.

SQL> select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                          72773 06-05-11 07:02:04       NO

SQL> select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_ind_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                                 NUM_ROWS  LAST_ANALYZED     STATT  STA
------------------------------ ------------------------------ ----------   -----------------    -----   ---
ANAND                          STATS_TEST                                        72773      06-05-11 07:02:13          NO

SQL>

The STATTYPE_LOCKED value is null and STALE_STATS is “NO”. Lets perform some dmls and check back –

SQL> insert into stats_test select * from stats_test;

72773 rows created.

SQL> commit;

Commit complete.
SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                          72773 06-05-11 07:02:04       NO

Still Oracle says, Stats are not stale.Really aren’t stats stale yet ?

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='STATS_TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
STATS_TEST                          72773          0          0

SQL>
SQL>
SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                          72773 06-05-11 07:02:04       YES

SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_ind_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                          72773 06-05-11 07:02:13       YES

Now Oracle says stats are stale.The procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary. The GATHER_*_STATS procedures internally flush monitoring information, so it is not necessary to run this procedure before gathering the statistics.

Gather the table stats and check the plan back, does E-Rows = A-Rows

SQL> exec dbms_stats.gather_table_stats('ANAND','STATS_TEST',estimate_percent => 100,cascade => true);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL> select /*+gather_plan_statistics */ owner,object_name,object_type,status from stats_test where object_id in (100,200,300);

OWNER                          OBJECT_NAME                                                                                                              OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
SYS                            ORA$BASE                                                                                                                 EDITION             VALID
SYS                            ORA$BASE                                                                                                                 EDITION             VALID
SYS                            I_SQL$TEXT_PKEY                                                                                                          INDEX               VALID
SYS                            I_SQL$TEXT_PKEY                                                                                                          INDEX               VALID
SYS                            RESOURCE_CAPABILITY$                                                                                                     TABLE               VALID
SYS                            RESOURCE_CAPABILITY$                                                                                                     TABLE               VALID

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2xr6zx4dg9bkj, child number 0
-------------------------------------
select /*+gather_plan_statistics */
owner,object_name,object_type,status from stats_test where object_id in
(100,200,300)

Plan hash value: 3422349770

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |      6 |00:00:00.01 |      12 |
|   1 |  INLIST ITERATOR             |             |      1 |        |      6 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS BY INDEX ROWID| STATS_TEST  |      3 |      6 |      6 |00:00:00.01 |      12 |
|*  3 |    INDEX RANGE SCAN          | OBJ_ID_INDX |      3 |      6 |      6 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OBJECT_ID"=100 OR "OBJECT_ID"=200 OR "OBJECT_ID"=300))

Yes, with the latest stats gathered on the table the explain plan shows E-Rows = A-Rows =6.

Now, lets lock the stats of the table

SQL> exec dbms_stats.lock_table_stats('ANAND','STATS_TEST');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   NO

SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_ind_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   NO

SQL>
SQL> insert into stats_test select * from stats_test;

145546 rows created.

SQL> commit;

Commit complete.

SQL> insert into stats_test select * from stats_test;

291092 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select count(1) from stats_test where object_id=300;

  COUNT(1)
----------
         8

SQL>
SQL>
SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   NO

SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_ind_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   NO

The STALE_STATS column still says “NO”

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='STATS_TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
STATS_TEST                         436638          0          0

SQL>
SQL>
SQL>
SQL>  select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   YES

SQL> select owner,table_name,num_rows,last_analyzed,stattype_locked,stale_stats from dba_ind_statistics where table_name='STATS_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED     STATT STA
------------------------------ ------------------------------ ---------- ----------------- ----- ---
ANAND                          STATS_TEST                         145546 06-05-11 07:05:11 ALL   YES

SQL>

Try to gather the stats

SQL> exec dbms_stats.gather_table_stats('ANAND','STATS_TEST',estimate_percent => 100,cascade => true);
BEGIN dbms_stats.gather_table_stats('ANAND','STATS_TEST',estimate_percent => 100,cascade => true); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

What about the explain plan

SQL> select /*+gather_plan_statistics */ owner,object_name,object_type,status from stats_test where object_id in (100,200,300);

OWNER                          OBJECT_NAME                                                                                                              OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
SYS                            ORA$BASE                                                                                                                 EDITION             VALID
.............................
SYS                            RESOURCE_CAPABILITY$                                                                                                     TABLE               VALID

24 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2urxzdkw5mfcn, child number 0
-------------------------------------
select /*+gather_plan_statistics */
owner,object_name,object_type,status from stats_test where object_id in
(100,200,300)

Plan hash value: 3422349770

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |     24 |00:00:00.01 |      44 |      7 |
|   1 |  INLIST ITERATOR             |             |      1 |        |     24 |00:00:00.01 |      44 |      7 |
|   2 |   TABLE ACCESS BY INDEX ROWID| STATS_TEST  |      3 |      6 |     24 |00:00:00.01 |      44 |      7 |
|*  3 |    INDEX RANGE SCAN          | OBJ_ID_INDX |      3 |      6 |     24 |00:00:00.01 |      11 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OBJECT_ID"=100 OR "OBJECT_ID"=200 OR "OBJECT_ID"=300))

The E-rows is still 6 , though the A-rows =24.So the stats are not updated.Locking stats of table prevents its statistics
collection causing the CBO to work with the old table statistics.Even though the table stats are locked , the STALE_STATS column can be “YES”.

Lets unlock the table stats, collect statistics and check the plan

SQL> exec dbms_stats.unlock_table_stats('ANAND','STATS_TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ANAND','STATS_TEST',estimate_percent => 100,cascade => true);

PL/SQL procedure successfully completed.

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2urxzdkw5mfcn, child number 0
-------------------------------------
select /*+gather_plan_statistics */
owner,object_name,object_type,status from stats_test where object_id in
(100,200,300)

Plan hash value: 3422349770

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |     24 |00:00:00.01 |      35 |
|   1 |  INLIST ITERATOR             |             |      1 |        |     24 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| STATS_TEST  |      3 |     24 |     24 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN          | OBJ_ID_INDX |      3 |     24 |     24 |00:00:00.01 |      11 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OBJECT_ID"=100 OR "OBJECT_ID"=200 OR "OBJECT_ID"=300))


22 rows selected.

E-Rows = A-Rows = 24

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s