lundi 7 mars 2011

ORA-02449: unique/primary keys in table referenced by foreign keys

L'erreur ORA-02449 indique simplement que vous tenter de dropper une table qui est référencée par une clé étrangère d'une autre table.

Pour résoudre ce problème il faudrait supprimer ces Foreign Key (FK) avant de dropper la table, mais comment déterminer les tables qui référencent cette Foreign Key ?

La requête suivante peut vous permettre de répondre à cette question:

select * from user_constraints 
where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name='NOM_TABLE_A_DROPPER') 
and CONSTRAINT_TYPE='R'; 

Le type de contrainte “R” indique qu’il s’agit d’une FK
La colonne R_CONSTRAINT_NAME contient le nom de la Primary Key référencée par la FK.

Voici un petit exemple :
SQL>  CREATE TABLE PAYS
  2      (      id_pays         numeric(10)     not null,
  3             nom_pays        varchar2(50)    not null,
  4             supplier_id     numeric(10)     not null,
  5             CONSTRAINT pays_pk PRIMARY KEY (id_pays)
  6      );

Table created.

SQL> CREATE TABLE CLIENT
  2      (      id_client       numeric(10)     not null,
  3             nom_client      varchar2(50)    not null,
  4             id_pays         numeric(10)     not null,
  5             CONSTRAINT client_pk PRIMARY KEY (id_client),
  6  CONSTRAINT fk_pays
  7               FOREIGN KEY (id_pays)
  8               REFERENCES PAYS(id_pays)
  9      );

Table created.

Si j’essaie de dropper la table PAYS je tombe sur l’erreur ORA-02449 :
SQL> drop table pays;
drop table pays
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


La requête suivante m’indique le nom de la foreign key à dropper pour pouvoir dropper ma table :
SQL> select table_name,CONSTRAINT_NAME from user_constraints
  2  where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name='PAYS')
  3  and CONSTRAINT_TYPE='R';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
CLIENT                         FK_PAYS

La FK étant identifié je peux la dropper puis dropper ma table :

SQL> alter table CLIENT drop constraint FK_PAYS;

Table altered.

SQL> drop table pays;

Table dropped.


Si on veut vraiment supprimer la table sans se soucier des éventuels FK, on peut utiliser la clause CASCADE CONSTRAINTS de la commande DROP TABLE. Cette clause indique à Oracle de supprimer automatiquement toutes les FK qui référenceraient la table à dropper :
SQL> drop table pays cascade constraints;

Table dropped.

jeudi 3 mars 2011

Row Prefetching (ARRAYSIZE)

J’avais en tête depuis un moment d’écrire un post sur le row prefetching et un problème de performance rencontré récemment m’en donne l’occasion aujourd’hui.

Le concept du row prefetching concerne l’étape du FETCH de l’exécution d’une requête c'est-à-dire l’étape du SELECT qui ramène les lignes de la base à l’application cliente. Chaque fetch call retournera tout ou une partie des lignes demandées par la requête. Le row prefetching correspond justement au nombre de lignes retournées par un fetch call. En augmentant la valeur du row Prefetching on peut réduire l’étape de FETCH d’une requête SQL.

Le problème de performance que j’ai eu à traiter récemment permet justement d’illustrer ce concept.

La requête incriminée était la suivante :
SELECT   Timestamptz,  OPEN,  High,  Low,  CLOSE,  Volume,  Timerange,  Gmt_Offset
  FROM V_Ticker_Intraday_Trade
WHERE Internal_Code = 'IND_EUROSTOXX_50'
AND TIMERANGE = 1
AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
ORDER BY PROVIDER_ID,TIMESTAMPTZ;

L’exécution de cette requête sous SQLPLUS met plus de 5 minutes pour retourner plus de 2 millions de lignes :
SQL> set autot trace
SQL> SELECT
  2    Timestamptz,
  3    OPEN,
  4    High,
  5    Low,
  6    CLOSE,
  7    Volume,
  8    Timerange,
  9    Gmt_Offset
 10    FROM V_Ticker_Intraday_Trade
 11  WHERE Internal_Code = 'IND_EUROSTOXX_50'
 12  AND TIMERANGE = 1
 13  AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
 14  AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
 15  ORDER BY PROVIDER_ID,TIMESTAMPTZ;

