Optimisation de requete car des full access table [Résolu]

[elkamaro]

Bonjour à tous

tout d'abord voici la requete incriminé :

Code :

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 :

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]

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]

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


Merci

[orafrance]

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

[elkamaro]

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]

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]

Envoyé par mnitu

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

Merci mais c un peu pas possible, c est une base infogeré, donc faut que je demande qu on m envoi les fichier trace ect .. ca prend une plomb


par contre des que je remplace la requete qui se trouve dans le in ( ..) deja ca enleve 3 access full)

.. j'ai oublié de préciser a la base je ne suis pas un vrai dba mais j essaye de faire ma place

[elkamaro]

Envoyé par mnitu

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

Encore moi,

je viens d'avoir un accés direct au serveur, voila ce que j'ai fait :

Code :

1
2
SQL>ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SQL>EXPLAIN plan FOR [la requpete la haut]

ensuite je suis allé dans le user_dump_dest
mais rien dans la dernière trace
j ai refait l opération plusieurs fois et toujours rien

[mnitu]

La requête ne doit pas exister dans le shared_pool. Il faut que l'optimiseur fasse un hard parse pour avoir la trace.

[elkamaro]

Très bien, je vais essayer de voir comment faire.

sinon a part cette solution nous n'avons pas possibilité de savoir pourquoi le index ne sont pas utilisées ?

[mnitu]

Envoyé par elkamaro

...
sinon a part cette solution nous n'avons pas possibilité de savoir pourquoi le index ne sont pas utilisées ?

La trace c’est la solution!
Dans votre cas il prend le choix de faire des hash joins à la place de nested loops et dans ce cas il n’utilise pas l’index.

[big1]

Bonjour,

avez-vous trouvé la solution ?
Sinon :
essayer de forcer l'usage d'index :

Code :

1
2
3
4
5
 
SELECT /*+ INDEX (employees emp_department_ix)*/ 
       employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;

Ou :

Code :

1
2
3
4
 
SELECT /*+ NO_USE_HASH(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id

Cordialement.

[elkamaro]

je susi vraiment desolé de vous avoir dérangé

j'ai fait revérifié les éléments, et il y a une différence de base de donnés
moi je suis en 10g, mais coté développeurs , ils sont en 9i

sincérement désolé

[orafrance]

CQFD

[elkamaro]

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]

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

Code :

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 :

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]

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 :

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")

[Mohamed.Houri]

Bonjour,

L'explain plan tiré convenablement de la mémoire n'est pas bien formaté (ou a perdu son formattage)

Avez vous utilisé

Code :

1
2
 
SET linesize 120?

ou même 150?

L'indentation est très importante pour la lecture des explain plan

Bien à vous

Mohamed Houri

[elkamaro]

Merci Waldar j 'étais entrain de le refaire

Je ne suis pas du tout un spécialiste sur la lecture des plans d exécution, merci pour vos aides à venir

[Mohamed.Houri]

(1) première remarque votre cursor_sharing = FORCE ou SIMILAR pourquoi l'avez vous forcé?

(2) il y a manifestement un decallage dans l'interpretation de statistique par le CBO pour la table VALEUR; en effet le CBO estime qu'en accedant à cette table il va selectioner uniquement 67 records alors qu'en réalité il en a sélectioner 1329.000 records

Code :

1
2
 
TABLE ACCESS BY GLOBAL INDEX ROWID| VALEUR | 1 | 67 | 1329K|

Il faudra voir les statistiques à ce niveau de la table.

Par contre pour les autres tables, les statistiques sont parfaites puisque les estimations faites par le CBO (E-Rows) coincident avec les valeurs réelles (A-Rows) lorsque ces dernières sont rapportées au nombre de fois l'opération a été executée (Starts)

Bien à vous

Mohamed Houri