Ce matin mon client m'a remonté un problème sur 3 process qui tournaient depuis une dizaine d'heures sur une base venant juste d'être migrée en 11g R2 et dont les tables venaient d'être partitionnées.
En listant les sessions actives j'ai remarqué qu'effectivement on avait 3 requêtes qui tournaient respectivement depuis 33963, 36617 et 57709 secondes:
SID PROG ADDRESS HASH_VALUE SQL_ID CHILD PLAN_HASH_VALUE EXECS AVG_ETIME EVENT SQL_TEXT
----- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------
606 sqlplus@ps C000000CE743FC08 1936645007 00jchz9tqxqwg 3 3981773947 3013 39.00 PL/SQL lock timer SELECT DISTINCT H.GROUP# FROM SYS.WRI$_OP
590 oracle@psp C000000CE743FC08 1936645007 00jchz9tqxqwg 3 3981773947 3013 39.00 PL/SQL lock timer SELECT DISTINCT H.GROUP# FROM SYS.WRI$_OP
1354 uvsh@psp20 C000000CE464D8A8 1685049779 8kgwdsdk6zndm 0 132338241 1 33,963.97 db file scattered re SELECT distinct EVC.EVC_NUM_CONTR,
408 uvsh@psp20 C0000002ADB65E28 3265361904 bwba60z1a2xzh 0 132338241 1 36,617.62 db file sequential r SELECT distinct EVC.EVC_NUM_CONTR,
969 uvsh@psp20 C000000C7E6776B0 1248359757 gwfkut956hxad 0 132338241 1 57,709.34 direct path read SELECT distinct EVC.EVC_NUM_CONTR,
En regardant de plus près les 3 requêtes je me suis aperçu qu'elles étaient quasiment identiques.
Voilà justement à quoi ressemble une des 3 requêtes:
Voilà justement à quoi ressemble une des 3 requêtes:
SELECT DISTINCT EVC.EVC_NUM_CONTR, -- NOM CLIENT par NI (SELECT MAX (NVL (PPH.PPH_NOM_MARITAL, PPH.PPH_NOM_PATRONYMIQUE)) FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH, TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%' AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2 WHERE RPE2.RPE_CD_ROLE LIKE 'O%' GROUP BY rpe2.RPE_NUM_CONTR)) AS NOM_CONTRACTANT, -- NOM personne morale (SELECT PMO.PMO_RAISON_SOCIALE FROM TDO_D_ASSIN_ROLE_PERSONNE RPE, TDO_D_ASSIN_PERS_MORA PMO WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION AND PMO.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PMO.PMO_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM TDO_D_ASSIN_ROLE_PERSONNE RPE2 GROUP BY rpe2.RPE_NUM_CONTR)) AS NOM2_CONTRACTANT, -- ID CLIENT par NI (SELECT MAX (RPe.RPE_ID_PERSONNE) FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION AND RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%') AS ID_CONTRACTANT, -- ID CLIENT (SELECT MAX (RPE.RPE_ID_PERSONNE) FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION AND RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%') AS ID2_CONTRACTANT, -- PRENOM CLIENT par NI (SELECT MAX (PPH.PPH_PRENOM) FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH, TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%' AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2 WHERE RPE2.RPE_CD_ROLE LIKE 'O%' GROUP BY rpe2.RPE_NUM_CONTR)) AS PRENOM, -- PRENOM CLIENT sans NI (SELECT MAX (PPH.PPH_PRENOM) FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH, TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%' AND RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2 WHERE RPE2.RPE_CD_ROLE LIKE 'O%' GROUP BY rpe2.RPE_NUM_CONTR)) AS PRENOM2, -- DATE NAISSANCE CLIENT par NI (SELECT TO_CHAR (MAX (PPH.PPH_DT_NAISSANCE), 'YYYY-MM-DD HH24:MI:SS') FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH, TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%' AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2 WHERE RPE2.RPE_CD_ROLE LIKE 'O%' GROUP BY rpe2.RPE_NUM_CONTR)) AS DT_NAISSANCE, -- DATE NAISSANCE CLIENT sans NI (SELECT TO_CHAR (MAX (PPH.PPH_DT_NAISSANCE), 'YYYY-MM-DD HH24:MI:SS') FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH, TDO_D_ASSIN_ROLE_PERSONNE RPE WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR AND RPE.RPE_CD_ROLE LIKE 'O%' AND RPE.NUM_INTEGRATION <> EVC.NUM_INTEGRATION AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN ( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2 WHERE RPE2.RPE_CD_ROLE LIKE 'O%' GROUP BY rpe2.RPE_NUM_CONTR)) AS DT2_NAISSANCE FROM AODS00.TDO_D_ASSIN_EVT_CONTR EVC WHERE EVC.NUM_INTEGRATION = 597033 AND EVC.FLAG_MAJ_ODS = '1' ORDER BY 1;
On note que la clause FROM ne contient qu'une seule table (TDO_D_ASSIN_EVT_CONTR) et que la complexité réside dans le fait que le SELECT contient plusieurs scalar subqueries.
La particularité des scalar subqueries dans une clause SELECT est qu'elles sont exécutées autant de fois qu'il y'a de lignes retournées par la requête principale.
Voici une des scalar subqueries executées dans cette requête:
La particularité des scalar subqueries dans une clause SELECT est qu'elles sont exécutées autant de fois qu'il y'a de lignes retournées par la requête principale.
Voici une des scalar subqueries executées dans cette requête:
(SELECT MAX (NVL (PPH.PPH_NOM_MARITAL, PPH.PPH_NOM_PATRONYMIQUE))
FROM AODS00.TDO_D_ASSIN_PERS_PHYS PPH,
TDO_D_ASSIN_ROLE_PERSONNE RPE
WHERE RPE.RPE_NUM_CONTR = EVC.EVC_NUM_CONTR
AND RPE.RPE_CD_ROLE LIKE 'O%'
AND RPE.NUM_INTEGRATION = EVC.NUM_INTEGRATION
AND PPH.NUM_INTEGRATION = RPE.NUM_INTEGRATION
AND PPH.PPH_ID_PERSONNE = RPE.RPE_ID_PERSONNE
AND (RPE.RPE_ID_PERSONNE, rpe.RPE_NUM_CONTR) IN
( SELECT MAX (RPE2.RPE_ID_PERSONNE), rpe2.RPE_NUM_CONTR
FROM AODS00.TDO_D_ASSIN_ROLE_PERSONNE RPE2
WHERE RPE2.RPE_CD_ROLE LIKE 'O%'
GROUP BY rpe2.RPE_NUM_CONTR))
La table TDO_D_ASSIN_ROLE_PERSONNE est celle qui apparait dans chacune des scalar subqueries.
Elle apparait même 2 fois à chaque fois: une fois dans la clause FROM principal de la scalar subquery et une autre fois dans la sous-requête de la clause WHERE.
Essayons maintenant de voir ce que donne le plan d'exécution.Elle apparait même 2 fois à chaque fois: une fois dans la clause FROM principal de la scalar subquery et une autre fois dans la sous-requête de la clause WHERE.
Vu que la requête était en train de tourner et afin d'avoir un plan avec des stats d'exécution j'ai récupéré le plan en utilisant le SQL Monitoring (DBMS_SQLTUNE.report_sql_monitor):
Global Stats
=================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=================================================================================
| 38031 | 36675 | 5.71 | 0.19 | 1350 | 42M | 2094 | 237MB |
=================================================================================
SQL Plan Monitoring Details (Plan Hash Value=132338241)
=======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
=======================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 38026 | +6 | 16483 | 16483 | | | | | | |
| -> 1 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 2 | NESTED LOOPS | | | | | | 16483 | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 194 | | | 16483 | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 193 | 37989 | +32 | 16483 | 0 | | | | | | |
| 5 | VIEW | VW_NSO_1 | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 6 | SORT GROUP BY | | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 7 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.21 | Cpu (195) | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | 389 | 6MB | | 11.89 | Cpu (1050) | |
| 9 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | 49 | 784KB | | 0.32 | Cpu (28) | |
| 10 | PARTITION RANGE SINGLE | | 1 | 1 | | | 392 | | | | | | | |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 1 | | | 392 | | | | | | | |
| 12 | INDEX UNIQUE SCAN | PK_ASSIN_RPE_CONTR_PERS | 1 | | | | | | | | | | | |
| 13 | PARTITION RANGE SINGLE | | 1 | | | | | | | | | | | |
| 14 | INDEX UNIQUE SCAN | PK_ASSIN_PPH_ID_PERSONNE | 1 | | | | | | | | | | | |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_PHYS | 1 | 1 | | | | | | | | | | |
| 16 | NESTED LOOPS | | | | | | 16483 | | | | | | | |
| 17 | NESTED LOOPS | | 1 | 191 | | | 16483 | | | | | | | |
| 18 | NESTED LOOPS | | 1 | 190 | 37989 | +32 | 16483 | 0 | | | | | | |
| 19 | VIEW | VW_NSO_2 | 1 | 190 | 37989 | +32 | 16483 | 392 | | | | | | |
| 20 | SORT GROUP BY | | 1 | 190 | 37989 | +32 | 16483 | 392 | | | | | | |
| 21 | PARTITION RANGE ALL | | 2 | 190 | 37989 | +32 | 16483 | 508 | | | | 2.63 | Cpu (232) | |
| 22 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 4G | 508 | 1 | 16384 | | 8.89 | Cpu (785) | |
| 23 | PARTITION RANGE SINGLE | | 1 | | | | 392 | | | | | | | |
| 24 | INDEX UNIQUE SCAN | PK_ASSIN_RPE_CONTR_PERS | 1 | | | | 392 | | | | | | | |
| 25 | PARTITION RANGE SINGLE | | 1 | | | | | | | | | | | |
| 26 | INDEX UNIQUE SCAN | PK_ASSIN_PMO_ID_PERSONNE | 1 | | | | | | | | | | | |
| 27 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_MORA | 1 | 1 | | | | | | | | | | |
| -> 28 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 29 | PARTITION RANGE SINGLE | | 1 | 2 | 1 | +35732 | 16483 | 0 | | | | 0.01 | Cpu (1) | |
| 30 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 2 | | | 16483 | | | | | | | |
| 31 | INDEX RANGE SCAN | PK_ASSIN_RPE_CONTR_PERS | 1 | 1 | | | | | | | | | | |
| -> 32 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 33 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.25 | Cpu (199) | |
| 34 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | | | | 12.28 | Cpu (1085) | |
| 35 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | | | | 0.25 | Cpu (22) | |
| -> 36 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 37 | NESTED LOOPS | | | | | | 16483 | | | | | | | |
| 38 | NESTED LOOPS | | 1 | 194 | | | 16483 | | | | | | | |
| 39 | NESTED LOOPS | | 1 | 193 | 37989 | +32 | 16483 | 0 | | | | | | |
| 40 | VIEW | VW_NSO_3 | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 41 | SORT GROUP BY | | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 42 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.58 | Cpu (228) | |
| 43 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | | | | 11.96 | Cpu (1056) | |
| 44 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | | | | 0.15 | Cpu (13) | |
| 45 | PARTITION RANGE SINGLE | | 1 | 1 | | | 392 | | | | | | | |
| 46 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 1 | | | 392 | | | | | | | |
| 47 | INDEX UNIQUE SCAN | PK_ASSIN_RPE_CONTR_PERS | 1 | | | | | | | | | | | |
| 48 | PARTITION RANGE SINGLE | | 1 | | | | | | | | | | | |
| 49 | INDEX UNIQUE SCAN | PK_ASSIN_PPH_ID_PERSONNE | 1 | | | | | | | | | | | |
| 50 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_PHYS | 1 | 1 | | | | | | | | | | |
| -> 51 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 52 | NESTED LOOPS | | | | 37989 | +32 | 16483 | 508 | | | | | | |
| 53 | NESTED LOOPS | | 1 | 197 | 37989 | +32 | 16483 | 508 | | | | | | |
| 54 | NESTED LOOPS | | 1 | 196 | 37989 | +32 | 16483 | 508 | | | | 0.01 | Cpu (1) | |
| 55 | VIEW | VW_NSO_4 | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 56 | SORT GROUP BY | | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 57 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.24 | Cpu (198) | |
| 58 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | | | | 12.36 | Cpu (1092) | |
| 59 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | | | | 0.19 | Cpu (17) | |
| 60 | PARTITION RANGE ALL | | 1 | 207 | 37989 | +32 | 392 | 508 | | | | 0.10 | Cpu (9) | |
| 61 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 207 | 37989 | +32 | 234M | 508 | | | | 0.28 | Cpu (25) | |
| 62 | INDEX RANGE SCAN | I1_TDO_D_ASSIN_ROLE_PERSONNE | 1 | 206 | 37989 | +32 | 79968 | 508 | 46 | 736KB | | | | |
| 63 | PARTITION RANGE ITERATOR | | 1 | | 37989 | +32 | 584 | 508 | | | | | | |
| 64 | INDEX UNIQUE SCAN | PK_ASSIN_PPH_ID_PERSONNE | 1 | | 37989 | +32 | 584 | 508 | 321 | 5MB | | | | |
| 65 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_PHYS | 1 | 1 | 37989 | +32 | 566 | 508 | 302 | 5MB | | | | |
| -> 66 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 67 | NESTED LOOPS | | | | | | 16483 | | | | | | | |
| 68 | NESTED LOOPS | | 1 | 194 | | | 16483 | | | | | | | |
| 69 | NESTED LOOPS | | 1 | 193 | 37989 | +32 | 16483 | 0 | | | | | | |
| 70 | VIEW | VW_NSO_5 | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 71 | SORT GROUP BY | | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 72 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.38 | Cpu (210) | |
| 73 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | | | | 11.90 | Cpu (1051) | |
| 74 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | | | | 0.23 | Cpu (20) | |
| 75 | PARTITION RANGE SINGLE | | 1 | 1 | | | 392 | | | | | | | |
| 76 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 1 | | | 392 | | | | | | | |
| 77 | INDEX UNIQUE SCAN | PK_ASSIN_RPE_CONTR_PERS | 1 | | | | | | | | | | | |
| 78 | PARTITION RANGE SINGLE | | 1 | | | | | | | | | | | |
| 79 | INDEX UNIQUE SCAN | PK_ASSIN_PPH_ID_PERSONNE | 1 | | | | | | | | | | | |
| 80 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_PHYS | 1 | 1 | | | | | | | | | | |
| -> 81 | SORT AGGREGATE | | 1 | | 38026 | +6 | 16483 | 16483 | | | | | | |
| 82 | NESTED LOOPS | | | | 37989 | +32 | 16483 | 508 | | | | | | |
| 83 | NESTED LOOPS | | 1 | 197 | 37989 | +32 | 16483 | 508 | | | | | | |
| 84 | NESTED LOOPS | | 1 | 196 | 37989 | +32 | 16483 | 508 | | | | | | |
| 85 | VIEW | VW_NSO_6 | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 86 | SORT GROUP BY | | 1 | 192 | 37989 | +32 | 16483 | 392 | | | | | | |
| 87 | PARTITION RANGE ALL | | 1 | 192 | 37989 | +32 | 16483 | 508 | | | | 2.30 | Cpu (203) | |
| 88 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 192 | 37989 | +32 | 4G | 508 | | | | 11.96 | Cpu (1056) | |
| 89 | INDEX SKIP SCAN | PK_ASSIN_RPE_CONTR_PERS | 2 | 190 | 37989 | +32 | 3M | 508 | | | | 0.25 | Cpu (22) | |
| 90 | PARTITION RANGE ALL | | 1 | 207 | 37989 | +32 | 392 | 508 | | | | 0.05 | Cpu (4) | |
| 91 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_ROLE_PERSONNE | 1 | 207 | 37989 | +32 | 234M | 508 | | | | 0.33 | Cpu (29) | |
| 92 | INDEX RANGE SCAN | I1_TDO_D_ASSIN_ROLE_PERSONNE | 1 | 206 | 37989 | +32 | 79968 | 508 | | | | | | |
| 93 | PARTITION RANGE ITERATOR | | 1 | | 37989 | +32 | 508 | 508 | | | | | | |
| 94 | INDEX UNIQUE SCAN | PK_ASSIN_PPH_ID_PERSONNE | 1 | | 37989 | +32 | 508 | 508 | | | | | | |
| 95 | TABLE ACCESS BY LOCAL INDEX ROWID | TDO_D_ASSIN_PERS_PHYS | 1 | 1 | 37989 | +32 | 508 | 508 | | | | | | |
| 96 | SORT ORDER BY | | 25124 | 13231 | | | 1 | | | | | | | |
| -> 97 | HASH UNIQUE | | 25124 | 13223 | 38026 | +6 | 1 | 0 | | | 3M | | | |
| -> 98 | PARTITION RANGE SINGLE | | 39388 | 13082 | 38026 | +6 | 1 | 16495 | | | | | | |
| -> 99 | TABLE ACCESS FULL | TDO_D_ASSIN_EVT_CONTR | 39388 | 13082 | 38026 | +6 | 1 | 16495 | 978 | 220MB | | 0.01 | db file scattered read (1) | 36% |
=======================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
9 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
11 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
12 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
filter("RPE"."RPE_NUM_CONTR"=:B1)
14 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
22 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
24 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
filter("RPE"."RPE_NUM_CONTR"=:B1)
26 - access("PMO"."NUM_INTEGRATION"=:B1 AND "PMO"."PMO_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PMO"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
30 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
31 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"=:B2)
34 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
35 - access("RPE"."RPE_NUM_CONTR"=:B1)
filter(("RPE"."RPE_NUM_CONTR"=:B1 AND "RPE"."NUM_INTEGRATION"<>:B2))
43 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
44 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
46 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
47 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
filter("RPE"."RPE_NUM_CONTR"=:B1)
49 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
58 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
59 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
61 - filter(("RPE"."RPE_CD_ROLE" LIKE 'O%' AND "RPE"."NUM_INTEGRATION"<>:B1 AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)"))
62 - access("RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR")
filter("RPE"."RPE_NUM_CONTR"=:B1)
64 - access("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION" AND
"PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PPH"."NUM_INTEGRATION"<>:B1)
73 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
74 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
76 - filter("RPE"."RPE_CD_ROLE" LIKE 'O%')
77 - access("RPE"."NUM_INTEGRATION"=:B1 AND "RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR" AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)")
filter("RPE"."RPE_NUM_CONTR"=:B1)
79 - access("PPH"."NUM_INTEGRATION"=:B1 AND "PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION")
88 - filter("RPE2"."RPE_CD_ROLE" LIKE 'O%')
89 - access("RPE2"."RPE_NUM_CONTR"=:B1)
filter("RPE2"."RPE_NUM_CONTR"=:B1)
91 - filter(("RPE"."RPE_CD_ROLE" LIKE 'O%' AND "RPE"."NUM_INTEGRATION"<>:B1 AND
"RPE"."RPE_ID_PERSONNE"="MAX(RPE2.RPE_ID_PERSONNE)"))
92 - access("RPE"."RPE_NUM_CONTR"="RPE_NUM_CONTR")
filter("RPE"."RPE_NUM_CONTR"=:B1)
94 - access("PPH"."NUM_INTEGRATION"="RPE"."NUM_INTEGRATION" AND
"PPH"."PPH_ID_PERSONNE"="RPE"."RPE_ID_PERSONNE")
filter("PPH"."NUM_INTEGRATION"<>:B1)
99 - filter(("EVC"."FLAG_MAJ_ODS"='1' AND "EVC"."NUM_INTEGRATION"=597033))
On constate que la table principale doit retourner 39 388 lignes (colonne Rows Estim) mais qu'au moment où j'ai généré le plan 16 495 lignes avaient été traitées (colonne Rows Actual).
Pour chacune de ces 39 388 lignes retournées Oracle doit exécuter les scalar subqueries présentes dans le SELECT principal.
On voit dans le plan que pour chaque scalar subquery on a un accès à TDO_D_ASSIN_ROLE_PERSONNE via l'index PK_ASSIN_RPE_CONTR_PERS en mode PARTITION RANGE ALL et PARTITION RANGE SINGLE.
Le PARTITION RANGE SINGLE a lieu lorsque la clause de partitionnement (colonne NUM_INTEGRATION) est précisée dans la clause WHERE alors que le PARTITION RANGE ALL a lieu lorsque la clause n'est pas précisée.
Quand on regarde la scalar subquery que j'ai postée plus haut on note que dans la clause WHERE principale on a bien un filtre sur le champ NUM_INTEGRATION d'où l'accès direct à la partition ciblée alors que dans la sous requête le predicat sur le NUM_INTEGRATION n'existe pas.
Le PARTITION RANGE ALL indique que pour récupérer les lignes de la table TDO_D_ASSIN_ROLE_PERSONNE toutes les partitions de l'index sont parcourues (il y'en a 224). Au moment où j'ai généré mon plan, le moteur SQL avait traité 16495 lignes.
Si on multiplie 16495 par 224, on obtient 3 694 880. Ca a l'air de correspondre aux 3 millions qu'on voit dans la colonne Execs du plan pour chaque accès à l'index PK_ASSIN_RPE_CONTR_PERS.
Si on multiplie ces 3 millions d'accès par le nombre de scalar subqueries et par le fait que 3 requêtes similaires tournaient en même temps, ça donne une explication au problème de performances qu'a connu mon client ce matin.
Dans ce genre de requête, plus on a une table dans la requête principale retournant beaucoup de lignes ainsi qu'un nombre de partitions attaquées dans la scalar sbquery important et plus les performances sont dégradées du fait du nombre d'I/O générés.
Le but de cet article est de rappeler que lorsqu'on décide de partitionner une table et ses index (index locaux) il faut s'assurer que la clause de partitionnement soit bien prise en compte dans les requêtes de l'application car ainsi notre SGBD préféré sera en mesure de n'attaquer que la partition requise en éliminant à la source les partitions inutiles. C'est ce qu'on appelle le "partition pruning". Si vous partitionnez vos tables et index localement et que vos requêtes ne prunent pas vous risquez d'obtenir des problèmes de performances encore plus importants qu'avant d'avoir partitionné. Le problème rencontré par mon client en est un exemple flagrant. D'ailleurs ce problème m'a rappelé un article très intéressant de Mohamed Houri que j'ai lu récemment et qui traite d'un problème du même type.
