optimisation de base de donnees oracle

OPTIMISATION DE BASE DE
DONNEES ORACLE
AHLOULLAY ABDELKADER
SOMMAIRE
•
•
•
•
•
INTRODUCTION
CONCEPTION DE L’APPLICATION
UTILISATION DE LA MEMOIRE
STOCKAGE DES DONNEES
REQUÊTES SQL
INTRODUCTION
L’optimisation 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.
CONCEPTION DE L’APPLICATION
• CONCEPTION EFFICACE DES TABLES
• DISTRIBUTION DES EXIGENCES
PROCESSEUR
La charge processeur devrait être planifiée
Exploitez la possibilité de transférer la charge processeur d’un
serveur vers un autre
Pour limiter les risques de verrouillage et de rollback, ainsi que les
conflits de demande de ressources processeur.
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.
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 d’un même fichier
sur plusieurs disques.
• Évaluation de l’utilisation des index
• Tablespaces gérés localement.
Pour éviter la plupart des problèmes de fragmentation de l’espace.
REQUÊTES SQL
• Une application bien conçue peut se heurter à des
problèmes de performances si les instructions SQL qu’elle
utilise sont mal construites.
• La clé dans l’optimisation 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, l’emplacement
physique des données n’est pas aussi important que leur
place logique dans la conception de l’application.
Génération de plan d’exécution
• La commande explain plan détermine le chemin d’accès
que la base de données utilisera pour satisfaire à une
requête.
• Cette instruction évalue le chemin d ‘exécution d’une
requête et palace les résultants de son évaluation dans une
table nommée PLAN_TABLE
Syntaxe de EXPLAIN PLAN
EXPLAIN PLAN
[SET statement_id=id]
[INTO table_name]
FOR sql_statement
• La première ligne de cette commande indique à la base de
données qu’elle va expliquer le plan d’exécution pour la
requête sans réellement l’exé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
• Interrogez la table PLAN_TABLE au moyen de la requête
suivante.
DEF id=id_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'
/
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)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
CONSTRAINT FK_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES SCOTT.DEPT (DEPTNO)
)
ALTER TABLE EMP
ADD CONSTRAINT PK_EMP
PRIMARY KEY (EMPNO)
NOT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
CREATE INDEX I_DEPTNO ON EMP(DEPTNO)
CREATE INDEX IN_IOB ON EMP (JOB)
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
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
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.
Clustered Join
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
5
4
1
3
2
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS (FULL), EMP (SCOTT)
TABLE ACCESS (BY INDEX ROWID), DEPT (SCOTT)
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.
Single Row by Cluster Join
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno = 7900;
6
5
2
1
4
3
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID), EMP
INDEX (RANGE SCAN), PK_EMP
TABLE ACCESS (BY INDEX ROWID), DEPT
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
6 Cette étape du plan définit cette instruction comme instruction SELECT.
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
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.
SELECT * FROM emp
WHERE job = 'CLERK'
UNION ALL
SELECT * FROM emp
WHERE deptno = 10
AND job <> '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.
6 Cette étape du plan définit cette instruction comme instruction SELECT.
SELECT dname, deptno
FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);
4
3
1
2
SELECT STATEMENT
FILTER
TABLE ACCESS (FULL), DEPT (SCOTT)
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.
SELECT dname, deptno FROM dept
WHERE NOT EXISTS
(SELECT deptno FROM emp
WHERE dept.deptno = emp.deptno);
4
3
1
2
SELECT STATEMENT
NESTED LOOPS (ANTI)
TABLE ACCESS (FULL), DEPT
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
Bibliographie
• KEVIN LONEY -MARLENE THERIAULT
ORACLE 9i DBA HANDBOOK ( Manage a Robust, HighPerformance Oracle Database)
• WWW.ORACLE.COM