Optimisation de requete car des full access table

**elkamaro** · 30/05/2011, 14h17

Bonjour à tous

tout d'abord voici la requete incriminé :

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
SELECT   * 
FROM (
	SELECT   VALEUR.ID_VALEUR, REF1.LIBELLE AS L1, REF2.LIBELLE AS L2, REF3.LIBELLE AS L3, PRODUIT.LIBELLE AS L4, PRODUIT.IDENTIFIANT_PRODUIT, VALEUR.VALEUR, 
					TO_CHAR(VALEUR.DATE_VALEUR,'YYYYMMDDHH24MISS'), VALEUR.LST_STATUT, TO_CHAR(VALEUR.DATE_DEBUT_STATUT,'YYYYMMDDHH24MISS'), 
					PRODUIT.ID_PRODUIT,VALEUR.REF_CREATEUR, VALEUR.LST_FLAG_VIVANT, 
					row_number() over (order by VALEUR.ID_VALEUR) as ligne 
			FROM VALEUR, PRODUIT, CATALOGUE.REFERENTIEL REF1, CATALOGUE.REFERENTIEL REF2, CATALOGUE.REFERENTIEL REF3
			WHERE VALEUR.DATE_FIN_STATUT IS NULL
			AND VALEUR.ID_PRODUIT = PRODUIT.ID_PRODUIT
			AND PRODUIT.REF_SOURCE=REF1.ID_REF
			AND PRODUIT.REF_UNITE=REF2.ID_REF
			AND PRODUIT.REF_DIFFUSEUR=REF3.ID_REF
			AND VALEUR.ID_VALEUR>'44948237' AND PRODUIT.ID_PRODUIT IN(
	SELECT PRODUIT.ID_PRODUIT
			FROM PRODUIT, CATALOGUE.REFERENTIEL REF1, 
			ISEDOM.PLUGIN_PERIMETRE_REPLICATION PREP
			WHERE PRODUIT.REF_SOURCE=REF1.ID_REF
			AND REF1.LIBELLE = PREP.SOURCE
			AND PREP.APPLICATION='PLUGIN'
			AND PRODUIT.IDENTIFIANT_PRODUIT LIKE PREP.PRODUIT
			) 
) 
WHERE ligne<=1000;

secondo, voici le plan d'exécution que j'obtiens pour cette requete :

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
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                         | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                              | 21494 |  6192K| 11805 |       |       |
|   1 |  VIEW                                      |                              | 21494 |  6192K| 11805 |       |       |
|   2 |   WINDOW SORT PUSHED RANK                  |                              | 21494 |  3463K| 11805 |       |       |
|   3 |    HASH JOIN                               |                              | 21494 |  3463K| 11019 |       |       |
|   4 |     TABLE ACCESS FULL                      | REFERENTIEL                  |   263 |  3156 |  3 |          |       |
|   5 |     HASH JOIN                              |                              | 21284 |  3180K| 11015 |       |       |
|   6 |      TABLE ACCESS FULL                     | REFERENTIEL                  |   263 |  3156 |  3 |          |       |
|   7 |      HASH JOIN                             |                              | 21284 |  2930K| 11011 |       |       |
|   8 |       TABLE ACCESS FULL                    | REFERENTIEL                  |   263 |  3156 |  3 |          |       |
|   9 |       HASH JOIN SEMI                       |                              | 22186 |  2794K| 11008 |       |       |
|  10 |        HASH JOIN                           |                              | 22514 |  2550K|  5868 |       |       |
|  11 |         PARTITION HASH ALL                 |                              | 22514 |   901K|  5102 |     1 |    10 |
|  12 |          TABLE ACCESS BY GLOBAL INDEX ROWID| VALEUR                       | 22514 |   901K|  5102 | ROWID | ROWID |
|  13 |           INDEX RANGE SCAN                 | PK_VALEUR                    | 33532 |       |    96 |     1 |    10 |
|  14 |         TABLE ACCESS FULL                  | SERIE                        | 89621 |  6564K|   336 |       |       |
|  15 |        VIEW                                | VW_NSO_1                     |  2986K|    37M|  1401 |       |       |
|  16 |         HASH JOIN                          |                              |  2986K|   182M|  1401 |       |       |
|  17 |          TABLE ACCESS FULL                 | PLUGIN_PERIMETRE_REPLICATION | 53197 |  1454K|    71 |       |       |
|  18 |          HASH JOIN                         |                              | 90508 |  3181K|   340 |       |       |
|  19 |           TABLE ACCESS FULL                | REFERENTIEL                  |   263 |  3156 |  3 |          |       |
|  20 |           TABLE ACCESS FULL                | SERIE                        | 89621 |  2100K|   335 |       |       |
---------------------------------------------------------------------------------------------------------------------------

