Database : Nouveau Cache pour plus de Performances

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”

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *