Home » Developer & Programmer » Precompilers, OCI & OCCI » Database Delay
| Database Delay [message #94014] |
Mon, 25 August 2003 00:24 |
Martin Andersson
Messages: 1
Registered: August 2003
|
Junior Member |
|
|
2 clients and 1 DBMS server
Clients are using OCI 8.1.7
This is what happens:
1. Client 1 (CL01) connects to database.
2. Client 2 (CL02) connects to database.
3. UPDATE of table CL02.testtable started from CL02. UPDATE is made in a PL/SQL loop. See below.
4. UPDATE of table CL01.testtable started from SERVER. UPDATE is made in a PL/SQL loop (same but different table).
5. CL01 and CL02 receives notifications using Oracle AQ (printing some message on screen every second). The subscription is set up using a trigger, a stored procedure and a non-persistent single consumer queue. The objects are created separately in CL01 and CL02's SCHEMAS. The client programs register for notification using OCI code. See below.
6. I disconnect CL01 (unplugging the network cable). CL01 (of course) doesn't receive any further notifications.
7. MY PROBLEM: AFTER APPROX. 23 SECONDS THE NOTIFICATIONS STOP TO ARRIVE ON CL02 (NO NETWORK TRAFFIC). WHY??? HOW TO AVOID THIS???
8. After additionaly 50 seconds 50 notifications (update every second) arrive at the same time on CL02.
9. Notifications arrive as normal again on CL02.
* A normal shutdown of CL01 in step 6 above, will not cause a delay.
* Trigger and procedure works fine during notification stop. Tested with a timestamp write into a table every second.
* During the stop there is no network traffic at all. Network is scanned.
* Problem tested on Oracle 9i with faster computer, then the 50 seconds delay is always 23 seconds.
* Activities observed during the delay: High delay times for log file sync, log file parallell write, control file parallell write and control file sequential read.
PL/SQL loop:
DECLARE
Cnt NUMBER(5);
BEGIN
Cnt := 120;
WHILE (Cnt >= 1) LOOP
DBMS_LOCK.SLEEP(1);
UPDATE CL02.testtable
SET value=Cnt;
commit;
Cnt := Cnt - 1;
END LOOP;
END;
/
AQ Objects:
CREATE OR REPLACE TRIGGER CL02.aqtrigger
AFTER UPDATE ON CL02.testtable
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
CL02.ENQPROC('CL02.thequeue');
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE CL02.ENQPROC(queue varchar2)
AS
enq_opt dbms_aq.enqueue_options_t;
enq_msgid raw(16);
msg_prop dbms_aq.message_properties_t;
payload raw(10);
BEGIN
enq_opt.visibility:=dbms_aq.IMMEDIATE;
DBMS_AQ.ENQUEUE(queue, enq_opt, msg_prop, payload, enq_msgid);
END;
/
BEGIN
DBMS_AQADM.CREATE_NP_QUEUE(QUEUE_NAME => 'CL02.thequeue',
MULTIPLE_CONSUMERS => FALSE);
DBMS_AQADM.START_QUEUE('CL02.thequeue');
END;
/
OCI Code:
used parameters of interest:
char Subscription[[256]]="CL02.subscriber";
// Allocate subscription handle
OCIHandleAlloc((dvoid*) h->envhp, (dvoid**) &h->subhp,
OCI_HTYPE_SUBSCRIPTION, 0, 0);
// Set subscription name in handle
OCIAttrSet((dvoid*) h->subhp, OCI_HTYPE_SUBSCRIPTION,
(dvoid*) Subscription,
(ub4) strlen((char*) Subscription),
OCI_ATTR_SUBSCR_NAME, h->errhp);
// Set callback function in handle
OCIAttrSet((dvoid*) h->subhp, OCI_HTYPE_SUBSCRIPTION,
(dvoid*) func, 0, OCI_ATTR_SUBSCR_CALLBACK,
h->errhp);
// Set context of callback function
OCIAttrSet((dvoid*) h->subhp, OCI_HTYPE_SUBSCRIPTION,
pTBL, 0, OCI_ATTR_SUBSCR_CTX, h->errhp);
// Set namespace in handle
OCIAttrSet((dvoid*) h->subhp, OCI_HTYPE_SUBSCRIPTION,
(dvoid*) &NameSpc, 0, OCI_ATTR_SUBSCR_NAMESPACE,
h->errhp);
// Register the subscriptions
OCISubscriptionRegister(h->svchp, &h->subhp, (ub2) 1,
h->errhp, OCI_DEFAULT);
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 02:44:07 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
