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