E - Optimisation
AWR ou Automatic Worload Repository d’Oracle est un référentiel qui stocke un historique des informations utiles pour l’optimisation. Il s’appuie sur un certain nombre de tables et de vues du dictionnaire de données.
Il existe depuis la version 10 , mais est le successeur des scripts UTLSTAT et des STATPACKs des précédentes versions.
AWR fournit des informations sous différentes formes et peut être utilisé soit à la console Enetrprise Manager) ou en SQL (voire en PLSQL grace aux packages AWR). il permet notamment d’avoir :
- des clichés (Snapshots) de consommation de ressource instantanés (sur lesquels il v’a s’appuyer)
- des charges par périodes de temps : Baselines
- des vues spécifiques du référentiel
- des rapports de charge
Parmi les statistiques Oracle qu’il récupère on trouve quantifiées :
- les attentes ( Wait events ) used to identify performance problems.
- les statistiques temporelles associésa process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
- l’historique de session ou ASH ( Active Session History )(ASH) statistics ( -> V$ACTIVE_SESSION_HISTORY )
- les statistiques transversales sur le système ( -> V$SYSSTAT )
- les sessions ( -> V$SESSTAT )
- l’utilisation des objets
- les ordres SQL très ‘gourmands’ ( Top SQL statements )
exemple dӎcran EM dur les ordres SQL les + consommateurs
 détail d’un ordre SQL obtenu à partir de l’écran précédent
