Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, OMAR.

Asked: April 28, 2021 - 10:09 am UTC

Last updated: April 28, 2021 - 5:09 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Can you advise me in how optimize this batch script :
CREATE OR REPLACE PACKAGE BODY ADMIN_WAFA.PCK_Trait_Pre IS
--------------------------------------------------------------------------------
FUNCTION Fun_Traiter_Prelevement(P_CONTROL_ANO VARCHAR2) RETURN VARCHAR2 IS
-------------------------------------------------------------------------------------------------------------------------
---curseur prelevement-----
CURSOR Prov_Cursor  IS
       SELECT    *
       FROM     Prov_Prelevement;

---------------------------
Code_Tier NUMBER;
v_sql VARCHAR2(1000);
--------------- ?????????????????????????????? ------------------------------------
BEGIN
  /***********************
   Calcul des STATS
  ************************/
  BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'ADMIN_WAFA',
                                TABNAME          => 'PROV_PRELEVEMENT',
                                DEGREE           => 8,
                                CASCADE          => TRUE,
                                METHOD_OPT       => 'FOR COLUMNS SIZE AUTO',
                                ESTIMATE_PERCENT => 20);
  END;

  /************************
  Resoudre PB PERF
  ************************/
--  alter session set optimizer_mode=RULE;
  EXECUTE IMMEDIATE ('alter session set optimizer_mode=RULE');

--Début FZ HANOUNOU
IF(P_CONTROL_ANO ='O') THEN
  Proc_Test_Anomalie;
END IF;
--Fin   FZ HANOUNOU
  ---------------------------------------------
  --Modification par Karim EL HALOUI
  --le 15/12/2010
  --pour la mise à jour de la vue matérialisée
  ---------------------------------------------
  -- suppression
  BEGIN
    EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW vue_anomalie_ie_aff');
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  -- création
  v_sql:='CREATE MATERIALIZED VIEW vue_anomalie_ie_aff
          AS
          SELECT a.ano_num_aff,a.ano_ide_reg,a.ano_dat_ech
          FROM anomalie a
          where a.ano_pret_a>45';
  execute immediate v_sql;
  ------------------ ?????? -------------------
  v_sql:='alter FUNCTION F_ECH_ANOM compile';
  execute immediate v_sql;
  v_sql:='alter package PKG_CTRL_PROMESSE_REG compile body';
  execute immediate v_sql;
  ---------------------------------------------
  v_nom_fonction:='Proc_Traiter_Prelevement';
   open Prov_cursor;
  LOOP
    FETCH  Prov_cursor INTO Prov_record;
    EXIT WHEN Prov_cursor%NOTFOUND OR Prov_cursor%NOTFOUND is NULL;

    ----------------------------------
    -- recherche du code_societe
    ----------------------------------
    select s.code_societe
    into v_code_societe
    from societes s
    where s.lib_societe=prov_record.prov_societe;
    ---------------------------------------------
    IF not Fun_Test_New_aff THEN
       IF Fun_Test_New_tier THEN
          Code_Tier:=Fun_Ajout_Tier;
       ELSE
          Code_Tier:=Fun_Cle_Tier;
        END IF;
    ELSE
        Code_Tier:=Fun_Ajout_Tier;
    END IF;
    Proc_Ajout_Prelevement(Code_Tier);
  END LOOP;
  execute immediate('truncate table prov_prelevement');
  v_nom_fonction:='proc_generation_interne';

  pck_ban.proc_generation_interne;
  --------------------------------------------------------------------
  v_nom_fonction:='proc_saisie_imp_bqe_interne';
 
  proc_saisie_imp_bqe_interne;

  v_nom_fonction:='pck_ban.proc_generation_doti';

  pck_ban.proc_generation_doti;

  update /*+choose*/ prelevement p
  set p.pre_dat_ech=to_char(sysdate,'DD/MM/YYYY')
  where exists (select null from vacation v
                where v.pre_ide_reg=p.pre_ide_reg)
  and  p.pre_ide_reg<0
  and  exists (select null from tiers t
               where p.tier_cle=t.tier_cle
               and   t.tier_code_societe=51
               and   t.tier_nom_agence='REPRCTX')
  and   p.pre_dat_ech>to_char(sysdate,'DD/MM/YYYY');
  ------Fin forcage

  return ('OK');
  --exception
   --when others then
          --return(v_nom_fonction);
          --rollback;
