Toutefois, il peut arriver qu’on souhaite forcer le hard parse à chaque exécution pour une requête en particulier.
Imaginons que vous ayez une requête qui se base notamment sur une table temporaire alimentée préalablement par la session. Le contenu d’une table temporaire étant propre à chaque session, vous pouvez avoir une première session exécutant cette requête avec une table temporaire contenant 100 lignes. Comme c’est la première fois que la requête est exécutée celle-ci sera hard parsée et un plan tenant compte des 100 lignes dans la table temporaire sera calculé par le CBO (dynamic sampling effectué sur la table temporaire pour estimer le nombre de lignes retournées). Maintenant une autre session va exécuter la même requête mais cette fois la table temporaire contiendra 1 million de lignes. Comme la même requête a déjà été exécutée auparavant par l’autre session (au niveau du texte c’est exactement la même requête) et qu’un curseur partageable est déjà présent dans la library cache, la requête sera seulement soft parsée et donc le plan qui avait été calculé pour la première session sera réutilisé. Ce plan qui avait été calculé en partant du principe que la table temporaire ne contenait que 100 lignes risque fortement de ne pas être approprié pour cette exécution. Il serait en effet moins couteux ici d’avoir un hard parse supplémentaire avec un plan optimal, plutôt qu’un soft parse mais avec un plan désastreux.
Voici un petit cas simple pour illustrer ce genre de situation que j’ai personnellement déjà rencontré par le passé.
Je crée d’abord une table temporaire T1_TEMP et une table T1 contenant 100 lignes :
SQL> CREATE GLOBAL TEMPORARY TABLE T1_TEMP 2 ON COMMIT PRESERVE ROWS 3 AS SELECT * FROM DBA_OBJECTS WHERE 1=2; Table created. SQL> create index IDX_OBJ_TYP on T1_TEMP(OBJECT_TYPE); Index created. SQL> create table t1 2 as select * from dba_objects 3 where rownum<=100; Table created.Dans une session 1 j’insère dans la table temporaire 100 lignes et j’exécute une requête basée sur cette table temporaire :
SQL> -- session1
SQL> insert into T1_TEMP
2 SELECT * FROM T1;
100 rows created.
SQL> insert into t1
2 select * from T1_TEMP
3 where OBJECT_TYPE='CLUSTER';
5 rows created.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID 7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'
Plan hash value: 3185690956
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.02 | 28 | 2 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:00.02 | 28 | 2 |
|* 2 | TABLE ACCESS FULL | T1_TEMP | 1 | 5 | 5 |00:00:00.01 | 4 | 0 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='CLUSTER')
Note
-----
- dynamic sampling used for this statement (level=2)
La partie SELECT de la requête retourne 5 lignes mais comme la table temporaire contient peu de lignes, le CBO a judicieusement opté pour un Full Table Scan. D’ailleurs le nombre de lignes estimées par le CBO ( E-ROWS) correspond bien au nombre de lignes réellement traitées par le moteur SQL (A-ROWS). Maintenant j’ouvre une autre session et je vais exécuter la même requête sauf que cette fois je vais insérer 1 million de lignes dans la table temporaire : SQL> -- session2
SQL> insert into t1_temp
2 select t1.* from t1,t1 t2,t1 t3;
1000000 rows created.
SQL> insert into t1
2 select * from T1_TEMP
3 where OBJECT_TYPE='CLUSTER';
50000 rows created.
Elapsed: 00:00:02.93
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID 7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'
Plan hash value: 3185690956
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:02.94 | 18507 | 10198 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:02.94 | 18507 | 10198 |
|* 2 | TABLE ACCESS FULL | T1_TEMP | 1 | 5 | 50000 |00:00:02.83 | 11314 | 10196 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='CLUSTER')
Note
-----
- dynamic sampling used for this statement (level=2)
Comme la même requête avait déjà été exécutée par une autre session, cette requête n’a pas été hard parsée et c’est donc le plan déjà existant dans la shared pool qui a été utilisé. Le Full San n’est pas approprié et on voit que le nombre de lignes estimées correspond aux lignes retournées par la requête de la première session. Si cette requête avait été hard parsée on aurait surement eu un plan avec un accès indexé. Pour s’en assurer on peut flusher la shared pool et relancer la même requête : SQL> rollback;
Rollback complete.
Elapsed: 00:00:36.39
SQL> alter system flush shared_pool;
System altered.
SQL> insert into t1
2 select * from T1_TEMP
3 where OBJECT_TYPE='CLUSTER';
50000 rows created.
Elapsed: 00:00:01.92
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID 7df6acdvufgud, child number 0
-------------------------------------
insert into t1 select * from T1_TEMP where OBJECT_TYPE='CLUSTER'
Plan hash value: 1045437032
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:01.68 | 8285 | 747 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:01.68 | 8285 | 747 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1_TEMP | 1 | 29962 | 50000 |00:00:01.53 | 1027 | 744 |
|* 3 | INDEX RANGE SCAN | IDX_OBJ_TYP | 1 | 29962 | 50000 |00:00:00.62 | 441 | 195 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='CLUSTER')
Note
-----
- dynamic sampling used for this statement (level=2)
Un hard parse a été effectué et le CBO a estimé une cardinalité plus proche de la réalité. Le plan contient un accès indexé et est bien meilleur puisqu’on passe de 18507 logical reads à 8285. Cet exemple est assez basique mais il montre bien que dans certains cas il peut être intéressant d’avoir un hard parse à chaque exécution plutôt que de réutiliser toujours le même plan. Comment donc forcer un hard parse ?
Il n’existe pas dans Oracle de moyen explicite de le faire : pas de hint /*+ FORCE_HARD_PARSE */ ni de paramètre caché « _HARD_PARSE » qu’on pourrait mettre à TRUE au niveau de la session. Il faut donc réfléchir un peu et se poser la question suivante: Quand est-ce qu’un Hard parse est effectué ?
Réponses :
1) Lorsqu’il n’existe pas de curseur associé à la requête dans la library cache c'est-à-dire lorsque la requête n’a jamais été exécutée ou bien que le curseur a été flushé de la shared pool pour faire de la place.
2) Lorsque le curseur a été invalidé à cause par exemple d’un calcul de stats sur une table référencée dans la requête
3) Lorsque l’environnement d’exécution est différent (ex : modification d’un paramètre de l’optimiseur au niveau de la session)
Dans mon exemple précédent j’ai utilisé la commande « ALTER SYSTEM FLUSH SHARED_POOL », mais il s‘agit là d’une solution un peu « bourrin » qu’il ne faut surtout pas utiliser dans une application puisqu’il vide tous les curseurs partagés dans la library cache. Avec cette solution on forcerait le hard parse pour toutes les requêtes ce qui dégraderait fortement les performances générales (latches, CPU….).
Solution 1 : Générer un SQL_ID différent
L’idéal pour forcer le hard parse c’est de faire en sorte que chaque exécution de la même requête devienne aux yeux d’Oracle une exécution différente. On pourrait par exemple utiliser une requête SQL dynamique avec une clause WHERE qui serait toujours vraie mais serait différente pour chaque exécution. Par exemple on pourrait ajouter une clause « WHERE :random_value = :random_value » où la variable random_value contiendrait un nombre aléatoire. Cette clause étant toujours vraie, elle n’aurait pas d’incidence sur le résultat de la requête et permettrait de générer un SQL_ID différent à chaque exécution. Imaginons que la requête soit exécutée dans du code PL/SQL, la partie du code qui exécute la requête pourrait ressembler à ceci :
DECLARE random_value number; BEGIN random_value := dbms_random.value; execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value || '=' || random_value; END; /Rejouons notre test précédent en utilisant ce bloc PL/SQL pour l’exécution de la requête qu’on souhaiterait voir hard parsée à chaque exécution :
-- session 1
SQL> insert into T1_TEMP
2 SELECT * FROM T1;
100 rows created.
Elapsed: 00:00:00.04
SQL> Declare
2 random_value number;
3 BEGIN
4 random_value := dbms_random.value;
5 execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value || '=' || random_value;
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';
SQL_ID
-------------
80y71zqh47kr4
1 row selected.
Elapsed: 00:00:00.01
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 80y71zqh47kr4
Enter value for child_number:
old 1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new 1: select * from table(dbms_xplan.display_cursor('80y71zqh47kr4','','allstats last'))
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID 80y71zqh47kr4, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .63062214722457742739970953577612375345=.63062
214722457742739970953577612375345
Plan hash value: 3185690956
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.01 | 8 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL | T1_TEMP | 1 | 5 | 5 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='CLUSTER')
Note
-----
- dynamic sampling used for this statement (level=2)
On voit que pour la session où la table temporaire contient 100 lignes on a bien un plan avec le Full table Scan. Le SQL_ID correspondant à cette requête est le 80y71zqh47kr4. Maintenant exécutons la requête pour la session 2 (celle qui contient 1 millions de lignes) : -- session 2
SQL> insert into t1_temp
2 select t1.* from t1,t1 t2,t1 t3;
1000000 rows created.
Elapsed: 00:00:43.79
SQL> Declare
2 random_value number;
3 BEGIN
4 random_value := dbms_random.value;
5 execute immediate 'insert /*test_PL */ into t1_temp select * from T1_TEMP where OBJECT_TYPE=''CLUSTER'' and ' || random_value || '=' || random_value;
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.12
SQL> select sql_id from v$sql where sql_text like 'insert /*test_PL%';
SQL_ID
-------------
5a5mryn0ug6sg
80y71zqh47kr4
2 rows selected.
Elapsed: 00:00:00.00
SQL> @plan_curs
SQL> SET LINES 500
SQL> SET PAGES 500
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'));
Enter value for sql_id: 5a5mryn0ug6sg
Enter value for child_number:
old 1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','allstats last'))
new 1: select * from table(dbms_xplan.display_cursor('5a5mryn0ug6sg','','allstats last'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID 5a5mryn0ug6sg, child number 0
-------------------------------------
insert /*test_PL */ into t1_temp select * from T1_TEMP where
OBJECT_TYPE='CLUSTER' and .97829861972676033156035029701963270324=.97829
861972676033156035029701963270324
Plan hash value: 1045437032
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:02.75 | 13565 | 775 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:02.75 | 13565 | 775 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1_TEMP | 1 | 109K| 50000 |00:00:02.51 | 1047 | 775 |
|* 3 | INDEX RANGE SCAN | IDX_OBJ_TYP | 1 | 109K| 50000 |00:00:01.04 | 461 | 206 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='CLUSTER')
Note
-----
- dynamic sampling used for this statement (level=2)
La requête a bien été hard parsée puisqu’on obtient directement notre plan avec accès indexé. Le SQL_ID vaut 5a5mryn0ug6sg et est bien différent du premier SQL_ID. Au fur et à mesure des exécutions le SQL_ID générée sera différent et donc un hard parse sera effectué à chaque fois. Il est important dans le bloc PL/SQL de ne pas utiliser de clause USING et d’avoir un paramètre CURSOR_SHARING à EXACT, sinon la variable contenant la valeur aléatoire deviendrait une Bind Variable, et on aurait un seul curseur partagée réutilisée (et donc un même plan) à chaque exécution de la requête.Solution 2 : Invalider le curseur
Au lieu de générer un nouveau SQL_ID à chaque exécution une autre solution consisterait à invalider le curseur déjà dans la shared pool. Pour ce faire vous pouvez utiliser la procédure SET_TABLE_STATS du package DBMS_STATS.
Voyons ça avec un exemple :
-- session 1
SQL> drop table t1;
Table dropped.
SQL>
SQL> create table t1 as select * from dba_objects;
Table created.
SQL>
SQL> create index t1_idx on t1(object_id);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SQL> select sid from v$mystat where rownum=1;
SID
----------
136
Dans une session 1 j’ai crée une table T1et j’ai récupéré l’identifiant de ma session (SID=136). Dans une 2ème session je regarde les statistiques « parse count (hard) » et « parse count (total) » pour ma première session : -- session 2 SQL> SELECT sn.name, ss.value 2 FROM v$statname sn, v$sesstat ss 3 WHERE sn.statistic# = ss.statistic# 4 AND sn.name IN ( 5 'parse count (hard)','parse count (total)') 6 AND ss.sid =136; NAME VALUE ---------------------------------------------------------------- ---------- parse count (total) 1500 parse count (hard) 269Dans ma session 1 j’exécute une requête sur la table T1 :
-- session 1
SQL> select object_id from t1 where object_id= 20;
OBJECT_ID
----------
20
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) 1501 parse count (hard) 270J‘exécute de nouveau ma requête dans ma 1ère session, puis je regarde les stats :
-- session 1
SQL> select object_id from t1 where object_id= 20;
OBJECT_ID
----------
20
-- Session 2
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 1502
parse count (hard) 270
Cette fois la statistique « parse count (total) » a été incrémenté 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 nous allons invalider ce curseur grâce au package DBMS_STATS :
SQL> -- invalidation curseur
SQL> exec dbms_stats.set_table_stats(USER, 'T1', numrows=>null, no_invalidate=>false);
-- Session 1
SQL> select object_id from t1 where object_id= 20;
OBJECT_ID
----------
20
-- Session 2
SQL> SELECT sn.name, ss.value
2 FROM v$statname sn, v$sesstat ss
3 WHERE sn.statistic# = ss.statistic#
4 AND sn.name IN (
5 'parse count (hard)','parse count (total)')
6 AND ss.sid =136;
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 1503
parse count (hard) 271
Après avoir invalidé le curseur et relancer la même requête sur T1 on constate que la statistique « parse count (hard) » a de nouveau été incrémentée de 1. C’est donc que le Hard Parse a bien eu lieu. Il est important de mettre le paramètre NO_INVALIDATE de la procédure à FALSE afin de forcer l’invalidation du curseur. Vous noterez comme moi qu’Oracle aime parfois compliquer les choses. Au lieu d’avoir un paramètre INVALIDATE à TRUE ou FALSE ils ont préféré un paramètre NO_INVALIDATE. C’est de la triple négation que j’ai du mal à comprendre…enfin bref. L’inconvénient de cette solution c’est que tous les curseurs correspondant aux requêtes qui référencent la table T1 seront invalidés. Alors qu’on souhaiterait uniquement agir sur une requête en particulier, avec cette solution on risque d’impacter beaucoup plus de requêtes.3ème solution : Flusher le SQL_ID
La 3ème solution qui me vient à l’esprit serait de récupérer avant chaque exécution l’identifiant de la requête à hard parser et de flusher le curseur associé grâce à la procédure dbms_shared_pool.purge. J’avais déjà parlé de cette astuce en Décembre 2010 dans un article intitulé « Flusher un curseur de la shared pool avec DBMS_SHARED_POOL».
CONCLUSION :
Il existe différents moyens de forcer un hard parse pour une requête donnée :
- Faire en sorte de générer un SQL_ID différent à chaque exécution
- Invalider le curseur présent dans la shared pool
- Flusher le curseur de la shared ppol
Lorsque vous décidez de forcer un hard parse pour une requête donnée il faut bien avoir à l’esprit du risque encouru : contentions au niveau de la library cache, parse call plus long et consommation accrue de CPU.
Le hard parse forcé est justifié quand l’utilisation d’un unique plan pour une requête donnée conduit à des performances désastreuses. Il peut arriver aussi qu’on souhaite forcer un hard parse mais sans qu’on ait la possibilité de toucher au code. Randolf Geist a mis en évidence dans son blog comment il était possible de générer des clauses WHERE de manière dynamique en utilisant le VPD (Virtual Private Database). Cet article est disponible ici.
Merci beaucoup Ahmed, c'est un article très très intéressant...
RépondreSupprimerJ'ai utilisé votre méthode pour rendre le curseur invalid, je le vois la plus parfaite dans ma situation..
Encore Merci