DBLink et ORA-12154

Si vous tombez sur ce lien après une recherche sur google il est fort probable que avez eu affaire au même problème que moi.

SQL> create public database link MOX_SOXS12.WORLD
  2    connect to MOX$OWNER identified by moxowner
  3    using 'OPARSOXS12';

Database link created.

SQL> desc mox_change@MOX_SOXS12.WORLD
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Après avoir crée un DB link pointant sur une base dont l'alias est OPARSOXS12 je me suis retrouvé dans l'incapacité d'accéder aux données de ma base distante. Il semble que l'alias OPARSOXS12 ne puisse être résolu.
Un tnsping sur cet alias montre pourtant que la résolution s'effectue correctement:

C:\>tnsping oparsoxs12

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 09-APR-2013 15:08:06

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

Used parameter files:
C:\oracle\ora11.2\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = SOXDBGDB01.fr.world.socgen) (Port = 1524))) (CONNECT_DATA = (SID = SOXS12)))
OK (80 msec)

Jettons un oeil dans mon TNSNAMES.ora:

IFILE =S:\_Tools\ora11201\network\admin\tnsnames.ora 

Mon TNSNAMES.ora pointe en fait sur un autre fichier tnsnames grâce à la clause IFILE.
L'alias marche très bien lorsqu'on se connecte via SqlPlus mais ça a l'air de poser problème lorsqu'on utilise un DBLink.