2298385 rows selected.

Elapsed: 00:05:19.70

J’avais un fort doute que les 5 minutes étaient essentiellement causées par la récupération des 2 millions de lignes. Pour en être sûr j’ai remplacé la requête par un CTAS :
SQL> create table aha_t1 as
  2  SELECT
  3    Timestamptz,
  4    OPEN,
  5    High,
  6    Low,
  7    CLOSE,
  8    Volume,
  9    Timerange,
 10    Gmt_Offset
 11    FROM V_Ticker_Intraday_Trade
 12  WHERE Internal_Code = 'IND_EUROSTOXX_50'
 13  AND TIMERANGE = 1
 14  AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130)
 15  AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND to_date('2011/01/01','yyyy/mm/dd')
 16  ORDER BY PROVIDER_ID,TIMESTAMPTZ;

Table created.

Elapsed: 00:00:22.95

En exécutant la requête ci-dessus consistant à ne pas ramener les lignes côté client, j’obtiens un temps d’exécution de presque 23 secondes. On est donc bien loin des 5 minutes.
Le plan d’exécution suivant indique comment sont réparties les 22 secondes dans les différentes opérations du plan :
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL_ID  5zzpky782tr91, child number 0
-------------------------------------
create table aha_t1 as SELECT   Timestamptz,   OPEN,   High,   Low,   CLOSE,   Volume,   Timerange,   Gmt_Offset   FROM V_Ticker_Intraday_Trade WHERE Internal_Code
= 'IND_EUROSTOXX_50' AND TIMERANGE = 1 AND (PROVIDER_ID = 1 OR PROVIDER_ID = 2 OR PROVIDER_ID = 130) AND DAYS BETWEEN to_date('1980/01/01','yyyy/mm/dd') AND
to_date('2011/01/01','yyyy/mm/dd') ORDER BY PROVIDER_ID,TIMESTAMPTZ

Plan hash value: 948258494

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  LOAD AS SELECT                       |                    |      1 |        |      1 |00:00:22.20 |   96511 |  36895 |  34284 |   519K|   519K|  519K (0)|         |
|   2 |   SORT ORDER BY                       |                    |      1 |    435 |   2298K|00:00:14.15 |   76367 |  36880 |  18098 |   159M|  4250K|   97M (0)|     142K|
|   3 |    NESTED LOOPS                       |                    |      1 |    435 |   2298K|00:00:06.95 |   76365 |  18782 |      0 |       |       |          |         |
|   4 |     NESTED LOOPS                      |                    |      1 |      1 |      2 |00:00:00.01 |      11 |      9 |      0 |       |       |          |         |
|   5 |      NESTED LOOPS                     |                    |      1 |      1 |      2 |00:00:00.01 |       9 |      9 |      0 |       |       |          |         |
|   6 |       TABLE ACCESS BY INDEX ROWID     | INSTRUMENT         |      1 |      1 |      1 |00:00:00.01 |       4 |      4 |      0 |       |       |          |         |
|*  7 |        INDEX UNIQUE SCAN              | INSTRUMENT_CODE_IX |      1 |      1 |      1 |00:00:00.01 |       3 |      3 |      0 |       |       |          |         |
|   8 |       TABLE ACCESS BY INDEX ROWID     | TICKER             |      1 |      1 |      2 |00:00:00.01 |       5 |      5 |      0 |       |       |          |         |
|*  9 |        INDEX RANGE SCAN               | TICKER_AK          |      1 |      1 |      2 |00:00:00.01 |       3 |      3 |      0 |       |       |          |         |
|* 10 |      INDEX UNIQUE SCAN                | PROVIDER_PK        |      2 |      1 |      2 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  11 |     PARTITION HASH ITERATOR           |                    |      2 |   1823 |   2298K|00:00:06.95 |   76354 |  18773 |      0 |       |       |          |         |
|* 12 |      TABLE ACCESS BY LOCAL INDEX ROWID| INTRADAY_TRADE     |      2 |   1823 |   2298K|00:00:04.65 |   76354 |  18773 |      0 |       |       |          |         |
|* 13 |       INDEX RANGE SCAN                | INTRADAY_TRADE_IX5 |      2 |    184K|   2314K|00:00:00.01 |   11594 |  11054 |      0 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

