samedi 22 décembre 2012

Index Bitmaps et problèmes de locks

Si vous utilisez des index bitmap sur une table pour laquelle de nombreuses mises à jour sont effectuées il est fort probable que vous rencontriez des problèmes de locks voir de dead locks. Les index bitmaps sur les bases de type OLTP constituent la 2ème source des problèmes de dead locks. La première étant lié aux clés étrangères non indexées.

Dans cet article on va essayer de comprendre pourquoi les index bitmaps sont sources de locks.

Tout d’abord, rappelons à quoi servent les index bitmaps.
Les index bitmaps constituent une fonctionnalité d’Oracle disponible uniquement dans l’Enterprise Edition. Si vous utilisez une Standard Edition vous ne pourrez pas utiliser les index bitmaps.

Ces index sont généralement utilisés sur des colonnes qui sont peu sélectives c'est-à-dire qui ont un relativement faible nombre de valeurs distinctes. Leur principal avantage est qu’ils peuvent être mergés très rapidement avec des index bitmaps sur d’autres colonnes, c’est pourquoi ils sont très efficaces sur des requêtes avec des combinaisons de critères. De plus ils sont très compacts par rapport aux index B-Tree et prennent donc moins de place.

Le fait que les index bitmap peuvent causer des locks sur plusieurs lignes d’une table s’explique par la manière dont ces index sont structurés. Contrairement aux index B-Tree pour lesquels une entrée d’index est associée à un seul ROWID de la table, les index Bitmap ont une entrée d’index qui pointe vers plusieurs ROWIDs. Lorsqu’on crée un index BITMAP sur la colonne OBJECT_TYPE, Oracle crée un bitmap pour chaque valeur distincte de la colonne. Chaque bitmap contient un bit pour chaque ligne de la table.

Exemple :
create bitmap index idx_bmp_t1 on t1(object_type);

L’index bitmap crée ci-dessus peut être représenté de la manière suivante :
value/Rowid    1    2    3    4    5    6    7    8    9    10
INDEX            0    0    0    0    1    0    0    0    0     1
TABLE            1    0    0    1    0    1    0    0    0     0            
SYNONYM     0    1    0    0    0    0    1    0    0     0
SEQUENCE   0    0    1    0    0    0    0    1    1     0

On voit que la ligne 5 a la valeur INDEX, alors que les lignes 1, 4 et 6 ont la valeur TABLE.

Lorsque la colonne pour laquelle existe un index Bitmap est modifiée pour une ligne donnée (via un update, insert ou delete), Oracle ne peut locker uniquement le bit correspondant à la ligne modifiée. Il lock tout le bitmap et donc par le même effet toutes les lignes adressées par le bitmap. Dans mon exemple je n’ai mis que 10 lignes mais ça peut être plusieurs centaine de milliers de lignes.

Donc si une autre session au même moment modifie une autre ligne de la table impactant la même entrée d’index elle risque fortement d’être bloquée par le lock précédent.

Illustrons cela avec un exemple.
Tout d’abord on crée une table T1 avec une colonne OBJECT_NAME et une colonne OBJECT_TYPE puis on crée un index bitmap sur la colonne OBJECT_TYPE
create table t1
as select object_name,object_type from dba_objects where 1=2;

create bitmap index idx_bmp_t1 on t1(object_type);

Dans la même session on insert une ligne dans la table T1 de type TABLE :
-- session 1
SQL> insert into t1 values ('TOTO','TABLE');

1 row created.

Dans une autre session on crée une 2ème ligne dans la table avec également OBJECT_TYPE à « TABLE » :
-- session 2
SQL> insert into t1 values ('TITI','TABLE');

Cette fois la session est lockée car la valeur « TABLE» correspond à l’entrée d’index dont le bitmap a été locké par la session 1. La session 2 doit attendre que la session 1 termine sa transaction pour pouvoir effectuer son INSERT. Avec un index B-Tree on n’aurait pas eu ce genre de problème car on aurait eu une entrée d’index pour chaque ligne.

Si à l’inverse une session 3 effectue un INSERT dans la table T1 avec par exemple la valeur « INDEX » celle-ci ne sera pas bloquée par le lock de la session 1 car il s’agit ici d’une autre entrée d’index et donc d’un autre bitmap :
-- session 3
SQL> insert into t1 values ('TUTU','INDEX');

1 row created.

Les updates sont encore plus bloquants car ils lockents 2 entrées d’index et donc 2 bitmaps : l’ancienne valeur et la nouvelle valeur.

Dans notre exemple précédent on effectue un commit sur toutes les sessions pour libérer tous les locks puis dans la session 1 on effectue l’update suivant :
-- session 1
SQL> update T1 set object_type = 'INDEX' where object_name='TOTO';

2 rows updated.

L’update ci-dessus lock le bitmap pour l’entré d’index « INDEX » mais aussi le bitmap pour l’entrée d’index « TABLE ». Si on insert dans une autre session une ligne avec une valeur TABLE et une ligne avec une valeur INDEX les 2 inserts seront blockés par l’update précédent :
-- session 2
SQL> insert into t1 values ('TATA','TABLE');

-- session 3
SQL> insert into t1 values ('TYTY','INDEX');