Essayons de mettre directement les informations dans le TNSNAMES.ORA du client (la base où se trouve le dblink que j'ai crée):

IFILE =S:\_Tools\ora11201\network\admin\tnsnames.ora
### MACHINE
OPARSOXS12.WORLD =
        (DESCRIPTION =
                (ADDRESS_LIST =
                        (ADDRESS =
                                (PROTOCOL = TCP)
                                (Host = SOXDBGDB01.fr.world.socgen)
                                (Port = 1524)
                        )
                )
                (CONNECT_DATA =
                        (SID = SOXS12)
                )
        )

Maintenant mon DB link fonctionne correctement:

SQL> desc mox_change@MOX_SOXS12.WORLD
 Name

                                                                                                                                   Null?    Type
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------- -----
--- ------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------

 ID

                                                                                                                                   NOT NULL NUMBER
 JOB_ID

                                                                                                                                            NUMBER
 CHANGETYPE_ID

                                                                                                                                   NOT NULL NUMBER
 CHANGE_USER

                                                                                                                                   NOT NULL VARCHAR2(200)
 CHANGE_DATE

                                                                                                                                   NOT NULL DATE
 CHANGE_COMMENT

                                                                                                                                   NOT NULL VARCHAR2(2000)

En faisant une petite recherche sur google je suis tombé sur la page suivante du site AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489021635775

Tom Kyte y dit que la clause IFILE n'est en fait pas supporté pour les fichiers de configuration Oracle Net (*.ORA). A la date où Tom Kyte a écrit ces mots il s'agissait de la version 8i.
La note MOS 1339269.1 semble indiquer qu'en 10g ça n'était toujours pas supporté:

ifiles are not officially supported with Oracle Net admin files
Enhancement request Bug 12676140  Would Like the Use of ifiles to be officially supported for net, is currently outstanding.

Si on effectue une recherche sur la doc Oracle 11g R2 on s'aperçoit que la clause IFILE n'est décrite que pour les fichier de paramétrage d'instance (init.ora).
 

un select qui génère du redo?

On sait tous que les REDO records sont générés normalement dès qu'une mise à jour est effectuée au niveau de la base pour pouvoir être rejoués lors d'un recovery mais surement pas lors d'un  SELECT n'est-ce pas? C'est peut-être ce que certains d'entre vous doivent penser. Et pourtant si. Un SELECT tout simple sans clause FOR UPDATE peut générer du REDO.

Avant d'expliquer la raison de ce phénomène voyons d'abord la preuve avec un exemple:

zizou-SQL>create table T1 as select * from dba_objects;

Table created.

zizou-SQL>insert into t1 select * from t1;

69269 rows created.

zizou-SQL>/

138538 rows created.

zizou-SQL>/

277076 rows created.

zizou-SQL>/

554152 rows created.

zizou-SQL>create index idx1 on t1(owner);

Index created.

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

PL/SQL procedure successfully completed.

zizou-SQL>select COMPONENT,CURRENT_SIZE/1024/1024,MAX_SIZE/1024/1024 from v$sga_dynamic_components;

COMPONENT          CURRENT_SIZE/1024/1024 MAX_SIZE/1024/1024
------------------------- ---------------------- ------------------
shared pool                     184        184
large pool                       4          4
java pool                       4          4
streams pool                       0          0
DEFAULT buffer cache                 340        340
KEEP buffer cache                   0          0
RECYCLE buffer cache                   0          0
DEFAULT 2K buffer cache                0          0
DEFAULT 4K buffer cache                0          0
DEFAULT 8K buffer cache                0          0
DEFAULT 16K buffer cache               0          0
DEFAULT 32K buffer cache               0          0
Shared IO Pool                       0          0
ASM Buffer Cache                   0          0

-- Nb de blocks dans le segment table
zizou-SQL>select blocks from dba_tables where table_name='T1';

    BLOCKS
----------
     15729

J'ai crée dans ma base ZIZOU une table T1 contenant un peu plus de 1 million de lignes. Mon buffer cache fait à peine 340MB et ma table contient 15 729 blocks.
Ma base a une taille de block de 8K.

Pour vérifier que chaque requête génère ou pas du redo je vais utiliser la commande AUTOTRACE.
J'effectue d'abord un select de ma table T1:

zizou-SQL>set autot trace stat 

zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1502  consistent gets
      0  physical reads
      0  redo size
     918591  bytes sent via SQL*Net to client
       6557  bytes received via SQL*Net from client
    560  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
       8384  rows processed

Le premier SELECT sur ma table n'a pas généré de redo (la stat redo size = 0).
Maintenant j'effectue un update massif de ma table T1 et j'exécute de nouveau le même select:

zizou-SQL>update t1 set last_ddl_time=SYSDATE;

1108304 rows updated.

Elapsed: 00:00:50.73
zizou-SQL>commit;

Commit complete.

zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 1767 consistent gets
 23 physical reads
 19124 redo size
 918591 bytes sent via SQL*Net to client
 6557 bytes received via SQL*Net from client
 560 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 8384 rows processed

Je constate cette fois que le SELECT a bien généré du redo.
Je relance une deuxième fois la requête:

zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1502  consistent gets
      0  physical reads
      0  redo size
     918591  bytes sent via SQL*Net to client
       6557  bytes received via SQL*Net from client
    560  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
       8384  rows processed

Là plus rien. La requête ne génère plus de redo.
Pour comprendre ce phénomène il faut se rappeler les concepts sur la gestion des transactions dans Oracle.
Quand une session modifie une ligne dans une table Oracle crée un lock au niveau row en ajoutant une entrée dans l'ITL (Interested Transaction List) dans l'entête du bloc de données contenant la ligne modifiée. Cette entrée ITL contient notamment l'identifiant de la transaction (permettant d'accéder à la table des transactions situées dans le bloc en-tête de l'undo segment) et l'adresse du dernier undo record généré par cette transaction. Si une deuxième session tente de lire cette ligne modifiée (pas encore committée) il verra en checkant l'entrée ITL qu'une transaction active existe pour cette ligne et utilisera les informations dans l'undo segment pour restituer l'image d'avant la modification (consistent read). Quand la transaction est committée, la table des transactions est modifiée dans l'entête du rollback segment pour indiquer que la transaction est committée. Oracle tente également de retrouver le data block modifié par la transaction afin de modifier l'entrée ITL correspondant à la transaction et y indiquer que les changements apportées au bloc par la transaction ont été committés. On dit alors que le "nettoyage" au niveau du bloc de donnée a été effectué lors du commit c'est ce qu'on appelle le commit cleanout. Cependant, si le bloc n'est plus en mémoire au moment du commit (le process DBWR peut avoir écrit le bloc dans le datafile suite à un checkpoint par exemple) ou si le nombre de blocs modifiés par la transaction est trop important, alors Oracle ne va pas s'autoriser à modifier tous les en-têtes des data blocs modifiées et va juste modifier le slot au niveau de la table des transaction dans le bloc en-tête de l'undo segment. La prochaine session qui lira les blocs se rendra compte que ce qu'on a dans l'entrée ITL (transaction non committée) n'est pas cohérent avec ce qu'on a dans la table des transactions de l'undo segment (transaction committée avec indication du numéro SCN) et va faire le travail de nettoyage du bloc lu c'est à dire qu'il va indiquer dans l'entrée ITL que la transaction est en fait bien commitée. Comme le nettoyage de l'en-tête du bloc se fait bien après que le commit ait eu lieu on appelle ce mécanisme le Delayed Block Cleanout.

Puisque le cleanout consiste à modifier l'entrée ITL et donc à modifier le bloc, du redo est forcément généré.
C'est ce qui s'est passé dans mon exemple précédent: le premier SELECT n'a pas généré de redo puisque les blocs étaient "cleans" ensuite lorsque j'ai modifié massivement plus d'un million de lignes le commit n'a pas fait le travail de nettoyage des blocs (Oracle n'a pas pu se permettre de revisiter tous les blocs pour modifier les entrées ITL et puis vu la petite taille de mon cache les blocs étaient pour la plus part déjà ecrits sur disc). Lors du select qui a suivi mon update le process server s'est rendu compte que les entrées ITL n'avaient pas été cleanés et a donc fait le travail de block cleanout pour chaque bloc récupérés par ma requête.

Si j'avais modifié un faible nombre de lignes lors de l'update il y' a de forte chances que le cleanout des blocks ait été fait au moment du commit.
Faisons le test pour s'en assurer:

zizou-SQL>update t1 set last_ddl_time=SYSDATE where owner='APPQOSSYS';

48 rows updated.

zizou-SQL>commit;

Commit complete.

zizou-SQL>select * from t1 where owner='APPQOSSYS';

48 rows selected.

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

Effectivement on constate que cette fois le SELECT n'a pas généré de redo c'est donc que le cleanout a bien été fait au moment du COMMIT.

ORA-12899 et character set

J'ai obtenu aujourd'hui l'erreur suivante en tentant de mettre à jour une table à partir d'une autre table située sur une base distante (via un dblink):

SQL> insert /*+ append */ into LNS$OWNER.MUT_ERROR_LOG_FILE
 2 select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12;
select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12
 *
ERROR at line 2:
ORA-12899: value too large for column
"LNS$OWNER"."MUT_ERROR_LOG_FILE"."ERR_COMMENT" (actual: 242, maximum: 240)

L'erreur précise que je souhaite insérer une chaine de caractères d'une taille de 242 (on verra après à quoi correspond ce nombre) alors que mon champ est défini par un VARCHAR2(240). Et pourtant la table source et la table cible ont étés crées à partir du même code SQL:

create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150) not null,
  ERR_USUALNAME       VARCHAR2(200) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

Comment se fait-il alors que mon INSERT plante si les 2 champs sont en VARCHAR2(240)?
La cause est à chercher du côté du jeu de caractères défini pour chacune des 2 bases.
Ma base source utilise un character set single-byte(WE8DEC) alors que ma base cible utilise un character set multi-bytes, en l'occurence AL32UTF8.
Ce qu'il faut savoir c'est qu'en single-byte, 1 caractère correspond toujours à un seul octect. Par contre en mult-byte un caractère peut être défini sur plusieurs octects (1 à 4 bytes pour UTF8).
Illustrons cela avec un exemple.
Dans ma base en single-byte je crée une table avec une colonne de type VARCHAR2(10):

SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=1: 224

J'ai inséré dans le champ C1 la valeur 'à' et le résultat de la fonction dump indique bien que la taille stockée pour ce caractère est de 1 octect (Len=1).
Faisons la même chose sur la base définie avec un jeu de caractères multi-bytes:

 SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=2: 195,160 

 Cette fois ci le même caractère est stocké sur 2 octects (Len=2).

Revenons maintenant au nombre 240 déterminant la taille de ma colonne ERR_COMMENT.
Le type VARCHAR2 peut être défini selon 2 types de taille: BYTES ou CHAR. Avec un jeu de caractères single_byte un caractère vaut forcément un BYTE. Par contre avec un characterset multibytes un caractère peut être stocké sur plusieurs bytes.
Par défaut si on ne précise pas le type de taille c'est celle définie par le paramètre NLS_LENGTH_SEMANTICS qui est utilisée.
Sur mes 2 bases ce paramètre était défini à BYTE.
Donc dans ma table source j'avais des lignes dont la colonne ERR_COMMENT stockait jusqu'à 240 BYTES soit donc 240 caractères puisqu'elle utilise un jeu de caractères single-byte.
Par contre ces 240 caractères vont consommer plus que 240 BYTES sur ma base cible puisqu'elle utilise un jeu de caractères multi-bytes.
Comme ma colonne est définie avec une taille max de 240 BYTES (puisque NLS_LENGTH_SEMANTICS=BYTE) elle ne peut stocker tous les caractères que ma table source contenait d'où mon erreur ORA-12899: value too large for column.
La solution consiste à créer la table en précisant le type de taille CHAR pour indiquer qu'on souhaite stocker 240 caractères maximum et non 240 Bytes:

create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150 CHAR) not null,
  ERR_USUALNAME       VARCHAR2(200 CHAR) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240 CHAR),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

