Gather empty schema statistics might cause index statistics to be marked as stale.<\/p>\n
Firstly, we will create a table with an index:<\/p>\n
drop user u cascade ;\ncreate user u identified by Temp_1234 ;\ncreate table u.test ( a varchar2(100) ) ;\nCREATE INDEX u.ind_test_a ON U.TEST (a);<\/code><\/pre>\nAs expected, there are no table statistics after table creation:<\/p>\n
select stale_stats,last_analyzed \n from dba_tab_statistics \n where owner='U' and table_name='TEST' \n;\nSTAL LAST_ANALY\n---- ----------\nNULL NULL<\/code><\/pre>\nAlthough the statistics for the index were gathered during the index creation, the stale_stats is NULL:<\/p>\n
select stale_stats<\/span>,last_analyzed \n from dba_ind_statistics<\/span> \n where table_owner='U' and table_name='TEST' \n;\nSTAL LAST_ANALYZED\n---- -------------------\nNULL<\/span> 25.10.2015 21:09:30<\/span><\/code><\/pre>\nLet’s gather empty schema statistics:<\/p>\n
begin\n SYS.DBMS_STATS.GATHER_schema_STATS (\n OwnName => 'U' ,\n Options => 'GATHER EMPTY' ,\n Cascade => TRUE\n );\nend ;\n\/<\/code><\/pre>\nThe table statistics are updated:<\/p>\n
select stale_stats,last_analyzed \n from dba_tab_statistics \n where owner='U' and table_name='TEST' \n;\nSTAL LAST_ANALYZED\n---- -------------------\nNO 25.10.2015 21:09:31<\/code><\/pre>\nHowever, according to LAST_ANALYZED the index statistics were not updated, but are marked as stale now:<\/p>\n
select stale_stats<\/span>,last_analyzed \n from dba_ind_statistics \n where table_owner='U' and table_name='TEST' \n;\nSTAL LAST_ANALYZED\n---- -------------------\nYES 25.10.2015 21:09:30<\/span><\/code><\/pre>\nPartitioned index<\/h1>\n
I discovered another variant of this problem when a local index is involved.<\/p>\n
Creating the model:<\/p>\n
drop table t ;\n\ncreate table t ( n1 integer, n2 integer ) partition by range (n1)\n(\n partition t_1 values less than (1) \n);\n\ninsert into t values (0,0) ; \n\ncommit ;\n\ncreate index ix_t_1 on t (n2) local ;<\/code><\/pre>\nAdjusting column formatting:<\/p>\n
column partition_name format a4\ncolumn stale_stats format a4\ncolumn index_name format a6<\/code><\/pre>\nGathering statistics:<\/p>\n
exec dbms_stats.gather_table_stats( null, 'T' ) ;\nselect index_name, partition_name, stale_stats, last_analyzed \n from user_ind_statistics where table_name = 'T' ;\n\nINDEX_ PART STAL LAST_ANALYZED\n------ ---- ---- -------------------\nIX_T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_1 NO 2020-12-04 18:23:05<\/code><\/pre>\nCreating a new partition:<\/p>\n
alter table t add partition t_2 values less than (2) ;\n\nINDEX_ PART STAL LAST_ANALYZED\n------ ---- ---- -------------------\nIX_T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_2<\/span><\/code><\/pre>\nIndex partition rebuild gathers statistics, but doesn’t populate the STALE_STATS column.<\/p>\n
alter index ix_t_1 rebuild partition t_2 online ;\n\nINDEX_ PART STAL LAST_ANALYZED\n------ ---- ---- -------------------\nIX_T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_2 2020-12-04 18:24:17<\/span><\/code><\/pre>\nGather empty schema statistics gathers the partition statistics, including the index statistics, but wrongly sets the STALE_STATS column to ‘YES’.<\/p>\n
begin\n SYS.DBMS_STATS.GATHER_schema_STATS (\n null,\n Options => 'GATHER EMPTY' ,\n Cascade => TRUE\n );\nend ;\n\/\n\nINDEX_ PART STAL LAST_ANALYZED\n------ ---- ---- -------------------\nIX_T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_1 NO 2020-12-04 18:23:05\nIX_T_1 T_2 YES 2020-12-04 18:24:17<\/span><\/code><\/pre>\nConclusion<\/h1>\n
In conclusion, implicit index statistics gathering through, for example index creation or rebuild, doesn’t set STALE_STATS. A subsequent gather empty schema statistics then wrongly sets the index STALE_STATS to ‘YES’.<\/p>\n
Updates<\/h1>\n\n- January 14, 2016 – Oracle filed the Bug 22544615 : GATHER EMPTY OPTION OF GATHERING STATS MARKS THE INDEX STALE<\/li>\n
- December 4, 2020 – the local index case and conclusion added<\/li>\n<\/ul>\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
The gathering of empty schema statistics might cause index statistics to be marked as stale. Continue Reading