Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Oracle automatic collection of statistical information how to achieve", in the daily operation, I believe that many people in the Oracle automatic collection of statistical information on how to achieve the problem, the editor consulted all kinds of data, sorted out a simple and easy-to-use method of operation, hope to answer the "Oracle automatic collection of statistical information how to achieve" help! Next, please follow the editor to study!

Automatic collection of statistics is provided in the 11g version of Oracle. During the deployment and installation of 11g Oracle software, one of the steps is to prompt whether to enable this feature (this feature is enabled by default).

1. Check the task and status of automatically collecting statistics:

SQL > select client_name,status from dba_autotask_client

CLIENT_NAME STATUS

-

Auto optimizer stats collection ENABLED

Auto space advisor ENABLED

Sql tuning advisor DISABLED

SQL >

II. The task of prohibiting automatic collection of statistical information

SQL > exec DBMS_AUTO_TASK_ADMIN.DISABLE (client_name = > 'auto optimizer stats collection',operation = > NULL,window_name = > NULL)

PL/SQL procedure successfully completed.

SQL > select client_name,status from dba_autotask_client

CLIENT_NAME STATUS

-

Auto optimizer stats collection DISABLED

Auto space advisor ENABLED

Sql tuning advisor DISABLED

III. Enable the task of automatically collecting statistics

SQL > exec DBMS_AUTO_TASK_ADMIN.ENABLE (client_name = > 'auto optimizer stats collection',operation = > NULL,window_name = > NULL)

PL/SQL procedure successfully completed.

SQL > select client_name,status from dba_autotask_client

CLIENT_NAME STATUS

-

Auto optimizer stats collection ENABLED

Auto space advisor ENABLED

Sql tuning advisor DISABLED

Fourth, obtain the execution time of the current automatic collection of statistical information:

SQL > col WINDOW_NAME format A20

SQL > col REPEAT_INTERVAL format A70

SQL > col DURATION format A20

SQL > set line 180

SQL > select t1.windowchart namewriting t1.repeatables intervalrecoveryt1.recording

From dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

Where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED')

WINDOW_NAME REPEAT_INTERVAL DURATION

-

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

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

7 rows selected.

Where: WINDOW_NAME: task name REPEAT_INTERVAL: task repetition interval DURATION: duration

five。 Time to modify the execution of statistics:

1. Stop the task:

SQL > BEGIN

DBMS_SCHEDULER.DISABLE (

Name = >'"SYS". "THURSDAY_WINDOW"'

Force = > TRUE);-- stop task is true

END

/

SQL >

two。 Modify the duration of the task in minutes:

SQL > BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

Name = >'"SYS". "THURSDAY_WINDOW"'

Attribute = > 'DURATION'

Value = > numtodsinterval (60 minutes minute)

END

/

3. Start execution time, BYHOUR=2, which means execution starts at 2: 00: 00:

SQL > BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

Name = >'"SYS". "THURSDAY_WINDOW"'

Attribute = > 'REPEAT_INTERVAL'

Value = > 'freq=daily;byday=THU;byhour=10;byminute=40;bysecond=0')

END

/

4. Start the task:

SQL > BEGIN

DBMS_SCHEDULER.ENABLE (

Name = >'"SYS". "THURSDAY_WINDOW"')

END

/

5. View the modified situation:

SQL > select t1.windowchart namewriting t1.repeatables intervalrecoveryt1.recording

From dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

Where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED')

WINDOW_NAME REPEAT_INTERVAL DURATION

-

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=10;byminute=40;bysecond=0 + 000 01:00:00

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

six。 View the history of statistics execution

-- maintenance window group

Select * from dba_scheduler_window_groups

-- maintenance window group corresponding window

Select * from dba_scheduler_wingroup_members

-- maintain window history information

Select* from dba_scheduler_windows

-- query the job that the automatic collection task is executing

Select * from DBA_AUTOTASK_CLIENT_JOB

-- query the execution status of automatic collection task history

Select * from DBA_AUTOTASK_JOB_HISTORY

Select * from DBA_AUTOTASK_CLIENT_HISTORY

At this point, the study on "how to realize the automatic collection of statistical information by Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.