CONCLUSION:

La morale de cette histoire c'est que lorsque vous utilisez des bases avec un jeu de caractères multi-bytes il vaut mieux préciser (pour les colonnes de type VARCHAR2) une taille de type CHAR plutôt que de type BYTE.
Pour ce faire vous pouvez soit définir le paramètre NLS_LENGTH_SEMANTICS à CHAR ou bien ajouter la clause CHAR lorsque vous définissez vos colonnes de type VARCHAR2.

Astuce: personnaliser son prompt Linux et SQL Plus

Lorsqu'on travaille sur les bases Oracle nous avons généralement plusieurs terminaux Linux ouverts et connectés sur différentes bases. On peut avoir ainsi à la fois des fenêtres connectées sur des bases de PROD et d'autres sur des bases DEV.

Pour éviter la confusion entre ces différentes connexions j'aime modifier mon prompt pour y afficher notamment le nom de l'instance sur laquelle pointe mon $ORACLE_SID. En ouvrant mon terminal je suis sûr de savoir sur quelle base je pointe.

Pour pouvoir modifier l'affichage du prompt il suffit de modifier la variable système $PS1 en ajoutant des variables BASH et/ou des variables d'environnement telles que $ORACLE_SID.

Personnellement je définie ma variable $PS1 de la manière suivante:
PS1='[\u@\W $ORACLE_SID]$ '

