hello,
looking for some help/input.
problem: we want to add some triggers on a few key tables in our system without introducing much blocking/waiting every time the triggers fire.
experiments:
-
we tried adding the triggers as regular pl/sql blocks (they call a java class in the jvm that does an api call to one of our production apis), but wait time is high when batch processes that update the tables run. single user queries weren't affected too much, but, still the waiting/blocking is an issue we want to address.
-
we tried changing the code in the triggers to create a job with auto-drop enabled (by default) and arguments hardcoded into the command so it would fire asynchronously, but dba's said no-way to creating jobs on the fly via triggers (is this a terrible idea?), so, we never tested that method.
-
we tried creating a disabled job with arguments using dbms_scheduler.create_job(), then pointing the triggers to call a procedure inside a package that would set the arguments and enable the job. i tried doing this with dbms_scheduler.enable and with dbms_scheduler.run_job. results were:
a) using dbms_scheduler.enable(), calls to this job were still blocking/waiting
b) with dbms_scheduler.run_job() and use_current_session set to false, there was still blocking/waiting for each call
c) with use_current_session set to true, i got errors that the job was already running. i guess it didnt spawn a slave process because the job names are identical, only the arguments changed?
- created a queueing table and just had the triggers insert a record into a queue table, and then wrote an external dequeueing process, but we're not thrilled about this model. we'd rather have the triggers work quickly/asynchronously if possible and not have to write external systems that we then have to monitor, support, etc. this method did run very quickly though. it was about 30-40% faster than the others.
we really are just looking for a simple way to fire the triggers off asynchronously. below is a sample of what they look like.
Any helpful comments or recommendations are welcomed!
Trigger structure example:
PRAGMA AUTONOMOUS_TRANSACTION;
vCustId CUST_ID%TYPE := :OLD.UMD_MI_ID;
vStr VARCHAR2(64);
vTrg VARCHAR2(30) := 'My Trigger Name';
vMsg VARCHAR2(30) := 'Failure Message';
BEGIN
IF vCustId IS NULL THEN RETURN; END IF;
EXECDELETEFROMCACHEBYCUSTIDJOB(vCustId, vTrg, vMsg);
COMMIT WRITE BATCH NOWAIT;
EXCEPTION
WHEN OTHERS THEN
LOGMESSAGE(vTrg, NULL, vMsg, SQLERRM, NULL);
NULL;
END ;
/
And the package code being executed:
..procedure inside package..
PROCEDURE execDeleteFromCacheByCustIdJob (
pCustId IN NUMBER,
pTrgNm IN VARCHAR2,
pMsg IN VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('JOB_NAME', 1, TO_CHAR(pCustId));
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('JOB_NAME', 2, pKeyPrefix);
DBMS_SCHEDULER.RUN_JOB('JOB_NAME', TRUE);
--DBMS_SCHEDULER.ENABLE('JOB_NAME'); /* commented out to use run_job instead */
EXCEPTION
WHEN OTHERS THEN
NULL;
END execDeleteFromCacheByCustIdJob;