Some knowledge of the Oracle 11g system collecting statistical information on its own initiative

Before 11g, when the data volume of the table was changed beyond 10% of the total amount of data, the statistics were collected at the end of the night. How to infer 10%. Previous posts have been researched: the principles and experiments of Oracle's own active statistics collection. This stale_percent=10% can not be changed, assuming that the table is very large. 10% is a lot of data that is likely to result in inaccurate statistical information.

After 11g, stale_percent=10% is able to change, divided into the global (dbms_stats. set_global_prefs) and table level (dbms_stats. Set_table_prefs). Table-level settings:

Change to 5% (range from 1-100): exec dbms_stats.set_table_prefs (NULL, ' table_name ', ' stale_percent ', 5);
Revert to 10%: exec dbms_stats.set_table_prefs (NULL, 'table_name', ' stale_percent ', null);
Query percentage: Select Dbms_stats.get_prefs (' Stale_percent ', NULL, ' table_name ') from dual;

---11g is Monday to Friday 22:00-2:00 Saturday Sunday 6:00-4:00

SELECT W.window_name, W.repeat_interval, W.duration, w.enabled
From Dba_autotask_window_clients C, Dba_scheduler_windows W
WHERE C.window_name = W.window_name
and c.optimizer_stats = ' ENABLED ';
Window_name Repeat_interval DURATION
-------------------- ------------------------------------------------------------ ---------------
Sunday_window freq=daily;byday=sun;byhour=6;byminute=0; Bysecond=0 +000 20:00:00
Friday_window freq=daily;byday=fri;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Wednesday_window freq=daily;byday=wed;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Saturday_window freq=daily;byday=sat;byhour=6;byminute=0; Bysecond=0 +000 20:00:00
Thursday_window freq=daily;byday=thu;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Tuesday_window freq=daily;byday=tue;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Monday_window freq=daily;byday=mon;byhour=22;byminute=0; Bysecond=0 +000 04:00:00

--Disable your own active collection
EXEC dbms_auto_task_admin. DISABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);
--Enable yourself to collect proactively
EXEC dbms_auto_task_admin. ENABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);
--View Job

Select Client_name,status from Dba_autotask_client;

client_name                     &NB Sp                                status
- -----------------------------------------------------------------------
Auto Optimizer stats collection                                  enabled
Auto Space advisor                             &NBSP ;                 ENABLED
SQL Tuning Advisor           & nbsp                          ,         ENA BLED

--Collect statistics for all users under the current database
EXEC dbms_stats.gather_database_stats (user);
--collects statistics for all objects under the current database user
EXEC dbms_stats.gather_schema_stats (user);
--collecting statistics for data dictionaries
exec dbms_stats.gather_dictionary_stats ();

-When the system has a very large partitioned table, it is assumed that all collections are always slower. 11g can be set after the incremental only the data changes in the partition to do the collection

exec dbms_stats.set_table_prefs (user, ' table_name ', ' INCREMENTAL ', ' TRUE ');--only the partitions that collect data changes

exec dbms_stats.set_table_prefs (user, ' table_name ', ' INCREMENTAL ', 'FALSE');--all to collect

Select Dbms_stats.get_prefs (' INCREMENTAL ', NULL, 'table_name') from dual;--view The value of the partition table INCREMENTAL

--Get Global Statistics collection settings options
Select Dbms_stats.get_prefs (' method_opt ') from dual;
Select Dbms_stats.get_prefs (' concurrent ') from dual;
Select Dbms_stats.get_prefs (' granularity ') from dual;

Select Dbms_stats.get_prefs (' INCREMENTAL ') from dual;
--Set STATISTICS collection options for global

EXEC dbms_stats. Set_param (' degree ', 4);

EXEC dbms_stats. Set_param (' INCREMENTAL ', ' TRUE ');

Some knowledge of the Oracle 11g system collecting statistical information on its own initiative