OPTIMISATION DE BASE DE DONNEES ORACLE

1
OPTIMISATION DE BASE DE DONNEES ORACLE

• AHLOULLAY ABDELKADER

2
SOMMAIRE

• INTRODUCTION
• CONCEPTION DE LAPPLICATION
• UTILISATION DE LA MEMOIRE
• STOCKAGE DES DONNEES
• REQUÊTES SQL

3
INTRODUCTION

• Loptimisation concerne toutes les applications
  de base de données.
• La plupart des problèmes de performances ne sont
  pas des symptômes isolés mais résultent plutôt de
  la conception de système.

4
CONCEPTION DE LAPPLICATION

• CONCEPTION EFFICACE DES TABLES
• DISTRIBUTION DES EXIGENCES PROCESSEUR
• La charge processeur devrait être planifiée
• Exploitez la possibilité de transférer la charge
  processeur dun serveur vers un autre
• Pour limiter les risques de verrouillage et de
  rollback, ainsi que les conflits de demande de
  ressources processeur.

5
UTILISATION DE LA MEMOIRE

• DEFINITION DE LA TAILLE DE LA SGA
• - SHARED_POOL_SIZE
• Taille du pool partagé.
• - DB_BLOCK_SIZE
• Taille de bloc de base de données.
• - DB_CACHE_SIZE
• La taille du Cache de tampons.
• Hit ratio est une mesure de la qualité de
  gestion des requêtes de données par le cache
  de tampons.

6
STOCKAGE DES DONNEES

• La façon dont la base stocke les données a
  également un impact sur les performances de
  requêtes.
• Pour améliorer les performances d E/S, Une
  solution RAID permet de distribuer les données
  dun même fichier sur plusieurs disques.
• Évaluation de lutilisation des index
• Tablespaces gérés localement.
• Pour éviter la plupart des problèmes de
  fragmentation de lespace.

7
REQUÊTES SQL

• Une application bien conçue peut se heurter à des
  problèmes de performances si les instructions SQL
  quelle utilise sont mal construites.
• La clé dans loptimisation SQL est réduire au
  minimum le chemin de recherche que la base
  utilise pour localiser les données
• Dans une base de données relationnelle,
  lemplacement physique des données nest pas
  aussi important que leur place logique dans la
  conception de lapplication.

8
Génération de plan dexécution

• La commande explain plan détermine le chemin
  daccès que la base de données utilisera pour
  satisfaire à une requête.
• Cette instruction évalue le chemin d exécution
  dune requête et palace les résultants de son
  évaluation dans une table nommée PLAN_TABLE

9
Syntaxe de EXPLAIN PLAN

• EXPLAIN PLANSET statement_ididINTO
  table_nameFOR sql_statement
• La première ligne de cette commande indique à la
  base de données quelle va expliquer le plan
  dexécution pour la requête sans réellement
  lexécuter.
• La deuxième ligne marque les enregistrements
  relatifs à cette requête dans la table PLAN_TABLE
  avec une valeur id dans la colonne Statement_Id.
• La requête à analyser suit le mot clé for

10

• Interrogez la table PLAN_TABLE au moyen de la
  requête suivante.
• DEF idid_of_explan_plan
• SELECT LPAD(' ', 2(level-1)) level
  '.' NVL(position,0) ' '
  operation ' ' options ' '
  object_name ' ' object_type ' '
