Download
1 / 202
E N D
1. Serveur de données Oracle10g Les propriétés « acides» de la Norme SQL/ANSI
Objectif: pourquoi l’architecture du serveur Oracle est-elle ainsi conçue?
2. Base Relationnelle Un serveur de données relationnel :
Rend compte de la « réalité » sous forme de table
Met en relation la perception logique et l’implémentation physique de la modélisation (d’où le qualificatif de Relationnel)
Rien n’est particularisé (pas de pointeurs à câbler)
L’ordre est sans importance pour stocker des données
Répond aux spécifications de l’Algèbre Relationnelle élaborée par l’anglais Edgar Codd
Opérateurs UNION, INTERCEPT, projection (SELECT), restriction (WHERE), produit cartésien (JOINT), prédicats complexes,..
Implémente un langage structuré de requête
SQL avec des schémas, clause FROM, possibilité de structurer les requêtes (les sous requêtes), effectuer des jointures et des produits cartésiens, etc.
Implémentation des propriétés d’acidité (ACID)
3. Gestion des transactions et l’acidité Les transactions d’une base de données relationnelle sont gouvernées par le principe d‘acidité.
L’acidité est une exigence de la norme SQL.
Un standard n’est pas une norme
Concerne les serveurs d’applications (J2EE, SAP,..)
Elle permet, à la façon d’une théorie, de rendre compte fidèlement de ce que l’on observe et de pouvoir effectuer des « prédictions » relatives à l’intégrité et la cohérence des données. En particulier, elle exige qu’il n’y ait aucune interférence entre les transactions.
ACID
Atomicité
Consistance
Isolation
Durabilité
4. Définition des propriétés ACID Atomicité
Les modifications sont préservées en totalité ou complètement annulées (COMMIT, ROLLBACK ou points d'arrêt appelés SAVEPOINT)
Consistance
La Base passe d'un état cohérent à un état cohérent.
Isolation
Si une autre transaction s'effectue en concurrence, elle semble s'être déroulée avant ou après (un peu comme si l'on était seul à travailler sur la base)
Il existe plusieurs niveaux d'isolation
Durabilité (Persistance)
Les modifications d'état sont permanentes. Elles sont conservées en cas d'incident (CRASH RECOVERY et MEDIA RECOVERY).
5. Interactions entre les composants constitutifs du serveur de données Oracle10g
6. Multi versions et multi utilisateurs En concurrence d’accès à la base:
Une lecture ne doit pas empêcher une écriture
Une lecture ne doit pas empêcher une lecture
Une écriture ne doit pas empêcher une lecture
Une écriture ne doit pas empêcher une écriture
Une écriture bloque une écriture qui porte sur la sur la même ligne
Dans ce cas, la base est réputée OLTP
On Line Transactional Processing (Haut débit transactionnel)
7. Propriétés ACID Requêtes non bloquantes
8. Gestion des transactions et l’acidité Les transactions d’une base de données relationnelle sont gouvernées par le principe d‘acidité.
L’acidité est une exigence de la norme SQL92.
Idem pour le EJB (J2EE) et les serveurs d’applications (SAP,..)
Elle permet, à la façon d’une théorie, de rendre compte fidèlement de ce que l’on observe et de pouvoir effectuer des « prédictions » relatives à l’intégrité et la cohérence des données. En particulier, elle exige qu’il n’y ait aucune interférence entre les transactions.
Atomicité
Consistance
Isolation
Durabilité
Comprendre pourquoi le serveur Oracle est ainsi fait
9. Rapide définition des propriétés ACID Atomicité
Les modifications sont préservées en totalité ou complètement annulées (COMMIT, ROLLBACK ou points d'arrêt appelés SAVEPOINT)
Consistance
La Base passe d'un état cohérent à un état cohérent.
Isolation
Si une autre transaction s'effectue en concurrence, elle semble s'être déroulée avant ou après (un peu comme si l'on était seul à travailler sur la base)
Il existe plusieurs niveaux d'isolation
Durabilité (Persistance)
Les modifications d'état sont permanentes. Elles sont conservées en cas d'incident (CRASH RECOVERY et MEDIA RECOVERY).
10. Requêtes non bloquantes Cohérence en lecture
Ne voit jamais des données non validées ou “dirty read”
Ne voit pas les modifications en cours apportées par une autre transaction
Résultat: vous obtenez la bonne réponse
Ne bloque pas les mises à jour
Ne peut être bloquée par une autre mise à jour
11. Requêtes non bloquantes Supposons que l’on ait besoin de faire l’addition du solde de tous comptes…
12. Requêtes non bloquantes Supposons que l’on ait besoin de faire l’addition du solde de tous comptes…
13. Requêtes non bloquantes Supposons que l’on ait besoin de faire l’addition du solde de tous comptes…
14. Requêtes non bloquantes Maintenant exécutons une transaction au même moment pour transférer la somme de €50 d’un compte vers un autre
Sans utiliser des requêtes non bloquante
Sans faire appel à des verrous en lecture
15. Requêtes non bloquantes Maintenant exécutons une transaction au même moment pour transférer la somme de €50 d’un compte vers un autre
Sans utiliser des requêtes non bloquante
Sans faire appel à des verrous en lecture
16. Requêtes non bloquantes Maintenant exécutons une transaction au même moment pour transférer la somme de €50 d’un compte vers un autre
Sans utiliser des requêtes non bloquante
Sans faire appel à des verrous en lecture
17. Requêtes non bloquantes Maintenant exécutons une transaction au même moment pour transférer la somme de €50 d’un compte vers un autre
Sans utiliser des requêtes non bloquante
Sans faire appel à des verrous en lecture
18. Requêtes non bloquantes Essayons maintenant avec des verrous posés en lecture
19. Requêtes non bloquantes Essayons maintenant avec des verrous posés en lecture
20. Requêtes non bloquantes Essayons maintenant avec des verrous posés en lecture
21. Requêtes non bloquantes Essayons maintenant avec des verrous posés en lecture
On obtient la bonne réponse mais les transactions en mise à jour sont bloquées
De même, les mises à jour peuvent bloquer les requêtes
22. Requêtes non bloquantes Essayons des requêtes non bloquantes avec un mécanisme de lecture cohérente
23. Requêtes non bloquantes Essayons des requêtes non bloquantes avec un mécanisme de lecture cohérente
24. Requêtes non bloquantes Essayons des requêtes non bloquantes avec un mécanisme de lecture cohérente
25. Requêtes non bloquantes Essayons des requêtes non bloquantes avec un mécanisme de lecture cohérente
Les lectures ne bloquent pas les mises à jour
Les mises à jour ne bloquent pas les lectures
26. Cohérence en lecture Les lignes contenues dans un bloc de données
27. Cohérence en lecture Donc, celui qui écrit ne bloque jamais celui qui lit ou bien celui qui lit ne bloquent jamais celui qui écrit.
Les données sont estampillées par un SCN, ou System Change Number
Les lignes sont contenues dans des blocs. Les lignes et les blocs possèdent des octets d’en-tête avec des méta-données
Le SCN garantit l’intégrité de la base
Fournit toujours un résultat conforme et cohérent
Pas d’escalade des verrous
Le verrouillage est une propriété, pas une ressource
La granularité du verrouillage se situe au niveau ligne
Le verrouillage est le moins restrictif possible
28. Définition SCN est un nombre qui peut définir une version enregistrée (COMMIT) de la base à un moment précis. Quand on valide une transaction, Oracle lui attribue un nombre unique, SCN, qui identifiera cette transaction.
SCN est une sorte d'horloge logique d‘Oracle à ne pas confondre avec l'horloge système. Il est unique et s'accroît dans le temps mais pas séquentiellement et il ne prend jamais la valeur 0 tant que la base n'est pas recréée (réincarnée).
Oracle effectue une restauration uniquement par rapport au SCN. Par contre vous pouvez choisir une autre méthode : par SCN, par HORODATE, ou par fichier de contrôle.
Pour un commit de 16 transactions par seconde, il faudrait 500 ans pour dépasser le SCN autorisé dans oracle. SCN désigne bien SYSTEM CHANGE NUMBER et non SYSTEM COMMIT NUMBER, il suffit de voir dans les vues V$ on utilise la colonne CHANGE# pour désigner le SCN.
29. Structure d’une ligne L’en-tête de la ligne contient des informations sur, au moins, trois octets :
Nombre de colonnes
Chaînage éventuel
Verrou
La taille de la ligne, trois octets minimum, contient également des méta-données et les données proprement dit.
30. Structure d’une colonne Chaque colonne est stockée avec un en-tête (longueur de la colonne) dont la taille varie de 1 à 3 octets.
La longueur totale d’une colonne dépend du type de la colonne et du contenu stockée effectivement dans la colonne.
CHAR(n) longueur fixe quelque soit la valeur
VARCHAR2(n) longueur variable (0 à n)
NUMBER(n,p) longueur variable (1 à 21 octets)
NULL 1 octet en milieu de ligne et aucun en fin de ligne
DATE longueur fixe de 8 octets (en vérité, c’est un objet)
31. Structure d’une colonne Les fonctions SQL VSIZE et DUMP permettent de connaître respectivement, en octets, la taille interne et la représentation interne d’une valeur:
SQL> SELECT VSIZE(ename),DUMP(ename),ename
FROM emp;
VSIZE(ENAME) DUMP(ENAME) ENAME
------------ --------------------------- ------
5 Typ=1 Len=5: 83,77,73,84,72 SMITH
32. Organisation du stockage dans les blocs
33. System Change Number
34. Consistance en lecture (explication) Le SCN joue un rôle important pendant la lecture des blocs Oracle.
Au début, un SCN est attribué à la requête (SCN1), après elle lit le SCN de la dernière modification dans le bloc (SCN2 ), si SCN2est supérieur à SCN1, cela signifie que le bloc à été modifié après le démarrage de la requête.
Dans ce cas, Oracle cherche une ancienne version du bloc dans les segments d’annulation (UNDO) ou dans les segments ROLL BACK.
35. La pseudo-colonne ORA_ROWSCN d’Oracle10g Une nouvelle pseudo-colonne, ORA_ROWSCN, contient System Change Number (SCN) de la dernière transaction qui a modifiée un enregistrement.
On ne peut pas l'utiliser pour les vues!
SCOTT> SELECT ORA_ROWSCN,empno FROM emp;
ORA_ROWSCN EMPNO
---------- ----------
848579 7369
848579 7566
848579 7782
848579 7788
…
36. SCN_TO_TIMESTAMP Une fonction très pratique vous permet de retrouver la DATE de la dernière modification d'une ligne, appelée :
SCN_TO_TIMESTAMP:
SQL> SELECT scn_to_timestamp(ora_rowscn)
FROM scott.emp;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
-------------------------------
16-MAR-06 11.53.39.000000000 AM
16-MAR-06 11.53.39.000000000 AM
..
37. Transaction Atomicité
38. Transactions Un traitement dans la base s'effectue dans le contexte d'une transaction, c’est-à-dire une série d'ordres qui représentent une unité LOGIQUE de travail.
39. Haut niveau de simultanéité transactionnelle Une transaction est associée à une variété de verrous (c’est-à-dire le contexte)
Le verrouillage doit être le moins restrictif possible
Il s'effectue par défaut au niveau de la ligne
Indispensqable au Haut débit transactionnelle
40. Mécanisme de verrouillage Gestion automatique du verrouillage par le serveur de données
SQL> CONNECT / AS SYSDBA
SQL> SHOW PARAMETER ROW_LOCKING
NAME TYPE VALUE
----------------------------------
row_locking string always
ALWAYS (verrouillage RECORD = LIGNE)
INTENT (verrouillage TABLE)
41. Création d’un second utilisateur de test pour les exercices d’accès en concurrence BETTY/BOOPS
42. BETTY/BOOPS Création d’un compte Oracle pour simuler les accès en concurrence.
Premiers principes de base de gestion des comptes Base de données & introduction à la sécurité des données
Autres fonctionnalités: FINE GRAINE, clause AUTHID, Rôles, authentification Oracle Internet Directory (OID) LDAP, emprunter un mot de passe, Access Control List (ACL), encryptage des données, accès Single Sign On (SSO) et Oracle10g Identity Management, PKI, indentification externe des comptes (OPS$), fichiers de mots de passes, SSL, etc.
43. Création de l’utilisateur BETTY/BOOPS CONNECT SYS/dba247 AS SYSDBA
GRANT CONNECT,RESOURCE
TO BETTY IDENTIFIED BY BOOPS;
ALTER USER BETTY DEFAULT TABLESPACE USERS;
ALTER USER BETTY TEMPORARY TABLESPACE TEMP;
GRANT CREATE SYNONYM TO betty;
CONNECT SCOTT/TIGER
GRANT ALL ON EMP TO BETTY;
GRANT ALL ON DEPT TO BETTY;
CONNECT BETTY/BOOPS
CREATE SYNONYM EMP FOR SCOTT.EMP;
CREATE SYNONYM DEPT FOR SCOTT.DEPT;
44. Fichiers de lancement des sessions Pour lancer rapidement et sans effort des sessions sous SCOTT, BETTY ou SYS, créez chaque fois un fichier avec les variables d’environnement appropriées:
SCOTT.BAT
SET ORACLE_HOME=d:\oracle\10.2.0\db
SET ORACLE_SID=ORCL
SET NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
%ORACLE_HOME%\bin\sqlplus scott/tiger
45. Verrouillage
46. Mécanisme de verrouillage C’est le mode de gestion par défaut
Verrous au niveau ligne pour les transactions LMD
Aucun verrou pour les SELECT sauf indication contraire
Niveaux variables de cohérence des données
L’utilisateur visualise une image statique des données, même en cours de modification par un autre utilisateur
Modes de verrouillage de type EXCLUSIVE et SHARE
Verrous externes maintenus jusqu'à la validation ou l'annulation des opérations (COMMIT ou ROLLBACK)
Nettoyage par le processus PMON
47. Mécanisme de mise en file d’attente Si trois utilisateurs mettent à jour une ligne, chacun obtiendra un verrou de type SHARE, mais seul le premier d’entre eux obtiendra un verrou sur ligne.
Un mécanisme de file d’attente est mis en place pour suivre:
Les utilisateurs en attente de verrous
Le mode verrouillage demandé
L’ordre dans lequel ils ont réclamé un verrou
Les paramètres DML_LOCKS et ENQUEUE_RESOURCES permettent d’accroître le nombre total de verrous disponibles (peut être nécessaire dans un environnement Cluster RAC)
48. Gestion des transactions (Atelier)
Simultanéité d’accès aux données
Cohérence des données
Durée
Isolation
Ouvrir deux sessions SQL en mode console
WINNT> SET ORACLE_SID=ORCL
WINNT> SET ORACLE_HOME=c:\oracle\ora92
WINNT> %ORACLE_HOME%\bin\sqlplus /nolog
49. Simultanéité d’accès aux données SQL> UPDATE emp
2 SET sal=sal*1.1
3 WHERE empno=7839;
1 row updated.
SQL> UPDATE emp
2 SET sal=sal+100;
14 row updated. SQL> UPDATE emp
2 SET sal=sal*1.1
3 WHERE empno=7900;
1 row updated.
SQL> SELECT sal
2 FROM emp
3 WHERE empno=7788;
SAL
----------
3000
50. Simultanéité Une transaction LMD obtient au moins deux verrous :
Un verrou de type SHARE sur la table, encore appelé verrou TM
La transaction acquière un verrou de type EXCLUSIVE, appelé TX, sur les lignes qu’elle modifie.
Chaque ligne obtient un octet de verrouillage placé en en-tête de ligne (Interested Transaction List) que la transaction utilise.
51. Cohérence des données SCOTT> UPDATE emp
SET sal = sal *1.1;
14 rows updated.
SCOTT> SELECT empno,sal
FROM emp;
EMPNO SAL
---------- ----------
7369 880
7499 1760
7521 1375
7566 3272.5
…/…
BETTY> SELECT empno,sal
FROM emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
…/…
52. Durée SCOTT>UPDATE emp
SET sal=sal*1.1
WHERE empno=7900;
1 row updated.
SCOTT> COMMIT;
BETTY> UPDATE emp
SET sal=sal+100;
Le verrou reste en place jusqu’à la fin de la transaction.
14 row updated.
53. Résolution manuelle de la contention
54. Résoudre des contentions par script L’accès en concurrence sur une ressource conduit à ce que l’on appelle la contention.
Activité en mode « jour » et en mode « nuit »
Une application OLTP et une base DSS utilisent une base dédiée pour éviter la contention
Fermeture des sessions
Demander à celui qui détient le verrou de valider ou d’annuler sa transaction
COMMIT ROLLBACK
En dernier recours, interrompre la session d’un utilisateur
ALTER SYSTEM KILL ..
55. Interrompre une session au moyen d’une commande SQL SQL> CONNECT / AS SYSDBA
SQL> SELECT sid, serial#, username
FROM V$SESSION
WHERE type='USER';
SID SERIAL# USERNAME
---------- ---------- ---------------------
9 354 SCOTT
20 125 SYS
SQL> ALTER SYSTEM KILL SESSION '9,354';
56. SID & SERIAL# NOTE: Les champs SID et SERIAL# sont également utilisés pour créer un événement (event) afin de tracer un aspect (ici, le code SQL) de l’activé de la base, par exemple:
BEGIN DBMS_SYSTEM.SET_EV('||sid||','||serial#||',10046,4,'''')
END;
/
57. Qu’est-ce qui provoque un blocage? Les développeurs ont utilisé un verrouillage inutilement élevé.
Des transactions sont inutilement longues.
Les ordres de validation ne sont pas émis au moment qui convient.
D’autres produits intégrés exigent un niveau de verrouillage plus élevés
etc..
58. Identifier la requête qui bloque (début) SQL> CREATE VIEW survey_lock AS
2 SELECT DISTINCT
3 xidusn,
4 l.object_id,d.object_name,
5 locked_mode,
6 l.session_id, s.serial#, s.username
7 ,SUBSTR(t.sql_text,1,50) SQL
8 FROM V$LOCKED_OBJECT l,
9 DBA_OBJECTS d,
10 V$SESSION s
11 ,v$sqltext t
12 WHERE d.object_id=l.object_id
13 AND l.session_id=s.sid
14 AND t.address=s.sql_address;
59. Identifier la requête qui bloque (fin) SQL> SET SERVEROUTPUT ON
SQL> EXEC print_table('SELECT * FROM SYS.SURVEY_LOCK');
------------------------------
PL/SQL procedure successfully completed.
SQL> EXEC print_table('SELECT * FROM SYS.SURVEY_LOCK');
------------------------------
XIDUSN : 4
OBJECT_ID : 54297
OBJECT_NAME : EMP
LOCKED_MODE : 3
SESSION_ID : 153
SERIAL# : 31
USERNAME : SCOTT
SQL : UPDATE EMP SET SAL=SAL*1.2
------------------------------
60. Exercice De gros batch sont lancés en parallèle. Le Pilote du projet vous demande de vérifier s’il n’y a pas de verrous mortels. Dans ce cas, les supprimer. Vous avez également la charge d’émettre une recommandation pour que cela n’arrive plus.
61. Surveillance l’activité de verrouillage Ouvrir 3 sessions, respectivement sous SCOTT, BETTY, et SYS
Depuis la session 1, augmentez le salaire de l’employé empno=7839 de 10%. NE VALIDER PAS!
Depuis la session 2, augmentez le salaire de l’employé empno=7839 de 10%.
Que se passe t-il?
Quelle table est impliquée?
62. Surveillance l’activité de verrouillage Les tables ont un identifiant (OBJECT_ID) d’objet dans la vue dynamique V$LOCKED_OBJECT:
SQL> SELECT xidusn,
object_id,
session_id,
locked_mode
FROM V$LOCKED_OBJECT;
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
4 30374 19 3
0 30374 9 3
La session 19 bloque la session 9
63. Quel objet est bloqué? Pour déterminer l’objet qui bloque, interrogez la vue DBA_OBJECTS
SQL> SELECT object_name
FROM dba_objects
WHERE object_id=30374;
OBJECT_NAME
-----------
EMP
64. Verrous mortels Les étreintes fatales
65. Les étreintes fatales Une étreinte fatale a lieu lorsque des utilisateurs se verrouillent mutuellement
Dans ce cas, les utilisateurs sont bloqués comme les automobilistes au carrefour
Oracle identifie et résout les verrous de ce type en annulant l’instruction qui les a détectés.
66. Suivi manuel d’un verrou mortel en cours CONNECT / AS SYSDBA
COL object_name FOR A10
SELECT DISTINCT
xidusn,
l.object_id,d.object_name,
locked_mode,
l.session_id, s.serial#, s.username
FROM V$LOCKED_OBJECT l,DBA_OBJECTS d,V$SESSION s
WHERE d.object_id=l.object_id
AND l.session_id=s.sid;
XIDUSN OBJECT_ID OBJECT_NAM LOCKED_MODE SESSION_ID SERIAL# USERNAME
---------- ---------- ---------- ----------- ---------- ---------- --------
0 31170 EMP 3 16 5 BETTY
3 31170 EMP 3 21 3 SCOTT
67. Situation d’étreinte fatale 1 SCOTT> UPDATE emp
SET sal=sal*1.1
WHERE empno=7839;
1 row updated.
3 SCOTT> UPDATE emp
SET sal=sal*1.1
WHERE empno=7369;
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
5> ROLLBACK; 2 BETTY> UPDATE emp
SET job='ANALYST'
WHERE empno=7369;
1 row updated.
4 BETTY> UPDATE emp
SET job='CEO'
WHERE empno=7839;
1 row updated.
68. Quelle requête SQL bloque? SQL> SET SERVEROUTPUT ON
SQL> EXEC print_table('SELECT * FROM SYS.SURVEY_LOCK');
------------------------------
XIDUSN : 7
OBJECT_ID : 54297
OBJECT_NAME : EMP
LOCKED_MODE : 3
SESSION_ID : 153
SERIAL# : 31
USERNAME : SCOTT
SQL : UPDATE emp SET job='CEO' ..
------------------------------
69. Traces produites …
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE emp SET sal = sal * 1.1 WHERE empno= :b1
----- PL/SQL Call Stack -----
object line object
handle number name
66E85040 6 procedure SCOTT.BATCH
66DD3538 1 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL.
…
SQL> SHOW PARAMETER DUMP
%ORACLE_HOME%\admin\ORCL\udump
70. « The following deadlock is not an ORACLE error» L’erreur est bien issue de l’application.
Conclusion: les traces doivent faire l’objet d’une surveillance régulière en exploitation. Par exemple:
Un shell invoqué depuis la crontab
Utilitaire de surveillance (Utilitaire ITO, etc..)
Manuellement..
71. Exercice: comment éviter les verrous mortels? Ouvrir deux sessions sous SCOTT
Ecrire une procédure PL appelée batch qui augmente de 10% le salaire d’un employé. Celui-ci est spécifié par son matricule empno passé en paramètre (p_empno) .
Cette procédure lève l’exception ORA-00060 (PRAGMA).
Si une étreinte fatale est détectée, un ROLLBACK aura lieu.
Commencez par noter le salaire des employés 7839 et 7369
Effectuez les gestes suivants:
a) Session 1: SQL> EXEC batch(7839);
b) Session 2: SQL> EXEC batch(7369);
c) Session 1: SQL> EXEC batch(7369);
d) Session 2: SQL> EXEC batch(7839);
L’étreinte, a-t-elle été résolue?
Quelle session a résolue le verrou mortel?
72. Détecter les verrous mortels .. CONNECT scott/tiger
CREATE OR REPLACE PROCEDURE batch (p_empno NUMBER)
IS
DEAD_LOCK EXCEPTION;
PRAGMA EXCEPTION_INIT(DEAD_LOCK,-00060);
BEGIN
DBMS_OUTPUT.ENABLE(32000);
UPDATE emp SET sal = sal * 1.1 WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE(p_empno || 'mis à jour');
EXCEPTION
WHEN DEAD_LOCK THEN
DBMS_OUTPUT.PUT_LINE('Etreinte résolue: '|| p_empno);
ROLLBACK;
END;
/
GRANT EXECUTE ON batch TO BETTY;
CREATE PUBLIC SYNONYM batch FOR scott.batch;
73. Notez les salaire des matricules 7839 et 7369 SQL> CONNECT scott/tiger
SQL> SELECT empno,sal
FROM emp
WHERE empno in (7839,7369);
EMPNO SAL
---------- ----------
7369 800
7839 5000
74. Provoquez l’étreinte fatale <1> EXEC batch (7839);
7839 mis à jour.
<3> EXEC batch (7369);
Bloqué..
7369 mis à jour
PL/SQL procedure successfully completed.
<2> EXEC batch (7369);
7369 mis à jour.
<4> EXEC batch (7839);
Bloqué
7839 mis à jour
PL/SQL procedure successfully completed.
75. Le verrou mortel a-t-il été résolu? SQL> CONNECT scott/tiger
SQL> SELECT empno,sal
FROM emp
WHERE empno in (7839,7369);
EMPNO SAL
---------- ----------
7369 800
7839 5000
76. Isolation Chaque utilisateur a l’impression d’être seul connecté au serveur.
L’isolation empêche les transactions concurrentes de voir les résultats partiels des autres, ce qui contribue à assurer l’intégrité des transactions.
77. Niveaux d’isolation Même si une autre transaction s'effectue en concurrence, elle semble s'être déroulée avant ou après.
C’est le niveau d’isolation qui permet à une transaction de se dérouler comme si elle était seule à travailler avec la base.
Le mode d’isolation prend effet à la prochaine requête.
Un COMMIT ou un ROLLBACK effectuer un retour au mode par défaut
78. Quatre niveaux d’isolation La norme SQL92 définit 4 niveaux d’isolation qu’une transaction peut expérimenter
ISOLATION UNCOMMITED;
ISOLATION LEVEL READ COMMITTED;
ISOLATION LEVEL SERIALIZABLE;
TRANSACTION READ ONLY;
79. Portée Le niveau d’isolation permet de contrôler trois situations d’interférence entre les transactions.
Dirty read
Non-repeatable (fuzzy) read
Les lectures fantôme
Il est possible de modifier le niveau d’isolation afin d’accepter ou non certaines interférences.
80. 1) Dirty Read READ COMMITED signifie que lorsqu’un processus accède en lecture à des données, celles-ci ont été validées. Dans le cas contraire, nous sommes en présence de lectures poussiéreuses.
Une transaction lit des données contenant des modifications non validées par une autre transaction. Une partie des données peut se révéler fausse selon que l’autre transaction la valide ou la défait.
Oracle ne laisse pas faire cela car cette option risque de déclencher le problème des tuples fantômes.
81. Opération en lecture multi versions Scott émet un ordre en lecture
Le processus serveur acquière un SCN puis commence à effectuer les lectures. Chaque fois qu’un bloc est lu, il est comparé au SCN qui estampille la requête au SCN de toute transaction en cours sur le bloc. Au cas où une modification non validée est détectée, le processus utilise des données présentes dans les segments d’annulation (UNDO) pour disposer de données consistante en lecture.
82. Opération en lecture multi versions Betty met à jour un enregistrement qui n’a pas encore été lue par le SELECT de Scott. Le processus serveur acquière de nouveau un SCN dès que l’ordre de Betty atteint cette donnée.
Betty valide sa transaction. Le processus serveur achève l’opération qui consigne des informations dans le bloc mise à jour. Oracle sera ainsi instruit qu’un nouveau SCN rend compte d’une transaction validée.
83. Lecture consistance multi versions
84. Exercice SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT empno,ename
FROM emp;
BEGIN
DBMS_OUTPUT.ENABLE(1000);
FOR i IN c1 LOOP
DBMS_LOCK.SLEEP(4);
DBMS_OUTPUT.PUT_LINE
(i.ename);
END LOOP;
END;
/
..
7839 KING
..
UPDATE emp
SET ename= 'TOTO'
WHERE empno=7839;
COMMIT;
85. Opération en lecture multi versions Le processus de Scott accède à la donnée récemment modifiée. Il voit que le bloc lu possède un SCN établi après qu’il ait émis sa propre requête. Le processus serveur regarde l’en-tête du bloc qui pointe sur un segment d’annulation où se trouve une copie antérieure de la donnée, une image de la donnée avant modification par Betty. C’est cette image avant qui participe à la lecture effectuée par Scott.
86. Déterminer l’instance du SCN en cours CONNECT SYS/dba247 as sysdba
SET SERVEROUTPUT ON
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
/
87. Note: Autre procédé pour déterminer la valeur courante du SCN CONNECT SYS/dba247 as sysdba
GRANT EXECUTE ON DBMS_FLASHBACK TO scott;
CONNECT scott/tiger
SET SERVEROUTPUT ON
VARIABLE scn NUMBER
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
:scn := iscn;
END;
/
PRINT :scn
SCN
----------
1954203
88. Exercice Créez une table avec les 500000 premiers nombres entiers.
CONNECT SCOTT/TIGER
DROP TABLE TEST CASCADE CONSTRAINTS;
CREATE TABLE test (
id NUMBER
CONSTRAINT PK_TEST PRIMARY KEY)
TABLESPACE users;
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO test VALUES (i);
END LOOP;
COMMIT;
END;
/
89. Exercice (double commit) Session A> La somme des 500000 premiers entiers peut se calculer de la façon suivante, instantanément.
SQL> COL RESULTAT FOR 999,999,999,999
SQL> SELECT 500000*500001/2 AS RESULTAT FROM DUAL;
SQL> SELECT SUM(a.id) AS RESULTAT
FROM test a,test b, test c, test d,test e,test f
WHERE a.id=b.id
AND b.id=c.id
AND c.id=d.id
AND d.id=e.id
AND e.id=f.id;
Session B> Quelques suppressions au début et à la fin puis COMMIT
Session B> Fin du SELECT;
Session B> COUNT(*);
Pourquoi trouve le même nombre d’enregistrements?
90. 2) Nonrepeatable (fuzzy) read Lectures non répétables
Une transaction lit une donnée alors qu’une seconde transaction change cette donnée. Lorsque la première relit la donnée, elle obtient une valeur différente.
On ne lit pas deux fois la même chose. Des données ont changé..
91. 3) Les lectures fantômes (définition 1) Pour effectuer des écritures, Oracle commence par rechercher de la place, puis le UPDATE a lieu.
Si une transaction exécute de nouveau une requête qui retourne un ensemble de données conformes à un prédicat, il se peut que des enregistrements qui n’existaient lors la première requête apparaissent. Ceux sont des tuples fantômes.
92. 3) Les lectures fantômes (autre explication) Si vous exécutez une requête au moment T1 et que vous la soumettiez de nouveau à l’instant T2, des lignes supplémentaires ajoutées à la base peuvent affecter le résultats.
Les lectures fantômes ne doivent pas être confondues avec des lectures non répétables. En cas de lectures fantômes les données que vous avez déjà lues n’ont pas été modifiées entre temps. Simplement, davantage de données correspondent au critère de recherche.
93. Le niveau d’isolation prend fin au COMMIT ou ROLLBACK Syntaxe
SQL> SET TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SQL> SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SQL> SET TRANSACTION READ ONLY;
SQL> ALTER SESSION
SET ISOLATION_LEVEL=SERIALIZABLE;
SQL> ALTER SESSION
SET ISOLATION_LEVEL READ COMMITTED;
94. SET TRANSACTION READ ONLY;
95. Tableau
96. Le standard ANSI/ISO SQL92 a défini trois sortes d’interférences possibles entre les transactions, et quatre niveaux d’isolation pour élever la cohérence contre de telles interactions:
97. Tester le niveau d’isolation SERIALIZABLE SQL> UPDATE scott.emp SET sal=sal*1.1;
SQL> SELECT -- Flag 7683 si UPDATE
CASE
WHEN BITAND(flag,268435456)=268435456
THEN 'serializable'
ELSE 'non-serializable'
END test
FROM v$transaction,v$session
WHERE taddr=addr
AND sid=(SELECT sid
FROM v$mystat
WHERE ROWNUM <2);
TEST
----------------
non-serializable
98. Exercice sur les lectures non répétables Ouvrir deux sessions respectivement sous SCOTT et sous BETTY
SCOTT calcule la somme des salaires (SAL) regroupés par département (DEPPNO)
BETTY augmente de 20% le salaire de l’employée empno=7839. Validez immédiatement la transaction de BETTY.
SCOTT calcule la somme des salaires de toute la table EMP.
Conclusion?
99. Lecture non reproductible <S> SELECT SUM(sal)
FROM emp
GROUP BY deptno;
SUM(SAL)
----------
8750
10875
9400
<S> SELECT SUM(sal)
FROM emp;
SUM(SAL)
----------
30025
<B> UPDATE emp
SET sal=sal*1.2
WHERE empno=7839;
1 row updated.
<B> COMMIT;
100. L’importance du niveau d’isolation SCOTT a pris en compte la transaction validée (COMMIT) de BETTY.
SCOTT voit ainsi que la somme des salaires diffère. C’est du reste logique (consistance).
Comment, dans ces conditions, effectuer un arrêté comptable de fin de mois (il finit à minuit légalement) si le batch s’arrête avant minuit ou si le batch finit après minuit fin de mois?
Réponse: changer le niveau d’isolation au début du batch de consolidation comptable.
101. Consolidation comptable
102. Mise à jour d’une clef primaire Créez une table test ayant une seule colonne qui servira également de clef primaire.
SQL> CONNECT scott/tiger
SQL> CREATE TABLE test (
Id NUMBER
CONSTRAINT pk_test PRIMARY KEY
);
SQL> CREATE SEQUENCE seq;
SQL> BEGIN
FOR i IN 1..10 LOOP
INSERT INTO test VALUES(SEQ.NEXTVAL);
END LOOP;
END;
/
SQL> COMMIT;
SQL> UPDATE test SET id = id + 1; -- ?
103. Exercice sur le verrouillage SCOTT crée une table TEST avec une colonne de type NUMBER. SCOTT insère 500000 premiers nombres entiers puis valide par COMMIT. SCOTT octroie à BETTY tous les droits sur cette table TEST .
BETTY ouvre une session. BETTY prépare un fichier avec une requête qui devra supprimer les nombres 1, 1000, 10000, 400000 et 500000 de la table. La suppression sera suivie d’une validation (COMMIT). BETTY émettra la requête ultérieurement.
SCOTT effectue la somme des nombres mais au moyen de 7 auto-jointures (afin de donner du temps à BETTY).
BETTY lance sa requête qui finira avant celle de SCOTT .
Conclusion?
Explication?
104. Solution de l’atelier (SCOTT) CREATE TABLE test (id NUMBER)
TABLESPACE USERS;
GRANT ALL ON test TO BETTY;
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO test VALUES(i);
END LOOP;
COMMIT;
END;
/
105. Solution de l’atelier (BETTY) Betty édite un fichier mais ne lance pas encore la requête:
vi c:\temp\betty_delete_some.sql
DELETE FROM scott.test
WHERE id IN
(1, 1000, 10000, 400000 ,500000);
COMMIT;
:wq!
106. Solution de l’atelier (SCOTT) SELECT SUM(a.id)
FROM test a,test b,
test c,test d,test e, test f
WHERE
a.id=b.id AND b.id=c.id AND c.id=d.id AND d.id=e.id AND e.id=f.id;
107. Solution de l’atelier (BETTY) SQL> @c:\temp\betty_delete_some.sql
Le requête de BETTY s’achève bien avant celle de SCOTT.
La requête de SCOTT retourne la bonne valeur, et cela malgré les quelques suppressions effectuées par BETTY.
Double validation par le processus DBWR..
108. Exercice Vous devez démarrer 3 sessions dans des fenêtres différentes.
Connectez-vous sous SCOTT/TIGER dans les sessions 1 et 3.
Connectez-vous sous / AS SYSDBA dans le session 2.
109. Exercice Dans le session 1, sous SCOTT, augmentez de 10% tous les salaires sal<1500 de la table EMP. NE PAS VALIDER.
Dans la session 2, sous /, vérifiez la présence éventuelle de verrous en interrogeant la vue V$LOCK.
Maintenant depuis la session 3, sous BETTY, tentez de supprimer la table EMP. Peut-on faire cela?
110. SAVEPOINT
111. Nommer une transaction Vous pouvez nommer une transaction grâce à la commande:
SET TRANSACTION NAME
La vue V$TRANSACTION permet au DBA de surveiller les transactions depuis une autre session
112. Suivi d’une transaction depuis une autre session SQLPLUS SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT empno,ename
FROM scott.emp;
BEGIN
DBMS_OUTPUT.ENABLE(1000);
SET TRANSACTION NAME 'scott_transaction';
FOR i IN c1 LOOP
DBMS_LOCK.SLEEP(1);
UPDATE scott.emp SET sal = sal + 10 WHERE empno=i.empno;
DBMS_OUTPUT.PUT_LINE (i.ename);
END LOOP;
ROLLBACK;
END;
/
113. Commit contre Rollback Commit
Tout le travail effectuer par une transaction est conservé
Les autres sessions peuvent voir les modifications effectuées par la transaction
Tous les verrous acquis sont libérés
Rollback
Tout le travail effectué par la transaction est défait, comme s’il n’avait jamais eut lieu
Tous les verrous acquis durant la transaction sont libérés
114. SAVEPOINT ou Point d’arrêt Permet de défaire (ROLLBACK) une partie seulement d’une transaction
Syntaxe:
BEGIN
…
SAVEPOINT x;
…
ROLLBACK TO X;
…
END;
/
115. Exemple DROP TABLE test CASCADE CONSTRAINT;
CREATE TABLE test (col VARCHAR2(16));
BEGIN
INSERT INTO test VALUES ('insert one ');
SAVEPOINT A;
INSERT INTO test VALUES ('insert two ');
SAVEPOINT B;
INSERT INTO test VALUES ('insert three ');
SAVEPOINT C;
ROLLBACK TO B;
-- ici action manquante ROLLBACK TO A, B, ou C ?
COMMIT;
END;
/
116. Exemple Si ROLLBACK TO B
Si /* ici action manquante */ est ROLLBACK B;
La troisième instruction INSERT et le SAVEPOINT C seront défaits. Mais les deux premiers INSERT auront bien lieu (SELECT * FROM TEST;)
Si ROLLBACK TO A
Le second INSERT sera défait, laissant seulement le premier s’exécuter (SELECT * FROM TEST;).
Le SAVEPOINT est souvent utiliser au sein de transactions compliquées au cas où si une partie de la transaction échouerait, elle pourra alors être annulée préservant la première partie (cas d’un réseau lent).
117. Oracle10g Nouvelles fonctionnalité de l’instruction COMMIT
118. Nouvelles fonctionnalité de l’instruction COMMIT Introduit avec Oracle10g R2
Quand une session émet un COMMIT, la zone tampon en mémoire est déposée dans les fichiers de reprise en ligne sur disque.
Ce procédé garantit que les transactions pourront, le cas échant, être re-jouées. Par exemple à l’issue d’une phase de récupération.
119. Nouvelles fonctionnalité de l’instruction COMMIT WRITE option Oracle10gR2 vous donne maintenant un moyen de contrôle sur la manière de consigner le flux de données dans les fichiers de reprise.
Pour cela, il suffit de spécifier une clause au sein même de l’instruction COMMIT pour en contrôler le comportement.
SQL> COMMIT WRITE <option>;
L’option WAIT reproduit le comportement par défaut du serveur de données. Dans ce cas vous ne reprendrez la main qu’au moment où le flux de données aura été déposé sur disque.
120. Nouvelles fonctionnalité de l’instruction COMMIT WRITE option Si vous souhaitez prendre la main aussi tôt, vous devrez émettre la clause NOWAIT
SQL> COMMIT WRITE NOWAIT;
Dans ce cas, le contrôle est immédiatement rendu à la session, avant même que le flux ne soit consigné dans les fichiers de reprise.
Si vous êtes en train d’effectuer une série de transactions, par exemple une série de traitements par lots ou batch, vous pouvez souhaiter ne pas vouloir valider trop souvent dans un environnement où beaucoup d’écritures ont lieu. Pour cela, changer d’application serait plus simple à dire qu’à faire. À moins d’utiliser la clause BATCH qui porte bien son nom:
SQL> COMMIT WRITE BATCH;
121. Nouvelles fonctionnalité de l’instruction COMMIT WRITE BATCH Cette commande demande aux écritures qu’elles aient lieu en mode batch plutôt qu’à chaque fois qu’un COMMIT survient, réduisant du même coup le nombre de vidange de la mémoire.
Vous pouvez toujours vidanger la mémoire sur disque en émettant l’instruction:
SQL> COMMIT WRITE IMMEDIATE;
122. Définir le comportement par défaut de la base ALTER SYSTEM Si vous voulez définir le comportement par défaut de la base, vous pouvez émettre l’une des deux instructions suivantes sous le compte SYSDBA.
Au niveau système:
SQL> ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
Au niveau session:
SQL> ALTER SESSION SET COMMIT_WORK = NOWAIT;
Note: Oracle10gR2 respecte la précédence SYSTEM, SESSION & instruction COMMIT.
123. Clause SELECT FOR UPDATE
124. Modes de verrouillage sur une table Les deux modes de verrouillage sur table de type TM fréquemment détenus par les transactions du DML sont:
RX (ROW EXCLUSIVE)
N’empêche pas les autres transactions de lire
Empêche les autres transactions de verrouiller manuellement la table en mode exclusif
Est alloué automatiquement (UPDATE, etc.).
RS (ROW SHARE)
Laisse les autres transactions effectuer des lectures et des écritures, voire même des verrouillages
Vous pouvez verrouiller des lignes au cours d’une interrogation à l’aide de l’instruction
SELECT…FOR UPDATE
Les instructions d’intégrité référentielle l’obtiennent implicitement
125. Note Metalink Note:1020008.6 Cette note fournit un script (disponible dans le répertoire commodités) assez complexe et hors formation. Le script détaille pleinement l’état des verrous posés à un instant donné dans la base par les transactions.
Les mieux est de créer une vues (TFSCLOCK) puis de lancer le script PRINT_TABLE depuis une session SYSDBA (PRINT_TABLE figure dans le répertoire commodités). SET ECHO off REM NAME: TFSCLOCK.SQL REM USAGE:"@path/tfsclock" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$ REM ------------------------------------------------------------------------ REM PURPOSE: REM The following locking information script provides fully DECODED REM information regarding the locks currently held in the database. REM The report generated is fairly complex and difficult to read, REM but has considerable detail. REM REM The TFTS series contains scripts to provide (less detailed) lock REM information in a formats which are somewhat less difficult to read: REM TFSMLOCK.SQL and TFSLLOCK.SQL. REM ------------------------------------------------------------------------ REM EXAMPLE: REM Too complex to show a representative sample here REM REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM set lines 200 set pagesize 66 break on Kill on sid on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column locking heading 'Lock Held/Lock Requested' format a40 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a30 heading "Table Name" column owner format a9 column LAddr heading "ID1 - ID2" format a18 column Lockt heading "Lock Type" format a40 column command format a25 column sid format 990 select nvl(S.USERNAME,'Internal') username, L.SID, nvl(S.TERMINAL,'None') terminal, decode(command, 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab, decode(command, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX', 11,'ALTER INDEX', 12,'DROP TABLE', 13,'CREATE SEQUENCE', 14,'ALTER SEQUENCE', 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE', 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE', 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER', 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE', 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE', 70,'ALTER RESOURCE COST', 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT', 76,'DROP SNAPSHOT', 79,'ALTER ROLE', 85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW', 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE', 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY', command||' - ???') COMMAND, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') request, l.id1||'-'||l.id2 Laddr, l.type||' - '|| decode(l.type, 'BL','Buffer hash table instance lock', 'CF',' Control file schema global enqueue lock', 'CI','Cross-instance function invocation instance lock', 'CS','Control file schema global enqueue lock', 'CU','Cursor bind lock', 'DF','Data file instance lock', 'DL','Direct loader parallel index create', 'DM','Mount/startup db primary/secondary instance lock', 'DR','Distributed recovery process lock', 'DX','Distributed transaction entry lock', 'FI','SGA open-file information lock', 'FS','File set lock', 'HW','Space management operations on a specific segment lock', 'IN','Instance number lock', 'IR','Instance recovery serialization global enqueue lock', 'IS','Instance state lock', 'IV','Library cache invalidation instance lock', 'JQ','Job queue lock', 'KK','Thread kick lock', 'MB','Master buffer hash table instance lock', 'MM','Mount definition gloabal enqueue lock', 'MR','Media recovery lock', 'PF','Password file lock', 'PI','Parallel operation lock', 'PR','Process startup lock', 'PS','Parallel operation lock', 'RE','USE_ROW_ENQUEUE enforcement lock', 'RT','Redo thread global enqueue lock', 'RW','Row wait enqueue lock', 'SC','System commit number instance lock', 'SH','System commit number high water mark enqueue lock', 'SM','SMON lock', 'SN','Sequence number instance lock', 'SQ','Sequence number enqueue lock', 'SS','Sort segment lock', 'ST','Space transaction enqueue lock', 'SV','Sequence number value lock', 'TA','Generic enqueue lock', 'TD','DDL enqueue lock', 'TE','Extend-segment enqueue lock', 'TM','DML enqueue lock', 'TO','Temporary Table Object Enqueue', 'TT','Temporary table enqueue lock', 'TX','Transaction enqueue lock', 'UL','User supplied lock', 'UN','User name lock', 'US','Undo segment DDL lock', 'WL','Being-written redo log instance lock', 'WS','Write-atomic-log-switch global enqueue lock', 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)', 'New block allocation enqueue lock (ID2=1)'), 'LA','Library cache lock instance lock (A=namespace)', 'LB','Library cache lock instance lock (B=namespace)', 'LC','Library cache lock instance lock (C=namespace)', 'LD','Library cache lock instance lock (D=namespace)', 'LE','Library cache lock instance lock (E=namespace)', 'LF','Library cache lock instance lock (F=namespace)', 'LG','Library cache lock instance lock (G=namespace)', 'LH','Library cache lock instance lock (H=namespace)', 'LI','Library cache lock instance lock (I=namespace)', 'LJ','Library cache lock instance lock (J=namespace)', 'LK','Library cache lock instance lock (K=namespace)', 'LL','Library cache lock instance lock (L=namespace)', 'LM','Library cache lock instance lock (M=namespace)', 'LN','Library cache lock instance lock (N=namespace)', 'LO','Library cache lock instance lock (O=namespace)', 'LP','Library cache lock instance lock (P=namespace)', 'LS','Log start/log switch enqueue lock', 'PA','Library cache pin instance lock (A=namespace)', 'PB','Library cache pin instance lock (B=namespace)', 'PC','Library cache pin instance lock (C=namespace)', 'PD','Library cache pin instance lock (D=namespace)', 'PE','Library cache pin instance lock (E=namespace)', 'PF','Library cache pin instance lock (F=namespace)', 'PG','Library cache pin instance lock (G=namespace)', 'PH','Library cache pin instance lock (H=namespace)', 'PI','Library cache pin instance lock (I=namespace)', 'PJ','Library cache pin instance lock (J=namespace)', 'PL','Library cache pin instance lock (K=namespace)', 'PK','Library cache pin instance lock (L=namespace)', 'PM','Library cache pin instance lock (M=namespace)', 'PN','Library cache pin instance lock (N=namespace)', 'PO','Library cache pin instance lock (O=namespace)', 'PP','Library cache pin instance lock (P=namespace)', 'PQ','Library cache pin instance lock (Q=namespace)', 'PR','Library cache pin instance lock (R=namespace)', 'PS','Library cache pin instance lock (S=namespace)', 'PT','Library cache pin instance lock (T=namespace)', 'PU','Library cache pin instance lock (U=namespace)', 'PV','Library cache pin instance lock (V=namespace)', 'PW','Library cache pin instance lock (W=namespace)', 'PX','Library cache pin instance lock (X=namespace)', 'PY','Library cache pin instance lock (Y=namespace)', 'PZ','Library cache pin instance lock (Z=namespace)', 'QA','Row cache instance lock (A=cache)', 'QB','Row cache instance lock (B=cache)', 'QC','Row cache instance lock (C=cache)', 'QD','Row cache instance lock (D=cache)', 'QE','Row cache instance lock (E=cache)', 'QF','Row cache instance lock (F=cache)', 'QG','Row cache instance lock (G=cache)', 'QH','Row cache instance lock (H=cache)', 'QI','Row cache instance lock (I=cache)', 'QJ','Row cache instance lock (J=cache)', 'QL','Row cache instance lock (K=cache)', 'QK','Row cache instance lock (L=cache)', 'QM','Row cache instance lock (M=cache)', 'QN','Row cache instance lock (N=cache)', 'QO','Row cache instance lock (O=cache)', 'QP','Row cache instance lock (P=cache)', 'QQ','Row cache instance lock (Q=cache)', 'QR','Row cache instance lock (R=cache)', 'QS','Row cache instance lock (S=cache)', 'QT','Row cache instance lock (T=cache)', 'QU','Row cache instance lock (U=cache)', 'QV','Row cache instance lock (V=cache)', 'QW','Row cache instance lock (W=cache)', 'QX','Row cache instance lock (X=cache)', 'QY','Row cache instance lock (Y=cache)', 'QZ','Row cache instance lock (Z=cache)','????') Lockt from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,1) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5 / SET ECHO off REM NAME: TFSCLOCK.SQL REM USAGE:"@path/tfsclock" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$ REM ------------------------------------------------------------------------ REM PURPOSE: REM The following locking information script provides fully DECODED REM information regarding the locks currently held in the database. REM The report generated is fairly complex and difficult to read, REM but has considerable detail. REM REM The TFTS series contains scripts to provide (less detailed) lock REM information in a formats which are somewhat less difficult to read: REM TFSMLOCK.SQL and TFSLLOCK.SQL. REM ------------------------------------------------------------------------ REM EXAMPLE: REM Too complex to show a representative sample here REM REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM set lines 200 set pagesize 66 break on Kill on sid on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column locking heading 'Lock Held/Lock Requested' format a40 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a30 heading "Table Name" column owner format a9 column LAddr heading "ID1 - ID2" format a18 column Lockt heading "Lock Type" format a40 column command format a25 column sid format 990 select nvl(S.USERNAME,'Internal') username, L.SID, nvl(S.TERMINAL,'None') terminal, decode(command, 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab, decode(command, 0,'BACKGROUND', 1,'Create Table', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DROP', 9,'CREATE INDEX', 10,'DROP INDEX', 11,'ALTER INDEX', 12,'DROP TABLE', 13,'CREATE SEQUENCE', 14,'ALTER SEQUENCE', 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE', 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE', 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER', 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE', 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE', 70,'ALTER RESOURCE COST', 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT', 76,'DROP SNAPSHOT', 79,'ALTER ROLE', 85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW', 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE', 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY', command||' - ???') COMMAND, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') request, l.id1||'-'||l.id2 Laddr, l.type||' - '|| decode(l.type, 'BL','Buffer hash table instance lock', 'CF',' Control file schema global enqueue lock', 'CI','Cross-instance function invocation instance lock', 'CS','Control file schema global enqueue lock', 'CU','Cursor bind lock', 'DF','Data file instance lock', 'DL','Direct loader parallel index create', 'DM','Mount/startup db primary/secondary instance lock', 'DR','Distributed recovery process lock', 'DX','Distributed transaction entry lock', 'FI','SGA open-file information lock', 'FS','File set lock', 'HW','Space management operations on a specific segment lock', 'IN','Instance number lock', 'IR','Instance recovery serialization global enqueue lock', 'IS','Instance state lock', 'IV','Library cache invalidation instance lock', 'JQ','Job queue lock', 'KK','Thread kick lock', 'MB','Master buffer hash table instance lock', 'MM','Mount definition gloabal enqueue lock', 'MR','Media recovery lock', 'PF','Password file lock', 'PI','Parallel operation lock', 'PR','Process startup lock', 'PS','Parallel operation lock', 'RE','USE_ROW_ENQUEUE enforcement lock', 'RT','Redo thread global enqueue lock', 'RW','Row wait enqueue lock', 'SC','System commit number instance lock', 'SH','System commit number high water mark enqueue lock', 'SM','SMON lock', 'SN','Sequence number instance lock', 'SQ','Sequence number enqueue lock', 'SS','Sort segment lock', 'ST','Space transaction enqueue lock', 'SV','Sequence number value lock', 'TA','Generic enqueue lock', 'TD','DDL enqueue lock', 'TE','Extend-segment enqueue lock', 'TM','DML enqueue lock', 'TO','Temporary Table Object Enqueue', 'TT','Temporary table enqueue lock', 'TX','Transaction enqueue lock', 'UL','User supplied lock', 'UN','User name lock', 'US','Undo segment DDL lock', 'WL','Being-written redo log instance lock', 'WS','Write-atomic-log-switch global enqueue lock', 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)', 'New block allocation enqueue lock (ID2=1)'), 'LA','Library cache lock instance lock (A=namespace)', 'LB','Library cache lock instance lock (B=namespace)', 'LC','Library cache lock instance lock (C=namespace)', 'LD','Library cache lock instance lock (D=namespace)', 'LE','Library cache lock instance lock (E=namespace)', 'LF','Library cache lock instance lock (F=namespace)', 'LG','Library cache lock instance lock (G=namespace)', 'LH','Library cache lock instance lock (H=namespace)', 'LI','Library cache lock instance lock (I=namespace)', 'LJ','Library cache lock instance lock (J=namespace)', 'LK','Library cache lock instance lock (K=namespace)', 'LL','Library cache lock instance lock (L=namespace)', 'LM','Library cache lock instance lock (M=namespace)', 'LN','Library cache lock instance lock (N=namespace)', 'LO','Library cache lock instance lock (O=namespace)', 'LP','Library cache lock instance lock (P=namespace)', 'LS','Log start/log switch enqueue lock', 'PA','Library cache pin instance lock (A=namespace)', 'PB','Library cache pin instance lock (B=namespace)', 'PC','Library cache pin instance lock (C=namespace)', 'PD','Library cache pin instance lock (D=namespace)', 'PE','Library cache pin instance lock (E=namespace)', 'PF','Library cache pin instance lock (F=namespace)', 'PG','Library cache pin instance lock (G=namespace)', 'PH','Library cache pin instance lock (H=namespace)', 'PI','Library cache pin instance lock (I=namespace)', 'PJ','Library cache pin instance lock (J=namespace)', 'PL','Library cache pin instance lock (K=namespace)', 'PK','Library cache pin instance lock (L=namespace)', 'PM','Library cache pin instance lock (M=namespace)', 'PN','Library cache pin instance lock (N=namespace)', 'PO','Library cache pin instance lock (O=namespace)', 'PP','Library cache pin instance lock (P=namespace)', 'PQ','Library cache pin instance lock (Q=namespace)', 'PR','Library cache pin instance lock (R=namespace)', 'PS','Library cache pin instance lock (S=namespace)', 'PT','Library cache pin instance lock (T=namespace)', 'PU','Library cache pin instance lock (U=namespace)', 'PV','Library cache pin instance lock (V=namespace)', 'PW','Library cache pin instance lock (W=namespace)', 'PX','Library cache pin instance lock (X=namespace)', 'PY','Library cache pin instance lock (Y=namespace)', 'PZ','Library cache pin instance lock (Z=namespace)', 'QA','Row cache instance lock (A=cache)', 'QB','Row cache instance lock (B=cache)', 'QC','Row cache instance lock (C=cache)', 'QD','Row cache instance lock (D=cache)', 'QE','Row cache instance lock (E=cache)', 'QF','Row cache instance lock (F=cache)', 'QG','Row cache instance lock (G=cache)', 'QH','Row cache instance lock (H=cache)', 'QI','Row cache instance lock (I=cache)', 'QJ','Row cache instance lock (J=cache)', 'QL','Row cache instance lock (K=cache)', 'QK','Row cache instance lock (L=cache)', 'QM','Row cache instance lock (M=cache)', 'QN','Row cache instance lock (N=cache)', 'QO','Row cache instance lock (O=cache)', 'QP','Row cache instance lock (P=cache)', 'QQ','Row cache instance lock (Q=cache)', 'QR','Row cache instance lock (R=cache)', 'QS','Row cache instance lock (S=cache)', 'QT','Row cache instance lock (T=cache)', 'QU','Row cache instance lock (U=cache)', 'QV','Row cache instance lock (V=cache)', 'QW','Row cache instance lock (W=cache)', 'QX','Row cache instance lock (X=cache)', 'QY','Row cache instance lock (Y=cache)', 'QZ','Row cache instance lock (Z=cache)','????') Lockt from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,1) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5 /
126. Suivi détaillé des verrous posée Depuis une première session:
SCOTT> UPDATE emp SET sal=sal*1.1;
Depuis une seconde sous SYS:
SYS> EXEC print_table(‘SELECT * FROM TFSCLOCK’);
127. Suivi détaillé des verrous posée SQL> EXEC print_table('select * from tfsclock');
------------------------------
USERNAME : SCOTT
SID : 144
TERMINAL : PANAMA
TAB : None
COMMAND : BACKGROUND
LMODE : Exclusive
REQUEST : NONE
LADDR : 458758-428
LOCKT : TX - Transaction enqueue lock
------------------------------
USERNAME : SCOTT
SID : 144
TERMINAL : PANAMA
TAB : None
COMMAND : BACKGROUND
LMODE : Row Exclusive
REQUEST : NONE
LADDR : 13610-0
LOCKT : TM - DML enqueue lock
128. Autre requête CREATE OR REPLACE VIEW verrou AS
SELECT NVL(S.USERNAME,'Internal') Username,
NVL(S.TERMINAL,'None') Terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) Tab,
DECODE(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) LMode,
DECODE(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) Request
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
AND T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
129. Exemple SQL> EXEC print_table('select * from verrous');
------------------------------
USERNAME : SCOTT
TERMINAL : PANAMA
KILL : 144,96
TAB : SYS.I_RLS_GRP
LMODE : Exclusive
REQUEST :
------------------------------
USERNAME : SCOTT
TERMINAL : PANAMA
KILL : 144,96
TAB : SCOTT.EMP
LMODE : Row Exclusive
REQUEST :
130. SELECT .. et les verrous Très souvent, un traitement qui a lieu dans une boucle modifie les données extraites par le curseur.
Le verbe SELECT ne pose aucun verrou afin de laisser les autres transactions mettre à à jour les données qui demeurent ainsi dans un état consistant.
131. SELECT .. avec un TIMEOUT Néanmoins, Oracle fournit un mécanisme qui permet à une lecture (SELECT) de poser des verrous ligne exclusifs sur un ensemble de données lorsque cela peut être nécessaire.
Syntaxe de la clause FOR UPDATE
SELECT .. FROM ..
[ORDER BY]
FOR UPDATE [OF column]
[NOWAIT | WAIT n]
132. Exemple CREATE OR REPLACE PROCEDURE
setlocks (p_deptno NUMBER)
IS
CURSOR c1(v_deptno NUMBER) IS
SELECT *
FROM emp
WHERE deptno = v_deptno
ORDER BY job
FOR UPDATE OF sal NOWAIT;
..
133. OPEN CURSOR Si une clause FOR UPDATE est présente, un verrou ligne exclusif sera posé sur les enregistrements concernés avant l’ouverture du curseur (OPEN).
Ces verrous préviennent les autres sessions de modifications cours. Les verrous ne seront libérés qu’à la fin de la transaction à l’issu d’une COMMIT ou d’un ROLLBACK.
134. NOWAIT & ORA-00054 Si une autre session a déjà posé des verrous, l’opération SELECT .. FOR UPDATE attendra qu’ils soient relâchés.
NOWAIT: afin de contrôler cette situation, la clause NOWAIT est disponible. Une erreur ORA-00054 pourra être levée informant la transaction que des verrous ont été posées. Il faudra réitérer plus tard le traitement ou bien manipuler un autre ensemble de données
135. Oracle9i et la clause FOR UPDATE Oracle9i et Oracle10g permettent au développeur de réclamer un délai de carence (TIME-OUT)
WAIT n où n est exprimé en seconde
Si les verrous ne sont pas libérés, un message d’erreur ORA-00054 pourra être levé
136. Exemple (fragment) CREATE OR REPLACE PROCEDURE
setlocks (p_deptno NUMBER)
IS
CURSOR c1(v_deptno NUMBER) IS
SELECT *
FROM emp
WHERE deptno = v_deptno
ORDER BY job
FOR UPDATE OF job,sal WAIT 10;
..
137. Exercice CONNECT scott/tiger
CREATE OR REPLACE PROCEDURE dealWithLocks
(p_deptno NUMBER) IS
CURSOR c1(v_deptno NUMBER) IS
SELECT * FROM emp WHERE deptno = v_deptno
FOR UPDATE OF sal ; --NOWAIT ;
DEPTNO_LOCKED EXCEPTION;
PRAGMA EXCEPTION_INIT(DEPTNO_LOCKED,-00054);
BEGIN
DBMS_OUTPUT.ENABLE(32000);
FOR i IN c1(p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(i.ename || ' updated.');
END LOOP;
EXCEPTION
WHEN DEPTNO_LOCKED THEN
DBMS_OUTPUT.PUT_LINE(p_deptno || ' locked! ');
END dealWithLocks;
/
GRANT EXECUTE ON dealWithLocks TO BETTY;
138. Curseur avec la clause NOWAIT Ouvrir une session sous SCOTT pour créer la procédure dealWithLocks précédente.
Ouvrir une seconde session sous BETTY afin de mettre à jour un employé du département 30 (par exemple KING 7839). BETTY ne valide pas sa transaction.
Depuis la session sous SCOTT , exécutez la procédure avec pour argument le département 30.
Observez ce qui se passe.
139. Curseur avec la clause NOWAIT
140. Curseur avec délai de carence (WAIT n) En vous inspirant de la procédure dealWithLocks, écrire une nouvelle procédure dealWithTimeout. Celle-ci doit lever l’exception ORA-30006.
Cette exception survient lorsque un délai de carence a été introduit dans la clause SELECT .. FOR UPDATE WAIT n, où n s’exprime en secondes. Attribuez un timeout de 10 ou 15 secondes.
A) Betty effectue la mise à jour d’un employé du département 30, mais sans valider.
B) Réitérez, mais cette fois ci, Betty libère ses verrous en validant (COMMIT) ou annulant (ROLLBACK) sa propre transaction avant que Scott ne reçoive une exception.
141. Lever une exception lorsqu’un timeout survient CREATE OR REPLACE PROCEDURE dealWithTimeout (p_deptno NUMBER)
IS
CURSOR c1(v_deptno NUMBER) IS
SELECT * FROM emp WHERE deptno = v_deptno
FOR UPDATE OF sal WAIT 30;
RESOURCE_BUSY EXCEPTION;
PRAGMA EXCEPTION_INIT(RESOURCE_BUSY,-30006);
BEGIN
DBMS_OUTPUT.ENABLE(32000);
FOR i IN c1(p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(i.ename || 'mis à jour');
END LOOP;
EXCEPTION
WHEN RESOURCE_BUSY THEN
DBMS_OUTPUT.PUT_LINE('I can''t wait any more!');
END dealWithTimeout ;
/
142. A) Betty ne valide pas
143. B) Betty libère ses verrous à temps
144. Autres exceptions pré-définies (Build-in) Au cas où..
ORA-00051 TIMEOUT_ON_RESOURCE
Un timeout vient de survenir sur une ressource.
Il s’agit d’une exception intégrée de PL/SQL.
ORA-00061 TRANSACTION_BACKED_OUT
La transaction a été défaite à cause d’une étreinte fatale.
Il s’agit d’une exception intégrée de PL/SQL.
145. Clause WHERE CURRENT OF
146. WHERE CURRENT OF Si un curseur (c1) contient une clause FOR UPDATE, la clause WHERE CURRENT OF peut être utilisée en conjonction avec le curseur pour effectuer des mises à jour (DELETE et UPDATE)
Syntaxe: ..WHERE CURRENT OF c1
Cette clause WHERE CURRENT OF évalue, au fur et à mesure, l’enregistrement qui vient d’être rapportée par le curseur c1.
147. Exemple SET ECHO ON SERVEROUTPUT ON
DECLARE
CURSOR c1 IS SELECT * FROM DEPT FOR UPDATE OF DNAME;
rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;
IF rec.deptno = 30 THEN
UPDATE DEPT SET DNAME = 'MIS'
WHERE CURRENT OF c1; -- iff FOR UPDATE associée à c1
END IF;
END LOOP;
CLOSE c1;
END;
/
SELECT * FROM DEPT WHERE DEPTNO = 30;
ROLLBACK;
148. Définir une transaction autonome
149. Transaction autonome Une transaction autonome fait partie d’une transaction principale.
Lorsque la transaction autonome se déclenche, la transaction principale est suspendue.
Dès que la transaction autonome prend fin, la transaction principale reprend.
Un COMMIT peut donc avoir lieu au milieu de plusieurs ROLLBACK.
150. Quand utiliser une transaction autonome? D’un coté, vous voulez journaliser des erreurs dans une table. De l’autre, vous voulez défaire la transaction de la partie principale du fait de l’erreur comme si la transaction toute entière n’était plus atomique.
151. AUTONOMOUS TRANSACTION PROCEDURE add_emp(..) IS
BEGIN
UPDATE..;
UPDATE..;
SAVEPOINT start_add;
INSERT..;
COMMIT;
EXECPTION
WHEN OTHERS THEN
ROLLBACK start_add;
Log(SQLEERM);
END;
/ PROCEDURE log(..)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log
VALUES(USER,SQLERRM,SYSDATE);
COMMIT;
END;
/
152. Exemple : Auditer un traitement (1/2) CREATE TABLE emp_historique(
usr VARCHAR2(32),
when DATE,
previousValue VARCHAR2(32),
currentValue VARCHAR2(32)
)
/
CREATE OR REPLACE TRIGGER auditer
BEFORE UPDATE OF ename ON emp
FOR EACH ROW
DECLARE
153. Exemple : Auditer un traitement (2/2)
154. Autonomous Transaction depuis SQL (1/4) SQL> CREATE TABLE query_trace(
2 table_name VARCHAR2(30),
3 rowid_info ROWID,
4 query_by VARCHAR2(30),
5 queried_at DATE)
6 /
Table created.
155. Autonomous Transaction depuis SQL (2/4)
156. Autonomous Transaction depuis SQL (3/4)
157. Autonomous Transaction depuis SQL (4/4)
158. Exemple (1/2)
159. Example (2/2)
160. Transactions discrètes
161. Transactions discrètes Cette fonctionnalité du gestionnaire de transaction permet aux modifications apportées aux données d’être différées au moment de la validation (COMMIT).
Cette fonctionnalité ne génère pas d’activité dans les segments d’annulation (UNDO) comme le font les transactions classiques.
Au lieu de cela, les segments liés à l’activité sont stockés dans la zone privée appelée PGA. Ils iront dans les espace appropriés seulement au COMMIT.
SQL> DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION
162. PRAGMA et pureté
163. Package avec une fonction CREATE OR REPLACE PACKAGE pkg IS
FUNCTION f (arg IN NUMBER) RETURN NUMBER;
TVA NUMBER := .206; -- global
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg IS
FUNCTION f (arg IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN(arg*TVA);
END f;
END pkg;
/
SQL> SELECT pkg.f(100) FROM DUAL;
PKG.F(100)
----------
20.6
164. Quatre niveaux Vous pouvez essayer d'affirmer toute combinaison formée de:
WNDS
Write no database state; no modification made to database tables
WNPS
Write no package state; no modification made to package variables
RNDS
Read no database state; no queries againt database tables or other database objects
RNPS
Read no package state; no read access of package variables
165. Solution
166. Expert : purity CREATE OR REPLACE PACKAGE BODY comp
IS
FUNCTION total
(sal IN NUMBER,
comm IN NUMBER := NULL)
RETURN NUMBER IS
BEGIN
RETURN sal + NVL(comm,0);
END total;
END;
/
167. Mécanisme fondamental d’une transaction
168. Interactions entre les composants constitutifs du serveur de données Oracle10g
169. Mécanisme d’une transaction élémentaire (1/2) Oracle examine le cache de tampons pour déterminer si le bloc de données cible se trouve en mémoire.
Si le bloc de données cible ne se trouve pas en mémoire, Oracle le récupère à partir du disque.
Il enregistre une entrée de reprise dans le tampon REDO.
Il consigne dans un segment d’annulation, le code et les données requises pour défaire la modification apportée au bloc. Auparavant, des vecteurs de changement sont générés pour cette action.
Oracle met à jour le bloc de données en mémoire avec la nouvelle valeur
170. Mécanisme d’une transaction élémentaire (2/2) Oracle génère une entrée de validation dans le tampon REDO et associe à la transaction un SCN de validation.
Il écrit dans le journal de reprise sur disque le contenu du tampon REDO.
Il libère les blocs du segment d’annulation (BEFORE IMAGE) qui contenaient les informations d’annulation de la transaction.
Il enregistre le bloc modifié dans un fichier de données
NB: Le processus PMON de charge de récupérer un processus utilisateur défaillant
171. Tables temporaires
172. Illustration: tables temporaires Une table temporaire est semblable à une table permanente, si ce n’est qu’elle contient des données privées durant une session ou bien le temps d’une transaction.
La création de tables temporaires ne génère pas d’entrées dans les fichiers de reprise.
Elles permettent de simplifier, et bien souvent, d’accélérer les traitements (BATCH).
Plans d’exécution des requêtes meilleurs, voire même inacceptables dans certains
173. Exemple de tables temporaires CREATE GLOBAL TEMPORARY TABLE "SCOTT"."T_TRANSACTION" ( "A" VARCHAR2(32)) ON COMMIT DELETE ROWS ;
CREATE GLOBAL TEMPORARY TABLE "SCOTT"."T_SESSION" ( "A" VARCHAR2(32)) ON COMMIT PRESERVE ROWS ;
INSERT INTO t_transaction (a)
VALUES('data persist for the transaction');
INSERT INTO t_session (a)
VALUES('data persist for the session ');
COMMIT;
174. ON COMMIT SQL> COMMIT;
SQL> SELECT * FROM t_transaction;
A
--------------------------------
no row selected.
SQL> SELECT * FROM t_session;
A
--------------------------------
data persist for the session
SQL> EXIT
175. Oracle10g permet de placer des VARRAY dans les segments temporaires Exemple
176. Review: Identifying Type Hierarchy
177. Création de tables temporaires avec une colonne de type VARRAY (Oracle10g)
178. Sample 1/4 DROP TABLE department;
DROP TYPE Project_lst FORCE;
DROP TYPE Project_typ FORCE;
CREATE TYPE Project_Typ AS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2)
);
/
CREATE TYPE Project_lst
AS VARRAY (50) OF Project_Typ;
/
179. Sample 2/4
180. Sample 3/4
181. Debug Mode Plan d’exécution, oradebug, TKProf
StackPack
(Complément - Overview)
182. Porter un jugement sur les performances d’une requête Mise en place des traces: SET AUTOTRACE ON. Constitue l’un des moyens de « tuner » les requêtes SQL afin d’ajuster les performances du SGBD.
Collectez des statistiques au moyen du paquetage DBMS_STATS sur les tables de SCOTT pour améliorer le comportement de l’optimiseur du SGBD
Comparez les plans d’exécution (XPLAIN PLAN) afin de déterminer la méthode la plus performante.
Jugement
183. SET AUTOTRACE SQL> CONNECT / AS SYSDBA
SQL> @?/sqlplus/admin/plustrce.sql
SQL> GRANT plustrace TO scott;
SQL> CONNECT scott/tiger
SQL> @?/rdbms/admin/utlxplan.sql
SQL> SET AUTOTRACE ON
184. Options de trace (Réglage des performances) SET AUTOTRACE OFF – Aucune trace affichée. C’est l’option par défaut.
SET AUTOTRACE ON EXPLAIN – Affiche seulement le chemin du plan d’exécution de l’optimiseur.
SET AUTOTRACE ON STATISTICS – Affiche seulement les statistiques associées au plan d’exécution par l’optimiseur.
SET AUTOTRACE ON - Affiche le chemin du plan d’exécution de l’optimiseur ainsi que des statistiques produites.
SET AUTOTRACE TRACEONLY - Comme SET AUTOTRACE ON, sans afficher de nouveau la requête.
185. Activer AUTOTRACE en mode console SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT e.ename,d.loc
2 FROM emp e,dept d
3 WHERE e.deptno=d.deptno
4 AND e.deptno IN (10,20)
5 ORDER BY e.sal;
ENAME LOC
---------- -------------
SMITH DALLAS
…
Execution Plan
----------------------------------------------------------
Plan hash value: 3357797783
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 270 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 270 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 2 | 42 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 8 | 264 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
186. Activer AUTOTRACE en mode console Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
3 - filter("D"."DEPTNO"=10 OR "D"."DEPTNO"=20)
4 - filter("E"."DEPTNO"=10 OR "E"."DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
603 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
187. Depuis SQL Developer (disponible depuis mars 2006)
188. Collecter des Statistiques Par défaut, Oracle10g utilise un optimiseur sur coût (statistique et non plus déterministe devenu obsolète) pour accéder aux données.
Pour collecter les statistiques nécessaires à l’optimiseur, invoquez le paquetage DBMS_STATS:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DEPT');
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP_DEPT');
SET> SET AUTOTRACE ON
SQL> Statement
189. Supprimer les statistiques dbms_stats.delete_table_stats(
ownname=> 'SCOTT',
tabname=> 'EMP');
end;
.
/
190. Requête avec jointure Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=770)
1 0 HASH JOIN (Cost=5 Card=14 Bytes=770)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=490)
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
1518 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
14 rows processed
191. oradebug SPID SELECT b.sid, b.serial#, b.username, spid
FROM V$SESSION b, v$process a
WHERE type='USER'
AND a.addr = b.paddr;
192. oradebug SQL> oradebug setospid 2961508
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.
193. Tkprof tkprof est un outil assez simple et pratique pour présenter les traces de façon intelligible.
SQL> ALTER SYSTEM SET sql_trace=FALSE SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SHOW PARAMETER sql_trace
194. Deux façons d’établir les traces SCOTT> EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);
PL/SQL procedure successfully completed.
SCOTT> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
SCOTT> SELECT * FROM scott.emp;
195. Editer le fichier de trace SQL> CONNECT / AS SYSDBA
SQL> SHOW PARAMETER dump
VALUE
--------------------------
C:\oracle\admin\PRMY\udump
SQL> EXIT
ls –rtl C:\oracle\admin\PRMY\udump
196. Sortie formatée avec TKProf C:\> cd c:\oracle\admin\ORCL\udump
C:\> ls -rtl
..
C:\oracle\admin\ORCL\udump\orcl_ora_2012.trc
C:\>%ORACLE_HOME%\bin\tkprof orcl_ora_2012.trc EXPLAIN=scott/tiger@orcl SYS=NO
output = c:\tmp\SCOTT_TRACE.TXT
C:\>notepad c:\tmp\SCOTT_TRACE.TXT
197. select *
from
emp,dept where emp.deptno=dept.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.46 0.52 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.06 12 45 0 42
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.46 0.58 12 45 0 42
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
14 HASH JOIN
4 TABLE ACCESS FULL DEPT
14 TABLE ACCESS FULL EMP
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
14 HASH JOIN
4 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DEPT'
14 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
198. Le TOP 10 des I/O Quels sont les 10 requêtes qui ont généré le plus d’I/O? (Cela ne se devine pas, mais c’est documenté!)
C:\>tkprof prmy_ora_3672.trc SORT=(PRSDSK,EXEDSK,FCHDSK) PRINT=10
199. Utilitaire PRINT_TABLE
200. Utilitaire PRINT_TABLE (#1/3) CONNECT / AS SYSDBA
CREATE OR REPLACE PROCEDURE print_table( p_query IN VARCHAR2 )
AUTHID CURRENT_USER --
IS
l_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnValue VARCHAR2(4000);
l_status INTEGER;
l_descTbl DBMS_SQL.desc_tab;
l_colCnt NUMBER;
PROCEDURE ln (str IN VARCHAR2) IS -- Inner procedure
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END ln;
201. BEGIN
EXECUTE IMMEDIATE
'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS'' ';
DBMS_SQL.parse ( l_theCursor, p_query, DBMS_SQL.native );
DBMS_SQL.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
FOR i IN 1 .. l_colCnt LOOP
DBMS_SQL.define_column(l_theCursor, i, l_columnValue, 4000);
END LOOP;
l_status := DBMS_SQL.execute(l_theCursor);
ln( '------------------------------' );
WHILE ( DBMS_SQL.fetch_rows(l_theCursor) > 0 ) LOOP
FOR i IN 1 .. l_colCnt LOOP
DBMS_SQL.column_value( l_theCursor, i, l_columnValue );
ln
(
RPAD( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue
);
END LOOP;
ln( '------------------------------' );
END LOOP;
EXECUTE IMMEDIATE
'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-RR'' ';
202. Utilitaire PRINT_TABLE (fin) EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE
'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-RR'' ';
RAISE;
END;
/
GRANT EXECUTE ON print_table TO PUBLIC;
SET SERVEROUTPUT ON SIZE 999999
203. Q