Un commit dans la session 1 et les session 2 et 3 sont automatiquement débloquées.

CONCLUSION :
Les index Bitmaps ont l’inconvénient de causer des locks sur un grand ensemble de lignes même si une seule ligne est modifiée.
Voilà pourquoi les index bitmap sont inappropriés pour les bases de type transactionnel. On utilise ces index essentiellement dans les bases de type décisionnel. Il est possible d'utiliser des index bitmap sur des bases transactionnelles à conditions que ce soient des tables pour lesquelles les mises à jours sont très très peu fréquentes.

vendredi 17 août 2012

count(1) VS count(*)

J'ai entendu un jour une discussion entre deux développeurs où l'un tentait d'expliquer à l'autre que pour compter les lignes d'un table il était préférable, d'un point de vue performance, de faire un COUNT(1) plutôt qu'un COUNT(*).D'après lui le COUNT(1) était plus rapide car il passerait par l'index alors que le COUNT(*) génèrerait un Full Table Scan.

On est typiquement ici dans un mythe bien ancré dans l'esprit de beaucoup d'utilisateurs des bases de données Oracle (développeurs voir même des DBAs).

Pour prouver que les deux manières de compter les lignes ont des performances identiques il suffit d'exécuter ces deux requêtes et de regarder leur plans ainsi que leurs stats d'exécutions:
 SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

SQL> select count(1) from t1;

  COUNT(1)
----------
     80034

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 3724264953

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.12 |    1142 |   1139 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.12 |    1142 |   1139 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |  80034 |  80034 |00:00:00.10 |    1142 |   1139 |
----------------------------------------------------------------------------------------------

SQL> select count(*) from t1;

  COUNT(*)
----------
     80034

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 3724264953

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.11 |    1142 |   1139 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.11 |    1142 |   1139 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |  80034 |  80034 |00:00:00.09 |    1142 |   1139 |
----------------------------------------------------------------------------------------------

 Que ce soit avec COUNT(1) ou COUNT(*) le plan d'exécution est identique et les 2 requêtes génèrent le même nombre de logical reads à savoir 1142.

Ce qu'on constate aussi c'est que pour les 2 requêtes l'optimiseur a choisi de faire un Full Table Scan alors que j'ai bien crée un index  IDX_T1 sur cette table. L'index étant plus petit que la table on peut se dire qu'on génèrerait moins de logical reads en passant par l'index, n'est-ce pas? Alors pourquoi ignorer l'index?

La raison se situe dans le fait que le champ indexé OBJECT_NAME est NULLABLE, or les valeurs nulles ne sont pas référencées dans un index B-TREE. L'optimiseur ne peut donc pas se baser sur cet index pour compter le nombre de lignes de la table.

Si l'index IDX_T1 avait été un index BITMAP, le CBO aurait pu choisir cet index pour le comptage du nombre de lignes car dans un index BITMAP les valeurs nulles sont indexées.
La preuve:
SQL> drop index idx_t1;

Index dropped.

SQL> create bitmap index idx_bmp_t1 on t1(object_name);

Index created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     80034

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

select count(*) from t1

Plan hash value: 2708570690

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |      1 |00:00:00.09 |     313 |    305 |
|   1 |  SORT AGGREGATE               |            |      1 |      1 |      1 |00:00:00.09 |     313 |    305 |
|   2 |   BITMAP CONVERSION COUNT     |            |      1 |  80034 |  49108 |00:00:00.08 |     313 |    305 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BMP_T1 |      1 |        |  49108 |00:00:00.05 |     313 |    305 |
---------------------------------------------------------------------------------------------------------------

L'index BITMAP est bien utilisé et le nombre de logical reads est passé de 1142 à 313.

Supprimons cet index BITMAP puis testons ce que donne le plan lorsqu'on crée une contrainte NOT NULL sur la colonne OBJECT_NAME:
SQL> drop index idx_bmp_t1;

Index dropped.

Elapsed: 00:00:00.01
SQL> create index idx_t1 on t1(object_name);

Index created.

SQL> alter table t1 modify object_name not null;

Table altered.

SQL> select count(1) from t1;

  COUNT(1)
----------
     80034

Plan hash value: 2101382132

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |      1 |00:00:00.08 |     401 |    393 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |      1 |00:00:00.08 |     401 |    393 |
|   2 |   INDEX FAST FULL SCAN| IDX_T1 |      1 |  80034 |  80034 |00:00:00.06 |     401 |    393 |
---------------------------------------------------------------------------------------------------

SQL> select count(*) from t1;

  COUNT(*)
----------
     80034

Plan hash value: 2101382132

------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |      1 |00:00:00.04 |     401 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |      1 |00:00:00.04 |     401 |
|   2 |   INDEX FAST FULL SCAN| IDX_T1 |      1 |  80034 |  80034 |00:00:00.02 |     401 |
------------------------------------------------------------------------------------------