• DECODE(id, 0, ' id 'statement_id' Cost
  'Position) "Query Plan"
• FROM plan_table
• START WITH id 0 AND statement_id 'id'
• CONNECT BY prior id parent_id
• AND statement_id 'id'
• /

11

• Schéma de la base
•  
• CREATE TABLE DEPT
• (
• DEPTNO NUMBER(2) NOT NULL,
• DNAME VARCHAR2(14) NULL,
• LOC VARCHAR2(13) NULL
• )
• ALTER TABLE DEPT
• ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
•  
• CREATE TABLE EMP
• (
• EMPNO NUMBER(4) NOT NULL,
• ENAME VARCHAR2(10) NULL,
• JOB VARCHAR2(9) NULL,
• MGR NUMBER(4) NULL,
• HIREDATE DATE NULL,
• SAL NUMBER(7,2) NULL,

12

• Full Table Scan
• EXPLAIN PLAN
• SET Statement_Id TEST
• FOR
• select from emp
•   
• 2 SELECT STATEMENT
• 1 TABLE ACCESS (FULL), EMP
•  
• 1- Cette opération extrait toutes les lignes de
  la table EMP en utilisant un balayage de
  table complet.
• 2- Cette étape du plan définit cette instruction
  comme instruction SELECT

13

• Single Row by Rowid
•  
• SELECT FROM emp
• WHERE ROWID 'AAAA7bAA5AAAA1UAAA'
•  
• 2 SELECT STATEMENT
• 1 TABLE ACCESS (BY USER ROWID), EMP
•  
• 1 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.1
• 2 Cette étape du plan définit cette instruction
  comme instruction SELECT. 1

14

• Single Row by Unique or Primary Key
•  
• SELECT
• FROM emp
• WHERE empno 7900
•  
• 3 SELECT STATEMENT
• 2 TABLE ACCESS (BY INDEX ROWID), EMP
• 1 INDEX (RANGE SCAN), PK_EMP
•  
• 1 Cette opération extrait plusieurs ROWID en
  balayant l'index PK_EMP (par ordre croissant
  des clés).
• 2 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 3 Cette étape du plan définit cette instruction
  comme instruction SELECT.
•  

15

• Clustered Join
•  
• SELECT FROM emp, dept
• WHERE emp.deptno dept.deptno
•  
• 5 SELECT STATEMENT
• 4 NESTED LOOPS
• 1 TABLE ACCESS (FULL), EMP (SCOTT)
• 3 TABLE ACCESS (BY INDEX ROWID), DEPT (SCOTT)
• 2 INDEX (UNIQUE SCAN), PK_DEPT (SCOTT)
•  
• 1- Cette opération extrait toutes les lignes de
  la table EMP en utilisant
• un balayage de table complet.
• 2- Cette opération extrait un ROWID unique via
  une consultation d'index de PK_DEPT
• 3- Cette opération extrait une ligne de la table
  DEPT sur la base de son ROWID.
• 4- Cette opération compare chaque ligne de la
  première source de lignes enfant avec toutes les
  lignes de la seconde, en joignant les paires de
  lignes répondant à la condition définie.
• 5- Cette étape du plan définit cette instruction
  comme instruction SELECT.

16

• Single Row by Cluster Join
•  
• SELECT FROM emp, dept
• WHERE emp.deptno dept.deptno
• AND emp.empno 7900
•  
• 6 SELECT STATEMENT
• 5 NESTED LOOPS
• 2 TABLE ACCESS (BY INDEX ROWID), EMP
• 1 INDEX (RANGE SCAN), PK_EMP
• 4 TABLE ACCESS (BY INDEX ROWID), DEPT
• 3 INDEX (UNIQUE SCAN), PK_DEPT
•   
• 1 Cette opération extrait plusieurs ROWID en
  balayant l'index PK_EMP
• (par ordre croissant des clés).
• 2 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 3 Cette opération extrait un ROWID unique via une
  consultation d'index de PK_DEPT.
• 4 Cette opération extrait une ligne de la table
  DEPT sur la base de son ROWID.
• 5 Cette opération compare chaque ligne de la
  première source de lignes enfant avec toutes les
  lignes de la seconde, en joignant les paires de
  lignes répondant à la condition définie

17

• ORDER BY on Indexed Column
•  
• SELECT
• FROM emp
• ORDER BY empno
•  
• 3 SELECT STATEMENT
• 2 SORT (ORDER BY)
• 1 TABLE ACCESS (FULL), EMP
•  
• 1 Cette opération extrait toutes les lignes de la
  table EMP en utilisant un balayage de table
  complet.
• 2 Cette opération renvoie sa source de lignes
  enfant triée sur la base des colonnes d'après la
  clause ORDER BY.
• 3 Cette étape du plan définit cette instruction
  comme instruction SELECT

18

• SELECT
• FROM emp
• WHERE job 'CLERK'
• OR deptno 10
•  
• 6 SELECT STATEMENT
• 5 CONCATENATION
• 2 TABLE ACCESS (BY INDEX ROWID), EMP
• 1 INDEX (RANGE SCAN), I_DEPTNO
• 4 TABLE ACCESS (BY INDEX ROWID), EMP
• 3 INDEX (RANGE SCAN), IN_IOB
•  
• 1 Cette opération extrait plusieurs ROWID en
  balayant l'index I_DEPTNO (par ordre croissant
  des clés).
