J’étais parti pour expliquer les principes d’utilisation de « Object Continuous Notification (OCN) » et « Query Result Continuous Notification (QRCN) » dans le contexte des caches niveau 2 des serveurs d’applications. Seulement, ça ne marche juste pas ! J’y reviendrai donc avec un exemple basé sur Streams. Alors à quoi servent ces technologies qui permettent, en quelques lignes, de s’abonner aux changements sur une table sur le résultat d’une requête ? « polling »?
Si vous voyez mis en oeuvre du « polling » pour notifier de manière très rapide un système ou un utilisateur, il y a de grandes chances que ce ne soit pas la meilleure approche. Les approches publish/subscribe sont souvent bien plus adaptées. « Continuous Query Notification (CQN) » et ses 2 variantes (OCN et QRCN) » permettent d’adapter très rapidement un algorithme de polling en publish/subscribe sans même que personne ne sache vraiment qu’il s’agisse de publish/subscribe. Alors ça vaut bien l’intérêt d’y passer quelques minutes !
Dans cet article vous trouverez 2 exemples de ces techniques ; pour tout savoir en détail, reportez-vous aux documents suivants :
- « Oracle® Database Advanced Application Developer’s Guide – 11g Release 2 (11.2) – 11 Using Continuous Query Notification (CQN)«
- Oracle® Database PL/SQL Packages and Types Reference – 11g Release 2 (11.2) – 36 DBMS_CQ_NOTIFICATION
Introduction
Il existe 2 types de handlers capables de gérer des notifications, le plus commun est développé en PL/SQL s’intègre sur le serveur ; le second s’appuie sur les OCI et est déclenché sur le client. Cet article illustre le premier cas écrit en PL/SQL.
Il est ensuite possible de s’abonner à tous les changements qui concernent une table ou, si vous avez besoin de fonctionnalités plus avancées, de vous abonner à tous les changements qui impactent (probablement) le résultat d’une requête. Vous trouverez ci-dessous les 2 cas appelés respectivement « Object Change Notification (OCN) » et « Query Result Change Notification (QRCN) ». Pour cela, nous allons créer une schéma d’exemple appelé DEMO.
Schéma exemple
Le script ci-dessous crée un utilisateur et une table pour démontrer la fonctionnalité :
create user demo
identified by demo
default tablespace users
temporary tablespace temp;
grant connect, resource, dba to demo;
grant execute on DBMS_CQ_NOTIFICATION to demo;
grant change notification to demo;
connect demo/demo
create table mytab
(id number,
attr1 number,
attr2 number,
val1 number,
val2 number);
insert into mytab
(select rownum, mod(rownum,11), mod(rownum,17), mod(rownum,5), mod(rownum,7)
from dual
connect by level <=10000);
commit;
Pour nos tests, nous définirons un handler pour suivre les modifications de la table mytab; nous nous abonnerons ensuite au changements qui impactent la requête suivante :
select id, attr1, attr2, val1, val2
from mytab
where attr1=7
and attr2=13;
ID ATTR1 ATTR2 VAL1 VAL2
---------- ---------- ---------- ---------- ----------
183 7 13 3 1
370 7 13 0 6
557 7 13 2 4
744 7 13 4 2
931 7 13 1 0
1118 7 13 3 5
[...]
9346 7 13 1 1
9533 7 13 3 6
9720 7 13 0 4
9907 7 13 2 2
53 rows selected.
PL/SQL Notification Handler
Pour créer un handler, il faut implémenter une procédure dont la spécification est la suivante :
PROCEDURE schema_name.proc_name(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)
Dans un cas réel, vous implémenterez un appel à votre applications via une API REST ou JMS, par exemple. Pour cette démonstration, nous allons simplement créer une table qlogs qui stockera quelques informations associées aux modifications et implementer la procédure qui stocke les informations associées au changement dans cette table.
create table qlogs
(rid number,
txid raw(8),
event_type number,
table_modified varchar2(4000),
info varchar2(4000));
create or replace procedure cqn_log(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR) is
tname varchar2(4000);
nrowid varchar2(4000);
numtables number;
numrows number;
begin
numtables := ntfnds.numtables;
for i in 1..numtables loop
tname:= ntfnds.TABLE_DESC_ARRAY(i).table_name ||', ' || tname;
numrows := ntfnds.TABLE_DESC_ARRAY(i).numrows;
for j in 1..numrows loop
nrowid:='OPS: ' ||ntfnds.TABLE_DESC_ARRAY(i).ROW_DESC_ARRAY(j).opflags
||' and ROWID: '||ntfnds.TABLE_DESC_ARRAY(i).ROW_DESC_ARRAY(j).row_id
|| ' - '|| nrowid;
end loop;
end loop;
insert into qlogs values
(ntfnds.REGISTRATION_ID, ntfnds.TRANSACTION_ID, ntfnds.event_type, tname, nrowid);
end;
/
Note:
CQN Utilise des jobs pour envoyer les changements de manière asynchrone et éviter d’impacter au maximum les ordres LMD. Pour cette raison, vérifiez que le paramètrejob_queue_processesest supérieur à 2
Enregistrer et tester le handler OCN
Enregistrez ensuite le handler. Il suffit de démarrer l’enregistrement avec DBMS_CQ_NOTIFICATION.NEW_REG_START et les bonnes options; toutes les requêtes qui suivent enregistreront les tables associées. Voici par exemple comment enregistrer MYTAB (et non, la clause where n’est pas prise en compte comme vous allez le découvrir !) :
DECLARE
v_cn_addr CQ_NOTIFICATION$_REG_INFO;
v_cursor SYS_REFCURSOR;
v_reg number;
BEGIN
-- Create object:
v_cn_addr := CQ_NOTIFICATION$_REG_INFO (
'demo.cqn_log', -- PL/SQL notification handler
DBMS_CQ_NOTIFICATION.QOS_RELIABLE -- reliable
+ DBMS_CQ_NOTIFICATION.QOS_ROWIDS, -- include rowids of changed objects
0, -- registration persists until unregistered
0, -- notify on all operations
0 -- notify immediately
);
v_reg:=DBMS_CQ_NOTIFICATION.NEW_REG_START(v_cn_addr);
DBMS_CQ_NOTIFICATION.ENABLE_REG(v_reg);
OPEN v_cursor FOR
-- Run query to be registered
select id, attr1, attr2, val1, val2
from mytab
where attr1=7
and attr2=13; -- register this query
CLOSE v_cursor;
-- Close registration
DBMS_CQ_NOTIFICATION.REG_END;
END;
/
Vous pouvez vérifier la configuration avec les requêtes qui suivent :
col CALLBACK format a25
col REGFLAGS format 999
col TABLE_NAME format a10
col REGID format 99999
select REGID, callback, table_name, regflags
from DBA_CHANGE_NOTIFICATION_REGS;
REGID CALLBACK TABLE_NAME REGFLAGS
------ ------------------------- ---------- --------
2 plsql://demo.cqn_log?PR=0 DEMO.MYTAB 12
set long 1000
set longchunksize 1000
col QUERYTEXT format a50 wor wra
col username format a8
set lines 120
set tab off
select QUERYID, QUERYTEXT, REGID, USERNAME
from DBA_CQ_NOTIFICATION_QUERIES
/
no rows selected
Pour tester le fonctionnement du handler, on peut simplement exécuter l’ordre LMD de votre choix comme ci-dessous :
update mytab set val1=9999 where id=1;
commit;
Vous retrouverez ensuite les informations collectées dans le handlers en interrogeant QLOGS :
col rid format 999
col event_type format 999
col table_modified format a12
col info format a40
select * from qlogs;
RID TXID EVENT_TYPE TABLE_MODIFI INFO
---- ---------------- ---------- ------------ ----------------------------------------
2 0400130088020000 6 DEMO.MYTAB, OPS: 4 and ROWID: AAASVpAAEAAAACvAAA -
select *
from mytab
where rowid='AAASVpAAEAAAACvAAA';
ID ATTR1 ATTR2 VAL1 VAL2
---------- ---------- ---------- ---------- ----------
1 1 1 9999 1
Autres Considérations
Avant d’illustrer l’autre manière de mettre en oeuvre CQN, voici 3 considérations que vous voudrez regarder.
- Diagnostique des problèmes
Si vous devez corriger les problèmes liés aux notifications, regardez les statuts des fichiers logs et traces des JOB qui déclenchent effectivement les processus associées.
- Utilisation conjointe avec Streams
CQN détecte tout les changements, y compris ceux qui sont générés par les framework Streams et Goldengate ce qui est important dans nombre d’architectures
- Impacts sur les performances
Il y a un l’impact sur les performances. Voici par exemple 2 chargements avec et sans CQN, certe sur un petit serveur. Pensez à ce risque dans votre contexte :
set timing on
begin
for i in 1..10000 loop
update mytab set val1=i where id=1;
commit;
end loop;
end;
/
Elapsed: 00:00:15.12
exec DBMS_CQ_NOTIFICATION.DEREGISTER (2);
set timing on
begin
for i in 1..10000 loop
update mytab set val1=i where id=1;
commit;
end loop;
end;
/
Elapsed: 00:00:03.54
Enregistrer et tester le handler QRCN
On va maintenant utiliser un handler qui détecte un changement sur le résultat d’une requête plutôt que sur un objet. Il faut pour cela que le paramètre compatible soit supérieur à 11.1 ; vous allez également changer le contenu de la procédure PL/SQL puisque dans le cas de QRCN, le champ TABLE_DESC_ARRAY est NULL et celui QUERY_DESC_ARRAY est rempli.
create or replace procedure cqn_log(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR) is
tname varchar2(4000);
begin
if ntfnds.QUERY_DESC_ARRAY is not NULL then
for i in 1..ntfnds.QUERY_DESC_ARRAY.count loop
tname:= 'QUERY : '||to_char(ntfnds.QUERY_DESC_ARRAY(i).queryid) ||', ' || tname;
end loop;
end if;
insert into qlogs values
(ntfnds.REGISTRATION_ID, ntfnds.TRANSACTION_ID, ntfnds.event_type, tname, null);
end;
/
La procédure pour enregistrer un handler QRCN est strictement identique à la précédente ; seul change le paramètre QOSFLAGS de l’objet CQ_NOTIFICATION$_REG_INFO qui doit contenir DBMS_CQ_NOTIFICATION.QOS_QUERY :
DECLARE
v_cn_addr CQ_NOTIFICATION$_REG_INFO;
v_cursor SYS_REFCURSOR;
v_reg number;
BEGIN
-- Create object:
v_cn_addr := CQ_NOTIFICATION$_REG_INFO (
'demo.cqn_log', -- PL/SQL notification handler
DBMS_CQ_NOTIFICATION.QOS_QUERY -- register queries instead of OCN
+ DBMS_CQ_NOTIFICATION.QOS_ROWIDS, -- include rowids of changed objects
0, -- registration persists until unregistered
0, -- notify on all operations
0 -- notify immediately
);
v_reg:=DBMS_CQ_NOTIFICATION.NEW_REG_START(v_cn_addr);
DBMS_CQ_NOTIFICATION.ENABLE_REG(v_reg);
OPEN v_cursor FOR
-- Run query to be registered
select id, attr1, attr2, val1, val2
from mytab
where attr1=7
and attr2=13; -- register this query
CLOSE v_cursor;
-- Close registration
DBMS_CQ_NOTIFICATION.REG_END;
END;
/
Cette fois-ci la requête est visible dans les vues de paramétrage de CQN :
col CALLBACK format a25
col REGFLAGS format 999
col TABLE_NAME format a10
col REGID format 99999
select REGID, callback, table_name, regflags
from DBA_CHANGE_NOTIFICATION_REGS;
REGID CALLBACK TABLE_NAME REGFLAGS
------ ------------------------- ---------- --------
3 plsql://demo.cqn_log?PR=0 DEMO.MYTAB 12
set long 1000
set longchunksize 1000
col QUERYTEXT format a50 wor wra
col username format a8
set lines 120
set tab off
select QUERYID, QUERYTEXT, REGID, USERNAME
from DBA_CQ_NOTIFICATION_QUERIES
/
QUERYID QUERYTEXT REGID USERNAME
---------- -------------------------------------------------- ------ --------
2 SELECT DEMO.MYTAB.ID , DEMO.MYTAB.ATTR1 , DEMO.MY 3 DEMO
TAB.ATTR2 , DEMO.MYTAB.VAL1 , DEMO.MYTAB.VAL2 FRO
M DEMO.MYTAB WHERE ( DEMO.MYTAB.ATTR1 = 7 AND
DEMO.MYTAB.ATTR2 = 13 )
Pour tester le fonctionnement du handler on commencera par purger la table QLOGS:
delete from qlogs;
commit;
On peut ensuite vérifier en modifiant une ligne qui ne modifie pas le résultat de la requête que le handler n’est pas déclenché :
update mytab set val1=9999 where id=1;
commit;
Vous retrouverez ensuite que rien n’est stocké dans QLOGS :
col rid format 999
col event_type format 999
col table_modified format a12
col info format a40
select * from qlogs;
no rows selected
Dans le cas où on modifie une valeur correspondant au critère de la requête :
update mytab set val1=9998 where id=8785;
commit;
Le handler est effectivement déclenché :
col rid format 999
set lines 120
col event_type format 999
col table_modified format a12
col info format a40
select * from qlogs;
RID TXID EVENT_TYPE TABLE_MODIFI INFO
---- ---------------- ---------- ------------ -----
3 050004005D040000 7 QUERY : 2,
Conclusion
Supprimez le schéma :
exec DBMS_CQ_NOTIFICATION.DEREGISTER (3);
connect / as sysdba
drop user demo cascade;
Il y a quelques subtilités complémentaires à comprendre et notamment la notion de « best effort ». Toutefois, cet outil permet très rapidement de notifier une application ou de déclencher, par exemple, le processus d’une architecture orientée service… Gardez-le dans un coin de la tête
