Les derniers tutoriaux - tuto

Détails
Affichages : 5906

Vote utilisateur: 4 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles inactives

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;
 

 

 Restrictions

Le cache de résultat pour les fonctions PL/SQL ne peut être utilisé dans les cas suivants:

  • La fonction possède des paramètres OUT
  • La fonction possède des paramètres IN de type LOB (CLOB, BLOB,NCLOB) ou REF CURSOR, une collection, objet ou enregistrement
  • La fonction retourne un LOB, un REF CURSOR, une collection, un objet ou enregistrement. 
  • La fonction ne peut donc pas être une fonction "pipelined"

 

Commentaire (0) Clics: 5906

Détails
Affichages : 15560

Vote utilisateur: 4 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles inactives

Sans avoir besoin d'un RAC (Real Application Cluster), d'un grid ou d'un exadata, Oracle fourni en standard un mécanisme de parallelisme très poussé.

Le parallelisme peut se retrouver dans les requêtes de selection mais également dans les chargements de masse via SQL (create as select, insert into select,...)

Commençons par étudier la partie selection et les mécanismes internes d'Oracle qui permettent d'accélérer les traitements par utilisation du parallelisme.

Une requête select utilisera le parallelisme si:

  • Un ou plusieurs objets utilisés par la requête (Table, Index, Vues matérialisée...) ont été créés (ou altérés) avec l'option PARALLEL
  • Ou si la requête elle même contient un hint +PARALLEL(table dop) ou +PARALLEL_INDEX(index dop) avec dop un entier (optionnel) représentant le degré de parallelisme souhaité.
  • Dans tous les cas, si l'optimiseur considère que l'utilisation du parallelisme est bien pertinant pour la requête.

Le CBO (Cost Based Optimizer), est bien le décideur pour savoir si le recours au parallélisme est utile ou non. En fonction des valeurs en paramètre par exemple (toujours à conditions d'avoir de bonnes statistiques). L'optimiseur peut décider que pour une valeur particulière en  prédicat il est préférable de faire un table scan et dans ce cas, si la table à l'option PARALLEL ou un hint PARALLEL, le scan se fera en parallèle, alors que pour une autre valeur du même prédicat, l'utilisation d'un index est préférable et l'optimiseur peut choisir un autre plan pour cette valeur.

L'utilisation du parallelisme réduisant la plupart (voir toujours) le cout d'un scan, pour l'optimiseur, on croit à tort, que le hint /*+PARALLEL(t)* forcera le scan de la table en parallèle. Or c'est faux, il est possible et probable que l'optimiseur bascule vers se type de plan mais il reste maitre de modifier le plan s'il considère le scan comme non optimal.

Exemple :

Commentaire (0) Clics: 15560

Détails
Affichages : 15886

Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

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.

requête calendrier Oracle
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.

 

Calendrier simple sur x années pour Mysql
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

 

Commentaire (0) Clics: 15886

Détails
Affichages : 7652

Vote utilisateur: 4 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles inactives

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) :

oracle_extented_explains.jpg





 

 

 

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' :

  • ALLSTATS : pour avoir un détail complet des statistiques étendues
  • LAST : pour n'avoir que les données de la dernière éxécution et non des statistiques cumulées.
obtenir un plan d'éxécution étendu grace au hint gather_plan_statistics
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'));
 
 

 

Commentaire (0) Clics: 7652
  1. Oracle : ora_rowscn pour connaitre les lignes modifiées et ne transférer que celles-ci
  2. Charger des données LinkedIn dans SAP HANA avec Informatica PowerCenter
  3. Oracle : Partitionnement et performance
  4. Oracle 11g : Index Invisible

Sous-catégories

Data Integration

Articles traitant de l'intégration de données

Oracle

Des tutoriaux et cours gratuits sur Oracle

Unix

Tutoriaux sur Unix et les shells scripts