Hi All,
We have setup regular run background process,setup to "throttle" user submitted
Batch Requests to Batch Processing System. Purpose of this "Throttle" DB level background
process (submitted using DBMS_SCHEDULER) to check for currently active Requests and
then accordingly (based on prevailing System Load) inject new requests for Batch Request accordingly.
This background process is scheduled to run every minute.
We find that UPDATE statement below performs well when Table being updated (FRM_BPF_REQUEST)
even when Table has upto 1 million rows. (Expected Production volume) UPDATE takes only few seconds (< 10 secs)
at most to execute
However, we find that when there is a burst of INSERTS happening to same Table (FRM_BPF_REQUEST)
via another database session, UPDATE statement suffers with severe degradation. Same UPDATE which used
to perform in matter of few seconds, takes upto 40 minutes when heavy INSERTS are happenning to
Table. We are trying to understand why Performance gets severely degraded when INSERTS are heavy on the Table,
Any thoughts or insights into issue would be greatly appreciated.
We are using Oracle DB 11.2.0.3.4 (on Linux)
CREATE OR REPLACE PROCEDURE BPF_DISPATCH_REQUEST_SP(V_THROTTLE_SIZE NUMBER DEFAULT 600) AS
-- Change History
--001 -Auro -10/09/2013 -Initial Version
-- v_throttle_size NUMBER DEFAULT 600;
v_active_cnt NUMBER DEFAULT 0;
v_dispatched_cnt NUMBER DEFAULT 0;
v_start_time TIMESTAMP := SYSTIMESTAMP;
v_end_time TIMESTAMP;
v_subject_str VARCHAR2(100) := '';
v_db_name VARCHAR2(20) := '';
BEGIN
/*
-- Determine Throttle Size
SELECT THROTTLE_SIZE
INTO v_throttle_size
FROM FRM_BPF_REQUEST_CONTROL;
*/
-- Determine BPF Active Request Count
SELECT COUNT(*)
INTO v_active_cnt
FROM FRM_BPF_REQUEST
WHERE STATUS IN('rm_pending','rm_ready','processing','worker_ready','failed','dependency_failed','recover_ready');
IF v_active_cnt < v_throttle_size THEN
UPDATE FRM_BPF_REQUEST
SET STATUS='dispatched'
WHERE ID IN (
SELECT ID FROM (
SELECT ID
FROM FRM_BPF_REQUEST
WHERE STATUS='new'
ORDER BY ID
) WHERE ROWNUM <= (v_throttle_size - v_active_cnt)
);
v_dispatched_cnt := SQL%ROWCOUNT;
COMMIT;
END IF;
v_end_time := SYSTIMESTAMP;
INSERT INTO FRM_BPF_REQUEST_DISPATCH_LOG
VALUES (
v_start_time,
v_active_cnt,
v_dispatched_cnt,
v_end_time,
NULL
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_end_time := SYSTIMESTAMP;
INSERT INTO FRM_BPF_REQUEST_DISPATCH_LOG
VALUES (
v_start_time,
v_active_cnt,
v_dispatched_cnt,
v_end_time,
NULL
);
COMMIT;
SELECT ORA_DATABASE_NAME
INTO v_db_name
FROM DUAL;
v_subject_str := v_db_name||' DB: Fatal Error in BPF Request Dispatch Process';
-- Alert Support
DBA_PLSQL.SEND_MAIL(P_RECIPIENTS => 'BPFSP@EXCH.OOCL.COM',
P_CC => 'IR4PRDSUP@EXCH.OOCL.COM',
P_BCC => 'IRISDBA@EXCH.OOCL.COM',
P_SUBJECT => v_subject_str,
P_BODY => SUBSTR(SQLERRM, 1, 250));
END;
/
show errors
Thanks
Auro