END Fun_Traiter_Prelevement;

FUNCTION FUN_AJOUT_TIER RETURN NUMBER IS
----------------------------------------------------------------------------------------------------------------------------------
Code_Seq NUMBER;
v_temp_tiers number :=0;
BEGIN
  v_nom_fonction:='FUN_AJOUT_TIER';
  -----------------------------------------
  -- recherche s'il s'agit affaire credor ou non
  --------------------------------------
  select count(*)
  into v_temp_tiers
  from wdv_aff_credor w
  where w.ie_affaire=Prov_record.prov_pre_num_aff;
  -----------------
  if v_temp_tiers<>0 then
     v_provenance:='C';
  else
     v_provenance:='W';
  end if;


  if substr(Prov_record.Prov_pre_rib_pay,1,3)='DOT' then
     v_matricule:=ltrim(substr(Prov_record.Prov_pre_rib_pay,7),'0');
     -- pour la doti on prend tjs les 7 premiers caracteres
     v_ref_externe:=substr(Prov_record.prov_pre_num_aff,1,7);
  elsif   substr(Prov_record.Prov_pre_rib_pay,1,2)='GE' then
       v_ref_externe:=Prov_record.prov_pre_num_aff;
       v_matricule:=ltrim(substr(Prov_record.Prov_pre_rib_pay,7),'0');
  else
     v_ref_externe:=Prov_record.prov_pre_num_aff;
  end if;
  ---------------------------------------
  -- le cas d'une ancienne affaire
  -- est traite dans la function fun_test_new_tiers
  ----------------------------------------
  insert into tiers (tier_cle,tier_mod_reg,tier_nom_pay
                     ,tier_pre_pay,tier_adr_pay
                     ,tier_nom_cli,tier_pre_cli
                     ,tier_dat_pre_ech,tier_dat_der_ech
                     ,tier_pret_k,tier_pret_n
                     ,tier_rib_pay,tier_pret_a,tier_tiers_impute
                     ,tier_nom_agence,tier_code_categ
                     ,tier_code_societe
                     ,tier_ref_externe
                     ,tier_code_provenance
                     ,tier_matricule)

              values(Seq_Tier.NEXTVAL,Prov_record.Prov_pre_mod_reg,Prov_record.Prov_pre_nom_pay
              ,Prov_record.Prov_pre_pre_pay,Prov_record.Prov_pre_adr_pay
              ,Prov_record.Prov_pre_nom_cli,Prov_record.Prov_pre_pre_cli
              ,Prov_record.Prov_pre_dat_pre_ech,Prov_record.Prov_pre_dat_der_ech
              ,Prov_record.Prov_pre_pret_k,Prov_record.Prov_pre_pret_n
              ,Prov_record.Prov_pre_rib_pay,Prov_record.Prov_pre_pret_a
              ,Prov_record.prov_pre_tiers_impute,Prov_record.prov_pre_nom_agence
              ,Prov_record.prov_pre_code_categ
              ,v_code_societe
              ,v_ref_externe
              ,v_provenance
              ,v_matricule);

  select   Seq_Tier.currVAL   into Code_Seq from dual;
          RETURN(Code_Seq);

END FUN_AJOUT_TIER;
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
--)))))))))))))))))))))))))))))))))))))))))))))))))))
--Fonction qui retourne le dernier numero de tiers pour un numero d'affiare
--Modifie par       :
--Date Modification :
-------------------------------------------------------------------------------------------------------------------------
FUNCTION FUN_CLE_TIER  RETURN NUMBER IS
-------------------------------------------------------------------------------------------------------------------------
Code_Seq NUMBER;
BEGIN
  v_nom_fonction:='FUN_CLE_TIER';
  SELECT max(tier_cle) into Code_Seq FROM prelevement WHERE pre_num_aff=prov_record.prov_pre_Num_Aff;
  RETURN(Code_Seq);
END FUN_CLE_TIER;