J'obtiens ainsi le prompt suivant:

[oracle@oradata orcl1]$ 

La variable BASH \u indique le username Linux, la variable \W indique le répertoire courant. Si je me connecte en SYSDBA je sais d'avance que je pointerai sur l'instance ORCL1.

Bien sûr il est préférable de définir cette variable dans votre fichier .bash_profile pour que votre prompt soit défini automatiquement.

Vous pouvez personnaliser votre prompt à votre guise en choisissant parmi les variables BASH dont la liste se trouve dans le manuel BASH (man bash):

PROMPTING
       When executing interactively, bash displays the primary prompt PS1 when it
       is ready to read a command, and the secondary prompt  PS2  when  it  needs
       more  input to complete a command.  Bash allows these prompt strings to be
       customized by inserting a number of backslash-escaped  special  characters
       that are decoded as follows:
              \a     an ASCII bell character (07)
              \d     the date in "Weekday Month Date" format (e.g., "Tue May 26")
              \D{format}
                     the format is  passed  to  strftime(3)  and  the  result  is
                     inserted  into the prompt string; an empty format results in
                     a  locale-specific  time  representation.   The  braces  are
                     required
              \e     an ASCII escape character (033)
              \h     the hostname up to the first ‘.’
              \H     the hostname
              \j     the number of jobs currently managed by the shell
              \l     the basename of the shell’s terminal device name
              \n     newline
              \r     carriage return
              \s     the  name of the shell, the basename of $0 (the portion fol-
                     lowing the final slash)
              \t     the current time in 24-hour HH:MM:SS format
              \T     the current time in 12-hour HH:MM:SS format
              \@     the current time in 12-hour am/pm format
              \A     the current time in 24-hour HH:MM format
              \u     the username of the current user
              \v     the version of bash (e.g., 2.00)
              \V     the release of bash, version + patch level (e.g., 2.00.0)
              \w     the current working directory, with $HOME abbreviated with a
                     tilde (uses the value of the PROMPT_DIRTRIM variable)
              \W     the  basename  of  the current working directory, with $HOME
                     abbreviated with a tilde
              \!     the history number of this command
              \#     the command number of this command
              \$     if the effective UID is 0, a #, otherwise a $
          \nnn   the character corresponding to the octal number nnn
              \\     a backslash
              \[     begin a sequence of non-printing characters, which could  be
                     used to embed a terminal control sequence into the prompt
              \]     end a sequence of non-printing characters

