Hello world!

/* Formatted on 10/07/2015 16:11:50 (QP5 v5.256.13226.35510) */
INSERT INTO MC4_SUIVI_LOG ANO (ANO.no_partition,
ANO.id_log,
ANO.cd_table,
ANO.id_enrgt,
ANO.dt_log,
ANO.id_run,
ANO.id_module,
ANO.id_procedure,
ANO.id_ano,
ANO.lb_ano,
ANO.lb_log,
ANO.cd_gravite)
(SELECT 1158,
SEQ_ID_LOG_MC4.NEXTVAL,
‘AFFECT_AUTO3_UTIL_PROV’,
AU1.no_prov,
SYSDATE,
’90’,
‘901’,
‘90651’,
‘ANO-REALLOC-PROV-010’,
‘ Provision pour laquelle la part couverte par la provision d�passe 100,00% ‘,
NULL,
0
FROM AFFECT_AUTO3_UTIL_PROV AU1
WHERE AU1.no_partition = 1159
AND AU1.cd_perim_real_reliquat IN (‘M09’, ‘M10’)
AND EXISTS
(SELECT 1
FROM suivi_flux SF
WHERE AU1.no_reference = SF.no_reference
AND SF.ind_mutu = 0
AND AU1.no_partition = SF.no_partition)
AND 100 <=
(SELECT CASE
WHEN ( NVL (MTU.mt_util_eur, 0)
+ NVL (MTU.mt_icne_eur, 0)
+ NVL (MTU.mt_interet_impaye_eu, 0))
– ( NVL (MTU.mt_prov_retenu_util_eur, 0)
+ NVL (MTU.mt_prov_retenu_icne_eur, 0)
+ NVL (MTU.mt_prov_retenu_i_imp_eur, 0)) <=
0
THEN
0
ELSE
( P.mt_prov_bilan_eur
/ ( ( NVL (MTU.mt_util_eur, 0)
+ NVL (MTU.mt_icne_eur, 0)
+ NVL (MTU.mt_interet_impaye_eu, 0))
– ( NVL (MTU.mt_prov_retenu_util_eur, 0)
+ NVL (MTU.mt_prov_retenu_icne_eur, 0)
+ NVL (MTU.mt_prov_retenu_i_imp_eur,
0))))
END
FROM ( SELECT MTR.no_prov,
SUM (MTR.mt_util_eur) mt_util_eur,
SUM (MTR.mt_icne_eur) mt_icne_eur,
SUM (MTR.mt_interet_impaye_eur)
mt_interet_impaye_eu,
SUM (MTR.mt_prov_retenu_util_eur)
mt_prov_retenu_util_eur,
SUM (MTR.mt_prov_retenu_icne_eur)
mt_prov_retenu_icne_eur,
SUM (MTR.mt_prov_retenu_i_imp_eur)
mt_prov_retenu_i_imp_eur
FROM ( SELECT AU2.no_prov,
AU2.no_util,
UTI.mt_util_eur,
UC.mt_icne_eur,
UC.mt_interet_impaye_eur,
SUM (AU3.mt_prov_retenu_util_eur)
mt_prov_retenu_util_eur,
SUM (AU3.mt_prov_retenu_icne_eur)
mt_prov_retenu_icne_eur,
SUM (AU3.mt_prov_retenu_i_imp_eur)
mt_prov_retenu_i_imp_eur
FROM affect_auto3_util_prov AU2,
affect_auto3_util_prov AU3,
util_commerciale UC,
utilisation UTI
WHERE AU2.no_partition = 1159
AND AU2.no_partition =
UC.no_partition(+)
AND AU2.no_util = UC.no_util(+)
AND AU2.no_partition =
UTI.no_partition
AND AU2.no_util = UTI.no_util
AND AU2.cd_perim_real_reliquat IN (‘M09’,
‘M10’)
AND AU2.no_util = AU3.no_util
AND UTI.no_partition =
AU3.no_partition
GROUP BY AU2.no_prov,
AU2.no_util,
UTI.mt_util_eur,
UC.mt_icne_eur,
UC.mt_interet_impaye_eur) MTR
GROUP BY MTR.no_prov) MTU,
provision P
WHERE AU1.no_prov = MTU.no_prov
AND AU1.no_partition = P.no_partition
AND AU1.no_prov = P.no_prov
AND P.cd_systeme_source IN (‘CAPRI’,
‘ADRI’,
‘ADCHR’,
‘ADTITRE’)))

