Basic execution plan statistics

We know that Oracle optimizes SQL queries based on CBO at the cost. The basis for Optimization Based on the cost is statistics.

If the statistics are not accurate, there are no rules for Oracle SQL queries, resulting in query plan problems.

 

1. Oracle 11g starts statistics collection tasks by default. The default Runtime is pm from Monday to Friday, and pm from Monday to Friday.

 

2. You can also disable automatic statistics for new collection tasks and select the manual collection method. However, this operation is generally not recommended.

 

Select window_name, window_next_time, autotask_status, optimizer_stats from dba_autotask_window_clients; window_name window_next_time autotask optimize activation fee -------- ----- monday_window 13-1-14 o'clock afternoon + 08:00 enabled fee 14-1-14 o'clock afternoon + 08:00 enabled fee 15-1-14 o'clock afternoon + 08:00 enabled fee 16-1 -14 PM + Enabled enabledfriday_window 17-1 pm-14 PM + Enabled AM + enabledsunday_window AM + enable00 enabled Enabled

Check whether automatic collection of statistics is allowed

select client_name,status from dba_autotask_client where client_name=‘auto optimizer stats collection‘;CLIENT_NAME                           STATUS--------------------------------------------auto optimizer stats collection      ENABLED

Collection prohibited

dbms_auto_task_admin.disable(client_name=>‘auto optimizer stats collection‘,operation=>NULL,window_name=>NULL);

Enable

begin  dbms_auto_task_admin.enable;  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,null);end;begin  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘WEDNESDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘FRIDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘SATURDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘THURSDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘TUESDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘SUNDAY_WINDOW‘);  dbms_auto_task_admin.enable(‘auto optimizer stats collection‘, null,‘MONDAY_WINDOW‘);end;

Query running status

-- Tracking job running status: -- You can query the view dba_autotask_history to track job running status: Select client_name, job_name, job_start_time from region where client_name = 'Auto optimizer stats collection 'order by job_start_time DESC;