Pour les 2 requêtes le CBO a bien choisi le même plan avec accès indexé. Il peut faire ce choix car la contrainte NOT NULL lui indique bien que toutes les valeurs de la colonne se trouvent dans l'index IDX_T1. Vous noterez que l'optimiseur a opté pour une opération de type INDEX FAST FULL SCAN et non pas INDEX FULL SCAN. La différence réside dans le fait que dans l'INDEX FAST FULL SCAN  tous les blocks de l'index sont récupérés du disque en accès multi-blocks (équivalent au Full Table Scan) alors que dans le cas de l'INDEX FULL SCAN les accès sont faits de manière mono-block. Il est possible ici de passer par un INDEX FAST FULL SCAN car on n'a pas besoin de suivre la structure de l'index (blocks racine puis branches puis feuilles), on a juste besoin de récupérer les blocks de l'index quel que soit leur ordre et de compter le nombre d'entrées dans les blocs feuilles.

Pour revenir à notre problématique de départ sur la comparaison entre COUNT(1) et COUNT(*) , on voit bien à travers les exemples ci-dessus que quelle que soit la méthode utilisée on obtient les mêmes performances et le même plan.

Trace 10053
Par curiosité, on pourrait regarder ce que donne la trace 10053 pour le cas du COUNT(1).
Je rappelle que le trace 10053 est une trace générée  par l'optimiseur lorsqu'on active l'évènement 10053. Cette trace contient pas mal d'informations sur comment l'optimiseur effectue son travail d'optimisation et notamment sur la manière dont il transforme les requêtes (partie Query Transformations):
SQL> alter system flush shared_pool;

System altered.

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'aangour_10053_trace';

Session altered.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.

SQL> explain plan for
  2  select count(1) from t1;

Explained.

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

Et justement, dans la partie Query Transformations de la trace 10053 on remarque les lignes suivantes:
CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) done.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TOTO"."T1" "T1"

Apparemment le CBO récrit la requête en remplaçant le COUNT(1) par un COUNT(*).

Voyons ce que donne la trace lorsqu'on exécute la requête avec le COUNT(*):
 CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) not done.

Le "not done" par rapport au "done" de la trace précédente indique qu'ici la récriture n'a pas été effectué.

CONCLUSION:
Dire que que le COUNT(1) est plus performant que le COUNT(*) (ou l'inverse) est un mythe.
On a pu voir, en regardant les plans d'exécutions, que les performances sont identiques dans les deux cas.
La trace 10053 nous montre que l'optimiseur tente de transformer (lorsque c'est possible)  les COUNT(1) ou autres types de COUNT en COUNT(*).

vendredi 10 août 2012

Privilèges, roles et PL/SQL

Cet article traite d'un problème très vicieux bien connu chez les développeurs utilisant une base Oracle.

Le problème est le suivant: vous avez besoin d'un privilège objet (sur une table d'un autre schéma par exemple) ou d'un privilège système. Votre DBA grant ce privilège à un role qui est lui même granté au user applicatif avec lequel votre application se connecte à la base. Vous vous connectez à la base pour tester et ça fonctionne. Toutefois lorsque vous utilisez ce privilège au sein d'une procédure stockée ou d'une fonction ça ne fonctionne plus. C'est comme si le privilège n'était pas granté. Pourquoi?

Avant de répondre à cette question, illustrons le problème avec un exemple.

J'ai un utilisateur TOTO qui veut donner au user TITI  un accès à sa table T1 via le role ROLE_TITI:
-- connecté à TOTO
SQL> create table t1 as select * from all_objects where rownum<=5;

Table created.

SQL> create role ROLE_TITI;

Role created.

SQL> grant select on t1 to ROLE_TITI;

Grant succeeded.

SQL> grant ROLE_TITI to TITI;

Grant succeeded.

Maintenant je me connecte à TITI et j'exécute une commande SELECT sur la table T1 de TOTO:
-- connecté TITI

SQL> select count(1) from toto.T1;

  COUNT(1)
----------
         5

Pas de soucis ça fonctionne. Puisque le privilege SELECT est granté au role ROLE_TITI et que ce role est granté au user TITI il est normal que TITI puisse accéder à cette table.

Maintenant connectons nous à TITI et effectuons le SELECT au sein d'une procédure stockée:
-- connecté à TITI

SQL> create or replace procedure test_proc is
  2  v_number number;
  3  begin
  4
  5     select count(1) into v_number from toto.T1;
  6
  7  end test_proc;
  8  /

Warning: Procedure created with compilation errors.

SQL> sho errors
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- ---------------------------------------------------
5/2      PL/SQL: SQL Statement ignored
5/42     PL/SQL: ORA-00942: table or view does not exist


La procédure ne compile pas car le user TITI ne voit plus la table T1 de TOTO, et pourtant le privilege SELECT est toujours granté au role ROLE_TITI:
SQL> select table_name,privilege from ROLE_TAB_PRIVS where role like 'ROLE_TITI';

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
T1                             SELECT

La problème vient du fait que les privilèges grantées via un role ne sont pas visibles dans le code d'une procédure ou d'une fonction car les rôles sont désactivées lors de leurs exécution.
La solution consiste à granter directement le privilège au USER qui en a besoin.

Dans notre exemple nous allons granter le privilège SELECT de la table T1 directement au user TITI:
-- connecté à TOTO

SQL> grant select on t1 to TITI;

Grant succeeded.