On voit par exemple que sur les 22 secondes, 14 secondes sont liées au tri du result set.

Le problème initial c'est qu’on souhaite quand même ramener les 2 millions de lignes du côté de l’application cliente. L’idée lorsqu’on doit fetcher autant de lignes c’est d’optimiser le nombre de lignes envoyées de la base au client par aller-retour réseau. Cette optimisation peut se faire en jouant sur le paramètre de row prefetching. Sous SQLPLUS ce paramètre s’appelle ARRAYSIZE et vaut 15 par défaut.

Voyons le nombre d’aller-retour réseau effectués pour récupérer les 2 millions de lignes lorsque l’ARRAYSIZE est à sa valeur par défaut :
SQL> set autot trace
SQL> select * from aha_t1;

2298385 rows selected.

Statistics
----------------------------------------------------------
        188  recursive calls
          0  db block gets
     168444  consistent gets
      16186  physical reads
          0  redo size
  101490001  bytes sent via SQL*Net to client
    1685825  bytes received via SQL*Net from client
     153227  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2298385  rows processed

Elapsed: 00:05:16.19

On voit que le nombre de SQL*Net roundtrips est de 153 227 pour récupérer exactement 2 298 385 lignes. Si on divise ces 2298385 lignes par la taille de l’ARRAYSIZE c-a-d 15 on obtient 153225 ce qui correspond quasiment au nombre de SQL*Net roundtrips constatés dans la partie statistiques de l'autotrace.

Voyons ce que donne la requête lorsqu’on passe le row prefetching à 100 :
SQL> set arraysize 100
SQL> select * from aha_t1;

2298385 rows selected.

Elapsed: 00:01:05.09

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39117  consistent gets
          0  physical reads
          0  redo size
   90289189  bytes sent via SQL*Net to client
     253163  bytes received via SQL*Net from client
      22985  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2298385  rows processed

La requête s’exécute désormais en 1 minute et le nombre d’aller-retour réseau est passé de 153 227 à 22 985 (2298385/100).

En mettant l’ARRAYSIZE à sa valeur maximale c'est-à-dire 5000 j’arrive à un temps d’exécution de 12 secondes :
SQL> set arraysize 5000
SQL> select * from aha_t1;

2298385 rows selected.

Elapsed: 00:00:12.74

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16659  consistent gets
          0  physical reads
          0  redo size
   88352125  bytes sent via SQL*Net to client
       5399  bytes received via SQL*Net from client
        461  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2298385  rows processed

Le nombre de SQL*Net roundtrips n’est plus que de 461.

On voit bien avec cet exemple combien en jouant sur la taille du row prefetching on arrive à obtenir de meilleures performances sur les requêtes ramenant beaucoup de lignes.

Ce qu’il faut bien avoir à l’esprit c’est que le row prefetching est quelque chose qui se paramètre du côté client et non pas du côté serveur. La manière de positionner ce paramètre dépend de l’application cliente utilisée. Sous SQLPLUS on a vu qu’il fallait toucher au paramètre ARRAYSIZE. Pour les application JAVA utilisant le driver JDBC, le row prefetching peut être modifié en changeant la valeur de la propriété defaultRowPrefetch des classes OracleDataSource ou OracleDriver. Par défaut, le Row Prefetching du JDBC vaut 10.

Pour les application utilisant ODP.NET la taille du fecth n’est pas définie en nombre de lignes mais en octects ce qui fait que le row prefetching va dépendre de la taille des lignes récupérées. Par défaut la taille de la propriété FetchSize de la classe OracleCommand ou OracleDataReader est de 65 536 octects. Si ma table a des lignes d’une taille de 100 octects alors par defaut ma requête sur cette table me ramènera les lignes avec une taille de fetch de 655 lignes environ (là où une application JAVA par défaut ramènerait les lignes avec une taille de fetch de 10 lignes seulement).