————————————————
Plan 12.1.0.2
————————————————

SQL_ID 074puhrs8wygf, child number 0
————————————-
INSERT INTO MC4_SUIVI_LOG ANO( ANO.no_partition, ANO.id_log,
ANO.cd_table, ANO.id_enrgt, ANO.dt_log, ANO.id_run,
ANO.id_module, ANO.id_procedure, ANO.id_ano, ANO.lb_ano,
ANO.lb_log, ANO.cd_gravite)(SELECT 1159,
SEQ_ID_LOG_MC4.NEXTVAL, ‘AFFECT_AUTO3_UTIL_PROV’, AU1.no_prov,
SYSDATE, ’90’, ‘901’, ‘90651’, ‘ANO-REALLOC-PROV-010’, ‘
Provision pour laquelle la part couverte par la provision d�passe
100,00% ‘, NULL, 0 FROM AFFECT_AUTO3_UTIL_PROV AU1 WHERE
AU1.no_partition = 1159 AND AU1.cd_perim_real_reliquat IN
(‘M09′,’M10′) AND EXISTS ( SELECT 1 FROM suivi_flux SF WHERE
AU1.no_reference = SF.no_reference AND SF.ind_mutu = 0 AND
AU1.no_partition = SF.no_partition) AND 100 <= ( SELECT CASE WHEN (NVL(MTU.mt_util_eur,0)+ NVL(MTU.mt_icne_eur,0) + NVL(MTU.mt_interet_impaye_eu,0)) – (NVL(MTU.mt_prov_retenu_util_eur,0) + NVL(MTU.mt_prov_retenu_ic Plan hash value: 1390908362 ——————————————————————————————————————————————————— | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | ——————————————————————————————————————————————————— | 0 | INSERT STATEMENT | | | | 209M(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | MC4_SUIVI_LOG | | | | | | | | 2 | SEQUENCE | SEQ_ID_LOG_MC4 | | | | | | | |* 3 | FILTER | | | | | | | | |* 4 | HASH JOIN RIGHT SEMI | | 85601 | 2675K| 2386 (8)| 00:00:01 | | | | 5 | PARTITION RANGE SINGLE | | 186 | 2232 | 2 (0)| 00:00:01 | 6 | 6 | |* 6 | INDEX FAST FULL SCAN | IDX_SUIVI_FLUX_Q1 | 186 | 2232 | 2 (0)| 00:00:01 | 6 | 6 | | 7 | PARTITION RANGE SINGLE | | 85601 | 1671K| 2380 (8)| 00:00:01 | 6 | 6 | |* 8 | TABLE ACCESS FULL | AFFECT_AUTO3_UTIL_PROV | 85601 | 1671K| 2380 (8)| 00:00:01 | 6 | 6 | | 9 | NESTED LOOPS | | 1 | 114 | 2643 (16)| 00:00:01 | | | | 10 | PARTITION RANGE SINGLE | | 1 | 23 | 3 (0)| 00:00:01 | KEY | KEY | |* 11 | TABLE ACCESS BY LOCAL INDEX ROWID | PROVISION | 1 | 23 | 3 (0)| 00:00:01 | KEY | KEY | |* 12 | INDEX UNIQUE SCAN | PK_PROVISION | 1 | | 2 (0)| 00:00:01 | KEY | KEY | | 13 | VIEW | | 1 | 91 | 2640 (16)| 00:00:01 | | | | 14 | SORT GROUP BY | | 1 | 87 | 2640 (16)| 00:00:01 | | | | 15 | VIEW | | 400 | 34800 | 2640 (16)| 00:00:01 | | | | 16 | SORT GROUP BY | | 400 | 44800 | 2640 (16)| 00:00:01 | | | | 17 | NESTED LOOPS | | 400 | 44800 | 2639 (16)| 00:00:01 | | | | 18 | NESTED LOOPS | | 400 | 44800 | 2639 (16)| 00:00:01 | | | | 19 | NESTED LOOPS OUTER | | 12 | 1128 | 2615 (16)| 00:00:01 | | | |* 20 | HASH JOIN | | 1 | 76 | 2613 (16)| 00:00:01 | | | | 21 | PARTITION RANGE SINGLE | | 2 | 48 | 17 (0)| 00:00:01 | 6 | 6 | |* 22 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| AFFECT_AUTO3_UTIL_PROV | 2 | 48 | 17 (0)| 00:00:01 | 6 | 6 | |* 23 | INDEX RANGE SCAN | IDX_AFFECT_AUTO3_UTIL_PROV_Q1 | 14 | | 3 (0)| 00:00:01 | 6 | 6 | | 24 | VIEW | VW_GBC_14 | 6174 | 313K| 2595 (16)| 00:00:01 | | | | 25 | SORT GROUP BY | | 6174 | 120K| 2595 (16)| 00:00:01 | | | | 26 | PARTITION RANGE SINGLE | | 513K| 9M| 2383 (8)| 00:00:01 | 6 | 6 | |* 27 | TABLE ACCESS FULL | AFFECT_AUTO3_UTIL_PROV | 513K| 9M| 2383 (8)| 00:00:01 | 6 | 6 | | 28 | PARTITION RANGE SINGLE | | 31 | 558 | 2 (0)| 00:00:01 | 6 | 6 | | 29 | TABLE ACCESS BY LOCAL INDEX ROWID | UTIL_COMMERCIALE | 31 | 558 | 2 (0)| 00:00:01 | 6 | 6 | |* 30 | INDEX UNIQUE SCAN | PK_UTIL_COMMERCIALE | 1 | | 1 (0)| 00:00:01 | 6 | 6 | | 31 | PARTITION RANGE SINGLE | | 1 | | 1 (0)| 00:00:01 | 6 | 6 | |* 32 | INDEX UNIQUE SCAN | PK_UTILISATION | 1 | | 1 (0)| 00:00:01 | 6 | 6 | | 33 | TABLE ACCESS BY LOCAL INDEX ROWID | UTILISATION | 33 | 594 | 2 (0)| 00:00:01 | 6 | 6 | ——————————————————————————————————————————————————— Predicate Information (identified by operation id): ————————————————— 3 – filter(>=100)
4 – access(“AU1″.”NO_REFERENCE”=”SF”.”NO_REFERENCE” AND “AU1″.”NO_PARTITION”=”SF”.”NO_PARTITION”)
6 – filter((“SF”.”IND_MUTU”=0 AND “SF”.”NO_PARTITION”=1159))
8 – filter((INTERNAL_FUNCTION(“AU1″.”CD_PERIM_REAL_RELIQUAT”) AND “AU1”.”NO_PARTITION”=1159))
11 – filter((“P”.”CD_SYSTEME_SOURCE”=’ADCHR’ OR “P”.”CD_SYSTEME_SOURCE”=’ADRI’ OR “P”.”CD_SYSTEME_SOURCE”=’ADTITRE’ OR
“P”.”CD_SYSTEME_SOURCE”=’CAPRI’))
12 – access(“P”.”NO_PARTITION”=:B1 AND “P”.”NO_PROV”=:B2)
20 – access(“AU2”.”NO_UTIL”=”ITEM_2″)
22 – filter((“AU2”.”CD_PERIM_REAL_RELIQUAT”=’M09′ OR “AU2”.”CD_PERIM_REAL_RELIQUAT”=’M10′))
23 – access(“AU2”.”NO_PARTITION”=1159 AND “AU2”.”NO_PROV”=:B1)
27 – filter(“AU3”.”NO_PARTITION”=1159)
30 – access(“UC”.”NO_PARTITION”=1159 AND “AU2″.”NO_UTIL”=”UC”.”NO_UTIL”)
32 – access(“UTI”.”NO_PARTITION”=1159 AND “AU2″.”NO_UTIL”=”UTI”.”NO_UTIL”)

Note
—–
– Warning: basic plan statistics not available. These are only collected when:
* hint ‘gather_plan_statistics’ is used for the statement or
* parameter ‘statistics_level’ is set to ‘ALL’, at session or system level

————————————————————————————————
Execution Plan 11.2.0.4
———————————————————-
Plan hash value: 774598470

——————————————————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
——————————————————————————————————————————————————–
| 0 | INSERT STATEMENT | | 1 | 32 | | 224M (1)| 03:41:01 | | |
| 1 | LOAD TABLE CONVENTIONAL | MC4_SUIVI_LOG | | | | | | | |
| 2 | SEQUENCE | SEQ_ID_LOG_MC4 | | | | | | | |
|* 3 | FILTER | | | | | | | | |
|* 4 | HASH JOIN RIGHT SEMI | | 85601 | 2675K| | 2401 (9)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 186 | 2232 | | 2 (0)| 00:00:01 | 6 | 6 |
|* 6 | INDEX FAST FULL SCAN | IDX_SUIVI_FLUX_Q1 | 186 | 2232 | | 2 (0)| 00:00:01 | 6 | 6 |
| 7 | PARTITION RANGE SINGLE | | 85601 | 1671K| | 2394 (9)| 00:00:01 | 6 | 6 |
|* 8 | TABLE ACCESS FULL | AFFECT_AUTO3_UTIL_PROV | 85601 | 1671K| | 2394 (9)| 00:00:01 | 6 | 6 |
| 9 | NESTED LOOPS | | 1 | 114 | | 2822 (1)| 00:00:01 | | |
| 10 | PARTITION RANGE SINGLE | | 1 | 23 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID | PROVISION | 1 | 23 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX UNIQUE SCAN | PK_PROVISION | 1 | | | 2 (0)| 00:00:01 | KEY | KEY |
| 13 | VIEW | | 1 | 91 | | 2819 (1)| 00:00:01 | | |
| 14 | SORT GROUP BY | | 1 | 87 | | 2819 (1)| 00:00:01 | | |
| 15 | VIEW | | 33309 | 2829K| | 2819 (1)| 00:00:01 | | |
| 16 | SORT GROUP BY | | 33309 | 2602K| 3040K| 2819 (1)| 00:00:01 | | |
| 17 | NESTED LOOPS | | | | | | | | |
| 18 | NESTED LOOPS | | 33309 | 2602K| | 2140 (1)| 00:00:01 | | |
| 19 | NESTED LOOPS OUTER | | 1010 | 62620 | | 113 (0)| 00:00:01 | | |
| 20 | NESTED LOOPS | | 32 | 1408 | | 49 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE SINGLE | | 2 | 48 | | 17 (0)| 00:00:01 | 6 | 6 |
|* 22 | TABLE ACCESS BY LOCAL INDEX ROWID| AFFECT_AUTO3_UTIL_PROV | 2 | 48 | | 17 (0)| 00:00:01 | 6 | 6 |
|* 23 | INDEX RANGE SCAN | IDX_AFFECT_AUTO3_UTIL_PROV_Q1 | 2 | | | 3 (0)| 00:00:01 | 6 | 6 |
| 24 | PARTITION RANGE SINGLE | | 14 | 280 | | 16 (0)| 00:00:01 | 6 | 6 |
| 25 | TABLE ACCESS BY LOCAL INDEX ROWID| AFFECT_AUTO3_UTIL_PROV | 14 | 280 | | 16 (0)| 00:00:01 | 6 | 6 |
|* 26 | INDEX RANGE SCAN | IDX_AFFECT_AUTO3_UTIL_PROV_Q2 | 2 | | | 2 (0)| 00:00:01 | 6 | 6 |
| 27 | PARTITION RANGE SINGLE | | 31 | 558 | | 2 (0)| 00:00:01 | 6 | 6 |
| 28 | TABLE ACCESS BY LOCAL INDEX ROWID | UTIL_COMMERCIALE | 31 | 558 | | 2 (0)| 00:00:01 | 6 | 6 |
|* 29 | INDEX UNIQUE SCAN | PK_UTIL_COMMERCIALE | 1 | | | 1 (0)| 00:00:01 | 6 | 6 |
| 30 | PARTITION RANGE SINGLE | | 1 | | | 1 (0)| 00:00:01 | 6 | 6 |
|* 31 | INDEX UNIQUE SCAN | PK_UTILISATION | 1 | | | 1 (0)| 00:00:01 | 6 | 6 |
| 32 | TABLE ACCESS BY LOCAL INDEX ROWID | UTILISATION | 33 | 594 | | 2 (0)| 00:00:01 | 6 | 6 |
——————————————————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – filter( (SELECT CASE WHEN NVL(“MTU”.”MT_UTIL_EUR”,0)+NVL(“MTU”.”MT_ICNE_EUR”,0)+NVL(“MTU”.”MT_INTERET_IMPAYE_EU”,0)-(NVL(“MTU”.”MT_PROV_
RETENU_UTIL_EUR”,0)+NVL(“MTU”.”MT_PROV_RETENU_ICNE_EUR”,0)+NVL(“MTU”.”MT_PROV_RETENU_I_IMP_EUR”,0))<=0 THEN 0 ELSE “P”.”MT_PROV_BILAN_EUR”/(NVL(“MTU”.”MT_UTIL_EUR”,0)+NVL(“MTU”.”MT_ICNE_EUR”,0)+NVL(“MTU”.”MT_INTERET_IMPAYE_EU”,0)-(NVL(“MTU”.”MT_PROV_RETENU_UT IL_EUR”,0)+NVL(“MTU”.”MT_PROV_RETENU_ICNE_EUR”,0)+NVL(“MTU”.”MT_PROV_RETENU_I_IMP_EUR”,0))) END FROM “PROVISION” “P”, (SELECT “MTR”.”NO_PROV” “NO_PROV”,SUM(“MTR”.”MT_UTIL_EUR”) “MT_UTIL_EUR”,SUM(“MTR”.”MT_ICNE_EUR”) “MT_ICNE_EUR”,SUM(“MTR”.”MT_INTERET_IMPAYE_EUR”) “MT_INTERET_IMPAYE_EU”,SUM(“MTR”.”MT_PROV_RETENU_UTIL_EUR”) “MT_PROV_RETENU_UTIL_EUR”,SUM(“MTR”.”MT_PROV_RETENU_ICNE_EUR”) “MT_PROV_RETENU_ICNE_EUR”,SUM(“MTR”.”MT_PROV_RETENU_I_IMP_EUR”) “MT_PROV_RETENU_I_IMP_EUR” FROM (SELECT “AU2″.”NO_PROV” “NO_PROV”,”AU2″.”NO_UTIL” “NO_UTIL”,”UTI”.”MT_UTIL_EUR” “MT_UTIL_EUR”,”UC”.”MT_ICNE_EUR” “MT_ICNE_EUR”,”UC”.”MT_INTERET_IMPAYE_EUR” “MT_INTERET_IMPAYE_EUR”,SUM(“AU3″.”MT_PROV_RETENU_UTIL_EUR”) “MT_PROV_RETENU_UTIL_EUR”,SUM(“AU3″.”MT_PROV_RETENU_ICNE_EUR”) “MT_PROV_RETENU_ICNE_EUR”,SUM(“AU3″.”MT_PROV_RETENU_I_IMP_EUR”) “MT_PROV_RETENU_I_IMP_EUR” FROM “UTILISATION” “UTI”,”UTIL_COMMERCIALE” “UC”,”AFFECT_AUTO3_UTIL_PROV” “AU3″,”AFFECT_AUTO3_UTIL_PROV” “AU2” WHERE “AU2”.”NO_PROV”=:B1 AND “AU2”.”NO_PARTITION”=1159 AND (“AU2”.”CD_PERIM_REAL_RELIQUAT”=’M09′ OR “AU2”.”CD_PERIM_REAL_RELIQUAT”=’M10′) AND “AU2″.”NO_UTIL”=”AU3″.”NO_UTIL” AND “AU3”.”NO_PARTITION”=1159 AND “AU2″.”NO_UTIL”=”UC”.”NO_UTIL”(+) AND “UC”.”NO_PARTITION”(+)=1159 AND “AU2″.”NO_UTIL”=”UTI”.”NO_UTIL” AND “UTI”.”NO_PARTITION”=1159 GROUP BY “AU2″.”NO_PROV”,”AU2″.”NO_UTIL”,”UTI”.”MT_UTIL_EUR”,”UC”.”MT_ICNE_EUR”,”UC”.”MT_INTERET_IMPAYE_EUR”) “MTR” GROUP BY “MTR”.”NO_PROV”) “MTU” WHERE “P”.”NO_PROV”=:B2 AND “P”.”NO_PARTITION”=:B3 AND (“P”.”CD_SYSTEME_SOURCE”=’ADCHR’ OR “P”.”CD_SYSTEME_SOURCE”=’ADRI’ OR “P”.”CD_SYSTEME_SOURCE”=’ADTITRE’ OR “P”.”CD_SYSTEME_SOURCE”=’CAPRI’))>=100)
4 – access(“AU1″.”NO_REFERENCE”=”SF”.”NO_REFERENCE” AND “AU1″.”NO_PARTITION”=”SF”.”NO_PARTITION”)
6 – filter(“SF”.”IND_MUTU”=0 AND “SF”.”NO_PARTITION”=1159)
8 – filter((“AU1”.”CD_PERIM_REAL_RELIQUAT”=’M09′ OR “AU1”.”CD_PERIM_REAL_RELIQUAT”=’M10′) AND “AU1”.”NO_PARTITION”=1159)
11 – filter(“P”.”CD_SYSTEME_SOURCE”=’ADCHR’ OR “P”.”CD_SYSTEME_SOURCE”=’ADRI’ OR “P”.”CD_SYSTEME_SOURCE”=’ADTITRE’ OR
“P”.”CD_SYSTEME_SOURCE”=’CAPRI’)
12 – access(“P”.”NO_PARTITION”=:B1 AND “P”.”NO_PROV”=:B2)
22 – filter(“AU2”.”CD_PERIM_REAL_RELIQUAT”=’M09′ OR “AU2”.”CD_PERIM_REAL_RELIQUAT”=’M10′)
23 – access(“AU2”.”NO_PARTITION”=1159 AND “AU2”.”NO_PROV”=:B1)
26 – access(“AU3”.”NO_PARTITION”=1159 AND “AU2″.”NO_UTIL”=”AU3″.”NO_UTIL”)
29 – access(“UC”.”NO_PARTITION”(+)=1159 AND “AU2″.”NO_UTIL”=”UC”.”NO_UTIL”(+))
31 – access(“UTI”.”NO_PARTITION”=1159 AND “AU2″.”NO_UTIL”=”UTI”.”NO_UTIL”)

Statistics
———————————————————-
345 recursive calls
291 db block gets
126714 consistent gets
11 physical reads
21276 redo size
865 bytes sent via SQL*Net to client
6825 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1108 sorts (memory)
0 sorts (disk)
2 rows processed

Posted in Uncategorized | 1 Comment