Une nouvelle fonctionnalité de la version 11g d'Oracle est l'introduction du cache de résultat ou "Result Cache". Ce cache est différent du cache de block déja présent ou db_cache/SGA. Il s'agit d'un cache en base de données coté base de données pour les jeux de données renvoyés par les requêtes ou des sous-requêtes.
Le cache de résultat est intégré dans le pool partagé (shared pool), il est utilisé par toutes les sessions sauf s'il est considéré comme invalide. L'invalidité d'un cache de résultat est déclaré lorsqu'un des objets utilisés par la requête est modifié.
Les requêtes qui balayent un grande quantité de blocks et retourne une faible quantité de données sont des bonnes candidates pour le result cache. Les requêtes de type décisionelle correspondent donc bien à cette définition.
Un paramètre d'initialisation : RESULT_CACHE_MODE permet de déterminer le comportement de la base :
Des paramètres d'initialisation permettent de limiter la consommation mémoire :
Lire la suite : Oracle 11g : Result Cache ou le cache de resultat
A partir de la version 11g d'Oracle, il est possible d'avoir un cache de fonction PL/SQL inter-session. Ce cache s'appuie sur la fonctionnalité de result_cache décrite dans un autre article. La SGA est utilisée pour stocker le résultat de la fonction PL/SQL.
la clause RESULT_CACHE dans la déclaration de la fonction indique à Oracle que la fonction peut utiliser le cache de résultat. La clause optionnelle RELIES_ON permet de spécifier quelle(s) table(s) influence le cache de résultat de la fonction. Si une ou plus des tables en "relies_on" est modifiée (DML/DDL), le cache est invalidé.
Si le système à besoin de mémoire il peut invalidé les caches les plus anciens.
PL/SQL Cache de Fonction | |
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE FUNCTION GetChangeRate(i_SrcCurrId, i_TgtCurrId, i_RefDate) RETURN NUMBER RESULT_CACHE RELIES_ON (TransCurrenciesDate) IS result NUMBER BEGIN SELECT Change_Rate INTO result FROM TransCurrenciesDate WHERE SrcCurrId = i_SrcCurrId AND TgtCurrId=i_TgtCurrId AND RefDate = i_RefDate; RETURN RESULT; END; |
Le cache de résultat pour les fonctions PL/SQL ne peut être utilisé dans les cas suivants:
Avec cette petite requête SQL que vous pouvez rediriger vers une table, il sera facile de créer 10 ans de données pour une table de temps sans avoir recours au PL/SQL.
SELECT TRUNC(SYSDATE - ROWNUM,'DD') AS cod_dat, TO_NUMBER(TO_CHAR(SYSDATE - ROWNUM,'D')) AS cod_jou_sem, TRUNC(sysdate-ROWNUM,'IW') AS cod_sem, TRUNC(SYSDATE - ROWNUM,'MM') AS cod_moi, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'IW')) AS cod_sem_ann, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'MM')) AS cod_moi_ann, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'Q')) AS cod_tri_ann, FLOOR(TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'Q'))/3)+1 AS cod_str_ann, TO_CHAR(sysdate-ROWNUM,'DD/MM/YY') AS lib_crt_dat, TO_CHAR(sysdate-ROWNUM,'DD/MM/YYYY') AS lib_moy_dat, RTRIM(TO_CHAR(sysdate-ROWNUM,'day'))||' '|| RTRIM(TO_CHAR(sysdate-ROWNUM,'DD')) ||' '|| RTRIM(TO_CHAR(sysdate-ROWNUM,'month'))||' '|| TO_CHAR(sysdate-ROWNUM,'YYYY') AS lib_lng_dat, ADD_MONTHS(TRUNC(SYSDATE - ROWNUM,'DD'),-12) AS cod_dat_ap1, ADD_MONTHS(TRUNC(SYSDATE - ROWNUM,'DD'),-24) AS cod_dat_ap2, TRUNC(SYSDATE - ROWNUM,'YY') AS cod_ann, TRUNC(SYSDATE - ROWNUM,'Q') AS cod_tri, CASE WHEN TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'MM')) < 7 THEN TRUNC(SYSDATE - ROWNUM,'YY') ELSE TO_DATE(TO_CHAR(sysdate-ROWNUM,'YYYY')||'0601','YYYYMMDD') END AS cod_str , TO_CHAR(SYSDATE - ROWNUM,'YY-MM') AS LIB_CRT_MOI, TO_CHAR(SYSDATE - ROWNUM,'mon YYYY') AS LIB_MOY_MOI, TO_CHAR(SYSDATE - ROWNUM,'month YYYY') AS LIB_LNG_MOI, TO_CHAR(SYSDATE - ROWNUM,'day') AS LIB_JOU_SEM, TO_CHAR(SYSDATE - ROWNUM,'mon') AS LIB_MOY_MOI_ANN, TO_CHAR(SYSDATE - ROWNUM,'month') AS LIB_LNG_MOI_ANN FROM all_objects WHERE TRUNC(SYSDATE - ROWNUM,'DD')>= TO_DATE(&v_start_date_yyyymmdd,'YYYYMMDD') UNION ALL SELECT TRUNC(SYSDATE + ROWNUM-1,'DD') AS cod_dat, TO_NUMBER(TO_CHAR(SYSDATE + ROWNUM-1,'D')) AS cod_jou_sem, TRUNC(SYSDATE+ROWNUM-1,'IW') AS cod_sem, TRUNC(SYSDATE + ROWNUM-1,'MM') AS cod_moi, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'IW')) AS cod_sem_ann, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'MM')) AS cod_moi_ann, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'Q')) AS cod_tri_ann, FLOOR(TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'Q'))/3)+1 AS cod_str_ann, TO_CHAR(SYSDATE+ROWNUM-1,'DD/MM/YY') AS lib_crt_dat, TO_CHAR(SYSDATE+ROWNUM-1,'DD/MM/YYYY') AS lib_moy_dat, RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'day'))||' '|| RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'DD')) ||' '|| RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'month'))||' '|| TO_CHAR(SYSDATE+ROWNUM-1,'YYYY') AS lib_lng_dat, ADD_MONTHS(TRUNC(SYSDATE + ROWNUM-1,'DD'),+12) AS cod_dat_ap1, ADD_MONTHS(TRUNC(SYSDATE + ROWNUM-1,'DD'),+24) AS cod_dat_ap2, TRUNC(SYSDATE + ROWNUM-1,'YY') AS cod_ann, TRUNC(SYSDATE + ROWNUM-1,'Q') AS cod_tri, CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'MM')) < 7 THEN TRUNC(SYSDATE + ROWNUM-1,'YY') ELSE TO_DATE(TO_CHAR(SYSDATE+ROWNUM-1,'YYYY')||'0601','YYYYMMDD') END AS cod_str , RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'YY-MM')) AS LIB_CRT_MOI, RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'mon YYYY')) AS LIB_MOY_MOI, RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'month YYYY')) AS LIB_LNG_MOI, TO_CHAR(SYSDATE + ROWNUM-1,'day') AS LIB_JOU_SEM, TO_CHAR(SYSDATE + ROWNUM-1,'mon') AS LIB_MOY_MOI_ANN, TO_CHAR(SYSDATE + ROWNUM-1,'month') AS LIB_LNG_MOI_ANN FROM all_objects WHERE TRUNC(SYSDATE + ROWNUM-1,'DD')<= TO_DATE(&v_end_date_YYYYMMDD,'YYYYMMDD') ORDER BY 1 ASC
Il suffit de modifier les dates de début et de fin (&v_start_date_yyyymmdd et &v_end_date_yyyymmdd) pour la période à insérer et le tour est joué.
Pour le calcul des jours ouvrés, une procedure ou une fonction sera la meilleure alliée.
SELECT date_add(current_date ,INTERVAL -1*@rownum:=@rownum+1 DAY) 'COD_DAY', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL -1*@rownum DAY) ,'%Y-%m-01') AS DATE) 'COD_MTH', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL -1*@rownum DAY) ,'%Y-01-01') AS DATE) 'COD_YRD' FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 366) p, (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10) q, (SELECT @rownum:=0) r WHERE date_add(current_date ,INTERVAL -1*@rownum DAY) > 20090101 UNION ALL SELECT date_add(current_date ,INTERVAL @rownumup:=@rownumup+1 DAY) 'COD_DAY', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL @rownumup DAY) ,'%Y-%m-01') AS DATE) 'COD_MTH', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL @rownumup DAY) ,'%Y-01-01') AS DATE) 'COD_YRD' FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 366) p, (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10) q, (SELECT @rownumup:=0) r WHERE date_add(current_date ,INTERVAL @rownumup+1 DAY) < '2013-01-01' ORDER BY 1 ASC
Il est possible de demander à Oracle de collecter des statistiques étendues au niveau du plan d'éxécution. Il est ensuite possible de voir quelles opérations sont les plus couteuses et celles qui sont loin des estimations de l'optimiseur (ecarts entre la colonne E-ROWS et A-ROWS) :
Pour pouvoir obtenir un tel plan, il faut spécifier un hint gather_plan_statitics et appeler ensuite le package dbms_xplan et le procedure display_cursor en utilisant l'option de détail 'ALLSTATS LAST' :
SQL>SELECT /*+gather_plan_statistics*/ COUNT(*) FROM soh_dev.sx3_gaccentry p, soh_dev.sx3_gaccentryd d, soh_dev.sx3_gaccentrya a WHERE p.NUM_0 = a.NUM_0 AND d.NUM_0 = a.NUM_0 AND p.NUM_0= d.NUM_0; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')); -- ou encore plus détaillé : SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED LAST +IOSTATS +MEMSTATS'));
Articles traitant de l'intégration de données
Des tutoriaux et cours gratuits sur Oracle
Tutoriaux sur Unix et les shells scripts