Lorsque j'exécute cette requête, elle met environ 13 minute.
Sur une autre base , elle à peine 13 secondes

Sauriez vous m'aider à voir d ou vient le probléme ou du moins une piste pour analyser le probleme

Merci

**Waldar** · 30/05/2011, 14h50

Il faudrait également fournir le plan d'exécution de la requête qui met treize secondes, et savoir si les volumétries / index / fraîcheur des statistiques sont similaires.

**elkamaro** · 30/05/2011, 15h05

bonjour le voici
pas la même presentation car ce n est pas moi qui l obtient

Merci

**orafrance** · 30/05/2011, 15h18

C'est bien la même version de base, stats à jour sur les 2 bases et volumétrie identique ?

**elkamaro** · 30/05/2011, 16h13

Envoyé par orafrance

C'est bien la même version de base, stats à jour sur les 2 bases et volumétrie identique ?

bonjour orafrance

oui ce sont deux bases similaires seul la quantité de donnée peuvent différé mais de pas beaucoup

j'ai recalculé les stats sur tous les schéma utlisés à + de 40% (100 même pour certains schéma)

**mnitu** · 30/05/2011, 16h42

Mêmes paramètres pour l'optimiseur ?
Tracez l'optimiseur via l'evenement 10053 sur les deux bases. Analysez les deux fichiers ainsi obtenu.

**elkamaro** · 16/06/2011, 11h26

bonjour

je me permets de continuer sur ce post car la question fait suite a ce qui a ete dit

Maintenant j'ai deux bases de données
une 10.2.0.4.0 et une 10.2.0.2.0