Voyons maintenant ce que donne la compilation de la procédure du côté de TITI:
-- connecté à TITI

SQL> create or replace procedure test_proc is
  2  v_number number;
  3  begin
  4
  5     select count(1) into v_number from toto.T1;
  6
  7  end test_proc;
  8  /

Procedure created.

La procédure compile bien car le privilège est désormais visible.

Lorsqu'on a la solution c'est facile mais je peux vous dire que lorsque vous rencontrez ce problème pour la première fois (comme ce fût le cas pour moi il y' a quelques années de ça) vous avez de quoi vous tapez la tête contre le clavier :-)

Remarques:
1) Le problème se pose aussi bien pour les privilèges objets que pour les privilèges systèmes
2) Le problème ne se pose pas pour les blocs PL/SQL anonymes
3) Le problème se pose aussi lors de la création de vues

vendredi 3 août 2012

Indexer uniquement certaines valeurs d'une colonne

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:
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

lundi 9 juillet 2012

Ajouter un hint sans toucher à la requête (grâce au SQL patch)

Dans mon article précédent, j'avais tenté de montrer comment grâce à l'utilisation d'un SQL profile il m'a été possible d'injecter un hint à une requête pour forcer le CBO à utiliser un index plus performant. Franck Pachot a alors rebondi sur ce post via un commentaire pour me faire remarquer que l'utilisation d'un SQL patch aurait permis d'arriver au même résultat comme intelligemment montré dans l'article Using SQL Patch to add hints to a packaged application issue du blog Oracle Optimizer (blog que je recommande fortement puisque les articles sont écrits par des membres de l'équipe de développement de l'optimiseur Oracle, autant dire que ces gens savent de quoi ils parlent).

Le SQL patch pour ceux qui ne le savent pas est une sorte d'objet (généralement crée par le SQL Repair Advisor) et qui permet d'indiquer au CBO de changer de plans pour éviter des bugs ou des problèmes  lors par exemple de l'accès à certaines structures ou à des jointures. Il est toutefois possible de se créer son propre SQL patch en utilisant la procédure (non documentée) i_create_patch du package dbms_sqldiag_internal. Un peu comme avec le package DBMS_SQLTUNE, on peut détourner le but premier du SQL patch pour indiquer à l'optimiseur d'utiliser tel ou tel hint pour une requête donnée.

Pour illustrer le principe, je vais repartir de ma requête précédente pour laquelle j'avais crée un SQL profile.
Je supprime d'abord le SQL profile pour que le CBO se remette à utiliser le mauvais index
SQL>  exec dbms_sqltune.drop_sql_profile('PROFILE_9ngkg85n7v9ks_MANUAL');

PL/SQL procedure successfully completed.

SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

....

 
7290 rows selected.

