Discussion :

[brolon]

Bonjour à tous,

Deux des scripts de l'ETL sur lequel je travaille chargent environ 18 millions de lignes chacun. Bien que le nombre de lignes et le nombre de colonnes soit relativement semblables, les performances sont grandement différentes:

Script 1:

18601974 rows created.
Elapsed: 01:17:05.21

Script 2:

18669334 rows created.
Elapsed: 00:02:21.37

Les deux scripts sont des "insert into (...) select (...) from", réalisés avec les hints append et parallel. Jusqu'ici la situation est assez similaire. Dans le script 1, une jointure est réalisée entre une grosse table d'input et deux petits subsets d'une table de mapping. Dans le script 2, aucune jointure n'est réalisée à partir de la table d'input.

Dans le cas du script 1, des index sont présents sur les colonnes utilisées pour réaliser la jointure, tant au niveau de la table d'input qu'au niveau de la table de mapping.

Malgré cela, la jointure peut-elle expliquer une si grande différence de performance entre les deux scripts?

Une autre chose qui est différente entre les deux scripts est que dans le script 1, on utilise des fonctions "customs" (pour convertir certains champs de type date) tandis que dans le script 2, on n'utilise que des fonctions "built-in".

Cela peut-il avoir un impact sur les performances?

D'avance, merci pour vos réponses.

[Waldar]

Malgré cela, la jointure peut-elle expliquer une si grande différence de performance entre les deux scripts ?

Une trace avec explain plan pourra expliquer ce phénomène, là comme ça difficile d'être factuel.

dans le script 1, on utilise des fonctions "customs" (pour convertir certains champs de type date) tandis que dans le script 2, on n'utilise que des fonctions "built-in".

Cela peut-il avoir un impact sur les performances ?

Oui, c'est certain, ça dépend vraiment de comment est écrite la fonction.

[brolon]

Voici l'explain plan obtenu dans toad. Pour ce test, la table d'input contient 2500000 records.

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
Plan
INSERT STATEMENT  ALL_ROWSCost: 1,566  Bytes: 339,999,456  Cardinality: 2,499,996  															
	20 LOAD AS SELECT CMC.CMC_CONTRACTS_CLOSED 														
		19 PX COORDINATOR  													
			18 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10002 Cost: 1,566  Bytes: 339,999,456  Cardinality: 2,499,996  												
				17 HASH JOIN PARALLEL_COMBINED_WITH_PARENT Cost: 1,566  Bytes: 339,999,456  Cardinality: 2,499,996  											
					14 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost: 8  Bytes: 98,790  Cardinality: 1,110  										
						13 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10001 Cost: 8  Bytes: 98,790  Cardinality: 1,110  									
							12 MERGE JOIN CARTESIAN PARALLEL_COMBINED_WITH_PARENT Cost: 8  Bytes: 98,790  Cardinality: 1,110  								
								3 SORT JOIN PARALLEL_COMBINED_WITH_PARENT 							
									2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 2  Bytes: 120  Cardinality: 3  						
										1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT CMC.CMC_CODIFICATION Cost: 2  Bytes: 120  Cardinality: 3  					
								11 BUFFER SORT PARALLEL_COMBINED_WITH_PARENT Cost: 6  Bytes: 18,130  Cardinality: 370  							
									10 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD 						
										9 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Bytes: 18,130  Cardinality: 370  					
											8 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10000 Bytes: 18,130  Cardinality: 370  				
												7 VIEW VIEW CMC.V_CMC_COD_NATURE Bytes: 18,130  Cardinality: 370  			
													6 SORT ORDER BY  Cost: 3  Bytes: 14,800  Cardinality: 370  		
														5 TABLE ACCESS BY INDEX ROWID TABLE CMC.CMC_CODIFICATION Cost: 2  Bytes: 14,800  Cardinality: 370  	
															4 INDEX RANGE SCAN INDEX CMC.CMC_IDE_CODIF_CODE_I Cost: 1  Cardinality: 370  
					16 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 1,550  Bytes: 117,499,812  Cardinality: 2,499,996  										
						15 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT CMCE.ETL_AMI_IN_CTF Cost: 1,550  Bytes: 117,499,812  Cardinality: 2,499,996

Concernant les fonctions, elles sont écrites assez simplement. Voici un exemple:

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
CREATE OR REPLACE FUNCTION VNC."VNC_CONVERT_DATE" (InDate IN VARCHAR2)
RETURN DATE
IS
 
    OutDate DATE;
 
BEGIN
 
    IF InDate <> '00000000' and InDate <> '19404040' and InDate <> '65069569' THEN
        OutDate := to_date(InDate,'YYYYMMDD');
    ELSE
        OutDate := null;
    END IF;
 
    RETURN OutDate;
END VNC_CONVERT_DATE;
/

[spdev666]

Bonjour,

Je suppose que si la différence viens de la fonction "custom", cela doit être visible en ne s'intéressant qu'aux selects, en dehors des inserts.

Peut être aussi que le la différence vient des index qui doivent être maintenus à jour sur les deux tables : il y en a peut etre plus sur la table 1 ?

[Waldar]

Difficile à relire votre plan, utilisez cette méthode sous Toad (execute as a script) :

Code :

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

1
2
3
4
5
6
7
set linesize 250;
-- À adapter selon le résultat visuel
 
explain plan for
votre_select;
 
