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