En analysant le rapport AWR d'une des bases du client pour lequel je travaille, j'ai remarqué dans le TOP N des requêtes les plus consommatrices la requête suivante:
Cette requête s'exécute plusieurs milliers de fois par jour avec le plan d'exécution suivant:
On constate qu'un Full Table Scan a été effectué pour ne récupérer qu'une seule ligne. Le coût de chaque exécution s'élève à 7477 logical reads.
A priori, un index sur la colonne TO_PROCESS serait fort utile:
Avec l'index on passe à 4 logical reads...c'est du tuning de base.
Vous devez vous demander pourquoi écrire un article au sujet d'une requête aussi facile à optimiser.
Tout simplement parce que l'intérêt se trouve ailleurs que dans le simple fait de créer un index pour éviter un Full Table Scan.
En effet, la table RFX_NFY_EVENT contient plus de 1 million de lignes mais très peu de lignes ont le champ TO_PROCESS valant 1. Cette table contient une liste de traitements effectués (TO_PROCESS=0) ou à effectuer (TO_PROCESS=1), et bien sûr, l'essentiel des données sont à l'état "Effectué".
La requête est exécutée périodiquement par l'application pour récupérer les actionsà traiter.
On a donc crée un index qui ne sera utile que lorsque la table est requêtée avec la clause "TO_PROCESS=1" mais qui pourtant stock toutes les valeurs de la colonne, ce qui rend l'index trop volumineux pour notre besoin:
L'index fait environ 17Mo avec une hauteur de 3 pour plus de 1 millions d'entrées. La hauteur de 3 indique qu'il faut parcourir 3 blocks pour atteindre le bloc feuille (un bloc pour la racine de l'index, un bloc branche et un bloc feuille) auxquels il faut ajouter un accès au block de la table pour récupérer la ligne de la table qui nous intéresse. Donc chaque accès via cet index va coûter 4 logical reads, et c'est bien ce qu'on voit dans la colonne BUFFERS du plan d'exécution précédent: 3 logical reads pour l'opération INDEX RANGE SCAN c'est à dire l'accès au bloc feuille de l'index et 4 logical reads au total pour l'opération TABLE ACCESS BY INDEX ROWID qui correspond à l'accès au bloc de la table grâce au ROWID stocké dans le bloc feuille de l'index (je rappelle que les stats au niveau du plan d'exécution sont cumulatives).
Ce qui serait intéressant ce serait de pouvoir stocker dans notre index uniquement la valeur dont on a besoin dans notre requête, à savoir la valeur 1. Il existe pour cela une astuce expliquée notamment dans le livre de Tom Kyte Expert Oracle Database Architecture (chapitre 11) et dans un article du blog de Richard FOOTE.
Cette astuce consiste à créer un index fonction qui retourne NULL pour les valeurs qu'on ne souhaite pas indexer. Ainsi, comme dans Oracle les valeurs nulles ne sont pas indexées (sauf pour les index Bitmaps) on aura un index qui ne contiendra que les lignes qui nous intéressent. On obtientdra ainsi un index moins volumineux mais aussi moins haut (HEIGHT).
Créeons un index fonction pour la colonne TO_PROCESS qui retournera NULL pour les valeurs différentes de 1:
Lorsqu'on crée un index fonction, une colonne virtuelle (SYS_NC00009$) est crée mais aucune stat n'est collectée sur cette colonne. Il faut donc lancer un calcul de stats:
La colonne virtuelle a maintenant des stats collectées et on a bien une seule valeur distinct pour cette colonne ce qui est vrai puisque seule la valeur 1 ou NULL est retournée par l'index fonction.
Voyons ce que donne les stats sur l'index:
L'index ne fait plus que 7Ko et n'a qu'une hauteur de 1, ce qui signifie que l'accès au block feuille de cet index ne coutera qu'un seul logical read auquel il faudra ajouter un logical read pour l'accès à la table, soit un coût total de 2 logical reads.
Voyons si en exécutant la requête cette hypothèse est confirmée:
Effectivement, l'exécution de cette requête ne génère plus que 2 logical reads.
Vous noterez que dans la requête j'ai dû modifié la clause WHERE sur le champ PROCESS pour y ajouter la fonction DECODE afin de pouvoir utiliser l'index fonction:
select rowid, evt.ID_EVT, evt.ID_LS, evt.ID_FD, evt.ID_FS, evt.ACTION, evt.TO_PROCESS, evt.SCHED_ID FROM RFX_NFY_EVENT evt WHERE TO_PROCESS = 1 ORDER BY ACTION DESC, sched_id DESC, ID_LS ASC, ID_FD ASC, ID_FS ASC;
Cette requête s'exécute plusieurs milliers de fois par jour avec le plan d'exécution suivant:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.23 | 7477 |
| 1 | SORT ORDER BY | | 1 | 215 | 1 |00:00:00.23 | 7477 |
|* 2 | TABLE ACCESS FULL| RFX_NFY_EVENT | 1 | 215 | 1 |00:00:00.23 | 7477 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TO_PROCESS"=1)
On constate qu'un Full Table Scan a été effectué pour ne récupérer qu'une seule ligne. Le coût de chaque exécution s'élève à 7477 logical reads.
A priori, un index sur la colonne TO_PROCESS serait fort utile:
SQL> create index idx_TO_PROCESS on RFX_NFY_EVENT(TO_PROCESS) tablespace INDXRFX_M01;
Index created.
Plan hash value: 963767828
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 4 |
| 1 | SORT ORDER BY | | 1 | 215 | 1 |00:00:00.03 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| RFX_NFY_EVENT | 1 | 215 | 1 |00:00:00.03 | 4 |
|* 3 | INDEX RANGE SCAN | IDX_TO_PROCESS | 1 | 215 | 1 |00:00:00.03 | 3 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TO_PROCESS"=1)
Avec l'index on passe à 4 logical reads...c'est du tuning de base.
Vous devez vous demander pourquoi écrire un article au sujet d'une requête aussi facile à optimiser.
Tout simplement parce que l'intérêt se trouve ailleurs que dans le simple fait de créer un index pour éviter un Full Table Scan.
En effet, la table RFX_NFY_EVENT contient plus de 1 million de lignes mais très peu de lignes ont le champ TO_PROCESS valant 1. Cette table contient une liste de traitements effectués (TO_PROCESS=0) ou à effectuer (TO_PROCESS=1), et bien sûr, l'essentiel des données sont à l'état "Effectué".
La requête est exécutée périodiquement par l'application pour récupérer les actionsà traiter.
On a donc crée un index qui ne sera utile que lorsque la table est requêtée avec la clause "TO_PROCESS=1" mais qui pourtant stock toutes les valeurs de la colonne, ce qui rend l'index trop volumineux pour notre besoin:
SQL> analyze index idx_TO_PROCESS validate structure; Index analyzed. SQL> select name, btree_space, lf_rows, height from index_stats; NAME BTREE_SPACE LF_ROWS HEIGHT ------------------------------ --------------- --------------- --------------- IDX_TO_PROCESS 17143584 1180669 3
L'index fait environ 17Mo avec une hauteur de 3 pour plus de 1 millions d'entrées. La hauteur de 3 indique qu'il faut parcourir 3 blocks pour atteindre le bloc feuille (un bloc pour la racine de l'index, un bloc branche et un bloc feuille) auxquels il faut ajouter un accès au block de la table pour récupérer la ligne de la table qui nous intéresse. Donc chaque accès via cet index va coûter 4 logical reads, et c'est bien ce qu'on voit dans la colonne BUFFERS du plan d'exécution précédent: 3 logical reads pour l'opération INDEX RANGE SCAN c'est à dire l'accès au bloc feuille de l'index et 4 logical reads au total pour l'opération TABLE ACCESS BY INDEX ROWID qui correspond à l'accès au bloc de la table grâce au ROWID stocké dans le bloc feuille de l'index (je rappelle que les stats au niveau du plan d'exécution sont cumulatives).
Ce qui serait intéressant ce serait de pouvoir stocker dans notre index uniquement la valeur dont on a besoin dans notre requête, à savoir la valeur 1. Il existe pour cela une astuce expliquée notamment dans le livre de Tom Kyte Expert Oracle Database Architecture (chapitre 11) et dans un article du blog de Richard FOOTE.
Cette astuce consiste à créer un index fonction qui retourne NULL pour les valeurs qu'on ne souhaite pas indexer. Ainsi, comme dans Oracle les valeurs nulles ne sont pas indexées (sauf pour les index Bitmaps) on aura un index qui ne contiendra que les lignes qui nous intéressent. On obtientdra ainsi un index moins volumineux mais aussi moins haut (HEIGHT).
Créeons un index fonction pour la colonne TO_PROCESS qui retournera NULL pour les valeurs différentes de 1:
SQL> drop index idx_TO_PROCESS; Index dropped. create index idx_TO_PROCESS on RFX_NFY_EVENT( decode( TO_PROCESS,1,1,null)); Index created. SQL> select column_name,num_distinct 2 from all_tab_cols where table_name='RFX_NFY_EVENT'; Column Name NUM_DISTINCT ------------------------------ --------------- SCHED_ID 3123 EVENT_DATE 1164160 TO_PROCESS 2 ACTION 1 ID_FS 4102 ID_FD 3470 ID_LS 1259 ID_EVT 1180669 SYS_NC00009$ .
Lorsqu'on crée un index fonction, une colonne virtuelle (SYS_NC00009$) est crée mais aucune stat n'est collectée sur cette colonne. Il faut donc lancer un calcul de stats:
SQL> exec dbms_stats.gather_table_stats(user,'RFX_NFY_EVENT'); PL/SQL procedure successfully completed. SQL> select column_name,num_distinct 2 from all_tab_cols where table_name='RFX_NFY_EVENT'; Column Name NUM_DISTINCT ------------------------------ --------------- SYS_NC00009$ 1 ID_EVT 1180669 ID_LS 1259 ID_FD 3470 ID_FS 4102 ACTION 1 TO_PROCESS 2 EVENT_DATE 1164160 SCHED_ID 3123
La colonne virtuelle a maintenant des stats collectées et on a bien une seule valeur distinct pour cette colonne ce qui est vrai puisque seule la valeur 1 ou NULL est retournée par l'index fonction.
Voyons ce que donne les stats sur l'index:
SQL> analyze index idx_TO_PROCESS validate structure; Index analyzed. SQL> select name, btree_space, lf_rows, height from index_stats; NAME BTREE_SPACE LF_ROWS HEIGHT ------------------------------ --------------- --------------- --------------- IDX_TO_PROCESS 7996 1 1
L'index ne fait plus que 7Ko et n'a qu'une hauteur de 1, ce qui signifie que l'accès au block feuille de cet index ne coutera qu'un seul logical read auquel il faudra ajouter un logical read pour l'accès à la table, soit un coût total de 2 logical reads.
Voyons si en exécutant la requête cette hypothèse est confirmée:
select rowid,
evt.ID_EVT,
evt.ID_LS,
evt.ID_FD,
evt.ID_FS,
evt.ACTION,
evt.TO_PROCESS,
evt.SCHED_ID
FROM RFX_NFY_EVENT evt
WHERE decode(TO_PROCESS,1,1,null) = 1
ORDER BY ACTION DESC, sched_id DESC, ID_LS ASC, ID_FD ASC, ID_FS ASC;
Plan hash value: 963767828
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| RFX_NFY_EVENT | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | IDX_TO_PROCESS | 1 | 1 | 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EVT"."SYS_NC00009$"=1)
Effectivement, l'exécution de cette requête ne génère plus que 2 logical reads.
Vous noterez que dans la requête j'ai dû modifié la clause WHERE sur le champ PROCESS pour y ajouter la fonction DECODE afin de pouvoir utiliser l'index fonction:
WHERE decode(TO_PROCESS,1,1,null) = 1
C'est parfait, on gagne en nombre de lectures de buffer, en taille d'index et pour d'autres cas cela peut être interressant mais au niveau de la maintenance, ce n'est pas facile a expliquer pour des personnes qui viennent en TMA prendre la suite de la maintenance.
RépondreSupprimerExplications limpides.
RépondreSupprimerBonjour,
RépondreSupprimeroui c'est bien sauf si tu ne peux pas changer la requête SQL... (ou que le crét** de développeur refuse de changer un ligne de code).
J'ai eu le cas et j'ai partitionné la table sur le statut (en mettant bien sûr le row movement à enable). L'update sur le statut était un peu plus long, mais la requête ne scannait que la partition avec les traitements en attente.
NicK.