Dégradation de performance : je veux comprendre

Version imprimable

Bonjour a tous,

J'ai du faire face a un problème en prod ce matin.
Un traitement qui prend normalement 6 minutes maximum tourne en plus de 7 heures!
J'ai identifié les 2 requêtes coupables. En voici une:

Code:

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
SELECT   /*+ use_hash(sr isp) */
         sr.DVID, isp.SBPID, sr.MCID,
         sr.unSPID, sr.PCID,
         sr.SPID, isp.ACID,
         NVL
            (ratio_to_report (MAX (sr.ratio_rlv)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_rlv,
         NVL
            (ratio_to_report (MAX (sr.ratio_01)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_01,
         NVL
            (ratio_to_report (MAX (sr.ratio_02)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_02,
         NVL
            (ratio_to_report (MAX (sr.ratio_03)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_03,
         NVL
            (ratio_to_report (MAX (sr.ratio_04)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_04,
         NVL
            (ratio_to_report (MAX (sr.ratio_05)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_05,
         NVL
            (ratio_to_report (MAX (sr.ratio_06)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_06,
         NVL
            (ratio_to_report (MAX (sr.ratio_07)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_07,
         NVL
            (ratio_to_report (MAX (sr.ratio_08)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_08,
         NVL
            (ratio_to_report (MAX (sr.ratio_09)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_09,
         NVL
            (ratio_to_report (MAX (sr.ratio_10)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_10,
         NVL
            (ratio_to_report (MAX (sr.ratio_11)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_11,
         NVL
            (ratio_to_report (MAX (sr.ratio_12)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_12
    FROM SOURCING_RATIO sr,
         RULE_SPLIT_PCT isp,
         (SELECT   pdr.SPID, pdr.MCID
              FROM (SELECT sourced_product_ref.unSPID,
                           sourced_product_ref.PCID,
                           company_ref.company_id manufacturer_id,
                           NVL (product_destination.company, 0) manufacturer,
                           market_ref.MCID MCID,
                           NVL (product_destination.market, 0) market,
                           sourced_product_ref.SPID
                                                           SPID,
                           sourced_product_ref.sourced_product
                                                              sourced_product,
                           product_destination.wave wave,
                           product_destination.start_shipping_date
                                                          start_shipping_date,
                           product_destination.end_shipping_date
                                                            end_shipping_date,
                           product_destination.product_destination_status
                                                   product_destination_status
                      FROM company_ref,
                           market_ref,
                           sourced_product_ref,
                           product_destination
                     WHERE NVL (product_destination.company, 0) = NVL (company_ref.company, 0)
                       AND NVL (product_destination.market, 0) = NVL (market_ref.market, 0)
                       AND product_destination.unsourced_product = sourced_product_ref.unsourced_product
                       AND product_destination.production_center = sourced_product_ref.production_center
                       AND NVL (product_destination.product_destination_status,'Z') <> 'X'
                       AND EXISTS (
                              SELECT 0
                                FROM RULE_TCAL
                               WHERE RULE_TCAL.SPID =
                                        sourced_product_ref.SPID
                                 AND RULE_TCAL.MCID =
                                                          market_ref.MCID)
                        ) pdr
          GROUP BY pdr.SPID, pdr.MCID) pdr
   WHERE sr.DVID = isp.DVID
     AND sr.DVID = 1059
     AND sr.MCID = isp.MCID
     AND pdr.SPID = sr.SPID
     AND pdr.MCID = sr.MCID
     AND sr.SPID = isp.SPID
GROUP BY sr.DVID,
         isp.SBPID,
         sr.MCID,
         sr.unSPID,
         sr.PCID,
         sr.SPID,
         isp.ACID

Voici le plan d'exécution

Code:

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
 
SELECT STATEMENT ALL_ROWS Cost: 418 																
	26 WINDOW BUFFER Cost: 418 Bytes: 241 Cardinality: 1 															
		25 SORT GROUP BY Cost: 418 Bytes: 241 Cardinality: 1 														
			24 VIEW VIEW VM_NWVW_1 Cost: 418 Bytes: 241 Cardinality: 1 													
				23 SORT GROUP BY Cost: 418 Bytes: 610 Cardinality: 1 												
					22 HASH JOIN SEMI Cost: 417 Bytes: 610 Cardinality: 1 											
						20 NESTED LOOPS 										
							18 NESTED LOOPS Cost: 271 Bytes: 584 Cardinality: 1 									
								16 NESTED LOOPS Cost: 269 Bytes: 563 Cardinality: 1 								
									13 HASH JOIN Cost: 268 Bytes: 546 Cardinality: 1 							
										10 NESTED LOOPS 						
											8 NESTED LOOPS Cost: 31 Bytes: 507 Cardinality: 1 					
												6 MERGE JOIN CARTESIAN Cost: 30 Bytes: 498 Cardinality: 1 				
													1 TABLE ACCESS FULL TABLE COMPANY_REF Cost: 30 Bytes: 5,890 Cardinality: 1,178 			
													5 BUFFER SORT Cost: 0 Bytes: 493 Cardinality: 1 			
														4 PARTITION LIST SINGLE Cost: 0 Bytes: 493 Cardinality: 1 Partition #: 15 		
															3 TABLE ACCESS BY LOCAL INDEX ROWID TABLE SOURCING_RATIO Cost: 0 Bytes: 493 Cardinality: 1 Partition #: 16 Partitions accessed #159	
																2 INDEX RANGE SCAN INDEX (UNIQUE) PK_SOURCING_RATIO Cost: 0 Cardinality: 1 Partition #: 17 Partitions accessed #159
												7 INDEX UNIQUE SCAN INDEX (UNIQUE) PK_MARKET_REF Cost: 0 Cardinality: 1 				
											9 TABLE ACCESS BY INDEX ROWID TABLE MARKET_REF Cost: 1 Bytes: 9 Cardinality: 1 					
										12 PARTITION LIST SINGLE Cost: 236 Bytes: 2,085,837 Cardinality: 53,483 Partition #: 20 						
											11 TABLE ACCESS FULL TABLE RULE_SPLIT_PCT Cost: 236 Bytes: 2,085,837 Cardinality: 53,483 Partition #: 21 Partitions accessed #159					
									15 TABLE ACCESS BY INDEX ROWID TABLE SOURCED_PRODUCT_REF Cost: 1 Bytes: 17 Cardinality: 1 							
										14 INDEX UNIQUE SCAN INDEX (UNIQUE) PK_SOURCED_PRODUCT_REF Cost: 0 Cardinality: 1 						
								17 INDEX RANGE SCAN INDEX REL_95B_FK Cost: 1 Cardinality: 1 								
							19 TABLE ACCESS BY INDEX ROWID TABLE PRODUCT_DESTINATION Cost: 2 Bytes: 21 Cardinality: 1 									
						21 TABLE ACCESS FULL TABLE (TEMP) RULE_TCAL Cost: 145 Bytes: 2,687,880 Cardinality: 103,380

Et l'analyse tkprof: avec en rouge le point qui m'interpelle.

Code:

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
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2   2066.44    2723.06     635296  304896444     255477       36585
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   2066.44    2723.06     635296  304896444     255477       36585

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 666     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=304896504 pr=635297 pw=330317 time=0 us)
  36585   VIEW  (cr=304895623 pr=635279 pw=330317 time=39095 us cost=182 size=507 card=1)
  36585    WINDOW SORT (cr=304895623 pr=635279 pw=330317 time=19554 us cost=182 size=555 card=1)
  36585     NESTED LOOPS  (cr=304895623 pr=634837 pw=329876 time=39114 us)
  64367      NESTED LOOPS  (cr=304856479 pr=634837 pw=329876 time=19546 us cost=181 size=555 card=1)
  35565       VIEW  (cr=304825782 pr=634837 pw=329876 time=0 us cost=181 size=260 card=1)
  35565        WINDOW BUFFER (cr=304825782 pr=634837 pw=329876 time=0 us cost=181 size=260 card=1)
  35565         SORT GROUP BY (cr=304825782 pr=634653 pw=329693 time=0 us cost=181 size=260 card=1)
  36585          VIEW  VM_NWVW_1 (cr=304825782 pr=634653 pw=329693 time=0 us cost=181 size=260 card=1)
  36585           SORT GROUP BY (cr=304825782 pr=634653 pw=329693 time=0 us cost=181 size=661 card=1)
  36598            FILTER  (cr=304825782 pr=634383 pw=329423 time=0 us)
  36598             HASH JOIN SEMI (cr=304825782 pr=634383 pw=329423 time=0 us cost=180 size=661 card=1)
  36598              NESTED LOOPS  (cr=304825474 pr=633855 pw=328895 time=19561 us)
181960948               NESTED LOOPS  (cr=225933635 pr=633855 pw=328895 time=14392845 us cost=35 size=635 card=1)
57061142                NESTED LOOPS  (cr=169414200 pr=633739 pw=328895 time=8243165 us cost=33 size=614 card=1)
57061142                 NESTED LOOPS  (cr=57136443 pr=633739 pw=328895 time=4086694 us cost=32 size=597 card=1)
57061142                  HASH JOIN  (cr=38028 pr=633738 pw=328895 time=860215 us cost=31 size=588 card=1)
58057730                   MERGE JOIN CARTESIAN (cr=4946 pr=305102 pw=259 time=1252003 us cost=30 size=485 card=1)
   1178                    TABLE ACCESS FULL COMPANY_REF (cr=63 pr=0 pw=0 time=0 us cost=30 size=5890 card=1178)
58057730                    BUFFER SORT (cr=4883 pr=305102 pw=259 time=782232 us cost=0 size=480 card=1)
  49285                     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4883 pr=0 pw=0 time=0 us cost=0 size=480 card=1)
  49285                      TABLE ACCESS BY LOCAL INDEX ROWID SOURCING_RATIO PARTITION: KEY KEY (cr=4883 pr=0 pw=0 time=0 us cost=0 size=480 card=1)
  49285                       INDEX RANGE SCAN PK_SOURCING_RATIO PARTITION: KEY KEY (cr=133 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 256611)
  56166                   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=33082 pr=0 pw=0 time=0 us cost=0 size=103 card=1)
  56166                    TABLE ACCESS BY LOCAL INDEX ROWID RULE_SPLIT_PCT PARTITION: KEY KEY (cr=33082 pr=0 pw=0 time=0 us cost=0 size=103 card=1)
  56166                     INDEX RANGE SCAN PK_IRULE_SPLIT_PERCENTAGE PARTITION: KEY KEY (cr=134 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 234864)
57061142                  TABLE ACCESS BY INDEX ROWID MARKET_REF (cr=57098415 pr=1 pw=0 time=0 us cost=1 size=9 card=1)
57061142                   INDEX UNIQUE SCAN PK_MARKET_REF (cr=37273 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 238058)
57061142                 TABLE ACCESS BY INDEX ROWID SOURCED_PRODUCT_REF (cr=112277757 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
57061142                  INDEX UNIQUE SCAN PK_SOURCED_PRODUCT_REF (cr=55216614 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 255508)
181960948                INDEX RANGE SCAN REL_95B_FK (cr=56519435 pr=116 pw=0 time=7498947 us cost=1 size=0 card=1)(object id 246465)
  36598               TABLE ACCESS BY INDEX ROWID PRODUCT_DESTINATION (cr=78891839 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
 109578              TABLE ACCESS FULL RULE_TCAL (cr=308 pr=0 pw=0 time=0 us cost=145 size=2364284 card=90934)
  64367       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=30697 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
  64367        INDEX RANGE SCAN PK_IRULE_SPLIT_PERCENTAGE PARTITION: KEY KEY (cr=30697 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 234864)
  36585      TABLE ACCESS BY LOCAL INDEX ROWID RULE_SPLIT_PCT PARTITION: 1 1 (cr=39144 pr=0 pw=0 time=0 us cost=0 size=295 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write temp                       7498        0.17          2.52
  direct path read temp                       28658        0.19         34.56
  resmgr:internal state change                    1        0.09          0.09
  db file sequential read                       134        0.03          0.70
  resmgr:cpu quantum                            348        0.18          6.05
  latch free                                      2        0.00          0.00
  latch: cache buffers chains                     2        0.00          0.00
********************************************************************************

Pour Info, même si la table temporaire RULE_TCAL est vide, la requête tourne pendant plus d'une heure pour finalement retourner 0 lignes!

La dernière fois que ce traitement a tourne c'est l'année dernière a peut près a la même période. Entre temps, nous avons migre de la 9i a 11g 11.1.0.7.
J'ai vérifié les stats, les segments,... RAS. Rien d'apparent via ADDM et ASH.
J'ai juste une recommandation pour créer un index sur RULE_TCAL colonnes SPID et MCID. Mais ca n'explique pas plus d'une heure d'exécution pour rien alors que la table RULE_TCAL est vide.

Je me suis dit que je vais filtrer le résultat de la main clause where avec RULE_TCAL pour au moins avoir une réponse rapide dans le cas ou RULE_TCAL est vide.

Voici la requête modifiée.

Code:

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
 
SELECT   /*+ use_hash(sr isp) */
         sr.DVID, isp.SBPID, sr.MCID,
         sr.unSPID, sr.PCID,
         sr.SPID, isp.ACID,
         NVL
            (ratio_to_report (MAX (sr.ratio_rlv)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_rlv,
         NVL
            (ratio_to_report (MAX (sr.ratio_01)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_01,
         NVL
            (ratio_to_report (MAX (sr.ratio_02)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_02,
         NVL
            (ratio_to_report (MAX (sr.ratio_03)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_03,
         NVL
            (ratio_to_report (MAX (sr.ratio_04)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_04,
         NVL
            (ratio_to_report (MAX (sr.ratio_05)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_05,
         NVL
            (ratio_to_report (MAX (sr.ratio_06)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_06,
         NVL
            (ratio_to_report (MAX (sr.ratio_07)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_07,
         NVL
            (ratio_to_report (MAX (sr.ratio_08)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_08,
         NVL
            (ratio_to_report (MAX (sr.ratio_09)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_09,
         NVL
            (ratio_to_report (MAX (sr.ratio_10)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_10,
         NVL
            (ratio_to_report (MAX (sr.ratio_11)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_11,
         NVL
            (ratio_to_report (MAX (sr.ratio_12)) OVER (PARTITION BY sr.DVID, sr.unSPID, sr.MCID, isp.ACID, isp.SBPID),
             0
            ) AS ratio_12
    FROM SOURCING_RATIO sr,
         RULE_SPLIT_PCT isp,
         (SELECT   pdr.SPID, pdr.MCID
              FROM (SELECT sourced_product_ref.unSPID,
                           sourced_product_ref.PCID,
                           company_ref.company_id manufacturer_id,
                           NVL (product_destination.company, 0) manufacturer,
                           market_ref.MCID MCID,
                           NVL (product_destination.market, 0) market,
                           sourced_product_ref.SPID
                                                           SPID,
                           sourced_product_ref.sourced_product
                                                              sourced_product,
                           product_destination.wave wave,
                           product_destination.start_shipping_date
                                                          start_shipping_date,
                           product_destination.end_shipping_date
                                                            end_shipping_date,
                           product_destination.product_destination_status
                                                   product_destination_status
                      FROM company_ref,
                           market_ref,
                           sourced_product_ref,
                           product_destination
                     WHERE NVL (product_destination.company, 0) = NVL (company_ref.company, 0)
                       AND NVL (product_destination.market, 0) = NVL (market_ref.market, 0)
                       AND product_destination.unsourced_product = sourced_product_ref.unsourced_product
                       AND product_destination.production_center = sourced_product_ref.production_center
                       AND NVL (product_destination.product_destination_status,'Z') <> 'X'
                       AND EXISTS (
                              SELECT 0
                                FROM RULE_TCAL
                               WHERE RULE_TCAL.SPID =
                                        sourced_product_ref.SPID
                                 AND RULE_TCAL.MCID =
                                                          market_ref.MCID)
                        ) pdr
          GROUP BY pdr.SPID, pdr.MCID) pdr
   WHERE sr.DVID = isp.DVID
     AND sr.DVID = 1059
     AND EXISTS (
                   SELECT 0
                   FROM RULE_TCAL
                   WHERE RULE_TCAL.SPID =sr.SPID
                         AND RULE_TCAL.MCID = sr.MCID)
     AND sr.MCID = isp.MCID
     AND pdr.SPID = sr.SPID
     AND pdr.MCID = sr.MCID
     AND sr.SPID = isp.SPID
GROUP BY sr.DVID,
         isp.SBPID,
         sr.MCID,
         sr.unSPID,
         sr.PCID,
         sr.SPID,
         isp.ACID

La l'exécution est immédiate et il n'y a bien aucune ligne de retournée si RULE_TCAL est vide.

Maintenant si je remplie RULE_TCAL (env 110000 lignes) voici le plan d'exécution:

Code:

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
 
SELECT STATEMENT ALL_ROWS Cost: 566 																
	27 WINDOW BUFFER Cost: 566 Bytes: 241 Cardinality: 1 															
		26 SORT GROUP BY Cost: 566 Bytes: 241 Cardinality: 1 														
			25 VIEW VIEW VM_NWVW_1 Cost: 566 Bytes: 241 Cardinality: 1 													
				24 SORT GROUP BY Cost: 566 Bytes: 636 Cardinality: 1 												
					23 HASH JOIN Cost: 565 Bytes: 636 Cardinality: 1 											
						21 HASH JOIN SEMI Cost: 534 Bytes: 631 Cardinality: 1 										
							19 HASH JOIN SEMI Cost: 388 Bytes: 605 Cardinality: 1 									
								17 NESTED LOOPS 								
									15 NESTED LOOPS Cost: 241 Bytes: 579 Cardinality: 1 							
										13 NESTED LOOPS Cost: 239 Bytes: 558 Cardinality: 1 						
											10 HASH JOIN Cost: 238 Bytes: 541 Cardinality: 1 					
												7 NESTED LOOPS 				
													5 NESTED LOOPS Cost: 2 Bytes: 502 Cardinality: 1 			
														3 PARTITION LIST SINGLE Cost: 1 Bytes: 493 Cardinality: 1 Partition #: 14 		
															2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE SOURCING_RATIO Cost: 1 Bytes: 493 Cardinality: 1 Partition #: 15 Partitions accessed #159	
																1 INDEX RANGE SCAN INDEX (UNIQUE) PK_SOURCING_RATIO Cost: 1 Cardinality: 1 Partition #: 16 Partitions accessed #159
														4 INDEX UNIQUE SCAN INDEX (UNIQUE) PK_MARKET_REF Cost: 0 Cardinality: 1 		
													6 TABLE ACCESS BY INDEX ROWID TABLE MARKET_REF Cost: 1 Bytes: 9 Cardinality: 1 			
												9 PARTITION LIST SINGLE Cost: 236 Bytes: 2,085,837 Cardinality: 53,483 Partition #: 19 				
													8 TABLE ACCESS FULL TABLE RULE_SPLIT_PCT Cost: 236 Bytes: 2,085,837 Cardinality: 53,483 Partition #: 20 Partitions accessed #159			
											12 TABLE ACCESS BY INDEX ROWID TABLE SOURCED_PRODUCT_REF Cost: 1 Bytes: 17 Cardinality: 1 					
												11 INDEX UNIQUE SCAN INDEX (UNIQUE) PK_SOURCED_PRODUCT_REF Cost: 0 Cardinality: 1 				
										14 INDEX RANGE SCAN INDEX REL_95B_FK Cost: 1 Cardinality: 1 						
									16 TABLE ACCESS BY INDEX ROWID TABLE PRODUCT_DESTINATION Cost: 2 Bytes: 21 Cardinality: 1 							
								18 TABLE ACCESS FULL TABLE (TEMP) RULE_TCAL Cost: 145 Bytes: 3,223,870 Cardinality: 123,995 								
							20 TABLE ACCESS FULL TABLE (TEMP) RULE_TCAL Cost: 145 Bytes: 3,223,870 Cardinality: 123,995 									
						22 TABLE ACCESS FULL TABLE COMPANY_REF Cost: 30 Bytes: 5,890 Cardinality: 1,178

J'ai le resultat en 2 secondes!

Voici la trace via tkprof:

Code:

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
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      3.23       3.22          0          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       66      2.45       2.99        184     183202          2       32256
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       68      5.68       6.21        184     183208          2       32256
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5112  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
  32256  WINDOW BUFFER (cr=183202 pr=184 pw=165 time=0 us cost=565 size=241 card=1)
  32256   SORT GROUP BY (cr=183202 pr=18 pw=0 time=19566 us cost=565 size=241 card=1)
  33407    VIEW  VM_NWVW_1 (cr=183202 pr=18 pw=0 time=0 us cost=565 size=241 card=1)
  33407     SORT GROUP BY (cr=183202 pr=18 pw=0 time=0 us cost=565 size=636 card=1)
  33419      HASH JOIN  (cr=183202 pr=18 pw=0 time=0 us cost=564 size=636 card=1)
  33419       HASH JOIN SEMI (cr=183139 pr=17 pw=0 time=19564 us cost=534 size=631 card=1)
  33419        HASH JOIN SEMI (cr=182833 pr=17 pw=0 time=0 us cost=388 size=605 card=1)
  33419         NESTED LOOPS  (cr=182527 pr=17 pw=0 time=0 us)
 147841          NESTED LOOPS  (cr=130230 pr=17 pw=0 time=0 us cost=241 size=579 card=1)
  44422           NESTED LOOPS  (cr=103138 pr=1 pw=0 time=0 us cost=239 size=558 card=1)
  44422            HASH JOIN  (cr=54556 pr=1 pw=0 time=0 us cost=238 size=541 card=1)
  49284             NESTED LOOPS  (cr=54051 pr=1 pw=0 time=0 us)
  49284              NESTED LOOPS  (cr=4767 pr=1 pw=0 time=0 us cost=2 size=502 card=1)
  49284               PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4763 pr=0 pw=0 time=0 us cost=1 size=493 card=1)
  49284                TABLE ACCESS BY LOCAL INDEX ROWID SOURCING_RATIO PARTITION: 159 159 (cr=4763 pr=0 pw=0 time=0 us cost=1 size=493 card=1)
  49284                 INDEX RANGE SCAN PK_SOURCING_RATIO PARTITION: 159 159 (cr=128 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 256611)
  49284               INDEX UNIQUE SCAN PK_MARKET_REF (cr=4 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 238058)
  49284              TABLE ACCESS BY INDEX ROWID MARKET_REF (cr=49284 pr=0 pw=0 time=0 us cost=1 size=9 card=1)
  53488             PARTITION LIST SINGLE PARTITION: KEY KEY (cr=505 pr=0 pw=0 time=0 us cost=236 size=2085837 card=53483)
  53488              TABLE ACCESS FULL RULE_SPLIT_PCT PARTITION: 159 159 (cr=505 pr=0 pw=0 time=0 us cost=236 size=2085837 card=53483)
  44422            TABLE ACCESS BY INDEX ROWID SOURCED_PRODUCT_REF (cr=48582 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
  44422             INDEX UNIQUE SCAN PK_SOURCED_PRODUCT_REF (cr=4160 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 255508)
 147841           INDEX RANGE SCAN REL_95B_FK (cr=27092 pr=16 pw=0 time=0 us cost=1 size=0 card=1)(object id 246465)
  33419          TABLE ACCESS BY INDEX ROWID PRODUCT_DESTINATION (cr=52297 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
 109261         TABLE ACCESS FULL RULE_TCAL (cr=306 pr=0 pw=0 time=0 us cost=145 size=2814760 card=108260)
 109261        TABLE ACCESS FULL RULE_TCAL (cr=306 pr=0 pw=0 time=0 us cost=145 size=2814760 card=108260)
   1178       TABLE ACCESS FULL COMPANY_REF (cr=63 pr=1 pw=0 time=0 us cost=30 size=5890 card=1178)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      66        0.00          0.00
  db file sequential read                        18        0.02          0.19
  direct path write temp                          3        0.00          0.00
  direct path read temp                         146        0.00          0.08
  SQL*Net message from client                    66        6.83          8.95
  SQL*Net more data to client                   355        0.00          0.00
********************************************************************************

Les 2 full scan sur RULE_TCAL peuvent etre evite via un index sur les colonnes SPID et MCID.

J'ai résolut notre problème mais je n'arrive pas a parfaitement décrire et expliquer la cause. Cela ne me saute pas aux yeux!

Je me demande même si ceci n'est pas limite un bug de l'optimiseur.

Avez vous un avis?

Un grand merci a vous
jko

21/07/2011, 21h48
pachot

Bonjour,

Sans avoir creusé dans les détails, je pense que le problème du premier plan d'exécution est le MERGE JOIN CARTESIAN. Il faudrait comprendre pourquoi Oracle choisit celà. Peut-être en regardant le plan d'exécution avec prédicats.
Cela raméne 58057730 lignes, alors qu'Oracle estime la cardinalité à 1 ... donc il choisit un nested loop mais se retrouve à exécuter la deuxième partie 58 millions de fois.

Cordialement,
Franck.

Hello,
Merci pour ton message! Je me sentais bien seul :P

Tu as raison sur le merge et je l'avais vu. Mais ce que je ne comprends pas c'est que l'optimiseur fait un merge entre une table de la sous requete (COMPANY_REF) et une des tables de la main clause from (SOURCING_RATIO).
Code:

1 2 3 4 5 6 7 6 MERGE JOIN CARTESIAN Cost: 30 Bytes: 498 Cardinality: 1 1 TABLE ACCESS FULL TABLE COMPANY_REF Cost: 30 Bytes: 5,890 Cardinality: 1,178 5 BUFFER SORT Cost: 0 Bytes: 493 Cardinality: 1 4 PARTITION LIST SINGLE Cost: 0 Bytes: 493 Cardinality: 1 Partition #: 15 3 TABLE ACCESS BY LOCAL INDEX ROWID TABLE SOURCING_RATIO Cost: 0 Bytes: 493 Cardinality: 1 Partition #: 16 Partitions accessed #159 2 INDEX RANGE SCAN INDEX (UNIQUE) PK_SOURCING_RATIO Cost: 0 Cardinality: 1 Partition #: 17 Partitions accessed #159
Après avoir changé la requête en rajoutant le EXISTS au niveau de la main clause where le merge disparait alors que la sous requête n'a pas été touchée.

C'est troublant non?
Jko

22/07/2011, 09h11
ojo77

Bonjour,

Pour déterminer ce qui va pousser l'optimiseur à choisir un plan ou un autre il faut regarder la trace 10053.

Vous dites que la requête à un comportement retourne un nombre de lignes différent en fonction du contenu de la table RULE_TCAL. Les statistiques sur cette table sont-elles à jour à chaque exécution de la requête ?
22/07/2011, 09h27
orafrance

Citation:

Envoyé par jkofr

Pour Info, même si la table temporaire RULE_TCAL est vide, la requête tourne pendant plus d'une heure pour finalement retourner 0 lignes!

Ca sent un problème de High Water Mark, essaye de faire un TRUNCATE de la table si elle est toujours vide ;)

Sinon, faudrait connaitre le plan d'exécution avant le souci aussi et voir si la dérive ne coïnciderait pas avec un calcul des stats.
22/07/2011, 09h49
jkofr

Citation:

Envoyé par ojo77

Bonjour,

Pour déterminer ce qui va pousser l'optimiseur à choisir un plan ou un autre il faut regarder la trace 10053.

Vous dites que la requête à un comportement retourne un nombre de lignes différent en fonction du contenu de la table RULE_TCAL. Les statistiques sur cette table sont-elles à jour à chaque exécution de la requête ?

Hello,

Les stats on ete collectées avec la trace 10053.

La table RULE_TCAL est une table temporaire. Il n'y a pas de stats dessus et donc Oracle fait un dynamic sampling au moment de l'exécution.
22/07/2011, 09h50
jkofr

Citation:

Envoyé par orafrance

Ca sent un problème de High Water Mark, essaye de faire un TRUNCATE de la table si elle est toujours vide ;)

Sinon, faudrait connaitre le plan d'exécution avant le souci aussi et voir si la dérive ne coïnciderait pas avec un calcul des stats.

Les stats sont ok, je vais poster l'inventaire précis des segments.

Jko
22/07/2011, 10h17
jkofr

Ok,

Voici ce qu'il me semble est la conclusion de tout ca:

Les 2 tables partitionnées SOURCING_RATIO et RULE_SPLIT_PCT sont, au début du traitement vides. Elles sont l'output final du process.

Les partitions sont crées au moment de la création du "Data business period" et la partition key est le DVID de la période.

Donc au début du traitement, le système commence par calculer les stats de toutes les tables qui interviennent dans le traitement. Ces deux tables sont évaluées et révèlent 0 rows au niveau des stats. Tout ca est normal.

Ensuite, plus loin dans le process ces tables sont remplies et sont utilises par la suite pour faire un insert as select dans une table résultat. C'est cette requête qui part dans les choux!

Car les données dans la table ne reflètent pas les stats.
Pour valider ca, je viens de recalculer les stats de ces 2 tables avec les donnes après traitement et la requête de base fonctionne très bien.

C'est donc la logique du process qui est a modifier. Peut-être supprimer les stats sur ces deux tables au début du process pour forcer un dynamic sampling...

Une autre idée?

Merci a vous
jko
22/07/2011, 10h27
orafrance

Citation:

Envoyé par jkofr

Les stats sont ok, je vais poster l'inventaire précis des segments.

Jko

Et le TRUNCATE ? ;)
22/07/2011, 10h28
orafrance

Citation:

Envoyé par jkofr

Ensuite, plus loin dans le process ces tables sont remplies et sont utilises par la suite pour faire un insert as select dans une table résultat. C'est cette requête qui part dans les choux!

Ha ben oui, effectivement si les stats disent que la table est vide c'est pas super :mrgreen:
22/07/2011, 10h46
ojo77

Citation:

Envoyé par orafrance

Ha ben oui, effectivement si les stats disent que la table est vide c'est pas super :mrgreen:

On a de la chance, elles ne le disent pas :

Dans le premier exemple :

Code:

TABLE ACCESS FULL RULE_TCAL (cr=308 pr=0 pw=0 time=0 us cost=145 size=2364284 card=90934)

Dans le second :

Code:

TABLE ACCESS FULL RULE_TCAL (cr=306 pr=0 pw=0 time=0 us cost=145 size=2814760 card=108260)

Hello,

Voici la requête originale avec les stats a jour.

Code:

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
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |     1 |   198 |   921   (5)|       |       |
|   1 |  WINDOW BUFFER                     |                        |     1 |   198 |   921   (5)|       |       |
|   2 |   SORT GROUP BY                    |                        |     1 |   198 |   921   (5)|       |       |
|   3 |    VIEW                            | VM_NWVW_1              |     1 |   198 |   921   (5)|       |       |
|   4 |     SORT GROUP BY                  |                        |     1 |   238 |   921   (5)|       |       |
|*  5 |      HASH JOIN                     |                        |     1 |   238 |   920   (5)|       |       |
|*  6 |       HASH JOIN                    |                        |     1 |   233 |   889   (5)|       |       |
|   7 |        NESTED LOOPS                |                        |       |       |            |       |       |
|   8 |         NESTED LOOPS               |                        |     1 |   194 |   653   (7)|       |       |
|*  9 |          HASH JOIN                 |                        |     2 |   346 |   649   (7)|       |       |
|  10 |           VIEW                     | index$_join$_007       | 55554 |   922K|   231   (1)|       |       |
|* 11 |            HASH JOIN               |                        |       |       |            |       |       |
|  12 |             INDEX FAST FULL SCAN   | REL_30E_FK             | 55554 |   922K|   141   (1)|       |       |
|  13 |             INDEX FAST FULL SCAN   | PK_SOURCED_PRODUCT_REF | 55554 |   922K|    89   (0)|       |       |
|* 14 |           HASH JOIN                |                        |   107K|    15M|   417   (9)|       |       |
|  15 |            SORT UNIQUE             |                        |   107K|  2730K|   145   (1)|       |       |
|  16 |             TABLE ACCESS FULL      | RULE_TCAL              |   107K|  2730K|   145   (1)|       |       |
|* 17 |            HASH JOIN               |                        | 49279 |  6256K|   238   (1)|       |       |
|  18 |             TABLE ACCESS FULL      | MARKET_REF             |   576 |  5184 |    30   (0)|       |       |
|  19 |             PARTITION LIST SINGLE  |                        | 49279 |  5823K|   207   (1)|   KEY |   KEY |
|  20 |              TABLE ACCESS FULL     | SOURCING_RATIO         | 49279 |  5823K|   207   (1)|   159 |   159 |
|* 21 |          INDEX RANGE SCAN          | REL_95B_FK             |     1 |       |     1   (0)|       |       |
|* 22 |         TABLE ACCESS BY INDEX ROWID| PRODUCT_DESTINATION    |     1 |    21 |     2   (0)|       |       |
|  23 |        PARTITION LIST SINGLE       |                        | 53483 |  2036K|   236   (1)|   KEY |   KEY |
|  24 |         TABLE ACCESS FULL          | RULE_SPLIT_PCT         | 53483 |  2036K|   236   (1)|   159 |   159 |
|  25 |       TABLE ACCESS FULL            | COMPANY_REF            |  1178 |  5890 |    30   (0)|       |       |
------------------------------------------------------------------------------------------------------------------

Je pense que que CBO n'arrive pas à faire son "query unnesting" correctement :

est il possible de réécrire la "main clause from" comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    FROM SOURCING_RATIO sr,
         RULE_SPLIT_PCT isp,
         (SELECT distinct market_ref.MCID MCID,
		                  sourced_product_ref.SPID SPID
          FROM company_ref,
               market_ref,
               sourced_product_ref,
               product_destination
			   rule_tcal rtc
         WHERE NVL (product_destination.company, 0) = NVL (company_ref.company, 0)
           AND NVL (product_destination.market, 0) = NVL (market_ref.market, 0)
           AND product_destination.unsourced_product = sourced_product_ref.unsourced_product
           AND product_destination.production_center = sourced_product_ref.production_center
           AND NVL (product_destination.product_destination_status,'Z') <> 'X'
           AND rtc.spid=sourced_product_ref.SPID
		   AND rtc.mcid=market_ref.MCID
            ) pdr

afin de voir si Oracle est capable à ce moment de sortir rule_tcal de la vue dynamique et évidemment s'il y a un gain

22/07/2011, 11h14
PpPool

Attention, sur la release 11g nous avons constaté pour notre part un sampling level 7 !!! pour toutes les tables dés lors qu'une table ne comportait pas de stats

donc même pour les tables de travail, il te faut collecter des statistiques

de notre côté nous les avons locké une fois celles-ci assez représentatives

attention également à ta release, il existe des bugs référencés sur Metalink et l'estimation de la cardinalité réalisée par l'optimizer et les index partitionnés

patchet set : 11.2.0.2
22/07/2011, 11h20
jkofr

Hello,

J'avais essayé mais l'incohérence des stats produisait le même effet.

Ce qui est navrant c'est que la solution trouvée dans l'urgence biaise juste l'optimiseur et l'oblige a revoir son plan que les stats en présence lui suggère.

L'équipe du change management est déjà en train de préparer le patch...

Personnellement, dans tous les traitements ou des tables partitionnées sont utilisées de la sorte j'ajouterai un step pour supprimer les stats des partitions et ainsi, activer le dynamic sampling.

Je peux aussi proposer l'utilisation de SQL plan management .

Pour info nous somme en: 11.1.0.7

Voila je pense que j'ai toutes les explications a mon problème.
Je clouterai ce post ce soir.
Merci pour vos conseils.
jko
22/07/2011, 14h43
pachot

Citation:

Envoyé par jkofr

C'est donc la logique du process qui est a modifier. Peut-être supprimer les stats sur ces deux tables au début du process pour forcer un dynamic sampling...

Oui, c'est une bonne idée de supprimer les stats sur ce genre de tables (et de locker les stats pour être sur qu'un job automatique ne vienne pas le calculer).
Autres solutions (mais pas meilleures...):
- forcer la cardinalité avec le hint opt_estimate
- forcer le dynamic samplig avec hint

Cordialement,
Franck.
26/07/2011, 13h46
Ahmed AANGOUR

Attention au dynamic sampling sur des tables de travail. Si ces tables à force d'inserts et de deletes massifs se retrouvent fragmentées le Dynamic Sampling risque de ne sampler que des blocks vides et estimer que la cardinalité retournée sera zéro (ajusté à 1).
Est-ce que le DS est appliqué à la table SOURCING_RATIO ?

Si vous êtes face à ce pb, les solutions sont:
- shrinker la table
- sinon augmenter le level du DS pour sampler un plus grand nombre de blocks.
26/07/2011, 13h59
jkofr

Citation:

Envoyé par Ahmed AANGOUR

Attention au dynamic sampling sur des tables de travail. Si ces tables à force d'inserts et de deletes massifs se retrouvent fragmentées le Dynamic Sampling risque de ne sampler que des blocks vides et estimer que la cardinalité retournée sera zéro (ajusté à 1).
Est-ce que le DS est appliqué à la table SOURCING_RATIO ?

Si vous êtes face à ce pb, les solutions sont:
- shrinker la table
- sinon augmenter le level du DS pour sampler un plus grand nombre de blocks.

A mon sens, sur des table temporaires (de travail), le probleme ne se pose pas.
Par contre pour une partition comme SOURCING_RATIO il peut se poser.

Dans ce cas:
- truncate partition
- delete stats

Merci a vous
jko
26/07/2011, 14h02
Ahmed AANGOUR

Bien sûr je parlais des tables de travail qui ne sont pas des Global Temporary Tables