Travaux Pratiques

Gestion des transactions dans Oracle

Procédure de connexion à Oracle

Doc. technique Oracle

Isolation des transactions dans Oracle

Stockage interne dans Oracle

Partie I : Transactions et concurrence

Dans cette partie, on veut étudier le comportement d'Oracle en cas d'accès concurrents à la même ressource. Pour cela on va simuler l'exécution concurrente de programmes à l'aide du petit ensemble de lectures/écritures.

Créer une table Clients et un compteur par les commandes suivantes:

CREATE TABLE C (
NB      number(8) not null,
NOM     varchar2(20),
PRENOM  varchar2(20),
TYPE    varchar2(10)
)
/
CREATE SEQUENCE COMPTEUR
       INCREMENT BY 1
       START WITH 1
       NOCYCLE
/

Insérer dans la table des clients le contenu de la table B avec de nouveau NB obtenus par le compteur. Ajouter un attribut cave a la table C. Initialiser cet attribut pour tous les clients en fonction de leur type (petit=50, moyen=100, gros=500). Créer 3 fichiers, nommés stock.sql, livraison.sql et conso.sql

stock.sql 

PROMPT 'Recherche du stock de Cesar';
SELECT 'La réserve de Cesar est de : ' || cave || ' bouteilles'
FROM c WHERE nom='Cesar';

livraison.sql 

PROMPT 'Un achat de 120 bouteilles est effectué par Cesar'
UPDATE C SET cave = cave + 120 WHERE nom = 'Cesar'; 
PROMPT 'Nouveau stock de Cesar';
SELECT cave FROM C where nom = 'Cesar';

conso.sql 

PROMPT 'Cesar retire 10 bouteilles de son stock';
UPDATE C SET cave = cave - 10 WHERE  nom = 'Cesar'; 
PROMPT 'Nouveau stock de Cesar';
SELECT cave FROM C where nom = 'Cesar';

Ouvrir deux fenêtres SQLPLUS. Chaque session est considérée par ORACLE comme un utilisateur, et on a donc 2 utilisateurs, nommés 1 et 2, en situation de concurrence. Dans tout ce qui suit, on note INSTRi l'exécution de l'instruction INSTR par l'utilisateur i. Par exemple LIVRAISON1 correspond à l'exécution du fichier livraison dans la première fenêtre par la commande @livraison. On note de même ROLi et COMi l'exécution des commandes rollback; et commit; dans la fenêtre i.

ORACLE pratique une verrouillage à deux phases assez particulier. L'objectif est d'essayer de comprendre quel est ce type de verrouillage. Il faut savoir que par défaut, Oracle travail en degré d'isolation 1 (READ COMMITTED).

Exécuter les séquences d'instruction décrites ci-dessous. Qu'observez-vous ? expliquez.

Tester les exécutions précédentes en spécifiant le mode suivant après chaque commit : SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. Comparer et expliquez comment Oracle verrouille les données.

Si vous avez compris comment fonctionne le verrouillage Oracle, proposez une execution concurrente de deux transactions de votre choix qui soit non serialisable et acceptée par Oracle. Comment faire pour que Oracle ne laisse pas passer ce genre d'executions ?

Partie I : Organisation physique, Optimisation

Augmenter la taille de la table Clients à l'aide du script suivant:

BEGIN
FOR x in 1..50 LOOP
insert into C (nb, nom, prenom, type) select compteur.nextval, nomb, prenomb, type from b;
commit;
END LOOP;
END;
/

Calculer le nombre de blocs de données occupés par cette table et le nombre de n-uplets par bloc. Les commandes suivantes vous donnent des indications sur la position physique de chaque n-uplet de la table C:

select DBMS_ROWID.ROWID_TO_RESTRICTED (rowid,0) from c
/
select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) || '.' || DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID) from c
/

Consulter la table USER_SEGMENTS de la méta-base pour connaitre la taille en octets (attribut bytes) et le nombre de blocs (attribut blocks) du volume de stockage (segment) reservé pour cette table. Comparer la taille réservée pour la table a celle réellement occupée par les n-uplets.

Effectuer des suppressions de tuples de la table et vérifier l'évolution de la taille réellement occupée par les n-uplets de la table C et celle qui lui est réservée.

Créer une copie de la table C en spécifiant les critères d'allocation d'espace pour ce segment. Utiliser la clause STORAGE (voir doc en ligne). Vérifier et comparer, la fragmentation de l'espace alloué aux tables.

Optimisation :

Le but est de voir le plan d'exécution choisi par le SGBD. La prise en compte d'index peut améliorer le coût d'une exécution. Un index est intéressant à partir du moment ou il est sélectif, c.a.d. qu'une restriction du type att = val ne renvoie que très peu d'attribut (e.g. 1%). De plus, il n'est utile que quand la relation indexé est suffisamment volumineuse (> à quelques pages). Trouver un scénario basé sur une sélection par index sur la plus grosse table (qu'on grossira artificiellement si nécessaire). Ce scénario doit vous permettre de montrer (1) l'intérêt de l'index pour un attribut très sélectif, (2) l'handicap de l'index pour un attribut peu sélectif et (3) le surcoût en mise à jour (ou insertions) de ces index.

Jouer sur changement du mode d'optimisation d'oracle (voir doc en ligne) par la commande 

alter session set optimizer_mode = xxxxxx

Ou xxxx peut valoir :

Générer, expliquer et comparer les plans d'exécutions obtenus.

Remarque. En cas de besoin, vous pouvez désactiver un index ainsi : pour un test att = valeur, si attribut est de type varchar, faire attribut || ' ' = valeur (concaténation d'un espace). Si c'est un nombre, attribut + 0 = valeur.

Jointures

Créer un jeu de requêtes joignant plusieurs tables et faisant des sélections à divers endroits. Exécuter ces requêtes de différentes manières : par jointure 'plates', par imbrication avec in, par imbrication avec exists. Regarder, expliquer et comparer les plans d'exécutions générés.

A1 : Connexion à Oracle