Les utilitaires suivants d’Oracle s’appuient sur AWR :
- la console Enterprise Manager d’une manière générale,
- ADDM : Automatic Database Diagnostic Monitor
- l’assistant d’optimisation SQL : SQL Tuning Advisor
- et aussi les assistants sur l’UNDO et le stockage : Undo Advisor et Segment Advisor
Clichés
Des clichés de consommations de ressources (Workload snapshots) sont pris cycliquemente et automatiquement par Oracle. Le défaut est toutes les heures avec une rétention max de 7 jours.
On peut consulter le paramétrage courant dans la vue DBA_HIST_WR_CONTROL avec le SQL :
SQL> select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
--------- ----------------- ----------------- --------
179121004 00000 00:30:00.0 +00030 00:00:00.0 DEFAULT
et on peut modifier cette fréquence et cette rétention avec un package PLSQL dédié :
SQL>
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- divisé par 1440min -> 30j
interval => 30); -- en Minutes.
END;
/
Les informations spécifiques sur les Snapshot se trouvent dans la vue DBA_HIST_SNAPSHOT
exemple de cliché ADDM obtenu avec la console EM
Intervalles ( baselines )
Une baseline correspond a une période de temps entre 2 snapshots identifiés par leu ‘id’.
Ces baseliens sont gérées à la console ou grace à une proc PLSQL.
SQL> — un exemple en PLSQL
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 100,
end_snap_id => 500,
baseline_name => ‘journee critique du jeudi…’);
END;
/
Les principales vues du dictionnaires concernant AWR sont :
- DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
- DBA_HIST_BASELINE - Displays baseline information.
- DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
- DBA_HIST_SNAPSHOT - Displays snapshot information.
- DBA_HIST_SQL_PLAN - Displays SQL execution plans.
- DBA_HIST_SQL_STAT- Displays SQL stats.
- DBA_HIST_WR_CONTROL - Displays AWR settings.
exemple de requete donnant la consommation de ressources (triés par ordre de buffer gets décroissant) sur une période donnée
SQL> -- le parametre &1 est le nombre de jours en arrière
SELECT TO_char(min( sn.begin_interval_time ),'DD/MM') "debut",
substr(parsing_schema_name,1,5) "schema",
substr(module,1,8) "program",
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "Gets(Mo)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "Disk read(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "Ecoule(mn)",
sum(s.fetches_delta) "Fetch",
sum(s.rows_processed_delta) "Rows",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / sum(s.executions_delta) ) "BG/EXEC"
FROM dba_hist_sqlstat s, dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND sn.snap_id >= ( select min(snap_id) from dba_hist_snapshot
where trunc(begin_interval_time) = trunc(sysdate-&1)
group by trunc(begin_interval_time) )
and sn.snap_id <= ( select max(snap_id) from dba_hist_snapshot
where trunc(begin_interval_time) = trunc(sysdate-1)
group by trunc(begin_interval_time) )
GROUP BY parsing_schema_name,s.sql_id,plan_hash_value,module
HAVING sum(s.executions_delta) > 0
AND sum(s.elapsed_time_delta) > 0
ORDER BY 6 DESC
/
debut schem program Gets Disk Ecoule
(Mo) read(K) CPU(s) (mn) Fetch Rows EXEC BG/EXEC
---- ----- -------- ----- ------ ------ ------ ------ ----- ----- -------
26/05 HR RTSDGN@s 18.48 1.38 3524.41 59.16 13504 12622 13369 1382
26/05 FCS CORIOLIS 1.11 109.34 2473.28 41.99 0 0 2 553451
26/05 FCS CORIOLIS 290.53 39.22 1696.53 28.51 0 0 1 290525140
26/05 SGL SQL*Plus 44.64 1.04 1186.51 19.66 0 1 1 44642186
26/05 SGL SQL*Plus 44.45 21 1181.96 19.57 125 9644 63 705518
26/05 SPE5 SQL Deve 112.83 34.01 945.22 16.92 148 7340 4 28207411
26/05 SGL frmweb@s 243.82 71.52 912.43 17.1 158 814 101 2414094
26/05 SAP frmweb@s 45.9 5.12 869.8 16.64 11749 3373 8373 5482
26/05 APEX_ httpd@sl 27.63 176.54 588.4 11.2 0 1855 1855 14895
26/05 SYSTE Admin Co 0 .03 523.81 9.5 1068 1342 1062 2
Les Rapports AWR
Oracle fournir 2 scripts qui génèrent des rapports (texte ou HTML) :
– a lancer en tant que DBA…
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
Pour éviter une erreur il faut que pendant la fourchette de Snaphot la base ait toujours été active, sinon :
ERROR at line 1:
ORA-20200: The instance was shutdown between snapshots 4095 and 4229
ORA-06512: at line 42
Exemple de report :
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
PRDUN 2299686464 PRDUN 1 10.2.0.5.0 NO sr-app-2
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 32051 24-May-11 00:00:19 106 6.3
End Snap: 32227 27-May-11 15:00:16 1,052 29.6
Elapsed: 5,219.95 (mins)
DB Time: 2,906.26 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 2,176M 2,272M Std Block Size: 8K
Shared Pool Size: 1,312M 1,120M Log Buffer: 14,328K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 201,044.61 25,285.10
Logical reads: 31,842.47 4,004.78
Block changes: 1,028.07 129.30
Physical reads: 594.05 74.71
Physical writes: 60.26 7.58
User calls: 1,056.92 132.93
Parses: 233.69 29.39
Hard parses: 10.08 1.27
Sorts: 224.42 28.23
Logons: 0.27 0.03
Executes: 1,105.96 139.09
Transactions: 7.95
% Blocks changed per Read: 3.23 Recursive Call %: 76.34
Rollback per transaction %: 15.78 Rows per Sort: 25.31
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.96 Redo NoWait %: 100.00
Buffer Hit %: 98.53 In-memory Sort %: 100.00
Library Hit %: 97.54 Soft Parse %: 95.69
Execute to Parse %: 78.87 Latch Hit %: 99.92
Parse CPU to Parse Elapsd %: 54.52 % Non-Parse CPU: 94.34
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 77.95 70.57
% SQL with executions>1: 89.04 84.84
% Memory for SQL w/exec>1: 84.71 81.64
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 120,538 69.1
db file sequential read 38,327,591 21,072 1 12.1 User I/O
db file scattered read 3,364,177 7,381 2 4.2 User I/O
enq: TX - row lock contention 2,371 6,640 2801 3.8 Applicatio
RMAN backup & recovery I/O 201,829 6,622 33 3.8 System I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: PRDUN/PRDUN Snaps: 32051-32227
-> Total time in database user-calls (DB Time): 174375.4s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 143,838.3 82.5
DB CPU 120,537.6 69.1
parse time elapsed 15,483.2 8.9
hard parse elapsed time 14,190.6 8.1
PL/SQL execution elapsed time 5,629.5 3.2
inbound PL/SQL rpc elapsed time 4,184.3 2.4
RMAN cpu time (backup/restore) 2,564.6 1.5
Voir le détail du rapport awrrpt ici
L’indexation est un mécanisme d’optimisation extrêmement efficace et parfois négligé…ou méconnu, cet article devrait remettre les pendules a l’heure.
LE PRINCIPE est de stocker dans une structure annexe de la table, des entrées d’index (les clés) et les adresses des blocs de table correspondants. Cette structure doit être organisée de manière efficace pour se parcourir très rapidement.
L’index étant maintenu à jour (et équilibré !) lors des mises à jour de la table.
rem : un index peut être ajoputé et supprimé à tout moment. Un index peut porter sur des colonnes multiples ( concatenated index)
Plus d’infos dans DBA_INDEXES et DBA_TAB_COLUMNS.
Oracle propose un certain nombre de type d’index différents adaptés aux différnetes qualités de données et de requêtes.
Index standard ou B*tree
Comme leur nom l’indique , organisés en B*tree ou arbres équilibrés (B alanced …et pas B inaire !)
Les entrées d’index sont ordonnées dans un arbre hiérarchique équilibré (toutes les feuilles sont a la même distance / profondeur de la racine de l’arbre.
Les adresses des lignes de la tables associées sont des ROWID (identifiant physique unique de chaque ligne).
Voici un exemple de clés numériques rangées dans un B*tree (122 est la racine, les + petits a gauche, les + grands à droite)
EFFICAce : pour stocker des clé primaires ou des clés très sélectives (très peu redondantes). Pour faire des tris (puisque les données … sont déjà triées ! )
Index bitmap
UN index bitmap est organisé comme un tableau a 2 dimensions :
- autant de lignes que de valeurs distinctes dans la colonne
- autant de colonnes que lignes dans la tables
Ce tableau peut être compressé si tant est que les lignes soient relativement ordonnées ou regroupées (clustering factor) ==> peu de place utilisée, décompression en RAM. Produit une liste de Rowids.
EFFICACE : pour les données très redondantes donc avec peu de valeurs distinctes : nb valeurs < 1% nb lignes ou nb valeurs répétées > 100 ?
Pour les requêtes complexes avec des AND et des OR, qui utilisent PLUSIEURS index bitmap.
rem : il existe également des BITMAP JOIN INDEX qui stocke le ROWID des lignes correspondantes de la table jointe en plus du ROWID de la table primaire…
Index organized tables ( oracle IOT )
les IOT sont des tables dont les données sont triées suivant la clef primaire. Toutes les colonnes d’une ligne de la table sont stockées dans la feuille de l’index de clef primaire, en plus de la PK elle même. Les acces à la table APRES l’index deviennent inutiles…
Syntaxe : CREATE TABLE <nom_table> (def_colonnes…,
CONSTRAINT <constraint_name>
PRIMARY KEY (<colonne pk>))
ORGANIZATION INDEX;
EFFICACE : pour gagner de la place et diviser les IO par 2 ! …mais sur des tables assez ’statiques’
Index partitionnés
Les index sont regroupés suivant certains critères dans différentes parttions. Les règles de partitionnement peuvent etre identiques à celles de l atable indexée ou spécifiques à l’index
L’optimisation est un processus intelligent complexe qui vise à améliorer les performances du système application / réseau / OS / SGBD (système lui-même complexe…).
Les gains peuvent être soit en terme de temps de réponse utilisateur, soit en consommation de ressources.
Un système optimisé doit pouvoir supporter de manière linéaire une montée en charge, soit en terme de volume, soit en terme de nombre d’utilisateurs. En d’autres termes la consommation de ressources CPU, mémoire, E/S doit être proportionnelle à la montée en charge, le système est alors dit ‘SCALABLE’.
Dans la pratique un système non optimisé va consommer de manière inflationiste et s’effondrer.
Il existe 2 approches d’optimisation complémentaires :
- préventive
qui couvre tous les cycles de dévelopement du S.I ( conception, developpement, production, maintenance)
- curative
qui intervient principalement en maintenance du système en production
Il va donc falloir ORGANISER l’optimisation à ces différents niveaux.
Rappel : Principe valable dans toutes les phases du S.I : ‘LESS IS MORE’ ou KISS : Keep It Simple Stupid !
Optimisation de l’architecture technique
- privilégier les architectures 3 tier
- simplifier les couches logicielles
Attention aux couches d’abstraction (PDO, jdbc, odbc), aux pilotes génériques plutôt que natifs, aux machines virtuelles, aux clients lourds, aux applets java, …
- utiliser SSO et plus généralement d’annuaire pour les users, mais aussi les bases, les machines, les services réseau, …
Optimisation du modèle : tables
- NORMALISATION RAISONNABLE
pas de table ‘universelle’ unique comme dans les systèmes de gestion de fichiers d’antan (trop de colonnes, trop de redondance de données)
- DENORMALISATION INTELLIGENTE
à l’inverse pas de modèle ultra normalisé, qui donnerait une pléthore de tables non redondantes mais nécessitera de nombreuses jointures pour ramener une info pertinente. mise en place de intelligente
- REDONDANCE CALCULEE
Il est plus efficace parfois de stocker les résultats de calculs complexes dans les tables, lors des mises à jour, plutôt que de les calculer ‘à la volée’ lors des consultations.
Optimisation du modèle : index
- CHOISIR LE BON TYPE D’INDEX (voir mon article : les différents types d’index Oracle )
- NE PAS OUBLIER LES CLES primaires et étrangères 1clé = 1 index) !!!!
- TROP D’INDEX TUE L’INDEX
1) en terme de volume : si on indexe toutes le colonnes on double le volume de la base !!!
2) parce que lors des mises à jour de la table tous les index doivent être maintenus et cela coute desressources
- CHOISIR LES BONNES COLONNES à indexer
en général les colonnes qui vont servir de critères de recherches fréquents (qui apparaissent dans le clause WHERE du SELECT)
- INDEXER LES FONCTIONS
corrolaire du précédent si une fonction apparait dans la clause WHERE …on peut l’indexer aussi
- ELARGIR LES INDEX… pour en faire des données
si Oracle trouve dans l’index toutes les données de la SELECT LIST…il ne prend pas la peine de consulter les tables !
Conseils généraux d’optimisation du développement Oracle
- utiliser les types de données appropriés et les fonctions associées ( par exemple ne pas réinventer le format DATE…), utiliser des VARCHAR plutôt que des CHAR, se méfier des LOBs
- implémenter un maximum de contraintes d’intégrité au niveau table (factorise le code et évite le traitement de données non fiabilisées)
- utiliser des curseurs : evite de re interpréter totalement les ordres SQL (dissocie la phase de parsing de la phase d’execution qui est seule rejouée )
- utiliser des règles de casse dans les ordres SQL (évite le SQL rewriting ou reparsing)
- utiliser des VRAIS ordres SQL paramétrés (sql_prepare + sql_bind)
- vérifier les migrations dev -> test , test -> prod (pertes d’index possibles)
- éviter les vues de vuesjointes à des vues. Simplifie le codage, mais masque totalement la complexité sous jacente des requêtes. Solution : créer des vues directement sur les tables !
- encapsuler les ordres SQL dans du PLSQL (traitement par bloc et précompilation)
Conseils généraux de paramétrage de l’instance
L’instance de la base de données possède un certain nombre de paramètres d’initialisation , dont certains ont un impact fort sur les performances. Nous allons citer les principaux :
- COMPATIBLE = <no_version_oracle>
permet de fonctionner dans un mode antérieur, notamment si des spécifités de l’optimiseur avaient été utilisés. A l’inverse peut dégrader les performances dans le cas contraire.
- SGA_TARGET = <montant_memoire_max>
laisse Oracle organiser les sous-zones de cache de son cache principal (la SGA). Il faut bien sûr donner une valeur compatible avec votre RAM et prenant en compte le fait que Oracle n’est pas tout seul…
- UNDO_MANAGEMENT = AUTO
gestion des espace de Rollback. Ne pas changer cette valeur !
Dimensionnement des fichiers et espaces logiques
- REDOLOG files
de 100MO a qq GO, devarient switcher toutes les 1/2 heures en gros
- TABLESPACES applicatifs
chaque application devrait avoir (au moins) son tbs spécifique, avec une volumétrie estimée
- TABLESPACE temporaire
- UNDO : gérer la taille des transactions
doit être dimensionné en fonction des besoins de la zone de tri :
L’exécution d’un ordre SQL
Les principales étapes mises en jeu pour l’exécution d’un ordre SQL sont les suivantes :
1. Vérification syntaxique (présence et ordre des mots clés,…) réalisée par le ‘parser’
2. Vérification sémantique (est ce les noms des objets sont corrects, les droits , etc)
3. optimisdation de la requête et recherche du meilleur plan d’exécution
4. execution effective de la requête et rapatriement des lignes du résultat
Nous allons nous focaliser ici sur l’étape d’optimisation.
L’optimiseur statistique
Pour exécuter une requête SQL de la manière la + efficace, le moteur d’Oracle utilise un optimiseur, qui détermine un plan / chemin d’exécution jugé optimal.
A partir de la version 9, cet optimiseur est généralement basé sur le coût de la requête ( COST BASED ) et s’appuie pour ce faire sur des statistiques qui quantifient la qualité des données et des index.
remarques :
- historiquement dans les versions précédentes, l’optimiseur s’appuyait sur des règles internes figés (Rule Based Optimizer ou RBO).
- l’optimiseur pouvant donnere des résultats très différents d’une version à une autre, il est possible de conserver un fonctionnement de l’optimiseur d’une version précédente grâce au paramètre OPTIMIZER_FEATURES_ENABLE
SQL> ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE=10.0.0;
L’optimiseur effectue plusieurs taches :
Phase 1 : Réécriture de la requête (QUERY TRANFORMATION ou QUERY REWRITE)
Plusieurs techniques peuvent être utilisées pour transformer / simplifier la requête avant la recherche du plan d’exécution :
- fusion des vues ( view merging )
- insertion de prédicate ( predicate pushing )
- extraction des sous-requêtes ( subquery unnesting ) : les sous-requêtes sont transformées en jointures…
- utilisation de vues matérialisées ( query rewrite with MVs) : si un SELECT est proche d’une vue matérialisée, c’est le résultat de la vue matérialisée (stocké / pré calculé) qui est utilisé
Les statistiques sur les objets
on y trouve par exemple des :
- statistiques sur les Tables : nb de lignes, nb de blocs, lg moyenne de lignes
- statistiques sur les Colonnes : (nb de valeurs distinctes, nb de valeurs NULL, histogramme de distribution des valeurs
- statistiques sur les Index : nb de feuilles, niveau (level) ou profondeur des index, facteur de grappe ( clustering factor )
- statistiques sur le Système d’exploitation : performance et utilisation des E/S, performance et utilisation du CPU
Ces statistiques sont disponibles dans le dictionnaire notamment dans les tables suivantes :
- DBA_TABLES
- DBA_TAB_STATISTICS
- DBA_TAB_COL_STATISTICS
- DBA_TAB_HISTOGRAMS
- DBA_INDEXES
- DBA_IND_STATISTICS
On peut par exemple récuperer les statitiques des tables de SCOTT ainsi :
SQL> select table_name, num_rows, blocks, empty_blocks,
avg_row_len, sample_size, last_analyzed
from dba_tab_statistics
where owner='SCOTT';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
---------- -------- ------ ------------ ----------- ----------- ------------------
BONUS 0 0 0 0 0 07/11/2008 16:05:46
DEPT 4 5 0 20 4 07/11/2008 16:05:46
DUMMY 1 5 0 2 1 07/11/2008 16:05:46
EMP 14 5 0 37 14 07/11/2008 16:05:46
SALGRADE 5 5 0 10 5 07/11/2008 16:05:46
TESTECB 5376 80 0 50 5376 09/11/2008 04:06:04
On peut aussi vérifier la dernière mise à jour de ces infos dans les vues USER_TABLES ou DBA_TABLES :
SQL> select table_name,last_analyzed
from user_tables;
TABLE_NAME LAST_ANALYZED
------------ -------------------
DD 24/08/2009 22:00:14
DD_DBA_VIEWS 28/09/2008 12:20:31
Collecte automatique des statistiques
Les stats sont automatiquement collectés via le job GATHER_STATS_JOB. et ce pour tous les objets qui ont soit des stats manquantes soit des stats obsoletes.
CE Job est créé (normalement!) automatiquement lors de la création de la base, et doit (normalement) s’exécuter pendant la fenêtre de maintenance de la base (par défaut chaque nuit de 10H à 6H du mat).
On peut vérifier que ce job est présent et s’exécute correctement avec la console em ou grid, dans l’onglet ‘ Administration ‘ rubrique ‘ gestion des statistiques ‘ ou en SQL :
SQL> select owner,job_name,job_creator
from dba_scheduler_jobs;
OWNER JOB_NAME JOB_CREATOR
----- --------- -----------
SYS GATHER_STATS_JOB SYS
SYS AUTO_SPACE_ADVISOR_JOB SYS
et si on veut des infos plus précises sur les récentes exécutions du JOB on regarde la vue des LOG de JOBs : dba_scheduler_job_log. Ca nous donne
SQL> select LOG_ID, LOG_DATE, JOB_NAME, STATUS
from dba_scheduler_job_log
where job_name like '%STAT%';
LOG_ID LOG_DATE JOB_NAME STATUS
------ --------------------------------- ---------------- ---------
10198 27/04/2010 22:27:57.812343 +02:00 GATHER_STATS_JOB SUCCEEDED
10479 17/05/2010 22:11:36.792538 +02:00 GATHER_STATS_JOB SUCCEEDED
10465 13/05/2010 22:02:28.469371 +02:00 GATHER_STATS_JOB SUCCEEDED
10165 23/04/2010 22:26:20.788045 +02:00 GATHER_STATS_JOB SUCCEEDED
10314 04/05/2010 22:11:01.983675 +02:00 GATHER_STATS_JOB SUCCEEDED
remarque : il se peut que toutes les LAST_ANALYZED ne soient pas à la même date, apparemment Oracle ne fait un COMPUTE STATISTIC que sur les tables qui le nécessitent…
Les différents stratégies d’optimisation d’ Oracle ( OPTIMIZER GOAL )
L’optimiseur a 2 stratégies au choix :
- minimiser les ressources pour obtenir TOUTES les leslignes ( best throughput)
- minimiser le temps de réponse pour obtenir LA PREMIER ligne de résultat ( best response time)
Le choix de la stratégie peut être configuré à 3 niveaux :
- au niveau base ( ALTER SYSTEM SET OPTIMIZER_MODE = … )
- au niveau session ( ALTER SESSION SET OPTIMIZER_MODE= … )
- au niveau de l’ordre SQL ( HINT )
Les différentes valeurs de OPTIMIZER_MODE sont les suivantes :
FIRST_ROWS (déconseillé par Oracle)
FIRST_ROWS_n (avec n= 1, 10, 100, ou 1000) -> temps réponse minimal
ALL_ROWS -> ressources minimales (c’est de défaut !)
Voici quelques exemples :
SQL> — changement au niveau base DYNAMIQUEMENT (sans restart instance)
SQL> alter system set optimizer_mode =first_rows_10 scope=memory
SQL> — changement au niveau session
SQL> alter session set optimizer_mode =all_rows
SQL> — et un HINT qui force l’optiseur au sein même d’un ordre SQL
SQL> select /*+ ALL_ROWS */ * from scott.emp
rem : ATTENTION !! les erreurs de syntaxe dans les HINTs sont ignorées
SQL> select /*+ TURLUTUTU */ * from scott.emp;
SQL> …
SQL> 14 lignes ramenées
Il est possible de voir globalement le temps écoulé lors de l’exécution d’une requête SQL grace à l’option SQL*Plus ‘ SET TIMING ON’
Il est possible de visualiser ‘manuellement’ le plan d’exécution d’une requête SQL particulière grace à la commande ‘explain plan’.
Cette commande utilise une table système d’oracle, nommée PLAN_TABLE qui stocke les ligne correspondant au plan d’exécution choisi par l’optimiseur.
il existe un package PLSQL fourni par Oracle ( DBMS_XPLAN ) qui permet de visualiser le résultat formatté du plan d’exécution.
Voir l’exemple ci-après :
Se connecter à SH et lister les quantités et montant total des ventes par produitson
SQL> connect SH/SH
SQL> set timing on
SQL> select prod_name, sum(quantity_sold), sum(amount_sold)
from products p, sales s
where p.prod_id= s.prod_id
group by p.prod_name
71 rows selected.
Elapsed: 00:00:01.12
SQL> explain plan for
select prod_name, sum(quantity_sold), sum(amount_sold)
from products p, sales s
where p.prod_id= s.prod_id
group by p.prod_name
SQL> -- on configure un peu l'affichage
SQL> set pagesize 30
SQL> set linesize 150
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 504757596
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 4260 | 558 (14)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 71 | 4260 | 558 (14)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 4320 | 557 (14)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 553 (14)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 1152 | 553 (14)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL| | 918K| 14M| 498 (4)| 00:00:06 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 14M| 498 (4)| 00:00:06 | 1 | 28 |
| 7 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
Une vue matérialisée (VM) est un moyen simple de créer une vue physique d’une table. Comme son nom l’indique et à la différence d’une vue standard, les données sont dupliquées.2 utilisations principales :
On l’ utilise à des fins d’optimisation de performance, lorsque le select associé est particulièrement complexe ou lourd, la complexité du SELECT est alors déporté en amont du SELECT sur la VM
ou pour faire des réplications de table, sur un autre schema ou dans une base de secours ou d’infocentre.
Les VMs sont également particulièrement utiles dans le domaine des entrepôts de données (datawarehouse) ou il est fréquent de faire des requêtes complexes sur des objets volumineux.
Utilisation et raffraichissement
La ‘fraicheur’ des données de la VM dépend des options choisies. Le décalage entre les données de la table maître et la VM peut être nul (raffraichissement synchrone) ou d’une durée planifiée : heure, jour,etc.
Suivant le contexte il existe différents types de VMs possibles : sur clé primaire, rowid, et plus ou moins complexes : avec fonctions aggrégées, sous requêtes, jointures, etc.
Bien que séduisants ces objets devront être utilisés avec parcimonie et à bon escient. Il pourra être utile de faire des tests de refresh (complete et fast), de bien peser les volumétries, doser les fréquence de refresh en fonction des besoins réels, etc.
Si on n’a pas l’option “réplication avancée” d’Oracle (Oracle Advanced replication option) les VM ne pourront être utilisées qu’en lecture seule
Pour plus d’informations sur les modalités de création / raffraichissement des VMs voir mon article complet sur les Vues Matérialisées d’Oracle
Syntaxe minimale
SQL > CREATE MATERIALIZED VIEW MV1
AS SELECT * FROM scott.emp
créé une vue matérialisée simple, copie conforme de EMP de SCOTT, avec par défaut un raffraîchissement ? la demande sur clé primaire.
Par défaut le contenu de la VM est initialisé, la VM est remplie, lors de sa création. Il est possible de crééer une VM vide avec l’otion BUILD DEFERRED (le défaut est BUILD IMMEDIATE)
Le CREATE créé aussi de manière transparente une table de même nom que la vue matérialisée et une clé primaire.
Vues matérialisées et QUERY REWRITE
Une vue matérialisée peut être utilisée directement : on fait un SELECT sur le nom de la VM, ou implicitement suite à un SELECT sur la (les) table(s) maitre(s).
C’est ce qu’on appelle la technique de réécriture automatique de requete (QUERY REWRITE).
Lorque l’optimiseur reconnait une reqête sur une table proche de la requete qui a servi à la construction de la VM, si le serveur est correctement configuré c’est la vue materialisée qui sera utilisée en lieu et place de la table accédée par le users…
Pour que ce mécanisme soit opérationnel il faut que la VM ait été créée avec l’option QUERY REWRITE
SQL> CREATE MATERIALIZED VIEW MV1
ENABLE QUERY REWRITE AS
SELECT …
Les VMs peuvent être utilisés en QUERY REWRITE sur :
SELECTCREATE TABLE … AS SELECTINSERT INTO … SELECT- sous requete d’UPDATE
- SELECT avec operations ensemblistes :
UNION, UNION ALL, INTERSECT, et MINUS
L’activation du QUERY REWRITE dépend de certains paramètres :
- activation lors du
CREATE ou ALTER des MV
- initialization du parametre d’instance
QUERY_REWRITE_ENABLED
Hints REWRITE et NOREWRITE dans les ordres SQL- niveau d’intégrité du REWRITE
- Dimensions de la table et contraintes d’intégrité
Quelques exemples :
SQL> CREATE MATERIALIZED VIEW VM1µ
ENABLE QUERY REWRITE AS...
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SQL> — et un HINT…un !
SQL> SELECT ….,…, /*+ NOREWRITE */
FROM ma_table
WHERE …
SQL> SQL> SELECT ….,…, /*+ REWRITE */
FROM mon_autre_table
WHERE …
|