Scheduling Jobs in the Database
The PM_MEDIATION_TB table has the following structure.
| Name | Null? | Type |
|---|---|---|
| APPLICATION_NAME | NOT NULL | VARCHAR2(30) |
| SERVICE_NAME | NOT NULL | VARCHAR2(30) |
| OPERATION_NAME | NOT NULL | VARCHAR2(50) |
| START_TIME | NOT NULL | TIMESTAMP(6) |
| END_TIME | NOT NULL | TIMESTAMP(6) |
| TOT_REQ | NUMBER | |
| SUCCESS_COUNT | NUMBER | |
| FAILURE_COUNT | NUMBER | |
| AVAILABILITY | NUMBER |
The above task is independent of other on-line systems and happens exclusively within the database. This task can be scheduled as a database job. The database job has its own control table to take care of failures for rerun independent of the scheduler table managed by Oracle. This control table contains the data window that is independent of the run window.
The function is required to run only on a timed cycle or at a denoted time. The DB job is akin to a batch process that is data-centric and can crunch efficiently through large volumes offline without affecting the on-line systems such as the access gateway.
The DB Job uses the following control table to maintain the timestamp. The DB job should start from specific time. This timestamp will be maintained in the Prev_timestamp column. When the DB job is started for the first time, the control table will be empty. When the job runs for the first time, you should get the min (start_time) from the PERFORMANCE_METRICS_TB as the prev_timestamp for the DB job to run.
CONTROL_TB
The control table is given as below
| PROCESS_ID | PROCESS_NAME | PREV_TIMESTAMP |
|---|---|---|
| 1 | pr_AggregateMetrics | 2007-9-18.14.25. 30. 149000000 |
When the procedure "pr_AggregateMetrics" runs for the first time, it takes the prev_timestamp = min(start_time) from PERFORMANCE_METRICS_TB.
You also can configure the data collection interval and repeat interval in the table PM_DELTA_CONFIG. Assume that the application populates these data during startup.
PM_DELTA_CONFIG
| PROCESS_NAME | DATA_COLLECTION_ INTERVAL | REPEAT_INTERVAL |
|---|---|---|
| pr_AggregateMetrics | 60 (in secs) | 60 (in secs) |
The database job will take prev_timestamp from the CONTROL_TB table. The end time is prev_timestamp + DATA_COLLECTION_INTERVAL. Once the job is successfully executed, it will update prev_timestamp with the end_time. The DB job calculates the no_of_chunks of data to be processed as
No_of_chunks = REPEAT_INTERVAL / DATA_COLLECTION_INTERVAL
The db job processes the "n" minute data when it wakes up every time. This "n" minute is configured as "data_collection_interval" in the table PM_DELTA_CONFIG.
The following is the PL/SQL procedure that aggregates the data from Performance_Metrics_TB and populates the aggregated data into PM_MEDIATION_TB.
PROMPT Creating Package Specification For pkg_PerformanceMetrics
CREATE OR REPLACE PACKAGE pkg_PerformanceMetrics AS
-- Declaring the User Defined Exceptions in the Package Level.
PM_Data_Not_Available_Excp EXCEPTION;
PRAGMA EXCEPTION_INIT(PM_Data_Not_Available_Excp,-20100);
Large_Data_Window_Excp EXCEPTION;
PRAGMA EXCEPTION_INIT(Large_Data_Window_Excp,-20101);
-- procedure declaration
procedure pr_AggregateMetrics;
END pkg_PerformanceMetrics;
/
PROMPT Creating Package Body For pkg_PerformanceMetrics
CREATE or REPLACE package body pkg_PerformanceMetrics IS
PROCEDURE pr_AggregateMetrics AS
-- Declaring and Initializing the Variables
V_Err_Msg varchar2(4000);
V_Err_Code varchar2(100);
V_Err_Date DATE:=sysdate;
V_Proc_Name varchar2(50):= 'pr_AggregateMetrics';
v_currenttime timestamp ;
v_prevtimestamp timestamp ;
v_datacollection_interval number;
v_repeat_interval number;
v_noofchunks number;
v_start_time timestamp;
v_commit_cnt number := 0;
v_Metrics_found boolean := false;
v_min_time timestamp;
v_reccount number;
-- Defining the Cursor with parameters previous time and current
-- time
cursor PM_MetricsInfo_cur(v_prevtimestamp IN timestamp,
v_currenttime IN timestamp) is
select application_name,service_name,
operation_name,start_time,end_time,
sum(total_request) as total_request,
sum(success_count)as success_count,
sum(err_count) as failure_count ,
(sum(err_count)+sum(success_count))/sum(total_request) as
availability
from PERFORMANCE_METRICS_TB
where start_time>=to_timestamp(to_char(v_prevtimestamp,
'dd-mm-yyyy hh24:mi.ss.'),'dd-mm-yyyy hh24:mi.ss.') and
to_timestamp(to_char(end_time,'dd-mm-yyyy hh24:mi.ss.'),
'dd-mm-yyyy hh24:mi.ss.')<=v_currenttime
group by application_name,service_name,operation_name,
start_time,end_time;
BEGIN
-- Getting the data_collection_interval,repeat interval from
-- PM_DELTA_CONFIG
BEGIN
select DATA_COLLECTION_INTERVAL,
REPEAT_INTERVAL into v_datacollection_interval,
v_repeat_interval
from PM_DELTA_CONFIG where PROCESS_NAME='pr_AggregateMetrics';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(V_Err_Date ||'—'||
V_Proc_Name ||'—'|| 'Configuration details are missing
in PM_DELTA_CONFIG');
raise;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(V_Err_Date ||'—'||
V_Proc_Name ||'—'|| 'Exact fetch returns more than one
row. Please check PM_DELTA_CONFIG tables');
raise;
END;
IF (v_datacollection_interval>v_repeat_interval) THEN
V_Err_Msg:='Data Window is larger than the repeat interval';
--DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'||
V_Err_Msg);
raise_application_error(-20101, 'Data Window is larger than
the repeat interval');
END IF;
-- Finding out the number of chunks of data
v_noofchunks := v_repeat_interval/v_datacollection_interval;
For i in 1..v_noofchunks Loop
Begin
Begin
-- Get the previous timestamp from the control table for this
-- procedure "pr_AggregateMetrics"
select prev_timestamp into v_prevtimestamp
from CONTROL_TB
where process_name ='pr_AggregateMetrics';
Exception
WHEN TOO_MANY_ROWS THEN
V_Err_Msg:='Exact fetch returns more than one row.. Check the
CONTROL_TB for PROCESS_NAME= pr_AggregateMetrics1';
--DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'||
V_Err_Msg);
raise;
WHEN NO_DATA_FOUND THEN
BEGIN
-- No data found in control table.
-- Getting the min(start_time) from PERFORMANCE_METRICS_TB
-- for the first run.
select min(start_time) into v_start_time
from PERFORMANCE_METRICS_TB;
-- Updating the Control table with the start time.
IF (v_start_time is not null) THEN
insert into CONTROL_TB(process_ID,process_name,
prev_timestamp)
values(1,'pr_AggregateMetrics1',v_start_time);
commit;
v_prevtimestamp := v_start_time;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_Err_Msg:='Performance Metrics data is not yet populated';
raise_application_error(-20100,'Performance Metrics data is
not yet populated');
END;
End;
v_currenttime:= to_timestamp(to_char(v_prevtimestamp +
v_datacollection_interval*(1/24/60/60),
'dd-mm-yyyy hh24:mi:ss.')||to_char(to_number
(to_char(v_prevtimestamp,'FF'))+250),
'dd-mm-yyyy hh24:mi:ss.FF');
FOR Metric_rec in PM_MetricsInfo_cur(v_prevtimestamp,v_currenttime)
LOOP
BEGIN
-- DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'||'
-- Processing record with service name ' ||
-- Metric_rec.service_name);
v_Metrics_found := true;
SELECT count(*) into v_reccount FROM PM_MEDIATION_TB
WHERE service_name=Metric_rec.service_name and
application_name= Metric_rec.application_name and
operation_name = Metric_rec.operation_name and
start_time=Metric_rec.start_time and
end_time=Metric_rec.end_time;
IF (v_reccount=0) THEN
insert into PM_MEDIATION_TB (service_name,
application_name, operation_name, start_time,
end_time, tot_req, success_count,failure_count,
availability)
values(Metric_rec.service_name,
Metric_rec.application_name,
Metric_rec.operation_name,
Metric_rec.start_time,
Metric_rec.end_time,
Metric_rec.total_request,
Metric_rec.success_count,
Metric_rec.failure_count,
Metric_rec.availability
);
ELSE
UPDATE PM_MEDIATION_TB
SET tot_req=Metric_rec.total_request,
success_count=Metric_rec.success_count,
failure_count=Metric_rec.failure_count,
availability=Metric_rec.availability
WHERE service_name=Metric_rec.service_name and
application_name= Metric_rec.application_name and
operation_name = Metric_rec.operation_name and
start_time=Metric_rec.start_time and
end_time=Metric_rec.end_time;
END IF;
END;
END LOOP;
-- If the metrics are collected successfully,
-- update the prevtimestamp into the CONTROL_TB
IF (v_Metrics_found) THEN
UPDATE CONTROL_TB
SET prev_timestamp=v_currenttime
WHERE PROCESS_NAME='pr_AggregateMetrics';
END IF;
-- Making the DB changes permanent
COMMIT;
IF (not v_Metrics_found) THEN
select min(start_time) into v_min_time
from PERFORMANCE_METRICS_TB
where start_time>v_prevtimestamp;
-- dbms_output.put_line('start_time : ' ||v_min_time
-- || ' v_prevtimestamp : '||v_prevtimestamp);
IF (v_min_time is not null) THEN
UPDATE CONTROL_TB
SET prev_timestamp=v_min_time
WHERE PROCESS_NAME='pr_AggregateMetrics';
COMMIT;
END IF;
END IF;
END;
v_Metrics_found := false;
End Loop; -- for i loop
EXCEPTION
WHEN TOO_MANY_ROWS THEN
V_Err_Msg :=SQLERRM;
V_Err_Date:=sysdate;
--DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name ||'—'||
--V_Err_Msg);
WHEN NO_DATA_FOUND THEN
V_Err_Msg :=SQLERRM;
V_Err_Date:=sysdate;
ROLLBACK;
--DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name ||'—'||
--V_Err_Msg);
WHEN PM_Data_Not_Available_Excp THEN
V_Err_Msg :='Performance Data is not available';
V_Err_Date:=sysdate;
--DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name
--||'—'|| V_Err_Msg);
WHEN Large_Data_Window_Excp THEN
V_Err_Msg:='';
WHEN OTHERS THEN
V_Err_Msg :=SQLERRM;
ROLLBACK;
--DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name
--||'—'||V_Err_Msg);
END pr_AggregateMetrics;
END pkg_PerformanceMetrics;
/
Page 3 of 4
This article was originally published on November 30, 2007
