Procédures et performances

**raph-68i** · 04/03/2013, 14h03

Bonjour a tous,

belle journée pour être désigné comme nouveau "responsable" des performances de la BDD de ma boite ?

Voila le problème, je me retrouve a disséquer des procédures pour en améliorer les performances.

Je connais le PL/SQL mais reste un novice en terme de "Best Practices". Donc si vous avez des conseils sur le bout de code ci dessous qui met beaucoup de temps a s’exécuter (plus de 20 minutes 2 millions pour de ligne) ce serait parfait.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
  PROCEDURE GET_finance_FACTS AS
  cursor c_finance is
  select util_pkg.GET_CUSTOMER_ID(CUSTOMER_ID) as cus_ID,
    util_pkg.GET_SECTOR_BY_NATURAL_ID(SECTOR_ID) as sec_ID,
    util_pkg.GET_SERVICEAREA_ID(SERVICEAREA_ID) as ser_ID,
    util_pkg.GET_GEOGRAPHY_ID(FACILITY_ID,null) as faci_ID,
    util_pkg.GET_TIME_ID(YEAR_ID,MONTH_ID) as tran_date_ID,
    util_pkg.GET_PRODUCT_ID(PRODUCT_ID) as prod_ID,
    util_pkg.GET_SCENARIO_ID(SCENARIO_ID) as scen_ID,
    util_pkg.GET_CURRENCY_ID(CURRENCY_ID) as curr_ID,
    util_pkg.GET_SYSTEM_ID(SYSTEM_ID) as system_ID,
    VALUE,
    ACCOUNT_ID
    from UPLOAD_finance_NEW;
 
  TYPE t_var IS TABLE OF c_finance%ROWTYPE;
    l_finance t_var;
 
  BEGIN
 
 
    OPEN c_finance;    
        LOOP 
        FETCH c_finance
        BULK COLLECT INTO l_finance LIMIT 10000;
          EXIT WHEN l_finance.COUNT = 0; 
          --DBMS_OUTPUT.put_line('Loop starting...');
          FOR i IN l_finance.FIRST..l_finance.LAST
          LOOP
          BEGIN
 
                execute immediate 'INSERT INTO f_finance (c_id,
                s_id,
                sa_id,
                geo_id,
                t_id_transaction,
                prd_id,
                sen_id,
                cur_id,
                sys_id,
                pa_value,
                ACCOUNT_ID)
                        VALUES ('
                || ''''   ||  l_finance(i).cus_ID
                || ''',''' || l_finance(i).sec_ID
                || ''',''' || l_finance(i).ser_ID
                || ''',''' || l_finance(i).faci_ID
                || ''',''' || l_finance(i).tran_date_ID
                || ''',''' || l_finance(i).prod_ID
                || ''',''' || l_finance(i).scen_ID
                || ''',''' || l_finance(i).curr_ID
                || ''',''' || l_finance(i).system_ID
                || ''',''' || l_finance(i).value
                || ''',''' || l_finance(i).ACCOUNT_ID
                ||  ''' )';
 
 
 
          END;
        END LOOP;
          --DBMS_OUTPUT.put_line('Inner Loop completed... Rowcount ' || l_finance.LAST);        
        END LOOP;
        --DBMS_OUTPUT.put_line('Master Loop completed... ');
       CLOSE c_finance;
       COMMIT;
 
 
    NULL;
  END GET_finance_FACTS;

Pour info les fonctions du type "util_pkg.GET_XXX_ID" sont de la forme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
function GET_SYSTEM_ID( in_sys	VARCHAR2) return INTEGER RESULT_CACHE AS
    sys_id INTEGER;
    BEGIN    
 
      SELECT sys_id INTO sys_id FROM m_system WHERE upper(sys_system_name) = upper(in_sys); 
      return sys_id;
 
 
   END;

Si vous avez d'autres best practices a proposer n'hesitez pas je suis preneur !
Merci d'avance

Cordialement, Raphael

**mnitu** · 04/03/2013, 14h10

Précisez la version d'Oracle

Eliminer les appels à util_pck.get...
Faire les inserts avec FORALL ou réécrire la procédure comme INSERT INTO ... SELECT ... FROM ...

**pachot** · 04/03/2013, 15h19

Bonjour,

Envoyé par raph-68i

belle journée pour être désigné comme nouveau "responsable" des performances de la BDD de ma boite ?

Alors par quoi commencer pour ne pas décourager le nouveau responsable des perf...

Avant de parler de 'best practices', on va d'abord voire les 'worst practice' à éviter.

Et il se trouve qu'elles sont toutes illustrées dans ces quelques lignes de code:

1. SELECT util_pkg.GET_CUSTOMER_ID(CUSTOMER_ID) -> appel de fonction pl/sql pour chaque enregistrement ( switch de contexte entre SQL et PL/SQL)
2. fonctions du type "util_pkg.GET_XXX_ID" -> code procédural au lieu de faire des jointures
3. WHERE upper(sys_system_name) = upper(in_sys) -> utilisation de fonctions qui empêchent l'utilisation d'index
4. execute immediate ... -> utilisation de sql dynamique au lieu d'utiliser des bind variables
5. COMMIT; -> commit dans une procédure
6. une procédure qui s'appelle 'GET...' et qui fait des modifs !
7. LOOP -> boucles là où une requête SQL pourrait tout faire

Tout ça, c'est un bon inventaire des choses à éviter. Tu est sûr que ce n'est pas un bizutage pour tes nouvelles responsabilités ?
Sinon, la bonne nouvelle, c'est qu'il y a un fort potentiel d'amélioration.

Cordialement,
Franck.

**raph-68i** · 04/03/2013, 15h40

Merci pour vos réponses.
En effet, je me retrouve a passer de plus de 20 minutes a un résultat presque instantané pour cette procédure.

Je reprend le travail d'un stagiaire donc pour moi (la jeune recrue de l'équipe) me tape le sale boulot.

Cependant, l'utilisation des ces fonctions (voir plus haut) permettait l'insertion de certaines colonnes "null" ce que ne permet plus les jointures.
Du coup, je me retrouve avec moins de données.

Je pense principalement a un problèmes de data quality, mais si jamais vous avez une idée permettant de mettre en cas de jointure impossible ?

Merci encore.