la même requete donne des temps de reponses extremement différent (de 3secon sur la 10.2.0.2.0 et 16minute sur la 10.2.0.4.0.
J'obtient des full access table encore une fois alors que les index sont bien présents

j'ai reconstruit les index et recalculé les stats mais rien n'y fait

Est ce que cette différence de version peut jouer sur les temps de reponses de mes requêtes ?

Merci de votre aide

**Mohamed.Houri** · 16/06/2011, 12h21

Afin de connaitre les estimations faites par le CBO (Oracle Cost Based Optimizer) vous devriez faire ce qui suit:

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
 
(1) set linesize 150
(2) executer la requête en ajoutant le hint /*+ gather_plan_statistics */
 
SELECT   * 
FROM (
    SELECT /*+ gather_plan_statistics */ 
            VALEUR.ID_VALEUR
            ,REF1.LIBELLE AS L1
            ,REF2.LIBELLE AS L2
            ,REF3.LIBELLE AS L3
            ,PRODUIT.LIBELLE AS L4
            ,PRODUIT.IDENTIFIANT_PRODUIT
            ..../...
(3) select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

(4) poster ici l'explain plan généré à l'étape 3

Une autre test qui vaudra la peine d'être essayé c'est d'alterer la session(10.2.0.4.0) afin de la faire pointer sur le CBO 10.2.0.2.0

Code :

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

1
2
 
alter session set optimizer_features_enable='10.2.0.2.0';

Et de voir si le temps de réponse ne change pas.Vérifiez également que vous n'utilisez pas le mode first_rows.

Bien à vous

Mohamed Houri

**elkamaro** · 16/06/2011, 13h36

Bonjour Mohamed

Pour la deuxieme solution, je l'ai tester mais ca me mets toujours autant de temps et des access full table

pour la premiere solution voila le plan table que j'obtiens en esperant qu il soit 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
43
44
45
46
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                                |                              |      1 |     67 |   5000 |00:12:23.77 |      15M|    342K|       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK            |                              |      1 |     67 |   5001 |00:12:23.72 |      15M|    342K|  5793K|   983K| 5149K (0)|
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| VALEUR                       |      1 |     67 |   1329K|00:06:33.83 |      15M|    342K|       |       |          |
|   4 |     NESTED LOOPS                     |                              |      1 |     67 |     17M|00:11:37.43 |    2433K|  60862 |       |       |          |
|   5 |      NESTED LOOPS                    |                              |      1 |      1 |  71297 |00:10:51.74 |     217K|   2393 |       |       |          |
|   6 |       NESTED LOOPS                   |                              |      1 |      1 |  71297 |00:10:51.24 |     145K|   2393 |       |       |          |
|   7 |        NESTED LOOPS                  |                              |      1 |      1 |  71297 |00:10:50.60 |   74582 |   2390 |       |       |          |
|*  8 |         HASH JOIN SEMI               |                              |      1 |      1 |  71297 |00:10:49.53 |    3283 |   2389 |    10M|  2080K|   12M (0)|
|   9 |          TABLE ACCESS FULL           | SERIE                        |      1 |  89946 |  89946 |00:00:00.09 |    1514 |   1418 |       |       |          |
|  10 |          VIEW                        | VW_SQ_1                      |      1 |   1094K|  71297 |00:10:48.59 |    1769 |    971 |       |       |          |
|* 11 |           HASH JOIN                  |                              |      1 |   1094K|  71297 |00:10:48.51 |    1769 |    971 |  3236K|  1363K| 5740K (0)|
|* 12 |            TABLE ACCESS FULL         | PLUGIN_PERIMETRE_REPLICATION |      1 |  59609 |  59614 |00:00:00.01 |     248 |      0 |       |       |          |
|* 13 |            HASH JOIN                 |                              |      1 |  89946 |  89946 |00:00:00.46 |    1521 |    971 |  1155K|  1155K| 1180K (0)|
|  14 |             TABLE ACCESS FULL        | REFERENTIEL                  |      1 |    263 |    266 |00:00:00.01 |       7 |      0 |       |       |          |
|  15 |             TABLE ACCESS FULL        | SERIE                        |      1 |  89946 |  89946 |00:00:00.27 |    1514 |    971 |       |       |          |
|  16 |         TABLE ACCESS BY INDEX ROWID  | REFERENTIEL                  |  71297 |      1 |  71297 |00:00:00.97 |   71299 |      1 |       |       |          |
|* 17 |          INDEX UNIQUE SCAN           | PK_REFERENTIEL               |  71297 |      1 |  71297 |00:00:00.34 |       2 |      0 |       |       |          |
|  18 |        TABLE ACCESS BY INDEX ROWID   | REFERENTIEL                  |  71297 |      1 |  71297 |00:00:00.51 |   71299 |      3 |       |       |          |
|* 19 |         INDEX UNIQUE SCAN            | PK_REFERENTIEL               |  71297 |      1 |  71297 |00:00:00.24 |       2 |      0 |       |       |          |
|  20 |       TABLE ACCESS BY INDEX ROWID    | REFERENTIEL                  |  71297 |      1 |  71297 |00:00:00.45 |   71299 |      0 |       |       |          |
|* 21 |        INDEX UNIQUE SCAN             | PK_REFERENTIEL               |  71297 |      1 |  71297 |00:00:00.20 |       2 |      0 |       |       |          |
|  22 |      PARTITION HASH ALL              |                              |  71297 |   1056 |     17M|00:00:24.56 |    2216K|  58469 |       |       |          |
|* 23 |       INDEX RANGE SCAN               | IDX_IS_DV_LFV                |    712K|   1056 |     17M|00:00:06.88 |    2216K|  58469 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):                                                                                                   
---------------------------------------------------                                                                                                   
 
   1 - filter("LIGNE"<=:SYS_B_4)                                                                                                                      
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "V"."ID_VALEUR")<=:SYS_B_4)                                                                                
   3 - filter(("V"."ID_VALEUR">:SYS_B_2 AND "V"."DATE_FIN_STATUT" IS NULL))                                                                           
   8 - access("S1"."ID_SERIE"="VW_COL_1" AND "ID_SERIE"="S1"."ID_SERIE")                                                                              
  11 - access("REF4"."LIBELLE"="PREP"."SOURCE")                                                                                                       
       filter("S2"."IDENTIFIANT_SERIE" LIKE "PREP"."SERIE")                                                                                           
  12 - filter("PREP"."APPLICATION"=:SYS_B_3)                                                                                                          
 
PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
  13 - access("S2"."REF_SOURCE"="REF4"."ID_REF")                                                                                                      
  17 - access("S1"."REF_SOURCE"="REF1"."ID_REF")                                                                                                      
  19 - access("S1"."REF_UNITE"="REF2"."ID_REF")                                                                                                       
  21 - access("S1"."REF_DIFFUSEUR"="REF3"."ID_REF")                                                                                                   
  23 - access("V"."ID_SERIE"="S1"."ID_SERIE")