jeudi 21 février 2013

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
























































































































































































































1 commentaire:

  1. Ahmed,
    Tu me paiera déjà le resto qui tu me dois avant :-)
    Cependant, je te dois rien, car tu as déformé mes propos.
    ça ne s'inscrit pas du tout dans le le cas que je t'avais décris. Moi je parlais de la logique de la double négation. Je t'expliquerai mes propos autour du resto que tu me dois :-)
    Yahya

    RépondreSupprimer