Oracle 18c - CANCEL SQL

 

Hello,
Dans un article précédent, nous avions vu comment gagner un peu de temps & installer la fameuse 18c sans être obligé d'avoir un accès Cloud ou un exadata au fond du garage. (http://www.lami-dba.com/2018/03/oracle-18c.html)
Aujourd'hui, je vais vous parler d'une nouveauté dans cette versoin... le genre de nouveauté pas forcement des plus exceptionnelles, mais le genre de chose que j'aurai pu apprécier dans certaines cas.
Typiquement, il m'est arrivé d'être appelé car un client avait un script qui tournait et qui bloquait sur une requête (peu importe la raison)... Et très souvent cela finissait par un kill(C'est le Kill qui a inspiré le choix de la photo - toujours de moi bien sur ;)) de la session, une modification de script pour commenter la requête en question.. puis on relance...
A partir de la 18c, plus besoin de "killer" la sessoin. Il 'est tout à fait possible d'annuler la requête en cours tout en permettant à la session de continuer son boulot.
La preuve par l'exemple !
Pour mon test, j'ai besoin d'une table (je suis allé au plus simple).

SQL> select * from T;

         I
----------
         5
         3
         5
         5

SQL>

Une première session qui effectue un update sur la table, mais sans commit... (Mais si cela peut arriver).

SQL>
SQL>  update t set i=4 where i=5;

3 rows updated.

SQL>

Et enfin un script basique qui va effectuer également un update, et normalement suivi d'un select.
script r1.sql

[oracle@rasta taz]$
[oracle@rasta taz]$ cat r1.sql
update  t set i=3 where i=5;
commit;
spool /tmp/result.log
select count(*) from dba_tables;
spool off
[oracle@rasta taz]$

j'avais prévenu, ca ne vole pas très haut...
Je me connecte maintenant avec mon user LAO (ca rappelle des souvenirs ;)) et je lance mon script.

SQL>
SQL>
SQL> @r1.sql

Et j'attends... Je ne vous cache pas que je risque d'attendre longtemps...
Ma patience ayant des limites, j'ouvre une nouvelle session afin de tenter de comprendre..

Le constat est relativement simple ma première session qui effectue l'update sans avoir commité bloque le déroulement de mon script.
A partir de la soit on "kill" la session bloquante afin que le script continue son bout de chemin.. Mais si on fait cela, l'article perd de son interêt; On va donc partir du principe que l'objectif est que le script est prioritaire et que la requête d'update du script pourra être passé plus tard. Le tout sans "killer" la première session dont on ne sait identifier d'un point de vu metier quels seraient les impacts d'un kill...

Let's go !

SQL>
SQL>
SQL> Alter system cancel sql '82,37627,ahwuywsspfq8c';

System altered.

SQL> 

On retourne vite fait sur la session qui était bloquée.

SQL>
SQL>
SQL> @r1.sql
update  t set i=3 where i=5
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Commit complete.


  COUNT(*)
----------
      1701

SQL>

Nous avons bien l'information que c'est uniquement l'opération courrante qui a été suspendu. Mon script a ensuite continué à dérouler, et ma session existe toujours.

Remarque 1 : Afin d'éviter toute confusion j'avais bien pris soin de passer en paramètre du "alter system cancel sql" le sql_id concerné. Ca serait dommage qu'entre temps la session bloquante ai fini son travail et que moi par excès de confiance, je ne stoppe pas le bon traitement.

Remarque 2: Dans le cas d'une architecture "Real Application Cluster", j'aurai pu également passer en paramètre le numéro d'instance. (cf. exemple ci-dessous).

SQL>
SQL> Alter system cancel sql '82,37627,@2,ahwuywsspfq8c';

System altered.

SQL> 

Enjoy