Dans Oracle Database Server 11g, un nouveau « cache » permet de stocker le résultat d’une requête SQL ou d’une fonction PL/SQL et ainsi réduire à zéro (ou presque ! ) le temps de réponse.
La « Shared Pool » prévue jusqu’à présent pour contenir le Library Cache (ordres et analyse des ordres SQL et PL) et le Dictionary Cache (cache du dictionnaire de données), peut maintenant servir à conserver le résultat d’un SELECT ou la valeur retournée d’une fonction PL/SQL.
Cette partie de la « Shared Pool » s’appelle le « Result Cache« .
1- Objectif
Le but de cette fonctionnalité est de permettre la réutilisation directe d’un résultat sans avoir à ré-exécuter l’instruction. Ainsi, quelqu’un exécutant la même requête dans le même contexte reçoit instantanément le résultat qui était déjà prêt.
Les requêtes ou fonctions adaptées à l’exploitation de cette fonctionnalité sont celles travaillant sur de nombreuses lignes pour retourner un résultat peu volumineux.
Cette fonctionnalité peut s’appliquer à des sous-interrogations.
2- Conditions
Pour que la réutilisation du résultat soit possible il faut que plusieurs conditions soient réunies :
– les données de la table n’ont pas changé, pas de mise à jour
– la fonctionnalité soit activée (hint, paramètre, attribut de la table)
– la requête ne fait pas référence à des éléments contextuels ou temporaires
– le résultat soit encore en cache
3- Outils de gestion du RESULT_CACHE
- Package DBMS_RESULT_CACHE
- Vues
V$RESULT_CACHE_STATISTICS V$RESULT_CACHE_MEMORY V$RESULT_CACHE_OBJECTS V$RESULT_CACHE_DEPENDENCY
- Paramètres d’instance :
RESULT_CACHE_MODE={ MANUAL | FORCE }
RESULT_CACHE_MAX_RESULT
RESULT_CACHE_REMOTE_EXPIRATION
4- Mise en cache du résultat d’ordres SQL
- HINTS
/* + RESULT_CACHE */ /* + NO_RESULT_CACHE */
- Caractéristique de la table
CREATE TABLE….RESULT_CACHE (MODE { DEFAULT | FORCE } )
ou ALTER TABLE….RESULT_CACHE (MODE { DEFAULT | FORCE } )
5- Mise en cache du résultat d’une fonction PL/SQL
Ajouter l’option RESULT_CACHE dans la spécification, la clause « relies on » permet de spécifier de quelle table dépend le résultat et de l’invalider si le contenu de cette table change :
CREATE OR REPLACE FUNCTION …. (…) RETURN … RESULT_CACHE RELIES_ON (table) IS …;
6- Exemples d’utilisation
Consulter le contenu du Result Cache :
SQL> SELECT type, status, name, object_no, row_count,row_size_avg 2 FROM v$result_cache_objects 3 ORDER BY 1; no rows selected
Charger une requête dans le Result Cache :
SQL> SET TIMING ON SQL> SELECT /*+ result_cache q_test */ count(*) 2 FROM t_res_cache c1,t_res_cache c2,t_res_cache c3,t_res_cache c4,t_res_cache c5,t_res_cache c6,t_res_cache c7 3 WHERE c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b' and c6.c='b' and c7.c='b'; COUNT(*) ---------- 1 Elapsed: 00:00:24.16
Vérifier le contenu du Result Cache :
SQL> SELECT type, status, name, object_no, row_count,row_size_avg 2 FROM v$result_cache_objects 3 ORDER BY 1; TYPE STATUS ---------- --------- NAME ---------------------------------------------------------------------------------------------------------------- OBJECT_NO ROW_COUNT ROW_SIZE_AVG ---------- ---------- ------------ Dependency Published SCOTT.T_RES_CACHE 79129 0 0 Result Published SELECT /*+ result_cache q_test */ count(*) FROM t_res_cache c1,t_res_cache c2,t_res_cache c3,t_res_cache c4,t_res_cache c5,t_r 0 1 5
Constater le gain de temps aux exécutions suivantes :
SQL> SELECT /*+ result_cache q_test */ count(*) 2 FROM t_res_cache c1,t_res_cache c2,t_res_cache c3,t_res_cache c4,t_res_cache c5,t_res_cache c6,t_res_cache c7 3 WHERE c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b' and c6.c='b' and c7.c='b'; COUNT(*) ---------- 1 Elapsed: 00:00:00.10
Constater l’utilisation du Result Cache :
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ result_cache q_test */ count(*)
3 FROM t_res_cache c1,t_res_cache c2,t_res_cache c3,t_res_cache c4,t_res_cache c5,t_res_cache c6,t_res_cache c7
4 WHERE c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b' and c6.c='b' and c7.c='b';
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 557836369
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1764 | 8544P (1)|999:59:59 |
| 1 | RESULT CACHE | bd29u3xp38xhudk8vzx751h8mv | | | | |
| 2 | SORT AGGREGATE | | 1 | 1764 | | |
| 3 | MERGE JOIN CARTESIAN | | 344P| 15E| 8544P (1)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 1075T| 1443P| 26P (1)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 3358G| 3849T| 83T (1)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 10G| 9851G| 260G (1)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN | | 32M| 23G| 814M (1)|999:59:59 |
| 8 | MERGE JOIN CARTESIAN| | 102K| 49M| 2543K (1)| 08:28:41 |
|* 9 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7925 (1)| 00:01:36 |
| 10 | BUFFER SORT | | 320 | 80640 | 2535K (1)| 08:27:06 |
|* 11 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
| 12 | BUFFER SORT | | 320 | 80640 | 814M (1)|999:59:59 |
|* 13 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
| 14 | BUFFER SORT | | 320 | 80640 | 260G (1)|999:59:59 |
|* 15 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
| 16 | BUFFER SORT | | 320 | 80640 | 83T (1)|999:59:59 |
|* 17 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
| 18 | BUFFER SORT | | 320 | 80640 | 26P (1)|999:59:59 |
|* 19 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
| 20 | BUFFER SORT | | 320 | 80640 | 8544P (1)|999:59:59 |
|* 21 | TABLE ACCESS FULL | T_RES_CACHE | 320 | 80640 | 7923 (1)| 00:01:36 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("C7"."C"='b')
11 - filter("C6"."C"='b')
13 - filter("C5"."C"='b')
15 - filter("C4"."C"='b')
17 - filter("C3"."C"='b')
19 - filter("C2"."C"='b')
21 - filter("C1"."C"='b')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.T_RES_CACHE); attributes=(single-row); parameters=(nls);
name="SELECT /*+ result_cache q_test */ count(*)
FROM t_res_cache c1,t_res_cache c2,t_res_cache c3,t_res_cache c4,t_res_cache c5,t_r"
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_ccz3v2jbs8srk286a17c8" used for this statement
49 rows selected.
Vider le Result Cache :
exec dbms_result_cache.flush;
1 réflexion sur “Database : Nouveau Cache pour plus de Performances”
Fonctionnalité méconnue et tres interressante.- Merci.
Les commentaires sont fermés.