dimanche 29 août 2010

NLS_LANG et problèmes de conversions de données

Qui ne s'est jamais retrouvé avec des caractères invalides insérées dans sa base alors qu'il ne voulait qu'insérer du texte en français?

Ces problèmes d'affichage ou d'insertion sont dûs à une mauvaise définition de la variable d'environnement NLS_LANG côté client. Cette variable indique en fait à Oracle quel est le système d'encodage des caractères utilisé par le client.

Ce qu'il faut savoir tout d'abord c'est que lorsqu'une base et un client utilisent un jeu de caractères différent, la couche Oracle NET effectue une conversion implicite et transparente des données transmises entre les 2 systèmes d'encodage. Supposons par exemple que j'utilise un client Oracle sous windows en français avec un code page WE8MSWIN1252 et que ma base soit défini avec un characterset AL32UTF8, les données insérées seront donc converties en AL32UTF8 et les données de la base affichées côté client seront converties en WE8MSWIN1252.

Si la conversion se fait de manière automatique, comment se fait-il alors qu'on se retrouve parfois avec des caractères invalides?
Tout simplement parce que la variable NLS_LANG utilisé ne reflète pas le réel système d'encodage du client. Imaginons que dans mon exemple précédent le NLS_LANG ne soit pas défini avec le code page 1252 (WE8MSWIN1252 ) mais en AL32UTF8. Lorsque j'insère des données elles sont réellement encodées en code page 1252 mais Oracle considère que c'est du AL32UTF8 (c'est que dit la variable NLS_LANG) et donc Oarcle NET n'effectuera pas de conversion. Je me retrouve donc dans ma base avec des données encodées en WE8MSWIN1252 alors que le jeu de caractère de la base est AL32UTF8.

Ces problèmes de conversion se retrouvent souvent dans les environnements clients en Windows. En effet, Windows utilise 2 jeux de caractères différents: le code page 1252 pour la partie graphique et le code page 850 pour le mode texte (DOS).

Cela veut donc dire que selon qu'on utilise le mode texte ou le mode graphique la variable NLS_LANG doit être défini différemment. Par défaut cette variable est défini dans la base de registre (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE) et défini avec la valeur FRENCH_FRANCE.WE8MSWIN1252, ce qui signifie que si on se connecte à une base via SQLPLUS sous DOS la conversion se fera avec le code page 1252 alors que mes données sont encodées avec le code page 850. Des données invalides seront insérées dans ma base.

Voici un petit exemple pour bien comprendre.
soit:
- une base définie en AMERICAN_AMERICA.WE8ISO8859P1
- un client SQLPLUS graphique utilisant un code page 1252
- un client SQLPLUS DOS utilisant un code page 850

Sous le client SQLPLUS graphique:
SQL> create table nls_test(c1 varchar2(50));

Table créée.

SQL> insert into nls_test values ('une ligne insérée avec windows graphique');

1 ligne créée.

SQL> commit;

Validation effectuée.

Sous le client SQLPLUS texte:
SQL>  insert into nls_test values ('une ligne insérée avec windows texte');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

Sous le client SQLPLUS graphique:
SQL> select * from nls_test;

C1
--------------------------------------------------
une ligne ins¿r¿e avec windows texte
une ligne insérée avec windows graphique


Sous le client SQLPLUS texte:
SQL> select * from nls_test;

C1
--------------------------------------------------
une ligne ins┐r┐e avec windows texte
une ligne insÚrÚe avec windows graphique

Comment expliquer ces erreurs d'affichage?
Sous SQLPLUS en mode graphique le système d'encodage utilisée est WE8MSWIN1252 et la couche Oracle NET le sait (car NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252). Il sait aussi que la base est en WE8ISO8859P1 et effectue donc la bonne conversion lors de l'insertion. Le processus inverse est effectué lors du SELECT.

Sous SQLPLUS en mode texte le système d'encodage utilisée est WE8PC850 mais Oracle NET pense que le système utilisée est WE8MSWIN1252 (car NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252) et utilise donc le mauvais code page pour effectuer la conversion. Les données insérées sont donc invalides.

Pour effectuer des insertions correctes il aurait falu que je définisse au niveau de ma session DOS le bon NLS_LANG comme dans l'exemple ci-dessous:

Sous le client SQLPLUS texte:
D:\>set NLS_LANG=FRENCH_FRANCE.WE8PC850