FUNCTION FUN_TEST_NEW_AFF  RETURN BOOLEAN  IS
-------------------------------------------------------------------------------------------------------------------------
v_num    NUMBER;
BEGIN
  v_nom_fonction:='FUN_TEST_NEW_AFF';
  SELECT  count(pre_num_aff)
  INTO    v_num
  FROM    prelevement
  WHERE    pre_num_aff=prov_record.prov_pre_num_aff;
  IF v_num=0 THEN
     v_aff_nouv:=1;
   RETURN(TRUE);
  ELSE
    v_aff_nouv:=0;
    RETURN(FALSE);
  END IF;
END FUN_TEST_NEW_AFF;

FUNCTION FUN_TEST_NEW_TIER
RETURN BOOLEAN IS
-------------------------------------------------------------------------------------------------------------------------
tier_Chaine    varchar2(2000);
Prov_Chaine    varchar2(2000);
V_TIER_CLE     number(10);
---curseur du tiers------------------------
/*CURSOR tier_cursor (p_cur_pre_num_aff prelevement.pre_num_aff%type) IS
       SELECT  tier_nom_pay,
              tier_pre_pay,
              tier_adr_pay,
              tier_rib_pay,
              tier_pret_a,
              tier_tiers_impute,
              tier_code_categ,
              tier_code_societe,
              tier_ref_externe
       FROM    tiers
       WHERE  tier_cle = (SELECT max(tier_cle) FROM prelevement WHERE pre_num_aff=p_cur_pre_num_aff);*/

CURSOR tier_cursor (p_tier_cle prelevement.tier_cle%type) IS
       SELECT  tier_nom_pay,
              tier_pre_pay,
              tier_adr_pay,
              tier_rib_pay,
              tier_pret_a,
              tier_tiers_impute,
              tier_code_categ,
              tier_code_societe,
              tier_ref_externe
       FROM    tiers
       WHERE  tier_cle = p_tier_cle;

tier_record    tier_cursor%ROWTYPE;
-------------------------------------------
BEGIN
  v_nom_fonction:='FUN_TEST_NEW_TIER ';
  ---Optimistaion
  V_TIER_CLE:=NULL;
  SELECT max(tier_cle)
  INTO V_TIER_CLE
  FROM prelevement
  WHERE pre_num_aff=prov_record.prov_pre_num_aff;
  ---
  open tier_cursor(/*prov_record.prov_pre_num_aff,*/V_TIER_CLE);
  FETCH  tier_cursor INTO tier_record;
  --------------------------------------
  v_ref_externe:=tier_record.tier_ref_externe;
  --------------------------------------
  -- test sur = des champs
  -- selection de la ligne tiers
   tier_Chaine  :=rtrim(tier_record.tier_nom_pay)
                ||rtrim(tier_record.tier_pre_pay)
                ||rtrim(tier_record.tier_adr_pay)
                ||rtrim(tier_record.tier_tiers_impute)
                ||xtrim(tier_record.tier_code_societe)
                ||rtrim(tier_record.tier_rib_pay)
                ||xtrim(tier_record.tier_pret_a);
  -- selection de la ligne provisoir
  Prov_Chaine  :=rtrim(Prov_record.Prov_pre_nom_pay)
              ||rtrim(Prov_record.Prov_pre_pre_pay)
              ||rtrim(Prov_record.Prov_pre_adr_pay)
              ||rtrim(Prov_record.prov_pre_tiers_impute)
              ||xtrim(v_code_societe)
              ||rtrim(Prov_record.Prov_pre_rib_pay)
              ||xtrim(Prov_record.Prov_pre_pret_a);
  IF Prov_Chaine <> tier_Chaine THEN
    RETURN(true);
  ELSE
    RETURN(False)  ;
  END IF;
END FUN_TEST_NEW_TIER;


