10g also introduced the option to lock table statistics.
Now if you lock statistics in 10g in later using DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command. Unfortunately there is no corresponding "FORCE" option in CREATE INDEX available to overwrite that behaviour that I'm aware of so it looks like you're only left with two choices:
1. Use a separate DBMS_STATS.GATHER_INDEX_STATS call with the FORCE=>true option to override the lock on the statistics
2. Temporarily unlock the table statistics before creating the index
The first option can be costly if the index is large, the second option requires additional steps to be taken, and it obviously needs to be ensured that the table statistics are not modified while they are unlocked (e.g. by the default statistics job in 10g and later).
A small testcase run on 10.2.0.4 Win32 follows to demonstrate the issue. I got the same result on 11.1.0.7 Win32.
SQL>
SQL> drop table lock_test purge;
Table dropped.
SQL>
SQL> create table lock_test
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;
Table created.
SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')
PL/SQL procedure successfully completed.
SQL>
SQL> create index lock_test_idx on lock_test (object_name) compute statistics;
create index lock_test_idx on lock_test (object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL>
SQL> create index lock_test_idx on lock_test (object_name);
Index created.
SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';
NUM_ROWS LAST_ANA
---------- --------
SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1
SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';
NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09
SQL>
SQL> drop index lock_test_idx;
Index dropped.
SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')
PL/SQL procedure successfully completed.
SQL>
SQL> create index lock_test_idx on lock_test (object_name);
Index created.
SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';
NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09
SQL>
SQL> delete from lock_test where rownum <= 500;
500 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter index lock_test_idx rebuild compute statistics;
alter index lock_test_idx rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL>
SQL> alter index lock_test_idx rebuild;
Index altered.
SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';
NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09
SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')
PL/SQL procedure successfully completed.
SQL>
SQL> alter index lock_test_idx rebuild;
Index altered.
SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')
PL/SQL procedure successfully completed.
SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';
NUM_ROWS LAST_ANA
---------- --------
500 21.06.09
SQL>
SQL> analyze index lock_test_idx compute statistics;
analyze index lock_test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL>
SQL> spool off
The same applies to index rebuilds obviously.
Consequently the hopefully no longer used ANALYZE INDEX ESTIMATE/COMPUTE STATISTICS command can also not be used on locked tables and its indexes.
Thank you Randolf, this was very useful information, as I just recently made use of locked stats in an ongoing project. This might just save my day in the near future. ;-)
ReplyDeleteRegards,
Uwe
If you decided to unlock the table stats during the index create and are concerned that the table stats may get changed (either because it takes so long to create the index or you might get distracted and forget to re-lock the table stats) you can save the table stats out to a stats table first.
ReplyDeleteAlso, you can check if the automated stats job is likely to regenerate stats on the table by looking at sys.dba_tab_modifications. If the number of changes (total of inserts, updates and deletes) is more than 10% the number of rows in the table, the automated stats package will re-analyze that table on the next run.
On 10.2 (and 11 I think) you will have to use dbms_stats.flush_database_monitoring_info to push any changes that day into the sys.dba_tab_modifications table.
If you want to know more about dba_tab_modifications I blogged about it a few weeks ago:-
http://mwidlake.wordpress.com/2009/05/26/counting-the-cost-4-the-speedy-way/
Martin,
ReplyDeletethanks for your comment and the link.
Fortunately from 10g on even if you don't have saved the statistics explicitly via a stats table you can restore the statistics via the DBMS_STATS.RESTORE_*_STATS procedures. You can use e.g. the DBA/ALL/USER_TAB_STATS_HISTORY view to browse the automatically retained history of the table statistics. The retention can be controlled by DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.
See the documentation for more information.
Note that from 11g on the default staleness of 10% can be configured using the DBMS_STATS.SET_GLOBAL/DATABASE/SCHEMA/TABLE_PREFS with the STALE_PERCENT option.
Regards,
Randolf
Hi Randolf,
ReplyDeleteYes, if the stats have been gathered by the automated job it very nicely keeps the previous set (for 31 days by default, I think). It is a feature most people are unaware of and can be very useful if a query goes rogue on you over night.
Some sites though have replaced the automated job (usually for something put in place under V9), so may not save their old stats by default.
Martin,
ReplyDeletefortunately this is a gross underestimate of the feature.
Actually it is supposed to reflect EVERY change to statistics that is done via DBMS_STATS, no matter if performed automatically or manually. Even manual manipulations using SET_*_STATS will be reflected.
However, modifying statistics using the (deprecated for ages now ) ANALYZE command doesn't get picked up.
It's only tracked on table level but applies also to changes to partition level statistics.
A simple testcase to demonstrate:
drop table test_hist_stats purge;
create table test_hist_stats
as
select
*
from
all_objects
where
rownum <= 1000;
exec dbms_stats.gather_table_stats(null, 'test_hist_stats')
-- the baseline
select
table_name
, stats_update_time
from
user_tab_stats_history
where
table_name = 'TEST_HIST_STATS';
select
num_rows
from
user_tab_statistics
where
table_name = 'TEST_HIST_STATS';
variable v_date varchar2(30)
exec :v_date := to_char(systimestamp, 'DD.MM.YYYY HH24:MI:SS.FF')
print v_date
exec dbms_lock.sleep(5)
exec dbms_stats.set_table_stats(null, 'test_hist_stats', numrows=>500)
-- modified manually via set_*_stats
select
table_name
, stats_update_time
from
user_tab_stats_history
where
table_name = 'TEST_HIST_STATS';
select
num_rows
from
user_tab_statistics
where
table_name = 'TEST_HIST_STATS';
exec dbms_stats.restore_table_stats(null, 'test_hist_stats', to_timestamp(:v_date, 'DD.MM.YYYY HH24:MI:SS.FF'))
select
num_rows
from
user_tab_statistics
where
table_name = 'TEST_HIST_STATS';
-- restored to baseline
select
table_name
, stats_update_time
from
user_tab_stats_history
where
table_name = 'TEST_HIST_STATS';
delete from
test_hist_stats
where
rownum <= 500;
commit;
analyze table test_hist_stats compute statistics;
select
num_rows
from
user_tab_statistics
where
table_name = 'TEST_HIST_STATS';
-- statistics change not reflected in history
select
table_name
, stats_update_time
from
user_tab_stats_history
where
table_name = 'TEST_HIST_STATS';
Regards,
Randolf