CONCLUSION:

A chaque fois que vous avez à ramener beaucoup de lignes du côté de l'application pensez bien à positionner de manière appropriée le row prefetching. L'optimisation de ce paramètre dépendra du type d'application client utilisé (SQLPLUS,ADO.NET, JDBC, OCI etc.).

mardi 15 février 2011

Problème de performance lié à la désactivation du Bind Peeking (_OPTIM_PEEK_USER_BINDS)

J'ai été sollicité la semaine dernière par un développeur pour intervenir sur un problème de performance lié à la requête suivante:
SELECT  MAX(QRT_BENCH_DATE) PREVIOUS_DATE
FROM admlxa.MUT_EXCHANGE_NORM_LINK, admlxa.QRT_BENCH, admlxa.QRT_COMMON_PARAMS_ID_LNS 
WHERE ENL_EXCHANGE_NORM_ID IN (50002, 50003)
AND QRT_BENCH_ELIOT_ID = ENL_NORM_CODE
AND QRT_BENCH_NAME LIKE LNS_PORTFOLIO_TRANSCOI9QM || '%'
AND LNS_PORTFOLIO_TRANSCOI9QM IS NOT NULL
AND NOT QRT_BENCH_NAME LIKE '%TESTARVAL'
AND QRT_BENCH_PREMIUM <> 0
AND ENL_OBJECT_ID = :a1
AND LNS_ID_EUR = :a2
AND QRT_BENCH_DATE < :a3 ;
Cette requête était exécutée plusieurs fois dans une boucle et mettait à chaque fois plus de 2 minutes pour s'exécuter. Le plan d'exécution choisi par le CBO est le suivant:
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |      1 |        |      1 |00:02:13.08 |     285K|    260K|
|   1 |  SORT AGGREGATE                  |                             |      1 |      1 |      1 |00:02:13.08 |     285K|    260K|
|*  2 |   TABLE ACCESS BY INDEX ROWID    | QRT_BENCH                   |      1 |      2 |      0 |00:02:13.08 |     285K|    260K|
|   3 |    NESTED LOOPS                  |                             |      1 |      1 |   8048K|00:00:34.38 |   28503 |  28515 |
|   4 |     MERGE JOIN CARTESIAN         |                             |      1 |      1 |      1 |00:00:00.05 |      40 |     38 |
|*  5 |      TABLE ACCESS FULL           | MUT_EXCHANGE_NORM_LINK      |      1 |      1 |      1 |00:00:00.04 |      38 |     37 |
|   6 |      BUFFER SORT                 |                             |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| QRT_COMMON_PARAMS_ID_LNS    |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  8 |        INDEX RANGE SCAN          | PK_QRT_COMMON_PARAMS_ID_LNS |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|*  9 |     INDEX RANGE SCAN             | IDX_QRT_BENCH_1             |      1 |  78876 |   8048K|00:00:20.25 |   28463 |  28477 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(("QRT_BENCH_ELIOT_ID" IS NOT NULL AND "QRT_BENCH_NAME" NOT LIKE '%TESTARVAL' AND "QRT_BENCH_PREMIUM"<>0 AND
              "QRT_BENCH_NAME" IS NOT NULL AND "QRT_BENCH_ELIOT_ID"="ENL_NORM_CODE" AND "QRT_BENCH_NAME" LIKE
              "LNS_PORTFOLIO_TRANSCOI9QM"||'%'))
   5 - filter(("ENL_OBJECT_ID"=:A1 AND INTERNAL_FUNCTION("ENL_EXCHANGE_NORM_ID")))
   7 - filter("LNS_PORTFOLIO_TRANSCOI9QM" IS NOT NULL)
   8 - access("LNS_ID_EUR"=:A2)
   9 - access("QRT_BENCH_DATE"<:A3)