• 2 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 3 Cette opération extrait plusieurs ROWID en
  balayant l'index IN_IOB (par ordre croissant des
  clés).
• 4 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 5 Cette opération effectue l'union non distincte
  de ses sources de lignes enfant.
• 6 Cette étape du plan définit cette instruction
  comme instruction SELECT.

19

• SELECT FROM emp
• WHERE job 'CLERK'
• UNION ALL
• SELECT FROM emp
• WHERE deptno 10
• AND job ltgt 'CLERK'
• 6 SELECT STATEMENT
• 5 UNION-ALL
• 2 TABLE ACCESS (BY INDEX ROWID), EMP
• 1INDEX (RANGE SCAN), IN_IOB
• 4TABLE ACCESS (BY INDEX ROWID), EMP
• 3INDEX (RANGE SCAN), I_DEPTNO
•   
• 1 Cette opération extrait plusieurs ROWID en
  balayant l'index IN_IOB (par ordre croissant des
  clés).
• 2 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 3 Cette opération extrait plusieurs ROWID en
  balayant l'index I_DEPTNO (par ordre croissant
  des clés).
• 4 Cette opération extrait une ligne de la table
  EMP sur la base de son ROWID.
• 5 Cette opération accepte deux ensembles de
  lignes et renvoie l'agglomération des deux en
  conservant tous les doublons.

20

• SELECT dname, deptno FROM dept
• WHERE deptno NOT IN
• (SELECT deptno FROM emp)
•  
• 4 SELECT STATEMENT
• 3 FILTER
• 1 TABLE ACCESS (FULL), DEPT (SCOTT)
• 2 TABLE ACCESS (FULL), EMP (SCOTT)
•  
• 1 Cette opération extrait toutes les lignes de la
  table DEPT en
• utilisant un balayage de table complet.
• 2 Cette opération extrait toutes les lignes de la
  table EMP en
• utilisant un balayage de table complet.
• 3 Cette opération extrait une source de lignes et
  en exclut
• certaines pour renvoyer les lignes acceptées.
• 4 Cette étape du plan définit cette instruction
  comme instruction SELECT.

21

• SELECT dname, deptno FROM dept
• WHERE NOT EXISTS 
• (SELECT deptno FROM emp
• WHERE dept.deptno emp.deptno)   
• 4 SELECT STATEMENT
• 3 NESTED LOOPS (ANTI)
• 1 TABLE ACCESS (FULL), DEPT
• 2 INDEX (RANGE SCAN), I_DEPTNO
•    
• 1 Cette opération extrait toutes les lignes de la
  table DEPT en utilisant un balayage de table
  complet.
• 2 Cette opération extrait plusieurs ROWID en
  balayant l'index
• I_DEPTNO (par ordre croissant des clés).
• 3 Cette opération compare chaque ligne de la
  première source
• de lignes enfant avec toutes les lignes de la
  seconde, en joignant les paires de lignes
  répondant à la condition définie
• 4 Cette étape du plan définit cette instruction
  comme instruction SELECT
•  

22
Bibliographie

• KEVIN LONEY -MARLENE THERIAULT
• ORACLE 9i DBA HANDBOOK ( Manage a Robust,
  High-Performance Oracle Database)
• WWW.ORACLE.COM