Suppose I have a process PR_PROCESS_JOB that takes a certain amount of time to complete, say 25 seconds, and I create a scheduler to run this process every 15 seconds. The oracle scheduler runs in such a way that is wont start the next scheduler process till the previous job has run and completed.
This can lead to a scenario where the number of records to be processed can keep building up.
To optimize this we can run the process in the background using DBMS_JOB.SUBMIT.
Now the job will run in the background and the scheduler is ready to start a new thread at the next interval of 15 seconds thus improving the processing speed and ensuring that the records in queue to be processed are in a manageable limit.
Below are the scripts for running the job.
CREATE OR REPLACE PROCEDURE PR_PROCESS_JOB AS
v_program_name VARCHAR2(100) := 'PR_PROCESS_JOB';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
v_error_code NUMBER := PKG_ARCHIVE.SUCCESS;
v_archv_btn VARCHAR2(10) := 'STOP';
v_loop_cntr NUMBER := 0;
BEGIN
DBMS_LOCK.SLEEP(25);
v_err_desc := 'Calling scheduler every 25 seconds ' || v_loop_cntr;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
commit;
END PR_PROCESS_JOB;
create or replace PROCEDURE PR_TEST_SCHEDULER AS
i_jobno binary_integer;
BEGIN
DBMS_JOB.SUBMIT(i_jobno, 'BEGIN PR_PROCESS_JOB; END;',SYSDATE );
COMMIT;
END PR_TEST_SCHEDULER;
We give the following grants to HR schema to create a scheduler process.
grant create any job to HR;
GRANT EXECUTE ON DBMS_SCHEDULER TO HR;
BEGIN
DBMS_SCHEDULER.create_job (job_name => 'TEST_SCHEDULER_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'PR_TEST_SCHEDULER',
start_date => TO_dATE('31-dec-24 10:19:00','DD-MON-YY HH24:MI:SS'),
repeat_interval => 'freq=secondly; interval=15;',
enabled => TRUE);
END;
select OWNER, JOB_NAME, JOB_SUBNAME, JOB_STYLE, JOB_CREATOR, JOB_TYPE, JOB_ACTION,START_DATE, REPEAT_INTERVAL, STATE, LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE from all_scheduler_jobs where owner = 'HR_ARCHIVE';
| OWNER | JOB_NAME | JOB_SUBNAME | JOB_STYLE | JOB_CREATOR | JOB_TYPE | JOB_ACTION | START_DATE | REPEAT_INTERVAL | STATE | LAST_START_DATE | LAST_RUN_DURATION | NEXT_RUN_DATE |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| HR_ARCHIVE | DBMS_JOB$_201 | REGULAR | HR_ARCHIVE | PLSQL_BLOCK | BEGIN PR_PROCESS_JOB; END; | 01-JAN-25 10.32.00.000000000 AM +05:30 | RUNNING | 01-JAN-25 10.32.00.201000000 AM +05:30 | 01-JAN-25 10.32.00.144000000 AM +05:30 | |||
| HR_ARCHIVE | DBMS_JOB$_202 | REGULAR | HR_ARCHIVE | PLSQL_BLOCK | BEGIN PR_PROCESS_JOB; END; | 01-JAN-25 10.32.15.000000000 AM +05:30 | RUNNING | 01-JAN-25 10.32.15.270000000 AM +05:30 | 01-JAN-25 10.32.15.259000000 AM +05:30 | |||
| HR_ARCHIVE | TEST_SCHEDULER_JOB | REGULAR | HR_ARCHIVE | STORED_PROCEDURE | PR_TEST_SCHEDULER | 31-DEC-24 10.19.00.000000000 AM ASIA/CALCUTTA | freq=secondly; interval=15; | SCHEDULED | 01-JAN-25 10.32.15.120000000 AM ASIA/CALCUTTA | +00 00:00:00.179000 | 01-JAN-25 10.32.30.000000000 AM ASIA/CALCUTTA |
select * from all_scheduler_job_run_details where owner = 'HR_ARCHIVE' and LOG_DATE > to_date('01-JAN-25 10:16:00','DD-MON-YY HH24:MI:SS') ORDER BY LOG_DATE DESC;
SELECT * FROM ALL_SCHEDULER_JOB_LOG WHERE OWNER = 'HR_ARCHIVE' and LOG_DATE > to_date('01-JAN-25 10:16:00','DD-MON-YY HH24:MI:SS') ORDER BY LOG_DATE DESC;
select * from all_scheduler_running_jobs;
SELECT TXT_PROGRAM_NAME, TXT_REMARKS, CRTD_BY, CRTD_DT FROM ARCHIVE_ERROR_LOG ORDER BY CRTD_DT DESC;
| TXT_PROGRAM_NAME | TXT_REMARKS | CRTD_BY | CRTD_DT |
|---|---|---|---|
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 10:47:25 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 10:47:10 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 10:46:55 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 10:46:40 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 10:46:25 |
create or replace PROCEDURE PR_TEST_SCHEDULER AS
i_jobno binary_integer;
BEGIN
-- DBMS_JOB.SUBMIT(i_jobno, 'BEGIN PR_PROCESS_JOB; END;',SYSDATE );
PR_PROCESS_JOB;
COMMIT;
END PR_TEST_SCHEDULER;
If we check the timings of the process which is running in the log table, it will show an entry at a frequency of every 25 seconds and now the scheduler frequency of every 15 seconds.
Thus using DBMS_JOB.SUBMIT we can run the process asynchronously at regular scheduler intervals. The drawback of this is that if that if each process in PR_PROCESS_JOB takes a long time to complete the number of DBMS_JOB threads can increase and it can cause a performance bollleneck.
To control the number of this background processes we can check the count of these processes in PR_TEST_SCHEDULER. If this count is within a fixed number say 5, only then we call the next DBMS_JOB.SUBMIT process.
Now PR_TEST_SCHEDULER will check if the number of processes running is more than 5. If so it will simply exit and not call PR_PROCESS_JOB till one job gets completed. Thus at any point we can check and make sure that the number of jobs submitted is not > 'x' count and the scheduler processes are not increasing continuously and causing a CPU and performance bottleneck.
Below is the code for the same
SELECT TXT_PROGRAM_NAME, TXT_REMARKS, CRTD_BY, CRTD_DT FROM ARCHIVE_ERROR_LOG ORDER BY CRTD_DT DESC;
| TXT_PROGRAM_NAME | TXT_REMARKS | CRTD_BY | CRTD_DT |
|---|---|---|---|
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 11:03:06 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 11:02:41 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 11:02:16 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 11:01:51 |
| PR_PROCESS_JOB | Calling scheduler every 25 seconds 0 | HR_ARCHIVE | 01-JAN-25 11:01:26 |
SELECT COUNT(1) FROM
all_scheduler_jobs
where JOB_ACTION = 'BEGIN PR_PROCESS_JOB; END;'
and STATE = 'RUNNING';
create or replace PROCEDURE PR_TEST_SCHEDULER AS
i_jobno binary_integer;
v_job_cntr INTEGER;
BEGIN
SELECT COUNT(1)
INTO v_job_cntr
FROM all_scheduler_jobs
where JOB_ACTION = 'BEGIN PR_PROCESS_JOB; END;'
and STATE = 'RUNNING';
IF v_job_cntr <= 5
THEN
DBMS_JOB.SUBMIT(i_jobno, 'BEGIN PR_PROCESS_JOB; END;',SYSDATE );
COMMIT;
END IF;
END PR_TEST_SCHEDULER;
Thought for the day
Wisdom teaches her children
and gives help to those who seek her
Whoever loves her loves life,
and those who seek her from early morning are filled with joy.
(Wisdom beckons us to start the day with prayer and praises to our Creator)
Whoever holds her fast inherits glory,
and the Lord blesses the place she enters.
Those who serve her minister to the Holy One;
the Lord loves those who lover her.
Sirach 4:11-14
No comments:
Post a Comment