Le plan ci-dessus a été généré avec les stats d'exécution (paramètre STATISTICS_LEVEL=ALL au niveau de la session). On voit que la requête met plus de 2 minutes à s'exécuter(colonne A-TIME) et que l'essentiel du temps d'exécution concerne la récupération des lignes dans la table QRT_BENCH à partir des ROWIDs de l'index IDX_QRT_BENCH_1. Le problème ici est que l'optimiseur a décidé d'utiliser un accès indexé pour récupérer plus de 8 millions de lignes dans la table QRT_BENCH (soit la quasi totalité de la table). On devine la cause de l'erreur en comparant les colonnes E-ROWS et A-ROWS pour l'opération 9 du plan (INDEX RANGE SCAN). La colonne E-ROWS indique le nombre de lignes estimées par l'optimiseur pendant la phase de parsing de la requête alors que la colonne A-ROWS indique le nombre de lignes réellement traitées pendant l'exécution de la requête. On constate ici que l'optimiseur a estimé que le parcours de l'index retournerait 78 876 lignes alors que le nombre réel est de 8 048 000. Pourquoi l'optimiseur se trompe t-il à ce point? En regardant la trace 10053 j'ai pu m'apercevoir que le paramètre caché _OPTIM_PEEK_USER_BINDS avait été (pour des raisons encore obscures) désactivé sur cette base . Ce paramètre à FALSE indique simplement que la fonctionnalité du BIND VARIABLE PEEKING permettant à l'optimiseur de connaitre la valeur des variables bindées au moment de la phase de hard parse est désactivé. (Pour plus d'informations sur le BIND VARIABLE PEEKING vous pouvez voir ici mon poste précédent sur ce sujet). Si le Bind Peeking est désactivé le CBO utilise alors un pourcentage fixe de la table comme estimation de la cardinalité retournée. Ce que j'ai appris comme tout le monde dans le livre de Jonathan Lewis c'est que pour les clauses de type "<" ou ">" une sélectivité de 5% est utlisée par le CBO. Or ici 8763975*0.05 = 438198. Dans le plan on a une cardinalité de 78876. NOTE: le nombre 8763975 correspond à la colonne NUM_ROWS dans USER_INDEXES pour l'index IDX_QRT_BENCH_1. Voici le plan qu'on obtient pour une requête n'impliquant que la table QRT_BENCH:
SQL> explain plan for select * from  admlxa.QRT_BENCH where QRT_BENCH_DATE < :a3;
 Explained.

 Elapsed: 00:00:00.01

 SQL> select * from table(dbms_xplan.display);
 PLAN_TABLE_OUTPUT
 -----------------
 Plan hash value: 2896103184

 -----------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name            | Rows  | Bytes | Cost> (%CPU)| Time     |
 -----------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |                 |   424K|    81M| 3170 (4)| 00:00:05 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| QRT_BENCH       |   424K|    81M| 3170> (4)| 00:00:05 |
 |*  2 |   INDEX RANGE SCAN          | IDX_QRT_BENCH_1 | 78876 |       | 303> (5)| 00:00:01 |
 -----------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
   2 - access("QRT_BENCH_DATE"<:A3)