PROCEDURE PROC_AJOUT_PRELEVEMENT (p_tier_cle tiers.tier_cle%type) IS
-------------------------------------------------------------------------------------------------------------------------
Code_Tp_Pre varchar2(1);
CaseText VARCHAR2(2);
v_ban_code varchar2(6);
v_ge_code varchar2(6);
BEGIN
  v_nom_fonction:='PROC_AJOUT_PRELEVEMENT';
  CaseText:=(UPPER(SUBSTR(Prov_record.prov_pre_rib_pay,1,2)));

 IF CaseText='DO'OR CaseText='GE' THEN
     Code_Tp_Pre:=SUBSTR(CaseText,1,1);
     IF Code_Tp_Pre= 'G' THEN
        v_ge_code:=SUBSTR(Prov_record.prov_pre_rib_pay,4,3);
     END IF;
 ELSE
     Code_Tp_Pre:='B';
     v_ban_code:=SUBSTR(Prov_record.prov_pre_rib_pay,1,3);
 END IF;

  ---ajout dans la table prelevement
 insert into prelevement (TPRE_CODE ,
                          BAN_CODE,
                          GE_CODE ,
                          pre_num_aff,
                          pre_dat_ech,
                          tier_cle,
                          pre_ide_reg,
                          pre_nbr_ech_tot,
                          pre_num_ord_ech,
                          pre_nbr_ech_rest,
                          pre_aff_nouveau,
                          statut_libre)--ajout de cette colonne lors PeP du CAM
              values     (Code_Tp_Pre,
                          v_ban_code,
                          v_ge_Code,
                          Prov_record.prov_pre_num_aff,
                          Prov_record.prov_pre_dat_ech,
                          p_tier_cle,
                          Prov_record.prov_pre_ide_reg,
                          Prov_record.prov_pre_nbr_ech_tot,
                          Prov_record.prov_pre_num_ord_ech,
                          Prov_record.prov_pre_nbr_ech_rest,
                          v_aff_nouv,
                          Prov_record.Prov_Afft_Reg);
 ---ajout dans la table trace prelevement
 insert into trace_prelevement (PRE_IDE_REG,ANX_SEQ)
               values(Prov_record.PROV_PRE_IDE_REG,Prov_record.PROV_ANX_SEQ);
END PROC_AJOUT_PRELEVEMENT;


PROCEDURE Proc_Test_Anomalie IS
--------------------------------------------------------------------------------------------------------------------------------------------
--curseur controle anomalie---------------------debut
   CURSOR Controle_Anomalie_Cursor IS
          SELECT    *
          FROM      controle_anomalies_para
          where rtrim(CHAMP_TABLE)='PROV_PRELEVEMENT' and rtrim(cont_statut)='EXPL';

Controle_Anomalie_record  Controle_Anomalie_Cursor%ROWTYPE;
---------------------------------------------------
sqlTexte varchar2(14000);
V_MODE   VARCHAR2(15);
V_VIEW   VARCHAR2(1000);
BEGIN
    v_nom_fonction:='Fun_Test_Anomalie';
--/*+choose*/

    --DEBUT Gestion de MATERIALIZED VIEW
    -- suppression
    BEGIN
      EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW VUE_CDM_PROM_RPAT');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    -- création
    V_VIEW:='CREATE MATERIALIZED VIEW VUE_CDM_PROM_RPAT AS
             SELECT /*+choose*/ W.Ie_Affaire,w.pre_ide_reg
             FROM WDV_CDM_PROM W
             WHERE W.STATUT_PROM IN (''PCRE'', ''PVAL'', ''PENV'', ''PPRE'')
             AND W.AFFT_REG IN (''RPAT'', ''IRPT'')
             AND NOT EXISTS (SELECT NULL FROM WDV_PROM_EXCLUSION X WHERE X.IE_AFFAIRE = W.IE_AFFAIRE)';
    execute immediate V_VIEW;
    --FIN Gestion de MATERIALIZED VIEW

    open Controle_Anomalie_cursor;
    LOOP
      FETCH  Controle_Anomalie_cursor INTO Controle_Anomalie_record;
      EXIT WHEN Controle_Anomalie_cursor%NOTFOUND OR Controle_Anomalie_cursor%NOTFOUND is NULL ;
      IF Controle_Anomalie_record.cont_commentaire in ('PROM_RPAT===AFFAIRE A UNE OU PLUSIEURS ECH EN ANOMALIE',
                                                      'PROM_RPAT===ENCA NON IMPUTE NON DECAISSE SUR EKIP',
                                                      'PROM_RPAT===IMPAYE NON INJECTE DANS EKIP',
                                                      'PROM_RPAT===MNT PROM SAISI EST DIFFERENT DU MNT RECALCULE')  THEN
      V_MODE:='/*+rule*/';
      ELSE
      V_MODE:='/*+choose*/';
      END IF;

      sqltexte:= 'INSERT INTO anomalie (ANX_SEQ,tpre_code,ANO_MOD_REG,ANO_IDE_REG,ANO_NUM_AFF,ANO_NOM_PAY,ANO_PRE_PAY,ANO_ADR_PAY
                  ,ANO_NOM_CLI,ANO_PRE_CLI,ANO_DAT_PRE_ECH,ANO_DAT_DER_ECH,ANO_DAT_ECH,ANO_PRET_K,ANO_PRET_N
                  ,ANO_NBR_ECH_TOT,ANO_NUM_ORD_ECH,ANO_NBR_ECH_REST,ANO_RIB_PAY,ANO_PRET_A,ANO_MOTIF,ano_nom_agence
                  ,ano_code_categ,ano_tiers_impute,ano_societe)
                  select '||V_MODE||' Prov_ANX_SEQ,decode(substr(prov_pre_rib_pay,1,3),''GE '',''G'',''DOT'',''D'',''B''),
                  prov_pre_mod_reg,prov_pre_ide_reg,prov_pre_num_aff,prov_pre_nom_pay
                          ,prov_pre_pre_pay,prov_pre_adr_pay,prov_pre_nom_cli,prov_pre_pre_cli,prov_pre_dat_pre_ech
                          ,prov_pre_dat_der_ech,prov_pre_dat_ech,prov_pre_pret_k,prov_pre_pret_n,prov_pre_nbr_ech_tot