SQL> truncate table nls_test;

Table tronquée.

SQL> insert into nls_test values ('une ligne insérée avec windows texte');

1 ligne créée.

SQL> commit;

Validation effectuée.

SQL> select * from nls_test;

C1
--------------------------------------------------
une ligne insérée avec windows texte


CONCLUSION: La variable NLS_LANG doit toujours refléter le système d'encodage utilisé par le client.

mardi 17 août 2010

SELECT FOR UPDATE OF et ORA-01733

En 11G R2, je suis tombé récemment sur un bug un peu "sioux" qui pourrait être simplifié par le testcase suivant:

SQL> create table t1 (t1_c1 number);

Table crÚÚe.

SQL> select x.t1_c1 from
2  (select t1.t1_c1 from t1) x
3  for update of x.t1_c1;
for update of x.t1_c1
*
ERREUR à la ligne 3 :
ORA-01733: les colonnes virtuelles ne sont pas autorisées ici


SQL> select b.t1_c1 from
2  (select t1.t1_c1 from t1) b
3  for update of b.t1_c1;

aucune ligne sélectionnée

En gros, lorsque j'alias ma subquery avec la lettre "X" j'obtiens une erreur ORA-01733, par contre si j'utilise une autre lettre ça fonctionne.

Bon, par rapport au progiciel sur lequel je travaille, le workaround est simple il suffit de modifier le nom de l'alias.

J'ai quand même ouvert un case au support Oracle pour en savoir plus, et apparemment cette erreur est dû au fait que j'ai dans ma base un fonction qui porte le nom "X".
En faisant, une petite recherche sur le nom de mes objets je me suis effectivement aperçu qu'il existait un synonyme public (pointant sur une fonction du schéma MDSYS) qui portait ce nom:

SQL> select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from all_synonyms where SYNONYM_NAME='X';

SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ----------------------------------------- 
X                              MDSYS                          OGC_X

Le schema MDSYS est généralement présent lorsque les options MULTIMEDIA ou SPATIAL ont été installées.
Si mon alias s'appelait TOTO et que j'avais une fonction ou un synonyme portant ce nom, je serais tombé sur la même erreur:
SQL> select toto.t1_c1 from
  2  (select t1.t1_c1 from t1) toto
  3  for update of toto.t1_c1;

aucune ligne sélectionnée

SQL> create or replace function toto return number is
  2  begin
  3     return 1;
  4  end;
  5  /

Fonction créée.

SQL> select toto.t1_c1 from
  2  (select t1.t1_c1 from t1) toto
  3  for update of toto.t1_c1;
for update of toto.t1_c1
              *
ERREUR à la ligne 3 :
ORA-01733: les colonnes virtuelles ne sont pas autorisées ici

 
Donc pour résumer, si un jour vous tombez sur cette erreur lors d'un SELECT FOR UPDATE OF c'est qu'il existe surement un objet dans votre base portant le même nom que l'alias de votre sous-requête.

mercredi 11 août 2010

Execution Plan(1): Qu'est-ce qu'un plan d'exécution?


Ce post est une introduction à une série d'articles sur les plans d'exécution.
Il s'agit ici de définir à quoi sert un plan d'exécution.

Dans les autres postes je tenterai d'expliquer:
- comment on peut récupérer un plan d'exécution ?
- comment lire un plan d'exécution ?
- comment tuner une requête SQL non performante à partir de son plan d'exécution
?

Le plan d'exécution correspond en fait aux différentes opérations effectuées par Oracle pour exécuter une requête SQL c'est-à-dire qu'il définit la méthode de récupération des lignes désirées dans le carde d'un SELECT ou bien des lignes à modifier dans le carde d'une requête DML. Ce plan est calculé par l'optimiseur d'Oracle, qu'on appelle le CBO (COST-BASED OPTIMZER), pendant la phase de hard parsing de la requête. Il est choisi parmi plusieurs autres plans car il correspond au plan le moins couteux (COST le plus faible).

Le plan d'exécution va donc décrire la méthode d'accès aux tables (ex: Full scan de la table, accès via un index B-TREE etc.), l'ordre des tables jointes et la méthode de jointure adoptée.

Par exemple, dans le plan d'exécution suivant on voit que le CBO a choisi d'effectuer une jointure par NESTED LOOP pour joindre les tables EMPLOYEES et DEPARTMENT. On voit aussi que l'accès à la table EMPLOYEES se fait via l'index unique EMP_EMP_ID_PK et l'accès à la table DEPARTMENT se fait via l'index unique DEPT_ID_PK.