La cardinalité de l'opération 1 (accès aux données de la table) est de 424K ce qui correspond bien à une sélectivité de 5%: 8480798*0.05 = 424039 NOTE: le nombre 8480798 correspond à la colonne NUM_ROWS dans USER_TAB_STATISTICS pour la table QRT_BENCH. Par contre pour l'INDEX RANGE SCAN on retrouve nos 78 876. Alors d'où vient ce nombre? Après avoir reparcouru en vain le livre de Jonathan LEWIS j'ai décidé de lui envoyer un email directement pour connaitre quelle sélectivité le CBO utilisait pour les INDEX RANGE SCAN. A peine le mail parti que mon collègue avait déjà mis le doigt sur la réponse sur le site de Jonathan : http://www.jlcomp.demon.co.uk/cbo_book/ch_04.html#_Index_Selectivity_(14th_Dec_2005) On peut y lire que pour la sélectivité des index (pour les clauses "<" ou ">") le pourcentage appliqué est de 0.9%. Jonathan LEWIS avec sa grande gentillesse et l'humilité qui le caractérise m'a quand même répondu. Le plus drôle dans l'histoire c'est qu'il avait oublié qu'il connaissait la réponse. Il a d'ailleurs écrit un article le soir même sur son blog sur ce sujet: http://jonathanlewis.wordpress.com/2011/02/10/cboddity/ Pour en revenir à notre problème initial la solution consiste donc à réactiver le BIND VARIABLE PEEKING via le paramètre caché:
alter session set "_optim_peek_user_binds"=TRUE;
Le plan qu'on obtient désormais indique une bonne cardinalité pour la table QRT_BENCH et on voit que l'INDEX RANGE SCAN a laissé place à un Full Table Scan plus approprié.
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |      1 |        |      1 |00:00:17.91 |     246K|    197K|
|   1 |  SORT AGGREGATE                 |                             |      1 |      1 |      1 |00:00:17.91 |     246K|    197K|
|*  2 |   HASH JOIN                     |                             |      1 |     22 |      0 |00:00:17.91 |     246K|    197K|
|   3 |    MERGE JOIN CARTESIAN         |                             |      1 |      1 |      1 |00:00:00.04 |      40 |     35 |
|*  4 |     TABLE ACCESS FULL           | MUT_EXCHANGE_NORM_LINK      |      1 |      1 |      1 |00:00:00.03 |      38 |     34 |
|   5 |     BUFFER SORT                 |                             |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| QRT_COMMON_PARAMS_ID_LNS    |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  7 |       INDEX RANGE SCAN          | PK_QRT_COMMON_PARAMS_ID_LNS |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|*  8 |    TABLE ACCESS FULL            | QRT_BENCH                   |      1 |   4136K|    645K|00:00:17.20 |     246K|    197K|
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("QRT_BENCH_ELIOT_ID"="ENL_NORM_CODE")
       filter("QRT_BENCH_NAME" LIKE "LNS_PORTFOLIO_TRANSCOI9QM"||'%')
   4 - filter(("ENL_OBJECT_ID"=:A1 AND INTERNAL_FUNCTION("ENL_EXCHANGE_NORM_ID")))
   6 - filter("LNS_PORTFOLIO_TRANSCOI9QM" IS NOT NULL)
   7 - access("LNS_ID_EUR"=:A2)
   8 - filter(("QRT_BENCH_ELIOT_ID" IS NOT NULL AND "QRT_BENCH_NAME" NOT LIKE '%TESTARVAL' AND "QRT_BENCH_DATE"<:A3 AND
              "QRT_BENCH_PREMIUM"<>0 AND "QRT_BENCH_NAME" IS NOT NULL))

Bien sûr la requête peut être encore améliorée notamment en travaillant sur les clauses de jointure avec la table QRT_BENCH mais au moins avec le BIND PEEKING, le CBO arrive à estimer de bonnes cardinalités.

lundi 31 janvier 2011

Remonter dans le temps avec Flashback Query

La dernière application sur laquelle j'ai travaillé contenait des requêtes basées sur des tables de travail, c'est à dire des tables remplies en début de traitement et supprimées à la fin. Lorsque je devais traiter un problème de performance sur une des requêtes impliquant une ou plusieurs de ces tables je me heurtais au fait que je ne pouvais pas reproduire le problème de performance car ces tables ne contenaient plus les données processées au moment de l'exécution.

Heureusement le Flashback Query m'est venu plusieurs fois en aide.
Le principe du FlashBack Query consiste à requêter une table via une clause SELECT + une clause "AS OF" permettant de préciser à quelle date (Time Stamp) ou à quel Numero SCN on souhaiterait voir l’image de la table.
Imaginons par exemple que mon traitement batch s’exécute entre 4h et 6h du matin et qu’à 9H lors de mon arrivée au boulot (bon ok 10h pour ceux qui me connaissent), je dois analyser pourquoi une requête a mis 30 minutes pour s’exécuter au lieu de 2 minutes. L’idéal serait de pouvoir reproduire le problème de performance sur cette requête mais si les données de ma table de travail ont été supprimées à la fin de mon batch, c’est impossible. L’idée d'utiliser le Flashback Query ici est de pouvoir réinsérer dans cette table les données qu’elle contenait à 4 heures du matin. Selon la taille du tablespace Undo et/ou selon la valeur du paramètre UNDO_RETENTION, il y’a de fortes chances que je puisse récupérer les lignes grâce à la requête suivante :
select * from TABLE_TRAVAIL AS OF TIMESTAMP TO_TIMESTAMP('2011-01-31 04:00:00', 'YYYY-MM-DD HH24:MI:SS');

