Oracle

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

Leave a Reply