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