Exemple:

Comme le PSG a gagné ce week-end je vais me crée une table PSG contenant le numéro, nom et prénom de certains joueurs qui ont fait la gloire de mon club (tiens je viens de perdre des lecteurs marseillais):
SQL> create table PSG (num number, nom varchar2(20), prenom varchar2(20));

Table créée.

SQL> insert into PSG values(1,'LAMA','Bernard');

1 ligne créée.

SQL> insert into PSG values(10,'OKOCHA','Jay-Jay');

1 ligne créée.

SQL> insert into PSG values(9,'PAULETA','Pedro Miguel');

1 ligne créée.

SQL> insert into PSG values(11,'GINOLA','David');

1 ligne créée.

SQL> commit;

Validation effectuée.

SQL> select * from psg;

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David

La table contient 4 lignes.
Je vais maintenant supprimer toutes les lignes de ma table et biens sûr commiter:
SQL> delete psg;

4 ligne(s) supprimée(s).

SQL> commit;

Validation effectuée.

Voilà que j'ai perdu toutes mes lignes :-(

SQL> select * from PSG;

aucune ligne sélectionnée

Grâce au Flashback Query je peux voir les données que contenait cette table avant le DELETE.
La table ayant été supprimé il y'a quelques minutes je peux requêter la table en faisant un SYSTIMESTAMP - 10 minutes:
SQL> select * from PSG AS OF TIMESTAMP ( systimestamp - 10/1440);

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David

Comme par magie j'ai pu retrouver l'image de ma table telle qu'elle était à un moment dans le passé.

Si j'avais voulu requêter la table en précisant une heure précise j'aurais exécuté la requête suivante:
SQL>   select * from PSG AS OF TIMESTAMP TO_TIMESTAMP ('2011-01-31 15:45:00', 'YYYY-MM-DD HH24:MI:SS');

       NUM NOM                  PRENOM
---------- -------------------- --------------------
         1 LAMA                 Bernard
        10 OKOCHA               Jay-Jay
         9 PAULETA              Pedro Miguel
        11 GINOLA               David


CONCLUSION:
En plus d'épater la galerie le Flashback Query peut vraiment vous être d'une grande aide lors d'un DELETE accidentel ou bien lorsque vous vous voulez analyser un problème de requête en PROD sur des données volatiles.
Pour augmenter les chances de conserver vos données du passé vous pouvez augmenter la taille du tablespace UNDO et augmenter la valeur du paramètre UNDO_RETENTION. Ce dernier permet d'augmenter la durée de rétention des données commitées dans le tablespace UNDO.

vendredi 28 janvier 2011

Introduction au parallélisme d’Oracle

Cet article est une introduction à une série d’article à venir sur le parallélisme d’Oracle.

Principe :

Par défaut lorsque vous exécutez une requête celle-ci s’exécute de manière séquentielle c’est à dire que vous n’avez qu’un seul process server qui travaille pour vous. Donc même si vous avez plusieurs CPU, le process server ne pourra en utiliser qu’un seul.

Le but du parallélisme dans une base Oracle est de répartir sur plusieurs process esclaves ("slave process") une tâche adressée initialement à un process server. La coordination de ces process est géré par le process server lui-même qu’on appelle dans ce cas le « Query Coordinator » L’avantage c’est que chacun de ces process esclaves pourra utiliser un CPU différent. On profite ainsi pleinement des ressources disponibles sur le serveur (CPU, RAM, I/O).