Plan hash value: 2065181612

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.05 |    1453 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.05 |    1453 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.05 |    1453 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.04 |    1447 |
|*  4 |     INDEX RANGE SCAN          | IHISTOJOUR                |      1 |      1 |    486 |00:00:00.04 |    1404 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SYS_NC00093$">=HEXTORAW('878FF9E6FEF8FEFAFF')  AND "H"."SICOVAM"=68000339 AND
              "H"."SYS_NC00093$" IS NOT NULL)
       filter(("H"."SICOVAM"=68000339 AND SYS_OP_UNDESCEND("H"."SYS_NC00093$")<=TO_DATE(' 2012-06-25
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Le SQL profile n'existant plus le CBO se remet à choisir l'index IHISTOJOUR au lieu de l'index IHISTO.

Je crée un SQL patch pour injecter le hint index(h@SEL$1 IHISTO):
SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch(
  3             sql_text        =>      'select h.TOTAL_NAV
  4                                               from historique h, AM_NAV_STATUS_TRANSITIONS am
  5                                              where h.sicovam = am.code
  6                                                and h.sicovam = 68000339
  7                                                and h.TOTAL_NAV is not null
  8                                                and h.jour <= 39623 + to_date(''01/01/1904'', ''DD/MM/YYYY'')
  9                                                and am.new_state not in (7, 8)
 10                                              order by h.jour desc',
 11     hint_text       =>      'index(h@SEL$1 IHISTO)',
 12     name    => 'TEST_SQL_PATCH');
 13  END;
 14  /

PL/SQL procedure successfully completed.

On peut vérifier dans la vue DBA_SQL_PATCHES que le patch a bien été crée:
SQL> select name,status from DBA_SQL_PATCHES;

NAME                           STATUS
------------------------------ --------
TEST_SQL_PATCH                 ENABLED

1 row selected.

Je relance ma requête pour voir si le hint est bien pris en compte:
SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

........

7290 rows selected.

Plan hash value: 1572620151

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.01 |      54 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.01 |      54 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.01 |      54 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.01 |      48 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.01 |       5 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL patch "TEST_SQL_PATCH" used for this statement

Oui, l'index IHISTO est bien utilisé et la note à la fin du plan d'exécution nous indique que le SQL patch
TEST_SQL_PATCH a bien été utilisé pour l'exécution de cette requête.

En adaptant le script de Kerry Osborne sur le SQL profile, il est possible de se créer un script create_1_hint_sql_patch.sql permettant de générer un SQL patch pour un SQL_ID donné:
accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept patch_name -
       prompt 'Enter value for patch_name (PATCH_sqlid_MANUAL): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'

set sqlblanklines on

declare
l_patch_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);

begin

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
into l_patch_name
from dual;

sys.dbms_sqldiag_internal.i_create_patch(
sql_text => cl_sql_text,
hint_text => '&hint',
name => l_patch_name,
category => '&&category'
);

dbms_output.put_line('SQL Patch '||l_patch_name||' created.');

end;
/

undef patch_name
undef sql_id
undef category

Supprimons le SQL patch crée précédemment et recréons le en utilisant le script ci-dessus pour la requête dont le SQL_ID est 9ngkg85n7v9ks:
SQL> begin
  2    sys.dbms_sqldiag.drop_sql_patch('TEST_SQL_PATCH');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> @create_1_hint_sql_patch.sql
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 9ngkg85n7v9ks
SQL> accept patch_name -
>        prompt 'Enter value for patch_name (PATCH_sqlid_MANUAL): ' -
>        default 'X0X0X0X0'
Enter value for patch_name (PATCH_sqlid_MANUAL):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL>
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  l_patch_name varchar2(30);
  3  cl_sql_text clob;
  4  l_category varchar2(30);
  5
  6  begin
  7
  8  select
  9  sql_fulltext
 10  into
 11  cl_sql_text
 12  from
 13  v$sqlarea
 14  where
 15  sql_id = '&&sql_id';
 16
 17  select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
 18  into l_patch_name
 19  from dual;
 20
 21  sys.dbms_sqldiag_internal.i_create_patch(
 22  sql_text => cl_sql_text,
 23  hint_text => '&hint',
 24  name => l_patch_name,
 25  category => '&&category'
 26  );
 27
 28  dbms_output.put_line('SQL Patch '||l_patch_name||' created.');
 29
 30  end;
 31  /
old  15: sql_id = '&&sql_id';
new  15: sql_id = '9ngkg85n7v9ks';
old  17: select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
new  17: select decode('X0X0X0X0','X0X0X0X0','PATCH_'||'9ngkg85n7v9ks'||'_MANUAL','X0X0X0X0')
Enter value for hint: index(h@SEL$1 IHISTO)
old  23: hint_text => '&hint',
new  23: hint_text => 'index(h@SEL$1 IHISTO)',
old  25: category => '&&category'
new  25: category => 'DEFAULT'

PL/SQL procedure successfully completed.

SQL> undef patch_name
SQL> undef sql_id
SQL> undef category

On vérifie que le SQL patch a bien été crée :
SQL> select name from dba_sql_patches;

NAME
------------------------------
PATCH_9ngkg85n7v9ks_MANUAL

1 row selected.

En exécutant de nouveau la requête on obtient le bon plan:
Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.04 |      54 |      5 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.04 |      54 |      5 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.03 |      54 |      5 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.03 |      48 |      5 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.03 |       5 |      5 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      0 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL patch "PATCH_9ngkg85n7v9ks_MANUAL" used for this statement

CONCLUSION:

On a vu qu'en utilisant la technique du SQL patch il était possible comme avec le SQL profile d'injecter un hint à une requête SQL sans toucher au code de cette requête.

L'avantage ici par rapport au SQL profile c'est qu'il n'est pas nécessaire d'avoir payé la licence du TUNING PACK. Il suffit juste d'utiliser une Enterprise Edition.

L'inconvénient c'est qu'il ne semble pas possible de forcer l'utilisation du SQL patch pour les requêtes qui ne diffèrent qu'au niveau des valeurs littérales. En effet, à l'inverse de la procédure DBMS_SQLTUNE.import_sql_profile, il n'existe pas de paramètre FORCE_MATCH pour la procédure dbms_sqldiag_internal.i_create_patch même si pourtant une colonne FORCE_MATCHING existe dans la vue DBA_SQL_PATCHES ce qui laisse supposer que ça pourrait être possible...

lundi 2 juillet 2012

Ajouter un hint sans toucher à la requête

Dans un article précédent, j'avais tenté d'expliquer comment grâce à la création d'un SQL profile il était possible de forcer l'utilisation d'un plan d'exécution qui avait déjà fait l'objet d'une utilisation par le passé (plan d'exécution existant dans la shared pool ou dans l'AWR).

Dans ce nouvel article il s'agit d'une autre problématique: Comment ajouter un hint à une requête SQL sans modifier le code de cette requête? On va voir que là aussi les SQL profiles vont nous aider.

Les DBAs doivent souvent faire face à ce genre de problèmes soit parce que personne n'a accès au code applicatif (c'est le cas lorsqu'on utilise un progiciel ) ou bien parce que le délai de livraison d'un patch pouvant corriger la requête serait trop long alors que le problème doit être traité urgemment.

Pour illustrer ce principe, je vais partir d'une requête que j'ai eu à tuner récemment.

select h.TOTAL_NAV
  from historique h, AM_NAV_STATUS_TRANSITIONS am
 where h.sicovam = am.code
   and h.sicovam = 68000339
   and h.TOTAL_NAV is not null
   and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
   and am.new_state not in (7, 8)
 order by h.jour desc;


Cette requête est générée par un progiciel et est exécutée plusieurs centaines de milliers de fois par jour.
Comme le montre le plan ci-dessous elle génère plus de 1500 logical reads à chaque exécution:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  9ngkg85n7v9ks, child number 0
-------------------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 510344764

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.44 |    1599 |   1597 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.44 |    1599 |   1597 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.43 |    1599 |   1597 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.41 |    1447 |   1447 |
|*  4 |     INDEX RANGE SCAN          | IHISTOJOUR                |      1 |      1 |    486 |00:00:00.25 |    1404 |   1404 |
|*  5 |    TABLE ACCESS FULL          | AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.02 |     152 |    150 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SYS_NC00093$">=HEXTORAW('878FF9E6FEF8FEFAFF')  AND "H"."SICOVAM"=68000339 AND "H"."SYS_NC00093$"
              IS NOT NULL)
       filter(("H"."SICOVAM"=68000339 AND SYS_OP_UNDESCEND("H"."SYS_NC00093$")<=TO_DATE(' 2012-06-25 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(("AM"."CODE"=68000339 AND "AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))

L'essentiel des logical reads consommés est lié à l'accès à la table HISTORIQUE via l'index IHISTOJOUR. En forçant l'utilisation d'un autre index existant via un hint et en créant un index sur le champ CODE de la table AM_NAV_STATUS_TRANSITIONS j'obtiens de bien meilleurs performances:

SQL> create index idx_AM_NAV_CODE on AM_NAV_STATUS_TRANSITIONS(code);

Index created.



SQL> select /*+ index(h IHISTO) */ h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;



Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.18 |      54 |     32 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.18 |      54 |     32 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.18 |      54 |     32 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.16 |      48 |     31 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.01 |       5 |      3 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      1 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      1 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Ma requête ne consomme plus que 54 logical reads (au lieu de 1599). Vu que cette requête est exécutée plus de 100 000 fois par jour, le hint peut être vraiment bénéfique pour les performances globales de la base.
Cependant, il ne nous est pas possible d'ajouter ce hint dans la requête car elle est générée par un progiciel. Pour pouvoir utiliser ce hint lors de l'exécution de cette requête il suffit de créer un SQL profile via la procédure dbms_sqltune.import_sql_profile qui contiendra le hint. Ce SQL profile sera associé au SQL_ID de la requête qu'on veut tuner.

Pour créer un SQL profile contenant un hint, j'utilise le script suivant de Kerry Osborne:

----------------------------------------------------------------------------------------
--
-- File name:   create_1_hint_sql_profile.sql
--
-- Purpose:     Prompts for a hint and makes a profile out of it.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              profile_name: the name of the profile to be attached to a new statement
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in theshared pool)
--
--              category: the category to assign to the new profile 
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: This script prompt for a hint. It does not validate the hint. It creates a 
--              SQL Profile with the single hint and attaches it to the provided sql_id.
--              This script should now work with all flavors of 10g and 11g.
--              
--
--              See kerryosborne.oracle-guy.com for additional information.
----------------------------------------------------------------------------------------- 

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept profile_name -
       prompt 'Enter value for profile_name (PROFILE_sqlid_MANUAL): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (false): ' -
       default 'false'


set sqlblanklines on

declare
l_profile_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
into l_profile_name
from dual;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text, 
profile => sqlprof_attr('&hint'),
category => '&&category',
name => l_profile_name,
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
force_match => &&force_matching
);

dbms_output.put_line('Profile '||l_profile_name||' created.');

end;
/

undef profile_name
undef sql_id
undef category
undef force_matching

Lors de l'exécution de ce script vous êtes invités à saisir le SQL_ID de la requête et bien sûr le hint à associer à la requête. Pour le reste on a des valeurs par défaut (nom du SQL profile, category, force_match).

Maintenant, j'exécute le script pour ma requête 9ngkg85n7v9ks:

SQL> @sp_create_1_hint_sql_profile.sql
SQL>
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      create_1_hint_sql_profile.sql
SQL> --
SQL> -- Purpose:        Prompts for a hint and makes a profile out of it.
SQL> -
> -- Author:      Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              profile_name: the name of the profile to be attached to a new statement
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to (must be in theshared pool)
SQL> --
SQL> --              category: the category to assign to the new profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description: This script prompt for a hint. It does not validate the hint. It creates a
SQL> --              SQL Profile with the single hint and attaches it to the provided sql_id.
SQL> --              This script should now work with all flavors of 10g and 11g.
SQL> --
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 9ngkg85n7v9ks
SQL> accept profile_name -
>        prompt 'Enter value for profile_name (PROFILE_sqlid_MANUAL): ' -
>        default 'X0X0X0X0'
Enter value for profile_name (PROFILE_sqlid_MANUAL):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (false): ' -
>        default 'false'
Enter value for force_matching (false): TRUE
SQL>
SQL>
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  l_profile_name varchar2(30);
  3  cl_sql_text clob;
  4  l_category varchar2(30);
  5  l_force_matching varchar2(3);
  6  b_force_matching boolean;
  7  begin
  8
  9  select
 10  sql_fulltext
 11  into
 12  cl_sql_text
 13  from
 14  v$sqlarea
 15  where
 16  sql_id = '&&sql_id';
 17
 18  select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
 19  into l_profile_name
 20  from dual;
 21
 22  dbms_sqltune.import_sql_profile(
 23  sql_text => cl_sql_text,
 24  profile => sqlprof_attr('&hint'),
 25  category => '&&category',
 26  name => l_profile_name,
 27  -- use force_match => true
 28  -- to use CURSOR_SHARING=SIMILAR
 29  -- behaviour, i.e. match even with
 30  -- differing literals
 31  force_match => &&force_matching
 32  );
 33
 34  dbms_output.put_line('Profile '||l_profile_name||' created.');
 35
 36  end;
 37  /
Enter value for hint: index(h@SEL$1 IHISTO)

PL/SQL procedure successfully completed.

SQL> undef profile_name
SQL> undef sql_id
SQL> undef category
SQL> undef force_matching



Vous noterez que le hint que j'ai associé est le suivant: INDEX(h@SEL$1 IHISTO).
Celui que j'avais utilisé dans la requête était celui-ci:  INDEX(h IHISTO)
La différence se situe au niveau de l'alias du "query block" SEL$1 qui est généré par défaut par le CBO et qui permet d'identifier chaque bloc de requête (utile notamment lorsqu'on a des subqueries dans la requête).
Dans mon cas je n'ai qu'un seul bloc de requête et donc le hint lorsqu'il est mis directement dans la requête fonctionne même s'il ne contient pas d'alias au query block. Pour les SQL profiles c'est différent. Le hint ne fonctionnera pas s'il n'est pas aliassé. Pour connaitre les alias générés par l'optimiseur pour une requête donnée il suffit d'utiliser l'option ALIAS dans les fonctions du package dbms_xplan.
Exemple:

 SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic +alias'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 2065181612

-------------------------------------------------------------------
| Id  | Operation                     | Name                      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |
|   1 |  SORT ORDER BY                |                           |
|   2 |   HASH JOIN                   |                           |
|   3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |
|   4 |     INDEX RANGE SCAN          | IHISTOJOUR                |
|   5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |
|   6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |
-------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / H@SEL$1
   4 - SEL$1 / H@SEL$1
   5 - SEL$1 / AM@SEL$1
   6 - SEL$1 / AM@SEL$1


Revenons au SQL profile que j'ai crée grâce au script de Kerry Osborne. La requête suivante permet de vérifier qu'il a bien été crée:

SQL> @sp_list_sql_profiles.sql
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text, force_matching
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text','%')
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text:
Enter value for name: %9ngkg85n7v9ks%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_9ngkg85n7v9ks_MANUAL   DEFAULT         ENABLED  select h.TOTAL_NAV                                             YES

1 row selected.

Je relance la requête initiale générée par le progiciel (celle qui ne contient pas le hint dans le corps de la requête):

SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

.............

7290 rows selected.



SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  9ngkg85n7v9ks, child number 0
-------------------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.03 |      54 |      3 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.03 |      54 |      3 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.02 |      54 |      3 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.02 |      48 |      3 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.02 |       5 |      3 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      0 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL profile PROFILE_9ngkg85n7v9ks_MANUAL used for this statement


On s'aperçoit que le SQL profile a fonctionné puique maintenant l'index IHISTO est bien utilisé, et la petite note à la fin du plan d'exécution indique que le SQL profile PROFILE_9ngkg85n7v9ks_MANUAL a été utilisé pour cette requête.

Ainsi, grâce aux SQL profiles et à la procédure dbms_sqltune.import_sql_profile on a réussi à obtenir un meilleur plan pour notre requête (en utilisant un hint) sans avoir à toucher au code de la requête.

mercredi 27 juin 2012

Afficher le "Top N" d'une table

Il nous est tous arrivé au moins une fois d'avoir à écrire une requête ayant pour but d'afficher le Top N d'une table ou d'un résultat de requêtes (par exemple afficher les 10 plus gros salaires de sa société ;-) ).

J'ai pensé à écrire un article sur ce sujet car j'ai vu récemment un développeur qui, pour afficher les 20 plus récentes lignes d'une table, effectuait un SELECT de toute la table avec un tri décroissant sur le champ DATE puis au niveau applicatif ne récupérait que les 20 premières lignes qui l'intéressaient. Cette manière de procéder est justement celle à proscrire car elle implique un tri de toute la table. Heureusement, pour mon développeur la table ne contenait que quelques milliers de lignes mais imaginez une table contenant plusieurs millions de lignes, ça engendrerait surement un tri sur disque (swap sur tablespace temporaire car pas assez de mémoire).

La bonne approche dans Oracle consiste à utiliser la pseudo-colonne ROWNUM de la manière suivante:

select * from (select * from ma_table order by champ_date desc) where rownum <=20;

L'idée consiste à appliquer le ROWNUM sur une vue en ligne contenant la table triée par le champ date.
L'erreur à ne pas commettre est la suivante:

select * from ma_table  where rownum <=20 order by champ_date desc;
En effet, ici le ROWNUM est appliqué avant le ORDER BY, du coup on obtient les 20 premières lignes de la table récupérées par Oracle qui sont ensuite triées par le champ DATE.

Illustrons ce concept avec un exemple.

SQL> create table t1 as select * from all_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.
J'ai une table T1 contenant toutes les lignes de la vue ALL_TABLES.
Je souhaiterais afficher les 5 tables contenant le plus de lignes.
La bonne solution est la suivante:

SQL>  select * from
  2  (select table_name,num_rows from T1 order by 2 desc NULLS  LAST)
  3  where rownum<=5;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SALES                              918843
SALES_TRANSACTIONS_EXT             916039
WRI$_OPTSTAT_HISTGRM_HISTORY       710550
SOURCE$                            636698
WRH$_SYSMETRIC_HISTORY             250920

5 rows selected.

------------------------------------------------------------------------------------------ | Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT        |      |      1 |        |      5 |00:00:00.01 |     164 | |   1 |  COUNT STOPKEY          |      |      1 |        |      5 |00:00:00.01 |     164 | |   2 |   VIEW                  |      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   3 |    SORT ORDER BY STOPKEY|      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   4 |     TABLE ACCESS FULL   | T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | ------------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets         164  consistent gets           0  physical reads           0  redo size         647  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)           5  rows processed

Voyons ce que donne la requête si je filtre sur le ROWNUM avant l'ORDER BY:

SQL> select table_name,num_rows
  2  from T1  where rownum<=5
  3  order by 2 desc NULLS  LAST;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
CCOL$                               20566
CDEF$                               17426
SEG$                                 7761
MLOG$                                   0
UET$                                    0

Ce n'est clairement pas le résultat souhaité.

Pour la bonne requête j'ai affiché son plan d'exécution ainsi que les statistiques d'exécution fournies par AUTOTRACE. Comparons ces stats avec la requête qui consiste à récupérer toutes lignes triées pour ne filtrer que le top N côté client (celle utilisée par mon développeur):

select table_name,num_rows from T1 order by 2 desc NULLS  LAST;

------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |   4542 |00:00:00.01 |     164 | |   1 |  SORT ORDER BY     |      |      1 |   4542 |   4542 |00:00:00.01 |     164 | |   2 |   TABLE ACCESS FULL| T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | -------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------         726  recursive calls           0  db block gets         303  consistent gets         165  physical reads           0  redo size      145436  bytes sent via SQL*Net to client        3737  bytes received via SQL*Net from client         304  SQL*Net roundtrips to/from client          27  sorts (memory)           0  sorts (disk)        4542  rows processed
On constate qu'avec la bonne approche on retourne beaucoup moins de lignes à l'application cliente (5 vs 4542), que le nombre de logical reads est moins important (164 vs 303) et qu'on génère moins de tris (1 vs 27). La différence en termes de performance aurait été plus significative si la table était beaucoup plus volumineuse.

La différence sur le nombre de tris s'explique par l'utilisation de l'opération SORT ORDER BY STOPKEY qui permet de ne trier que les 5 lignes. D'ailleurs la colonne A-ROWS du plan d'exécution pour cette opération donne un chiffre de 5.Je rappelle que cette colonne du plan indique le nombre de ligne réellement processées pour chaque opération du plan. Tom Kyte dans son livre Effective Oracle By Design explique très bien comment Oracle, grâce à l'utilisation d'un tableau en mémoire, arrive à ne trier que N lignes au lieu de toutes les lignes de la table (après application des éventuels filtres dans la clause WHERE).
Voici ce que dit Tom Kyte dans son livre:
The first N rows will populate this array of rows in sorted order. When the N+1 row is fetched,
it will be compared to the last row in the array. If it would go into slot N+1 in the array, it gets
thrown out. Otherwise, it is added, sorted to this array, and one of the existing rows is discarded.
Our sort area holds N rows maximum, so instead of sorting one million rows, we sort N rows.
This seeming small detail of using an array concept and just sorting N rows can lead to huge
gains in performance and resource usage. It takes a lot less RAM to sort ten rows than it does to sort one million rows (not to mention TEMP space usage!).

Une autre manière d'afficher le Top N consiste à utiliser la fonction analytique RANK (ou DENSE_RANK):

SQL> select * from
  2  (
  3  select table_name,num_rows, dense_rank() over (order by num_rows desc nulls last) DR
  4  from T1
  5  ) where DR <=5;

TABLE_NAME                       NUM_ROWS         DR
------------------------------ ---------- ----------
SALES                              918843          1
SALES_TRANSACTIONS_EXT             916039          2
WRI$_OPTSTAT_HISTGRM_HISTORY       710550          3
SOURCE$                            636698          4
WRH$_SYSMETRIC_HISTORY             250920          5

------------------------------------------------------------------------------------------- | Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |      |      1 |        |      5 |00:00:00.01 |     164 | |   1 |  VIEW                    |      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   2 |   WINDOW SORT PUSHED RANK|      |      1 |   4542 |      6 |00:00:00.01 |     164 | |   3 |    TABLE ACCESS FULL     | T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | -------------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets         164  consistent gets           0  physical reads           0  redo size         718  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)           5  rows processed