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