,prov_pre_num_ord_ech,prov_pre_nbr_ech_rest,prov_pre_rib_pay,prov_pre_pret_a
                          ,'''||rtrim(Controle_Anomalie_record.CONT_COMMENTAIRE)||''',prov_pre_nom_agence,prov_pre_code_categ,prov_pre_tiers_impute,prov_societe
                          from prov_prelevement '
                          ||' where '||rtrim(Controle_Anomalie_record.CONT_CONDITION);
    sqltexte:=rtrim(sqltexte);
    EXECUTE IMMEDIATE sqltexte;
    sqltexte:='delete '||V_MODE||' prov_prelevement where '|| rtrim(Controle_Anomalie_record.CONT_CONDITION);
    EXECUTE IMMEDIATE sqltexte;
   end loop ;
    -- delete prov_prelevement where (prov_pre_num_aff,prov_pre_dat_ech) in(select ano_num_aff,ano_dat_ech from anomalie);
End proc_Test_Anomalie;
-------------------------------------------------------------------------------------------------------------------------
END;
/

Thanks a lot

with LiveSQL Test Case:

and Chris said...

There is a LOT going on in that code - giving detailed advice on how to make it faster is beyond the scope of what we can do here.

Here are a few observations and pointers to get you going:

open Prov_cursor;
LOOP
FETCH  Prov_cursor INTO Prov_record;


This is the best way to write SLOOOOOW code.

If you want fast processes, avoid cursor-for loops that process rows one at a time.

Ideally change this to be one SQL statement; if this is too tricky use bulk processing instead

https://blogs.oracle.com/oraclemagazine/on-bulk-collect

alter session set optimizer_mode=RULE


Just. Don't. Do. This!

EXECUTE IMMEDIATE sqltexte;


I don't see why you need dynamic SQL here; static SQL should suffice.

DBMS_STATS.GATHER_TABLE_STATS


Do you really need to do this at the start?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot

omareve, April 28, 2021 - 1:18 pm UTC

Thanks a lot for your feed-back,
What about the drop and recreate of this materialized view, is not better to just refresh it with DBMS_MVIEW ?
<
EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW vue_anomalie_ie_aff');


v_sql:='CREATE MATERIALIZED VIEW vue_anomalie_ie_aff
AS
SELECT a.ano_num_aff,a.ano_ide_reg,a.ano_dat_ech
FROM anomalie a
where a.ano_pret_a>45';
execute immediate v_sql;
>
Chris Saxon
April 28, 2021 - 5:09 pm UTC

Yes - it would be better to do a refresh, particularly if you can create MV logs to get fast refresh

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.