-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  NESTED LOOPS                       |               |
|   2 |   TABLE ACCESS BY INDEX ROWID       | EMPLOYEES     |
|*  3 |    INDEX UNIQUE SCAN                | EMP_EMP_ID_PK |
|   4 |   TABLE ACCESS BY INDEX ROWID       | DEPARTMENTS   |
|*  5 |    INDEX UNIQUE SCAN                | DEPT_ID_PK    |
-------------------------------------------------------------

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

3 - access("E"."EMPLOYEE_ID"=206)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


Le plan d'exécution pour une requête donnée peut être influencé par les éléments suivants:
- Les statistiques systèmes
- Les statistiques collectées sur les objets impliqués dans la requête
- Les paramètres d'instance liés à l'optimiseur (OPTIMZER_*, CURSOR_SHARING)
- Les hints

Exemple, si je rajoute le hint /*+ FULL(EMPLOYEES) */ à ma requête précédente le plan ne sera plus le même car je force ainsi le CBO à faire un FTS (Full table Scan) de la table EMPLOYEES:

-------------------------------------------------
| Id  | Operation                    | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  NESTED LOOPS                |             |
|   2 |   NESTED LOOPS               |             |
|*  3 |    TABLE ACCESS FULL         | EMPLOYEES   |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |
----------------------------------------------------

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

3 - filter("E"."EMPLOYEE_ID"=206)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

mardi 3 août 2010

Nouveauté 11g: Interval partitioning

Un des inconvénients lorsqu'on utilise le partitioning est le fait d'avoir à ajouter manuellement les partitions lorsqu'on insère des données pour lesquelles aucune partition existante ne correspond.
Par exemple, pour une table partitionnée selon la date de mise à jour (une partition = 1 mois) il fallait soit pré-créer à l'avance des partitions pour les futurs mois, soit créer de nouvelles partitions au fur et à mesure qu'on avance dans le temps.


Oracle gomme ce soucis en proposant avec la 11g le partitioning par INTERVAL.
Il s'agit en fait d'une extension du partitioning BY RANGE. Avec ce type de partitioning si une ligne d'une table partitionnée BY RANGE selon une colonne DATE ne correspond pas à une partition existante, oracle créera automatiquement la partition manquante.

EXEMPLE

Tout d'abord je crée une table partitionnée BY RANGE avec l'option INTERVAL:

SQL> CREATE TABLE test_interval
2  (id number,
3  creation_date date default sysdate)
4  partition by range (creation_date)
5  interval (numtoyminterval(1,'MONTH'))
6  ( PARTITION p_jan2010 VALUES
7  LESS THAN (TO_DATE('01-02-2010','DD-MM-RRRR')))
8  /

Table crÚÚe.

La table est partitionnée selon la colonne CREATION_DATE. La première partition correspond aux données ayant une date inférieure au 01/02/2010. La fonction interval (numtoyminterval(1,'MONTH') indique que chaque partition correspond à un mois.


SQL> set lines 500
SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Si j'insère une ligne dans la table avec une date au mois de janvier 2010 la ligne ira dans la partition existante, par contre si j'insère une ligne avec une date au mois de février Oracle va créer la partition manquante et mettre la ligne dans cette nouvelle partition:

SQL> insert into TEST_INTERVAL values (1, '01-01-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> insert into TEST_INTERVAL values (2, '01-02-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P27                        TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
La partition SYS_P27 est la partition crée par Oracle. On perd donc le contrôle sur le nommage des partitions mais on n'a plus le soucis de créer à la main les partitions manquantes.

Si vous venez de migrer de la 10g vers la 11g et que vous avez des tables partitionnées BY RANGE vous pouvez appliquer l'Interval partitioning sur cette table sans avoir à recréer votre table. Il suffit d'utiliser la commande suivante:


SQL> alter table test_interval set interval (NUMTOYMINTERVAL(1, 'MONTH'));

Table modifiÚe.

Pour que ça fonctionne il ne faut pas que vous ayez de partition avec une MAXVALUE sinon vous tomberez sur l'erreur suivante: ORA-14759.

Il existe d'autres nouveautés en matière de partitioning avec la 11g mais ils feront l'objet d'un nouveau post.