SELECT * FROM TABLE(dbms_xplan.display);

Il faut aussi l'analyse TKPROF de la trace pour savoir où est consommé le temps :

Code :

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

1
2
3
4
5
alter session set events '10046 trace name context forever, level 12';
 
votre_select;
 
alter session set events '10046 trace name context off';

[brolon]

Voici l'explain plan, qui j'espère sera plus lisible:

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
Explain complete.
 
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3618009043                                                                                                                                                                                                                               
 
----------------------------------------------------------------------------------------------------------------------------------------------                                                                                                            
| Id  | Operation                                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------                                                                                                            
|   0 | INSERT STATEMENT                         |                      |  2499K|   324M|  1566   (3)| 00:00:19 |        |      |            |                                                                                                            
|   1 |  LOAD AS SELECT                          | VNC_BIG_TABLE        |       |       |            |          |        |      |            |                                                                                                            
|   2 |   PX COORDINATOR                         |                      |       |       |            |          |        |      |            |                                                                                                            
|   3 |    PX SEND QC (RANDOM)                   | :TQ10002             |  2499K|   324M|  1566   (3)| 00:00:19 |  Q1,02 | P->S | QC (RAND)  |                                                                                                            
|*  4 |     HASH JOIN                            |                      |  2499K|   324M|  1566   (3)| 00:00:19 |  Q1,02 | PCWP |            |                                                                                                            
|   5 |      PX RECEIVE                          |                      |  1110 | 98790 |     8  (13)| 00:00:01 |  Q1,02 | PCWP |            |                                                                                                            
|   6 |       PX SEND BROADCAST                  | :TQ10001             |  1110 | 98790 |     8  (13)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |                                                                                                            
|   7 |        MERGE JOIN CARTESIAN              |                      |  1110 | 98790 |     8  (13)| 00:00:01 |  Q1,01 | PCWP |            |                                                                                                            
|   8 |         SORT JOIN                        |                      |       |       |            |          |  Q1,01 | PCWP |            |                                                                                                            
|   9 |          PX BLOCK ITERATOR               |                      |     3 |   120 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |                                                                                                            
|* 10 |           TABLE ACCESS FULL              | VNC_CODIFICATION     |     3 |   120 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |                                                                                                            
|  11 |         BUFFER SORT                      |                      |   370 | 18130 |     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |                                                                                                            
|  12 |          BUFFER SORT                     |                      |       |       |            |          |  Q1,01 | PCWC |            |                                                                                                            
|  13 |           PX RECEIVE                     |                      |   370 | 18130 |            |          |  Q1,01 | PCWP |            |                                                                                                            
|  14 |            PX SEND BROADCAST             | :TQ10000             |   370 | 18130 |            |          |        | S->P | BROADCAST  |                                                                                                            
|  15 |             VIEW                         | V_VNC_COD_NATURE     |   370 | 18130 |            |          |        |      |            |                                                                                                            
|  16 |              SORT ORDER BY               |                      |   370 | 14800 |     3  (34)| 00:00:01 |        |      |            |                                                                                                            
|  17 |               TABLE ACCESS BY INDEX ROWID| VNC_CODIFICATION     |   370 | 14800 |     2   (0)| 00:00:01 |        |      |            |                                                                                                            
|* 18 |                INDEX RANGE SCAN          | VNC_IDE_CODIF_CODE_I |   370 |       |     1   (0)| 00:00:01 |        |      |            |                                                                                                            
|  19 |      PX BLOCK ITERATOR                   |                      |  2499K|   112M|  1550   (2)| 00:00:19 |  Q1,02 | PCWC |            |                                                                                                            
|* 20 |       TABLE ACCESS FULL                  | ETL_BIG_TABLE        |  2499K|   112M|  1550   (2)| 00:00:19 |  Q1,02 | PCWP |            |                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------                                                                                                            
 
Predicate Information (identified by operation id):                                                                                                                                                                                                       
---------------------------------------------------                                                                                                                                                                                                       
 
   4 - access("ETL_BIG_TABLE"."COD_PRIV_PROF"="IDE_CODE" AND "V_VNC_COD_NATURE"."IDE_CODE"="ETL_BIG_TABLE"."COD_NATURE")                                                                                                                                
  10 - filter("IDE_CODIF_CODE"='COD_PRIV_PROF')                                                                                                                                                                                                           
  18 - access("IDE_CODIF_CODE"='COD_NATURE')                                                                                                                                                                                                              
  20 - filter("CUST_IDE_NPERS"<>'0000000000')                                                                                                                                                                                                             
 
 
35 rows selected.

Lorsque j'essaie d'obtenir le résultat de tkprof, j'obtiens une erreur de type 'insufficient privileges'.

[Waldar]

C'est le plan de quelle requête celui-ci, la lente ou la rapide ?

Pour la trace, voyez auprès de votre DBA, le fichier sera écrit dans ce répertoire :

Code :

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

1
2
3
SELECT value
  FROM sys.v_$diag_info
 WHERE name = 'Default Trace File';

[mnitu]

Dans votre exemple, votre fonction peut être remplacée par un case. Si vous avez N fonctions exécutez pour chaque enregistrement disons M il y a des forte chances que vous allez faire N * M changement de contexte entre le moteur SQL et PL/SQL. C’est à éviter. Bien sûr, sans trace SQL il sera hasardeux d’expliquer toute la différence de temps d’exécution par ce phénomène.