Tutoriaux | Page 7-4

Les derniers tutoriaux - tuto

Note utilisateur: 0 / 5

Détails: Écrit par Romain Ferraton; Mis à jour : 11 mars 2013; Affichages : 10387

Note utilisateur: 4 / 5

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 :

Lire la suite : Oracle et parallelisme

Commentaire (0) Clics: 10387

Détails: Écrit par Romain Ferraton; Mis à jour : 11 mars 2013; Affichages : 8083

Note utilisateur: 5 / 5

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

Détails: Écrit par Romain Ferraton; Mis à jour : 11 mars 2013; Affichages : 4676

Note utilisateur: 4 / 5

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

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

Détails: Écrit par Romain Ferraton; Mis à jour : 11 mars 2013; Affichages : 5821

Note utilisateur: 4 / 5

Il est très fréquents dans les entrepôt de données de vouloir récupérer seulement les données modifiées depuis le dernier traitement de chargement ETL.

Comme alternative à l'utilisation d'une colonne de dernière mise à jour dans certaines tables sources, si l'application opérationnel ne met pas à jour systématiquement cette colonne, cela signifie que l'extraction ETL va manquer certaines modifications.

Pour pallier ce manque coté application opérationnelle il est possible d'utiliser le ora_rowscn :

Petite explication de ce qu'est le ora_rowscn :

ora_rowscn est une pseudo-colonne comme la pseudo-colonne rowid.

Cette colonne peut-être interrogé via sql

exemple :

requête récupérant les 5 dernières lignes modifiées dans une table

SELECT
ROWIDTOCHAR(ROWID) row_id, ora_rowscn, num_0
FROM soh_dev.sx3_pinvoice
WHERE ROWNUM < 6
ORDER BY ora_rowscn DESC;

renvoi

ROW_ID             ORA_ROWSCN NUM_0
AAAR6nAAJAAAAQUAAF 18553598   OFAF07030000578
AAAR6nAAJAAAAQVAAJ 18553598   OFAF07030000914
AAAR6nAAJAAAAQVAAI 18553598   OFAF07030000913
AAAR6nAAJAAAAQVAAH 18553598   OFAF07030000767
AAAR6nAAJAAAAQVAAG 18553598   OFAF07030000765

Le SGDB estampille chaque ligne avec un ora_rowscn à chaque modification de la ligne. La précision est d'environ 3 secondes entre 2 SCNs consécutifs.

En utilisant cette colonne pour n'extraire que les lignes en sources avec un ora_rowscn supérieur ou égal au dernier ora_rowscn utilisé dans le dernier traitement d'extraction pour une table il serait donc possible de récupérer uniquement les lignes ayant subit une modification depuis le dernier traitement.

Cela impliquerait de mettre en place une nouvelle table de paramètres pour conserver le dernier ora_rowscn de chaque table traitée.

Le mécanisme d'extraction sera légèrement plus complexe qu'avec une date de dernier traitement commune à toutes les tables car il faudra :

Lire la suite : Oracle : ora_rowscn pour connaitre les lignes modifiées et ne transférer que celles-ci

Commentaire (0) Clics: 5821

Les derniers tutoriaux - tuto

Oracle et parallelisme

Une requête SQL pour créer un calendrier sur x années

Oracle : Plan d execution étendu (gather_plan_statistics hint)

Oracle : ora_rowscn pour connaitre les lignes modifiées et ne transférer que celles-ci

Sous-catégories

Excel

Oracle

SQL Server

Data Integration

Unix