mardi 29 mars 2011

ORA-01031: insufficient privileges

Si vous utilisez une base oracle sous windows et que vous obtenez l'erreur ORA-01031 lorsque vous vous connectez en SYSDBA sans préciser de mot de passe, il y'a de fortes chances que le problème soit lié à la configuration de votre fichier SQLNET.ORA
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 28 13:22:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

En effet lorsque le paramètre SQLNET.AUTHENTICATION_SERVICES de votre fichier SQLNET.ora est positionné à NONE ou pas positionné du tout (NONE est la valeur par défaut), alors vous ne pouvez pas vous connecter en SYSDBA sans préciser de mot de passe.
Pour préciser à oracle que l'authentification se fait par l'OS windows il faut mettre ce paramètre à NTS:
SQLNET.AUTHENTICATION_SERVICES = (NTS)

Ainsi, vous n'êtes plus obligé de préciser le mot de passe:
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 29 15:04:51 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

jeudi 24 mars 2011

ORA-00932: inconsistent datatypes: expected - got CLOB

Si vous tombez sur l’erreur ORA-00932 c’est que vous avez exécuté une requête contenant une colonne de type LOB dans la clause GROUP BY ce qui n’est absolument pas possible dans Oracle.

Voici un petit exemple très simple :
SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 add (job_desc clob);

Table altered.

SQL> update emp2 set job_desc=to_clob(job);

14 rows updated.

SQL> commit;

Commit complete.

SQL> select job, count(1) from emp2 group by job order by 2 desc;

JOB         COUNT(1)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

SQL> select job_desc, count(1) from emp2 group by job_desc order by 2 desc;
select job_desc, count(1) from emp2 group by job_desc order by 2 desc
                                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Dans mon exemple je pourrais m’en sortir en convertissant mon champ CLOB en VARCHAR2. Ceci n’est possible bien sûr que si mon CLOB ne fait pas plus de 4000 octects :
SQL> col jd for A20
SQL> select to_char(job_desc) jd, count(1) from emp2 group by to_char(job_desc) order by 2 desc;

JD                     COUNT(1)
-------------------- ----------
CLERK                         4
SALESMAN                      4
MANAGER                       3
ANALYST                       2
PRESIDENT                     1

Cette restriction fait partie d’un ensemble de restrictions d’utilisation de la clause GROUP BY. Ces restrictions sont listées dans la doc oracle « SQL Language reference » (chapitre 19) :
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065646

This clause is subject to the following restrictions:
• You cannot specify LOB columns, nested tables, or varrays as part of expr.
• The expressions can be of any form except scalar subquery expressions.
• If the group_by_clause references any object type columns, then the query will not be parallelized.

mercredi 9 mars 2011

Ignorer les hints ( _OPTIMIZER_IGNORE_HINTS)

A la question "est-il possible de dire au CBO d’ignorer les hints ? " la réponse est OUI.
Il existe en effet un paramètre caché nommé _OPTIMZER_IGNORE_HINTS qui par défaut est à FALSE et qui peut être mis à TRUE si l’on veut que l’optimiseur ne prenne pas en considération lors du parsing les hints contenus dans les requêtes SQL.

Cette astuce peut être utile notamment après un upgrade de votre base Oracle pour déterminer si les hints présents dans la version précédente de la base sont toujours nécessaires dans la nouvelle version. En combinant cette astuce avec SQL Performance Analyzer (fonctionnalité du Real Application Testing disponible en 11g seulement) vous pouvez très rapidement déterminer si en supprimant les hints les requêtes s’exécutent mieux ou moins bien après le passage en 11g.

Exemple:

Voici un petit test case très simple permettant de constater l' efficacité du paramètre _OPTIMZER_IGNORE_HINTS :

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> select count(1) from t1;

  COUNT(1)
----------
     50574

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)


SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

13 rows selected.


L’exemple précédent montre que le hint FULL(T1) force le Full Table Scan.
Sans le hint le CBO choisit un accès indexé.

Activons maintenant le paramètre _OPTIMZER_IGNORE_HINTS au niveau de la session:
SQL> alter session set "_optimizer_ignore_hints"=TRUE;

Session altered.

SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)

On s’aperçoit que le CBO a bien ignoré le hint qui pourtant fonctionnait bien lorsque le paramètre caché _OPTIMZER_IGNORE_HINTS était à sa valeur par défaut à savoir FALSE.

On peut aussi désactiver ce paramètre uniquement au niveau de la requête via un hint OPT_PARAM.
On a donc là un hint permettant de désactiver les hints. N’est-ce pas magnifique ?

SQL> explain plan for select /*+ opt_param('_optimizer_ignore_hints' 'TRUE') full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)


Si on met le OPT_PARAM après le hint FULL, le hint n’est alors pas ignoré. J’avoue avoir été surpris lorsque j’ai fait le test :

SQL> explain plan for select /*+ full(t1) opt_param('_optimizer_ignore_hints' 'TRUE') */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

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.).