Summary of jobs in Oracle databases

The Oracle database uses the SNP process to manage and run jobs. The biggest difference between the SNP process and other processes in the instance is that after the process is killed, the system automatically restarts a SNP,

The Oracle database uses the SNP process to manage and run jobs. The biggest difference between the SNP process and other processes in the instance is that after the process is killed, the system automatically restarts a SNP,

The Oracle database uses the SNP process to manage and run jobs. The biggest difference between the SNP process and other processes in the instance is that after the process is killed, the system automatically restarts a SNP, therefore, the running of Oracle instances is not affected. the SNP process itself is also periodically called by the system to view the JOB sequence directory in the data dictionary to see if there is a JOB to run. After running, the SNP enters the sleep state. wake-up: the time when the SNP is called is set in the database initialization file through the job_queue_interval parameter. When the database opens, it initializes the SNP process according to the initialization file.

I. Description of SNP Parameters

The granularity of job_queue_interval cannot be too large. The interval between JOB execution should not be greater than that of JOB execution (determined by the interval parameter). Otherwise, some jobs may fail to be executed. the granularity cannot be too small. Otherwise, the system will frequently call the SNP and increase the system throughput. Therefore, it must be set according to the JOB in the specific environment.

The job_queue_interval parameter sets the maximum number of running SNP in the system.

2. When a SNP starts executing a task, the process is as follows:

1. Start a new database session with the user name of the task owner.

2. When the task is submitted for the first time or modified for the last time, the change session NLS settings match the currently ready task.

3. Calculate the next execution time using the interval date expression and system time.

4. PL/SQL defined by the execution task.

5. If the task runs successfully, the next execution date of the task is updated. Otherwise, the count in the log file is increased by 1.

6. After JOB_QUEUE_INTERVAL seconds, the running time of another SNP is reached and the above process is repeated.

Iii. How to Handle job failures

Theoretically speaking, on the Internet and in books, when a JOB fails, the system will re-run the JOB in one minute. If the JOB still fails, the JOB will continue to run at an interval of 2 minutes between the 1st failures. If the JOB still fails, run the JOB four minutes after the failure and 2nd (as shown in the T = 2t-1 table, t indicates the number of times detected after the failure ), after T ≥ interval (the parameter set for the JOB to work normally), the system detects the interval as interval. this job will be set to broken until 16th failures. however, these introductions are very rough. If you need to design some reasonable jobs, some minor problems will be ignored, and these minor problems are exactly the key points.

Ask a question: it indicates that there is such a mechanism in the Oracle backend, that is, to determine whether T is greater than or equal to interval. Then, after a job fails, it should be judged first ,, wait for 1 minute before detection.

After the experiment, the result is obtained: first, the result is determined before detection. The operation error is slightly different from the theoretical value.