Un parallélisme approprié peut faire passer une requête de plusieurs heures à quelques minutes seulement, mais attention, un parallélisme inappropriée peut avoir l’effet inverse.

Analogie :

Le parallélisme utilisé par Oracle ressemble à ce que tout le monde fait dans la vie de tous les jours. Imaginons que vous souhaitez rénover votre appartement. Si vous êtes tout seul à travailler vous devrez repeindre chacune de vos pièces les unes après les autres. Vous travaillerez alors en mode séquentiel. Par contre si vous êtes 4 amis à travailler vous pouvez vous partager les tâches. Chacun par exemple pourra s’occuper de repeindre une pièce. Vous travaillerez donc en mode parallèle. Vous perdrez juste un peu de temps pour vous coordonner mais au final vous serez plus efficaces.

Maintenant imaginons que vous n’ayez qu’un mur à repeindre. Vous perdrez plus de temps en vous mettant à plusieurs qu’en le faisant tout seul (je suis en plein chantier peinture en ce moment chez moi c'est pour ça que cet exemple m'est venu à l'esprit).

Le parallélisme d’Oracle fonctionne selon le même principe. Si vous avez de grosses requêtes qui mettent du temps à s’exécuter et dont les opérations sont facilement divisibles en sous-tâches (ex : parcours de plusieurs partitions d’une table) alors vous y gagneriez en parallélisant. Par contre si vous décidez de paralléliser un select retournant qu’une ligne via un index unique alors votre requête sera moins performante car la gestion des process « slaves » sera plus coûteuse que l’exécution de la requête elle-même.

Quand utiliser le parallélisme :

Le parallélisme est approprié lorsque :
- Vous avez plusieurs CPU disponibles
- Les données sont réparties sur plusieurs disques (stripping)
- L’instruction SQL met du temps à s’exécuter car consomme beaucoup de ressources (ex : Full Table Scan sur une grosse table avec une clause ORDER BY).
- Votre requête ne s’exécute pas dans un environnement hyper concurrentiel (base OLTP)

Le parallélisme peut être utilisé en générale dans les instructions suivantes:
- Un select massif effectuant de l’accès multiblocks (Full Table Scan ou Fast Full Index Scan). On parle de « Parallel Query ».
- Un update/delete/insert massif. On parle de « Parallel DML ».
- Pour les rebuilds d’index ou les CTAS (Create Table As Select). On parle de « Parallel DDL ».


Degrée de parallélisme (DOP) :

Le degré de parallélisme correspond au nombre de process esclaves utilisé pour une instruction en mode parallèle.
Par exemple la commande ci-dessous crée une table TOTO en mode parallèle avec un DOP de 8. Cela veut dire que 8 process « slaves » (donc 8 CPU) seront utilisées pour les requêtes effectuées sur cette table :
CREATE TABLE TOTO (c1 NUMBER, c2 VARCHAR2(100)) PARALLEL 8 ;

Vous pouvez vérifier qu’une table est définie en mode parallèle ou pas en interrogeant la colonne DEGREE de la vue USER_TABLES :
SQL> select table_name,degree from user_tables where table_name='TOTO';

TABLE_NAME                     DEGREE
------------------------------ ----------
TOTO                                    8

Si la clause PARALLEL est utilisée sans spécifier le degré de parallélisme, le DOP utilisée sera alors le résultat de la multiplication suivante :
CPU_COUNT * PARALLEL_THREAD_PER_CPU
CPU_COUNT est le paramètre d’initialisation de la base indiquant le nombre de CPU disponibles et PARALLEL_THREAD_PER_CPU est le paramètre indiquant le nombre de process « slaves » possibles pour un CPU.

Lorsque vous utilisez le parallélisme je recommande de ne pas définir la table en mode parallèle mais plutôt d’utiliser le hint PARALLEL pour les requêtes que vous voulez vraiment paralléliser.

Chaque type d’instructions SQL parallélisable fera l’objet d’un post prochainement :
- Les SELECT en mode parallèle (Parallel Query)
- Les DML en mode parallèle (PDML ou Parallel DML)
- Les DDL en mode parallèle (PDDL ou Parallel DDL)