Oracle 11 - Résultat d'une requête en cache

Bonsoir,

Oracle 11 offre la possibilité de conserver le résultat d'une requête en cache. Pour cela, il suffit d'ajouter le hint /*+ resultat_cache */ dans votre requête. Alors allons y pour la preuve par l'exemple:

Pour cela, j'ai un user lao qui dispose d'une jolie table T3(i number,j number) de 40 millions de lignes, et je suis amené à faire des select count(*) dessus régulièrement.

---

sqlplus /nolog
connect lao/lao

SET AUTOTRACE ON
SELECT COUNT(*) FROM T3;

---

Résultat des courses :

Temps d'execution :9 secondes

60868 consistent gets et autant de lectures physiques, et globalement j'aurai le même résultat en rejouant la requête plusieurs fois.

Utilisons notre hint.

---

SELECT /*+ result_cache*/ COUNT (*) FROM T3;

---

Résultat:

La première execution donne bien evidemment le même résultat que sans le hint.

Mais la ou cela devient interessant, c'est lors des autres appels que ce soit dans ma session en cours ou depuis une autre session. Le résultat devient instantané et pour cause, puisqu'Oracle va chercher uniquement le résultat en cache.

Deux constatations:

1. On peut voir dans le plan d'execution l'apparition d'une ligne indiquant pour la colonne opération "Result cache". Ce qui nous montre bien qu'ORACLE soit allé chercher l'information dans le cache.
   
2. On peut également constater, dans les statistiques 0 consistent gets et 0 lecture physiques. Ce qui explique le coté instantané du résultat.

Remarque: Il convient d'utiliser cette technique sur des tables relativement statiques et savoir que potentiellement le résultat de la requête ne refletera pas la réalité du moment.

LAO.

Comments

[Franck]

Salut LAO,

une question: c'est quoi le nolog dans l'ouverture de SQL*Plus ?

Sinon, l'option mise en cache c'est cool, surtout lorsque l'on a une procédure stockée qui pour des besoins de traitements internes doit relancer X fois la même requête.

Mais d'après toi, dans un cas équivalent qu'est-ce qui est le plus performant: l'utilisation d'un tableau avec un BULK COLLECT ou bien l'utilisation du hint et d'un curseur ?

[LAO]

Salut Franck;


Et bien le nolog, c'est la possibilité d'ouvrir une session sql+ sans ouvrir de connexion à ORACLE. Dans une commande DOS, tu peux faire sqlplus system/****@MONALIAS ou  SQLPLUS /nolog, puis
SET INSTANCE MONALIAS et enfin connect system/*****


Je concède que c'est plus long, mais en fait lorsqu'on se connecte régulièrement à des bases différéntes on prends cette habitude. Il m'arrive d'avoir une session sqlplus ouverte non connecté,
prêt à dégainer sur une instance récalcitrante.


Si jamais tu accèdes à une base en local, tu peux également faire tout simplement SQLPLUS /nolog puis connect / as sysdba sans préciser de login et de mot de passe. Vive la sécurité. 


Pour la deuxieme question, je dirai que si dans un bulk, je m'attends à avoir le même résultat, alors je fais une fois la requete et je stocke la valeur dans une variable.


On utilisera le /*+ result_cache */ soit si on pense que la requete renverra le même résultat, soit si on tolère le fait d'avoir un résultat qui ne corresponds pas à l'état de la base (ce qui est
plutot rare).


Je dirai qu'on peut utiliser par exemple ce type de hint sur  une requete allant récuperer des informations de droits. Typiquement, je connais des applications ou a chaque fois qu'on charge
une page on va regarder 2000 règles de sécurité. Dans ce cas, on peut bien sur "Détruire" le code et faire une application propre ou alors si le code SQL est embarqué dans des procédures stockées
ajouter un le fameux hint (à condition d'etre sous ORACLE11).


En terme de cout de dev, c'est rapide, et ca rendra certainement heureux les utilisateurs et des managers dont la pureté technique est un acte de sorcellerie.


LAO.