Oracle will automatically collect statistics for all database objects, which are missing statistics of have stale statistics by running an Oracle AutoTask during a predefined maintenance window (10pm to 2am weekdays and 6am to 2am at the weekends). Statistics on a table are considered stale when more than STALE_PERCENT (default 10% total of inserts,updates, deletes rows) in a table. Oracle monitors DML activities for all objects and records the changes in the SGA. The monitoring information is periodically flushed to disk and is stored in the *_tab_modifications view.
It’s also possible to manually flush this data by calling the procedure DBMS_STATS.FLUSH_MONITORING_INFO.
SQL> SELECT client_name, status
2 FROM DBA_AUTOTASK_TASK
3 WHERE client_name like 'auto optimizer %';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
select
num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name,
inserts, updates, deletes, timestamp, truncated
, to_char(perc_updates, 'FM999,999,999,990.00') perc_updates
from (
select a.*
, nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates
from (
select
(select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows
, (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed
, (inserts + updates + deletes) tot_updates
, DBA_TAB_MODIFICATIONS.*
from sys.DBA_TAB_MODIFICATIONS
) a
) b
where perc_updates > 5 and
table_owner = 'TRAX_OWNER'
/
If you like to change the ‘STALE_PERCENT’ you can use
SQL> BEGIN
2 DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
3 END;
4 /
PL/SQL procedure successfully completed.
To disable automatic statistics job gathering altogether:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
No comments:
Post a Comment