Shulou(Shulou.com)06/01 Report--

13Automatic Optimizer Statistics Collection

Open:

BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE (client_name = > 'auto optimizer stats collection', operation = > NULL, window_name = > NULL); END;/

Close:

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE (client_name = > 'auto optimizer stats collection', operation = > NULL, window_name = > NULL); END;/

The database monitors the change information for each table and automatically collects statistics when the change exceeds 10%:

(is the query statistics out of date? DBA_TAB_STATISTICS)

Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated since the last time statistics were gathered. You can access information about changes of tables in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle Database propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

The GATHER_DATABASE_STATS orGATHER _ SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE orGATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

When to Use Manual Statistics?

Volatile tables that are deleted or truncated and rebuilt during the course of the day.

Objects that are the target of large bulk loads which add 10% or more to the object's total size.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.