Disable task execution using the DBMS_STAT Function

Disable task execution using the DBMS_STAT Function

Use the following statements to view SQL statements with large physical reads

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 $ sqlarea
Where executions> 0
And buffer_gets> 0
And (buffer_gets-disk_reads)/buffer_gets <0.8
And last_load_time> to_date ('2017-04-19 ', 'yyyy/mm/dd ')
Order by 4 desc;

The disk read of the statement is very high and the execution frequency is large.

Call dbms_stats.gather_database_stats_job_proc ()

GATHER_DATABASE_STATS_JOB_PROC is an internal stored procedure, which is basically the same as DBMS_STATS.GATHER_DATABASE_STATS. However, given internal priorities, more new tables give priority to collecting statistics.
Because this is a big task, we plan to close it. here we need to use the built-in functions of DBMS_SCHDULER. the built-in functions include the following process calls:
......
CREATE_PROGRAM
DROP_PROGRAM
CREATE_JOB
CREATE_SCHEDULE
DROP_SCHEDULE
DISABLE
ENABLE
......

Here, we need to use the following statement to close the specified task.
EXEC DBMS_SCHEDULER.DISABLE ('gather _ STATS_JOB ');

DISABLE (NAME, FORCE, COMMIT_SEMANTICS) lists the task names, whether to FORCE, whether to submit, and the default values of the latter two are Y
The ENBABLE (NAME, COMMIT_SEMANTICS) is the same
You can create a task scheduler in the following ways:
123 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 ')

Example:
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'arc _ MOVE ',
SCHEDULE_NAME => 'every _ 60_MINS ',
JOB_TYPE => 'executable ',
JOB_ACTION => '/U01/SCRIPTS/MOVE_ARCS.SH ',
ENABLED => TRUE,
COMMENTS => 'move archived logs to a different directory'
);
END;

Or yes.
DBMS_SCHDULER.CREATE_SCHEDULE (SCHEDULE_NAME, START_DATE, REPEAT_INTERVAL, END_DATE, COMMENTS)

Example:
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE
(
REPEAT_INTERVAL => 'freq = MINUTELY; INTERVAL = 1 ',
START_DATE => SYSDATE,
COMMENTS => 'rosanu _ create_job ',
SCHEDULE_NAME => 'schedule _ rosanu'
);
END;

Repeat_interval is worth noting. Here is the interval execution time of the task plan.
The FREQ keyword indicates the frequency. Optional parameters include:
YEARLY (year ),
MONTHLY (month ),
WEEKLY (week ),
DAILY (day ),
HOURLY (hour ),
MINUTELY (points ),
SECONDLY (seconds)

The INTERVAL keyword indicates the frequency. value range: 1 ~ 999. Run the task once every 10 days, as shown in the following figure:
REPEAT_INTERVAL => 'freq = DAILY; INTERVAL = 10 ';

The time of the specified day In the BYDAY keyword table. The following indicates that the statement is executed every Friday.
REPEAT_INTERVAL => 'freq = DAILY; BYDAY = fri ';

The day of the BYMONTHDAY keyword table month. The following indicates the last day of each month.
REPEAT_INTERVAL => 'freq = MONTHLY; BYMONTHDAY =-1 ';

The BYMONTH keyword indicates the specific month.
REPEAT_INTERVAL => 'freq = YEARLY; BYMONTH = MAR; BYMONTHDAY = 10 ';

The BYDATE keyword specifies the number of days by numerical value.
REPEAT_INTERVAL => 'freq = YEARLY; BYDATE = 100 ';

BYHOUR keyword indicates hour
REPEAT_INTERVAL => 'freq = DAILY; BYHOUR = 16, 17, 18 ';

You can also specify

REPEAT_INTERVAL => 'trunc (sysdate) + 1'
Run the following statement on the database to view all the current tasks.
Select owner,
JOB_NAME,
JOB_ACTION,
REPEAT_INTERVAL,
LAST_START_DATE,
COMMENTS
FROM ALL_SCHEDULER_JOBS

In addition, there are DBA_SCHEDULER_JOBS and USER_SCHEDULER_JOBS. The USER table does not have the OWNER field.