To close an execution task by using the Dbms_stat function

See the SQL statement with the following statement that a physical read occurred

Select executions, Disk_reads, Buffer_gets, round ((buffer_gets-disk_reads)/buffer_gets, 2) Hit_radio , round (disk_reads/executions, 2) Reads_per_run, Sql_text, sql_id, last_load_time from V$sqlare A Where executions > 0 and buffer_gets > 0 and (buffer_gets-disk_reads)/Buffer_gets < 0.8 and Last_load _time > To_date (' 2015-04-19 ', ' yyyy/mm/dd ') Order by 4 desc;

Just see that the statement disk reads very high and executes more often.

Call Dbms_stats.gather_database_stats_job_proc ()

Gather_database_stats_job_proc is an internal stored procedure, basically with dbms_stats. Gather_database_stats functions the same, but with internal prioritization, the more tables that are updated, the more priority the statistics will be collected.

Because the work is expensive and you intend to close it, you need to use the Dbms_schduler built-in function, which has the following procedure calls

...... Create_programdrop_programcreate_jobcreate_scheduledrop_scheduledisableenable ...

And what we need to do here is to close the specified task with the following statement

EXEC Dbms_scheduler. DISABLE (' gather_stats_job ');

DISABLE (name,force,commit_semantics) lists the task names, whether they are enforced, whether they are committed, and both default to Y

Enbable (Name,commit_semantics) Same

and creating a task plan can be done in the following ways

Dbms_schduler. Create_job (' job_name ', ' job_type ', ' job_action ', ' number_of_arguments ', ' start_date ', ' repeat_interval ', ' end_date ', ' Job_class ', ' enabled ', ' auto_drop ', ' comments ', ' credential_name ', ' destination_name ')

Gca

Begindbms_scheduler. Create_job (job_name = ' Arc_move ', schedule_name = ' every_60_mins ', Job_type = ' executable ', JO B_action = '/u01/scripts/move_arcs. SH ', ENABLED = true,comments = ' MOVE archived LOGS to A DIFFERENT DIRECTORY '); END;

Or it can be

Dbms_schduler. Create_schedule (schedule_name,start_date,repeat_interval,end_date,comments)

Gca

BEGIN Dbms_scheduler.        Create_schedule (repeat_interval = ' freq=minutely;interval=1 ', start_date = Sysdate, COMMENTS  = ' Rosanu_create_job ', schedule_name = ' Schedule_rosanu '); END;

Where Repeat_interval deserves our attention, here is the interval execution time that provides the task schedule.

The Freq keyword indicates the frequency and optional parameters are:

Yearly (years), MONTHLY (month), WEEKLY (weeks), DAILY (Sun), HOURLY (Hours), minutely (min), secondly (sec)

The interval keyword indicates the frequency, and the value range is 1~999. Run once every 10 days as follows:

Repeat_interval = ' freq=daily; Interval=10 ';

Byday keyword table specific day time, the following represents every Friday execution

Repeat_interval = ' freq=daily; Byday=fri ';

The day of the month in the ByMonthDay keyword table, the following represents the last day of the week

Repeat_interval = ' freq=monthly; Bymonthday=-1 ';

The Bymonth keyword indicates the specific month

Repeat_interval = ' freq=yearly; Bymonth=mar; Bymonthday=10 ';

bydate keyword to specify the number of days by numeric value

Repeat_interval = ' freq=yearly; Bydate=0310 ';

Byhour keyword indicates hours

Repeat_interval = ' freq=daily; Byhour=16,17,18 ';

You can also specify in this way

Repeat_interval = ' trunc (sysdate) +1 '

View all current execution tasks on the database by using the following statement

SELECT OWNER, Job_name, Job_action, Repeat_interval, Last_start_date, COMMENTS from All_sch Eduler_jobs

In addition to the Dba_scheduler_jobs and User_scheduler_jobs,user tables, there is no owner field.

This article is from the "linuxoracle" blog, make sure to keep this source http://onlinekof2001.blog.51cto.com/3106724/1636035

To close an execution task by using the Dbms_stat function