Discussion :

[tchorlz]

Bonjour,

Je penche actuellement sur un package Oracle qui contient entre autre la procédure suivante:

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
PROCEDURE calculate_ytd_data
IS
BEGIN
INSERT
     INTO A.aa_idp_facts_fin
    (
      SCENARIO_ID, YEAR_ID, MONTH_ID, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC,
      THIRD_PARTY_REVENUE, INTERCOMPANY_REVENUE, TOTAL_COST_OF_SITE, TOTAL_ALLOCATED_OVERHEADS, REVENUE_OVERHEADS, COST_OVERHEADS, SLA_OVERHEADS,
      COUNTRY_OVERHEADS, NON_OPERATING_OVERHEADS, REGIONAL_DIVISIONAL_OVERHEADS, TOTAL_NET_REVENUE, GROSS_PROFIT, TOTAL_COUNTRY_OVERHEADS,
      RESULTS_BEFORE_EBIT, EBIT, RECEIVABLES_UPTO_30, RECEIVABLES_31_TO_60, RECEIVABLES_61_TO_90, RECEIVABLES_91_TO_120, RECEIVABLES_121_TO_150,
      RECEIVABLES_151_TO_180, RECEIVABLES_181_TO_210, RECEIVABLES_211_TO_270, RECEIVABLES_271_TO_360, RECEIVABLES_OVER_360, UNALLOCATED_CASH, DSO, CREDIT_TERMS,
      AMOUNT_OVERDUE, BAD_DEBT, NUMBER_OF_INVOICES, TAX_RATE, THIRD_PARTY_RECEIVABLES, CURRENCY_ID, SOURCE_ID, UNCOLLECTABLE_CASH, TIMESERIES_ID, LOCAL_CUSTOMER_ID
    )
   SELECT /*+ ALL_ROWS */ SCENARIO_ID, YEAR_ID, period_ytd_id MONTH_ID, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC,
    sum(THIRD_PARTY_REVENUE) THIRD_PARTY_REVENUE, sum(INTERCOMPANY_REVENUE) INTERCOMPANY_REVENUE, sum(TOTAL_COST_OF_SITE) TOTAL_COST_OF_SITE, sum(TOTAL_ALLOCATED_OVERHEADS) TOTAL_ALLOCATED_OVERHEADS, sum(REVENUE_OVERHEADS) REVENUE_OVERHEADS, sum(COST_OVERHEADS) COST_OVERHEADS, sum(SLA_OVERHEADS) SLA_OVERHEADS,
    sum(COUNTRY_OVERHEADS) COUNTRY_OVERHEADS, sum(NON_OPERATING_OVERHEADS) NON_OPERATING_OVERHEADS, sum(REGIONAL_DIVISIONAL_OVERHEADS) REGIONAL_DIVISIONAL_OVERHEADS, sum(TOTAL_NET_REVENUE) TOTAL_NET_REVENUE, sum(GROSS_PROFIT) GROSS_PROFIT, sum(TOTAL_COUNTRY_OVERHEADS) TOTAL_COUNTRY_OVERHEADS,
    sum(RESULTS_BEFORE_EBIT) RESULTS_BEFORE_EBIT, sum(EBIT) EBIT, null RECEIVABLES_UPTO_30, null RECEIVABLES_31_TO_60, null RECEIVABLES_61_TO_90, null RECEIVABLES_91_TO_120, null RECEIVABLES_121_TO_150,
    null RECEIVABLES_151_TO_180, null RECEIVABLES_181_TO_210, null RECEIVABLES_211_TO_270, null RECEIVABLES_271_TO_360, null RECEIVABLES_OVER_360, null UNALLOCATED_CASH, null DSO, null CREDIT_TERMS,
    null AMOUNT_OVERDUE, null BAD_DEBT, null NUMBER_OF_INVOICES, null TAX_RATE, null THIRD_PARTY_RECEIVABLES, CURRENCY_ID, SOURCE_ID, null UNCOLLECTABLE_CASH, 'YTD' TIMESERIES_ID, LOCAL_CUSTOMER_ID
 
     FROM A.aa_facts_fin
  JOIN A.m_years
       ON ( years_id = year_id )
  JOIN
    (SELECT *
       FROM B.s_period2ytd
      WHERE ytd_type = 'CURR_YTD'
    )
       ON ( period_id   = month_id )
    WHERE timeseries_id = 'MTD'
 GROUP BY SCENARIO_ID, YEAR_ID, period_ytd_id, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC, CURRENCY_ID, SOURCE_ID, TIMESERIES_ID, LOCAL_CUSTOMER_ID    
    ;
COMMIT;
END calculate_ytd_data;

Cette procédure vient à la fin de 10 autres (une pour vider la table, 6 pour la remplir a partir d'autres tables et 3 pour calculer certaines colonnes) et elle prend à elle seule autant de temps que toutes les autres réunies. La table aa_facts_fin contient au début de la procédure aux alentours de 2 millions de lignes. Je pensais notamment rajouter l'hint /+APPEND NOLOGGING/ mais malheureusement mon expérience en SQL est limitée et je pense qu'on peut beaucoup gagner à retravailler la requête. Je suis donc ouvert à toute suggestion.

Merci d'avance et n'hésitez pas à demander s'il vous faut plus d'informations

[mnitu]

Commencez par une trace SQL pour apprendre ce qui se passe pendant l'exécution de la procédure. Après vous saura quoi faire.

[Treker]

En exécutant le select seulement ça donne quoi ?

[tchorlz]

Je vais faire une série de tests complémentaires dans la nuit mais je pense que le soucis vient du fait que le Cost Based Optimizer n'est absolument pas utilisé.

Je vous tiens au courant de l'avancement