En plus de personnaliser le prompt du terminal linux vous pouvez modifier le prompt SQL Plus en affichant par exemple le nom de l'instance.
Pour ce faire il suffit de modifier la variable SQL Plus sqlprompt de la manière suivante:

SQL> SET sqlprompt &_CONNECT_IDENTIFIER>
orcl1>

En ajoutant cette commande dans le fichier glogin.sql (qui se trouve dans $ORACLE_HOME/sqlplus/admin) la personnalisation du prompt se fera automatiquement à chaque connexion SQL Plus.

NO_INVALIDATE

Cet article est une réponse à Yahya Mezroui avec lequel j'ai fait un pari aujourd'hui. Je discutait avec lui sur le calcul de stats de tables et la possibilité d'invalider les curseurs liés à cette table présents dans la shared pool en jouant sur le paramètre NO_INVALIDATE de la procédure DBMS_STATS.GATHER_TABLE_STATS. Pour moi, le fait de mettre NO_INVALIDATE=FALSE invalidait les curseurs correspondant aux requêtes référençant la table pour laquelle on effectue un calcul de stats. Pour lui, et ce depuis la 11g, Oracle n'invaliderait plus les curseurs même si on précisait NO_INVALIDATE=FALSE.

Voici donc un petit test case (effectué sur une base 11.2.0.1) prouvant que j'avais bien raison.
J'ouvre une première session et je crée une table T1

-- Session 1
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table T1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

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

PL/SQL procedure successfully completed.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
    33

Ma session correspond au SID 33.

Dans une 2ème session je regarde les statistiques « parse count (hard) » et « parse count (total) » pour ma première session (SID 33) :

 SELECT sn.name, ss.value FROM v$statname sn, v$sesstat ss 
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('parse count (hard)','parse count (total)')
AND ss.sid =33;

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1731
parse count (hard)                            377

Je retourne ensuite sur ma première session et j'effectue une requête toute simple sur T1:

 -- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

Je regarde maintenant les stats sur mon autre session et je constate qu’un Hard Parse a bien été effectué (la statistique « parse count (hard) » a été incrémenté de 1) :

 -- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1736
parse count (hard)                            378

J'exécute de nouveau la requête dans ma session 1 puis dans ma session 2 je check de nouveau les stats de parsing:

-- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1737
parse count (hard)                            378

Cette fois la statistique « parse count (total) » a été incrémentée mais pas la stats « parse count (hard) », il s’agit donc d’un soft parse. C’est normal puisque un curseur partageable existait déjà dans la library cache depuis que la requête a été exécutée la 1ère fois.

Maintenant je lance un calcul de stats sur T1 (depuis une 3ème session pour éviter d'impacter les stats de parsing de ma session 1) mais en indiquant à Oracle que je ne souhaite pas invalider les curseurs (NO_INVALIDATE=TRUE):

-- Session 3
-- Recalcul de stats avec non invalidation du curseur (no_invalidate=TRUE)
SQL> exec dbms_stats.gather_table_stats(user,'T1',no_invalidate=>TRUE);

PL/SQL procedure successfully completed.

Si j'exécute de nouveau ma requête je constate que le « parse count (hard) » n'a pas été incrémenté:

 -- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1738
parse count (hard)                            378

C'est donc bien que le NO_INVALIDATE=TRUE n'a pas invalidé le curseur.

Calculons maintenant les stats en forçant l'invalidation du curseur c'est à dire en mettant le paramètre NO_INVALIDATE à FALSE:

-- Session 3
-- Recalcul de stats avec  invalidation du curseur (no_invalidate=FALSE)
SQL> exec dbms_stats.gather_table_stats(user,'T1',no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

-- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1743
parse count (hard)                            379

Cette fois ci le hard parse a bien eu lieu puisque la statistique « parse count (hard) » a été incrémenté c'est donc bien que le curseur présent dans ma shared pool a bien été invalidé par le calcul de stats effectué dans ma session 3.

Donc Yahya tu me dois bien un resto demain ;-)

Je le tacle gentiment ici mais je viens de passer une semaine avec lui en formation SQL Server (oui oui j'ai bien dit SQL Server) et il m'a appris beaucoup de choses sur Oracle rien qu'en discutant. C'est quelqu'un d'extrêmement compétent avec une connaissance très large des technos Oracle. Nos amis de la BNP ont bien de la chance de l'avoir dans leur équipe de DBAs. Ca n'empêche pas que demain il va me payer le resto...