Différence de performance Oracle 10 - Oracle 11

Bonjour

Je viens d'installer un nouveau serveur avec BDD Oracle 11g pour délester un serveur avec Oracle 10G.

C'est deux serveurs (Virtuels) ont les mêmes spécifications hardware.

Mon problème et quel e serveur 11 G est moins performant que mon serveur 10G.....en tout cas pour une requete.

Sur le serveur 10G 12 secondes de Traitement, sur le serveur 11G 1minute et des poussieres......

Cette requete est construite via un document Webi sur un Univers BO XI R2 et voici a quoi elle ressemble

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

SELECT VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.CONSO_COND), MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) FROM VEHICULE, t_AFFECT4, t_AFFECT2, DONNEE_TX, TR_TRA_PERSON, INDIVIDU, ETABSECT, SOCIETE, MARQUE, CATVEHIC, VEHICULE_AFFECTATION, INFSAL WHERE ( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID and VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ and VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ ) AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 ) AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 ) AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID ) AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE ) AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE ) AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE ) AND ( INFSAL.NOBUL(+)=1 and( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) and to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) ) AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR ) AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) ) AND ( INFSAL.SOCIETE=SOCIETE.SOCIETE(+) ) AND ( ( SOCIETE.RAIS = 'GT BRETAGNE' OR T_AFFECT2.FILIALE = 'BRETAGNE' ) AND DONNEE_TX.CONSO_TOT_AVG > 0 AND DONNEE_TX.IND_CONF = '0' AND to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209' ) GROUP BY VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, DONNEE_TX.CONSO_TOT_AVG, DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) HAVING sum(DONNEE_TX.DISTANCE) > 0

---

En cherchant d'ou pouvait provenir le problème j'ai refait la requete à la main en sortant les jointures de mon Where et la miracle 12 secondes pour répondre. Voici la requete, celle ci renvoie les même données.

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

select VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.CONSO_COND), MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) from donnee_tx left join infsal on infsal.anmois=to_number(to_char(datej,'YYYYMM')) and to_char(infsal.individu)=donnee_tx.id_conducteur and nobul=1 and anmois='201209' left join individu on to_char(individu.individu)=donnee_tx.id_conducteur join vehicule_affectation on VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID and VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ and VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ join T_AFFECT4 on VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 join T_AFFECT2 on VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 join vehicule on DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID join TR_TRA_PERSON on DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE join MARQUE on VEHICULE.MARQUE=MARQUE.COD_MARQUE join CATVEHIC on VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE left join SOCIETE on INFSAL.SOCIETE=SOCIETE.SOCIETE left join ETABSECT on INFSAL.SOCIETE=ETABSECT.SOCIETE AND INFSAL.ETAB=ETABSECT.ETAB AND INFSAL.SECTION=ETABSECT.SECTION where ( ( SOCIETE.RAIS = 'GT BRETAGNE' OR T_AFFECT2.FILIALE = 'BRETAGNE' ) AND DONNEE_TX.CONSO_TOT_AVG > 0 AND DONNEE_TX.IND_CONF = '0' AND to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209' ) group by VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, DONNEE_TX.CONSO_TOT_AVG, DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) HAVING sum(DONNEE_TX.DISTANCE) > 0

---

Est ce que ce problème de perf et du a un mauvais paramétrage de mon serveur? à la version de Oracle? Peut etre au fait que la requête n'est pas super optimisé. Mais dans ce cas pourquoi un tel écart.
Je tiens à préciser que certaines des tables sont des synonyms faisant référence a une autre BDD via un Dblink.

Je n'ai fait aucun paramétrage spécifique sur mon serveur 11G car je n'y connais pas grand chose en Oracle.

D'avance merci de votre aide

A votre place moi j'aurai procédé comme suit:

Code:

---

1 2 3

11g> alter session set OPTIMIZER_FEATURES_ENABLE = '10.2.0.4'; -- la version oracle exacte de votre base de données 10g 11g> re-exécutez la même requête sans modification par rapport à celle de 10g

---

Si vous arrivez à retrouver vos 12 secondes, alors vous avez déjà accompli un premier pas vers la solution définitive.

Il faudrait alors dans ce cas continuer en prenant les deux plan d'exécutions (a) celui de 11g sans changement du paramètre OPTIMIZER_FEATURES_ENABLE (b) celui de 11g avec changement de OPTIMIZER_FEATURES_ENABLE = '10.2.0.4'

Code:

---

1 2 3 4 5

11g> alter session set statistics_level=ALL; 11g> alter session set OPTIMIZER_FEATURES_ENABLE = '10.2.0.4'; 11g> exécutez la requête d origine 11g> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---

et

Code:

---

1 2 3 4

11g> alter session set statistics_level=ALL; 11g> exécutez la requête d origine 11g> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---

et postez ici les deux plan d'executions pour analyse des différences

Un post sur otn hier ressemble au votre; on y parle manifestement des synonymes aussi.

Sont vos statistiques à jour sur le nouveau serveur? Le paramétrage des ceux deux serveurs est-il identique ?
D’une manière générale l’optimiseur 11g est bien différente de celui d’Oracle 10g. Et il incorpore des mécanismes évolués de feedback de l’exécution des requêtes qui peuvent induire un changement de du plan d’exécution entre deux exécutions de la même requête.

Citation:

---

Envoyé par mnitu

Sont vos statistiques à jour sur le nouveau serveur?

---

S'il nous donne les deux plans d'exécutions comme demandés avec E-Rows et A-Rows on verra cela très rapidement.

Citation:

---

Le paramétrage des ceux deux serveurs est-il identique ?

---

Cela peut s'avérer très important

Citation:

---

Et il incorpore des mécanismes évolués de feedback de l’exécution des requêtes qui peuvent induire un changement de du plan d’exécution entre deux exécutions de la même requête.

---

Cardinality Feedback ,dont vous faites allusion ici, telle qu'elle est implémentée aujourd'hui, produit plus de problèmes que de solutions

cardinality feedback

Bonjour et merci pour vos réponses ca fait plaisir :)

Alors malgrés la modification

Code:

---

ALTER session SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.1';

---

Le temps est encore plus long 1min30.

qu 'elle sont les paramètres que je peux vérifier sur mes deux serveurs pour trouver une différence?

Voulez vous tout de meme les plan?

Citation:

---

Envoyé par sebgoe

Bonjour et merci pour vos réponses ca fait plaisir :)

Alors malgrés la modification

Code:

---

ALTER session SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.1';

---

Le temps est encore plus long 1min30.

qu 'elle sont les paramètres que je peux vérifier sur mes deux serveurs pour trouver une différence?

Voulez vous tout de meme les plan?

---

Oui on veut bien les plans générés tels que demandés s'il vous plaît

Alors voila les plan par contre je ne sais pas si cela fonctionne car je ne sais pas de quoi je parle.... Un plan .... bon j'ai éxécuter les commande que vous m'avez donné dans l'order et voila le resultat.

Je fais cela sous Toad.

Plan A avec la modification

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

PLAN_TABLE_OUTPUT SQL_ID 7nk8vynfn5gy6, child number 0 ------------------------------------- Select user, table_name from sys.user_snapshots   Plan hash value: 1089358075   --------------------------------------------------------------------- | Id | Operation | Name | E-Rows | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | | | 2 | NESTED LOOPS | | 1 | |* 3 | FILTER | | | | 4 | NESTED LOOPS OUTER | | 1 | | 5 | NESTED LOOPS | | 1 | |* 6 | TABLE ACCESS BY INDEX ROWID| SNAP$ | 1 | |* 7 | INDEX SKIP SCAN | I_SNAP1 | 1 | |* 8 | INDEX UNIQUE SCAN | I_SNAP_REFTIME1 | 1 | |* 9 | INDEX RANGE SCAN | I_RGCHILD | 1 | |* 10 | INDEX UNIQUE SCAN | I_USER1 | 1 | |* 11 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | ---------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter(NVL("R"."INSTSITE",0)=0) 6 - filter(((BITAND("S"."FLAG",268435456)<=0 OR BITAND("S"."OBJFLAG",32)<=0) AND BITAND("S"."FLAG2",33554432)<=0)) 7 - access("S"."INSTSITE"=0) filter("S"."INSTSITE"=0) 8 - access("T"."VNAME"="S"."VNAME" AND "T"."SOWNER"="S"."SOWNER" AND "T"."INSTSITE"=0 AND "T"."TABLENUM"=0) 9 - access("T"."SOWNER"="R"."OWNER" AND "T"."VNAME"="R"."NAME" AND "R"."TYPE#"='SNAPSHOT') filter("R"."TYPE#"='SNAPSHOT') 10 - access("S"."SOWNER"="U"."NAME") 11 - filter("U"."USER#"=USERENV('SCHEMAID'))   Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level

---

Le plan B donne exactement ma ùeùe chose. Peut etre que je m'y prends mal......
J'ai fermé la session Toad entre les deux plan pour etre sur de ne pas etre impacté par la commende

Code:

---

ALTER session SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.1';

---

Citation:

---

Note
-----
- Warning: basic plan statistics NOT available. These are only collected when:
* hint 'gather_plan_statistics' IS used FOR the statement OR
* parameter 'statistics_level' IS SET TO 'ALL', at session OR system level

---

Vous avez oublié ceci

Code:

---

1 2	ALTER session SET statistics_level=ALL;

---

Utilisez Sql*Plus pour cela au lieu de Toad.

Inspirez vous de ceci

En attendant, je me méfierai bien du INDEX SKIP SCAN

Citation:

---

Envoyé par Mohamed.Houri

Cardinality Feedback ,dont vous faites allusion ici, telle qu'elle est implémentée aujourd'hui, produit plus de problèmes que de solutions

---

@Mohammed,
Le problème ici n'est pas de savoir il est bon ou mauvais mais tout simplement qu'il existe dans une version et pas dans une autre. Et surtout qu'il peut déterminer le changement du plan d'exécution de la même requête entre deux exécutions consécutives. Je ne suis pas certain que beaucoup des utilisateurs sont bien conscient de ce fait quand il analyse les requêtes sur 11g.

Effectivement en sql plus cela fonctionne mieux.....

Le resultat apparement c est le la table infsal qui ralentit le tout...

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

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3zxw1wha1y0ty, child number 1 ------------------------------------- SELECT VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,' YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.CONS   Plan hash value: 1167207911   ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1009 |00:01:30.76 | 1378 | 1000 | | | | |* 1 | FILTER | | 1 | | 1009 |00:01:30.76 | 1378 | 1000 | | | | | 2 | HASH GROUP BY | | 1 | 53 | 1009 |00:01:30.75 | 1378 | 1000 | 804K| 804K| 1226K (0)| |* 3 | HASH JOIN OUTER | | 1 | 1049 | 1009 |00:01:30.71 | 1378 | 1000 | 706K| 706K| 1272K (0)| |* 4 | HASH JOIN | | 1 | 23 | 1009 |00:01:30.53 | 1378 | 1000 | 709K| 709K| 918K (0)| |* 5 | HASH JOIN | | 1 | 23 | 1009 |00:01:30.51 | 1365 | 1000 | 855K| 855K| 1220K (0)| | 6 | TABLE ACCESS FULL | T_AFFECT2 | 1 | 37 | 37 |00:00:00.01 | 3 | 0 | | | | |* 7 | HASH JOIN | | 1 | 629 | 4009 |00:01:30.46 | 1362 | 1000 | 1123K| 946K| 1400K (0)| | 8 | NESTED LOOPS OUTER | | 1 | 296 | 4009 |00:01:30.01 | 1236 | 1000 | | | | | 9 | NESTED LOOPS OUTER | | 1 | 296 | 4009 |00:01:23.37 | 1236 | 1000 | | | | |* 10 | HASH JOIN OUTER | | 1 | 296 | 4009 |00:01:16.78 | 1236 | 1000 | 962K| 962K| 1423K (0)| |* 11 | HASH JOIN | | 1 | 296 | 3959 |00:00:00.51 | 1236 | 1000 | 816K| 816K| 1231K (0)| | 12 | TABLE ACCESS FULL | CATVEHIC | 1 | 196 | 196 |00:00:00.01 | 3 | 0 | | | | |* 13 | HASH JOIN | | 1 | 296 | 3959 |00:00:00.46 | 1233 | 1000 | 888K| 888K| 1225K (0)| | 14 | TABLE ACCESS FULL | MARQUE | 1 | 154 | 154 |00:00:00.01 | 3 | 0 | | | | |* 15 | HASH JOIN | | 1 | 296 | 3959 |00:00:00.41 | 1230 | 1000 | 870K| 870K| 1417K (0)| |* 16 | HASH JOIN | | 1 | 337 | 3983 |00:00:00.29 | 1041 | 1000 | 821K| 821K| 1421K (0)| |* 17 | TABLE ACCESS FULL| DONNEE_TX | 1 | 336 | 3953 |00:00:00.22 | 1005 | 1000 | | | | |* 18 | TABLE ACCESS FULL| TR_TRA_PERSON | 1 | 854 | 854 |00:00:00.01 | 36 | 0 | | | | |* 19 | TABLE ACCESS FULL | VEHICULE | 1 | 4116 | 4699 |00:00:00.02 | 189 | 0 | | | | | 20 | REMOTE | INFSAL | 1 | 9578 | 194K|00:01:13.46 | 0 | 0 | | | | | 21 | REMOTE | ETABSECT | 4009 | 1 | 3854 |00:00:06.50 | 0 | 0 | | | | | 22 | REMOTE | SOCIETE | 4009 | 1 | 3854 |00:00:06.57 | 0 | 0 | | | | | 23 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 1 | 28064 | 28064 |00:00:00.09 | 126 | 0 | | | | | 24 | TABLE ACCESS FULL | T_AFFECT4 | 1 | 526 | 526 |00:00:00.01 | 13 | 0 | | | | | 25 | REMOTE | INDIVIDU | 1 | 25491 | 10325 |00:00:00.06 | 0 | 0 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU")) 4 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") filter(("SOCIETE"."RAIS"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE')) 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter(("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ")) 10 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU")) filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN (TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),2),'YYYYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),1),'YYYYMM')) ELSE TO_NUMBER("INFSAL"."ANMOIS") END ) 11 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 13 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 15 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 16 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE") 17 - filter(("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201209' AND "DONNEE_TX"."CONSO_TOT_AVG">0)) 18 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL) 19 - filter(("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL AND "VEHICULE"."COD_CATEGORIE" IS NOT NULL))     76 ligne(s) sélectionnée(s).   SQL> spool off;

---

Et voila le resultat avec la requete qui fonctionne

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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 81t4gt3b23wnf, child number 0 ------------------------------------- select VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.CON   Plan hash value: 1312641855   --------------------------------------------------------------------------------   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ---------------------------------------------------------------   | Id | Operation | Name | Starts | E-Ro ws | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |   -------------------------------------------------------------------------------- ---------------------------------------------------------------   | 0 | SELECT STATEMENT | | 1 | | 1009 |00:00:12.79 | 1378 | | | |     PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 1009 |00:00:12.79 | 1378 | | | |   | 2 | HASH GROUP BY | | 1 | 53 | 1009 |00:00:12.79 | 1378 | 804K| 804K| 1274K (0)|   |* 3 | HASH JOIN | | 1 | 10 49 | 1009 |00:00:12.74 | 1378 | 842K| 842K| 1220K (0)|   | 4 | TABLE ACCESS FULL | T_AFFECT4 | 1 | 5 26 | 526 |00:00:00.01 | 13 | | | |   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------   |* 5 | HASH JOIN | | 1 | 10 49 | 1009 |00:00:12.72 | 1365 | 855K| 855K| 1218K (0)|   | 6 | TABLE ACCESS FULL | T_AFFECT2 | 1 | 37 | 37 |00:00:00.01 | 3 | | | |   |* 7 | HASH JOIN | | 1 | 285 43 | 4009 |00:00:12.67 | 1362 | 1911K| 1055K| 2837K (0)|   | 8 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 1 | 280   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 64 | 28064 |00:00:00.08 | 126 | | | |   |* 9 | HASH JOIN OUTER | | 1 | 134 06 | 4009 |00:00:12.27 | 1236 | 1123K| 946K| 1409K (0)|   |* 10 | HASH JOIN | | 1 | 2 96 | 4009 |00:00:12.03 | 1236 | 816K| 816K| 1228K (0)|   | 11 | TABLE ACCESS FULL | CATVEHIC | 1 | 1 96 | 196 |00:00:00.01 | 3 | | | |     PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 12 | HASH JOIN | | 1 | 2 96 | 4009 |00:00:11.97 | 1233 | 888K| 888K| 1225K (0)|   | 13 | TABLE ACCESS FULL | MARQUE | 1 | 1 54 | 154 |00:00:00.01 | 3 | | | |   |* 14 | HASH JOIN | | 1 | 2 96 | 4009 |00:00:11.92 | 1230 | 962K| 962K| 1409K (0)|   |* 15 | HASH JOIN | | 1 | 3 37 | 4033 |00:00:11.79 | 1041 | 862K| 862K| 1229K (0)|   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------   |* 16 | TABLE ACCESS FULL | TR_TRA_PERSON | 1 | 8 54 | 854 |00:00:00.01 | 36 | | | |   | 17 | NESTED LOOPS OUTER | | 1 | 3 36 | 4003 |00:00:11.71 | 1005 | | | |   | 18 | NESTED LOOPS OUTER | | 1 | 3 36 | 4003 |00:00:06.13 | 1005 | | | |   |* 19 | HASH JOIN RIGHT OUTER| | 1 | 3   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 36 | 4003 |00:00:00.38 | 1005 | 766K| 766K| 1289K (0)|   | 20 | REMOTE | INFSAL | 1 | 96 | 1408 |00:00:00.12 | 0 | | | |   |* 21 | TABLE ACCESS FULL | DONNEE_TX | 1 | 3 36 | 3953 |00:00:00.10 | 1005 | | | |   | 22 | REMOTE | SOCIETE | 4003 | 1 | 3848 |00:00:05.64 | 0 | | | |     PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 23 | REMOTE | ETABSECT | 4003 | 1 | 3848 |00:00:05.51 | 0 | | | |   |* 24 | TABLE ACCESS FULL | VEHICULE | 1 | 41 16 | 4699 |00:00:00.02 | 189 | | | |   | 25 | REMOTE | INDIVIDU | 1 | 254 91 | 10325 |00:00:00.07 | 0 | | | |   -------------------------------------------------------------------------------- ---------------------------------------------------------------   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") filter(("SOCIETE"."RAIS"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE '))     PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter(("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEH ICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ"))   9 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU")) 10 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 12 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 14 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 15 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE") 16 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL) 19 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU") AND   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- TO_NUMBER("ANMOIS")=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATEJ"),' YYYYMM')))   21 - filter(("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_ TX"."DATEJ"),'YYYYMM')='201209' AND   "DONNEE_TX"."CONSO_TOT_AVG">0)) 24 - filter(("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS N OT NULL AND "VEHICULE"."COD_CATEGORIE" IS NOT NULL))       73 ligne(s) sélectionnée(s).   SQL> spool off;

---

D'avance merci de votre aide

J'ai ré-arrangé le deuxième plan pour plus de lisibilité

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

----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1009 |00:00:12.79 | 1378 | | | | -------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 1009 |00:00:12.79 | 1378 | | | | | 2 | HASH GROUP BY | | 1 | 53 | 1009 |00:00:12.79 | 1378 | 804K| 804K| 1274K (0)| |* 3 | HASH JOIN | | 1 | 1049 | 1009 |00:00:12.74 | 1378 | 842K| 842K| 1220K (0)| | 4 | TABLE ACCESS FULL | T_AFFECT4 | 1 | 526 | 526 |00:00:00.01 | 13 | | | | |* 5 | HASH JOIN | | 1 | 1049 | 1009 |00:00:12.72 | 1365 | 855K| 855K| 1218K (0)| | 6 | TABLE ACCESS FULL | T_AFFECT2 | 1 | 37 | 37 |00:00:00.01 | 3 | | | | |* 7 | HASH JOIN | | 1 | 28543 | 4009 |00:00:12.67 | 1362 | 1911K| 1055K| 2837K (0)| | 8 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 1 | 28064 | 28064 |00:00:00.08 | 126 | | | | |* 9 | HASH JOIN OUTER | | 1 | 13406 | 4009 |00:00:12.27 | 1236 | 1123K| 946K| 1409K (0)| |* 10 | HASH JOIN | | 1 | 296 | 4009 |00:00:12.03 | 1236 | 816K| 816K| 1228K (0)| | 11 | TABLE ACCESS FULL | CATVEHIC | 1 | 196 | 196 |00:00:00.01 | 3 | | | | |* 12 | HASH JOIN | | 1 | 296 | 4009 |00:00:11.97 | 1233 | 888K| 888K| 1225K (0)| | 13 | TABLE ACCESS FULL | MARQUE | 1 | 154 | 154 |00:00:00.01 | 3 | | | | |* 14 | HASH JOIN | | 1 | 296 | 4009 |00:00:11.92 | 1230 | 962K| 962K| 1409K (0)| |* 15 | HASH JOIN | | 1 | 337 | 4033 |00:00:11.79 | 1041 | 862K| 862K| 1229K (0)| |* 16 | TABLE ACCESS FULL | TR_TRA_PERSON | 1 | 854 | 854 |00:00:00.01 | 36 | | | | | 17 | NESTED LOOPS OUTER | | 1 | 336 | 4003 |00:00:11.71 | 1005 | | | | | 18 | NESTED LOOPS OUTER | | 1 | 336 | 4003 |00:00:06.13 | 1005 | | | | |* 19 | HASH JOIN RIGHT OUTER| | 1 | 336 | 4003 |00:00:00.38 | 1005 | 766K| 766K| 1289K (0)| | 20 | REMOTE | INFSAL | 1 | 96 | 1408 |00:00: 00.12| 0 | | | | |* 21 | TABLE ACCESS FULL | DONNEE_TX | 1 | 336 | 3953 |00:00: 00.10| 1005 | | | | | 22 | REMOTE | SOCIETE | 4003 | 1 | 3848 |00:00:05.64 | 0 | | | | | 23 | REMOTE | ETABSECT | 4003 | 1 | 3848 |00:00:05.51 | 0 | | | | |* 24 | TABLE ACCESS FULL | VEHICULE | 1 | 4116 | 4699 |00:00:00.02 | 189 | | | | | 25 | REMOTE | INDIVIDU | 1 | 25491 | 10325 |00:00:00.07 | 0 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") filter(("SOCIETE"."RAIS"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE')) 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter(("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ")) 9 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU")) 10 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 12 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 14 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 15 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE") 16 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL) 19 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU") AND TO_NUMBER("ANMOIS")=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATEJ"),'YYYYMM'))) 21 - filter(("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201209' AND "DONNEE_TX"."CONSO_TOT_AVG">0)) 24 - filter(("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL AND "VEHICULE"."COD_CATEGORIE" IS NOT NULL))

---

Effectivement la différence vient de l'accès distant à la table INFSAL

Mauvais plan

Code:

---

1 2 3 4 5

----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 20 | REMOTE | INFSAL | 1 | 9578 | 194K|00:01:13.46 |

---

Bon plan

Code:

---

1 2 3 4 5

----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 20 | REMOTE | INFSAL | 1 | 96 | 1408 |00:00:00:12|

---

Dans le premier cas il génère un volume de 194,000 lignes alors que dans le second cas il n'en rapporte que 1408 lignes. Il se peut que tout le temps perdu dans le premier cas l'a été au niveau de l'échange de cette masse de données entre la base locale et la base distante. Lorsque j'observe attentivement les deux requêtes et plus particulièrement la partie "Predicate" des plans, il me semble que les clauses WHERE concernant la table INFSAL ne sont pas les mêmes

Predicate mauvais plan

Code:

---

1 2 3 4 5 6

10 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU")) filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN (TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),2),'YYYYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),1),'YYYYMM')) ELSE TO_NUMBER("INFSAL"."ANMOIS") END )

---

Predicate bon plan

Code:

---

1 2 3

19 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU") AND TO_NUMBER("ANMOIS")=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DATEJ"),'YYYYMM')))

---

Les deux requêtes même semblent ne pas être totalement identiques surtout vis-à-vis de la table INFSAL. Par exemple, dans la première requête vous avez ceci

Code:

---

1 2	AND ( INFSAL.NOBUL(+) = 1

---

Alors que dans la deuxième vous avez ceci

Code:

---

1 2	AND nobul = 1

---

A votre place j'analyserai encore bien si les deux requêtes sont fonctionnellement équivalentes.

Alors effectivement il y a une différence entre les deux requetes sur celle qui fonctionne bien j'avais rajouter une condiction sur la jointure avec infsal "anmois='201209'".

Ceci explique la difference de perf entre les deux requete sur le meme serveur.

par contre si je prends les deux memes requetes exécutées sur les deux serveurs différents il y a un écart énorme de perf. Donc je vous poste les deux plan d'éxécution de la meme requete sur mes deux serveurs. la seul petite différence entre les deux requetes, il y a une table remote en plus sur celle éxécuté en 11G j'ai eu la flemme de créer la table sur le serveur 11G et je ne pense pas que cela influe beaucoup au vue des chiffres et du nmobre de lignes de cette table.

le premier plan sur le serveur 10 g qui fonctionne bien

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

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4qxh96jxp3y6q, child number 0 ------------------------------------- SELECT VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE_PAND.LIB_SOC, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE   Plan hash value: 2860224624   -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 944 |00:00:07.00 | 11166 | 1574 | | | | | 2 | HASH GROUP BY | | 1 | 2 | 944 |00:00:06.99 | 11166 | 1574 | | | | |* 3 | HASH JOIN OUTER | | 1 | 27 | 944 |00:00:06.97 | 11166 | 1574 | 705K| 705K| 1209K (0)| |* 4 | HASH JOIN | | 1 | 1 | 944 |00:00:06.88 | 11166 | 1574 | 707K| 707K| 1209K (0)| | 5 | NESTED LOOPS | | 1 | 1 | 944 |00:00:06.86 | 11126 | 1535 | | | | | 6 | NESTED LOOPS | | 1 | 1 | 944 |00:00:06.84 | 9238 | 1502 | | | | |* 7 | HASH JOIN | | 1 | 8 | 3864 |00:00:06.77 | 1510 | 1500 | 1068K| 949K| 1320K (0)| |* 8 | HASH JOIN | | 1 | 496 | 3864 |00:00:06.57 | 1341 | 1333 | 816K| 816K| 1156K (0)| | 9 | TABLE ACCESS FULL | CATVEHIC | 1 | 196 | 196 |00:00:00.01 | 24 | 23 | | | | | 10 | NESTED LOOPS OUTER | | 1 | 521 | 3864 |00:00:06.53 | 1317 | 1310 | | | | |* 11 | HASH JOIN RIGHT OUTER | | 1 | 521 | 3864 |00:00:03.42 | 1317 | 1310 | 842K| 842K| 1152K (0)| | 12 | TABLE ACCESS FULL | SOCIETE_PAND | 1 | 26 | 26 |00:00:00.01 | 22 | 20 | | | | |* 13 | HASH JOIN OUTER | | 1 | 521 | 3864 |00:00:03.38 | 1295 | 1290 | 919K| 919K| 1353K (0)| |* 14 | HASH JOIN | | 1 | 521 | 3864 |00:00:00.23 | 1295 | 1290 | 887K| 887K| 1165K (0)| | 15 | TABLE ACCESS FULL | MARQUE | 1 | 154 | 154 |00:00:00.01 | 24 | 23 | | | | |* 16 | HASH JOIN | | 1 | 521 | 3864 |00:00:00.21 | 1271 | 1267 | 820K| 820K| 1353K (0)| |* 17 | TABLE ACCESS FULL | DONNEE_TX | 1 | 554 | 3892 |00:00:00.13 | 1041 | 1039 | | | | |* 18 | TABLE ACCESS FULL | VEHICULE | 1 | 4950 | 4700 |00:00:00.04 | 230 | 228 | | | | | 19 | REMOTE | INFSAL | 1 | 9578 | 194K|00:00:01.78 | 0 | 0 | | | | | 20 | REMOTE | ETABSECT | 3864 | 1 | 3691 |00:00:03.09 | 0 | 0 | | | | | 21 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 1 | 27553 | 28084 |00:00:00.03 | 169 | 167 | | | | |* 22 | TABLE ACCESS BY INDEX ROWID| T_AFFECT2 | 3864 | 1 | 944 |00:00:00.06 | 7728 | 2 | | | | |* 23 | INDEX UNIQUE SCAN | T_AFFECT2_PK | 3864 | 1 | 3864 |00:00:00.03 | 3864 | 1 | | | | | 24 | TABLE ACCESS BY INDEX ROWID | T_AFFECT4 | 944 | 1 | 944 |00:00:00.02 | 1888 | 33 | | | | |* 25 | INDEX UNIQUE SCAN | T_AFFECT4_PK | 944 | 1 | 944 |00:00:00.01 | 944 | 1 | | | | |* 26 | TABLE ACCESS FULL | TR_TRA_PERSON | 1 | 854 | 854 |00:00:00.01 | 40 | 39 | | | | | 27 | REMOTE | INDIVIDU | 1 | 25491 | 10325 |00:00:00.04 | 0 | 0 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU")) 4 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE") 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter(("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ")) 8 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 11 - access("INFSAL"."SOCIETE"="SOCIETE_PAND"."SOCIETE") 13 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU")) filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN (TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),2),'YYYYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),1),'YYYYMM')) ELSE TO_NUMBER("INFSAL"."ANMOIS") END ) 14 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 16 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 17 - filter(("DONNEE_TX"."CONSO_TOT_AVG">0 AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201207' AND "DONNEE_TX"."IND_CONF"='0')) 18 - filter(("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL)) 22 - filter(("SOCIETE_PAND"."LIB_SOC"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE')) 23 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") 25 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 26 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL)     70 ligne(s) sélectionnée(s).   SQL> spool off;

---

Le deuxième sur le nouveau serveur en 11g

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

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4yjrjcsf37d3w, child number 0 ------------------------------------- SELECT VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then URANUS_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE.RAIS, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,' YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'Y YYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.   Plan hash value: 11378205   ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 944 |00:01:03.44 | 1340 | 1335 | | | | |* 1 | FILTER | | 1 | | 944 |00:01:03.44 | 1340 | 1335 | | | | | 2 | HASH GROUP BY | | 1 | 66 | 944 |00:01:03.43 | 1340 | 1335 | 804K| 804K| 1248K (0)| |* 3 | HASH JOIN OUTER | | 1 | 1310 | 944 |00:01:03.40 | 1340 | 1335 | 705K| 705K| 1268K (0)| |* 4 | HASH JOIN | | 1 | 29 | 944 |00:01:03.21 | 1340 | 1335 | 708K| 708K| 802K (0)| |* 5 | HASH JOIN | | 1 | 29 | 944 |00:01:03.17 | 1327 | 1324 | 855K| 855K| 1220K (0)| | 6 | TABLE ACCESS FULL | T_AFFECT2 | 1 | 37 | 37 |00:00:00.01 | 3 | 2 | | | | |* 7 | HASH JOIN | | 1 | 786 | 3886 |00:01:03.09 | 1324 | 1322 | 1064K| 946K| 1408K (0)| | 8 | NESTED LOOPS OUTER | | 1 | 369 | 3886 |00:01:02.68 | 1198 | 1199 | | | | | 9 | NESTED LOOPS OUTER | | 1 | 369 | 3886 |00:00:56.74 | 1198 | 1199 | | | | |* 10 | HASH JOIN OUTER | | 1 | 369 | 3886 |00:00:50.42 | 1198 | 1199 | 962K| 962K| 1423K (0)| |* 11 | HASH JOIN | | 1 | 369 | 3886 |00:00:00.59 | 1198 | 1199 | 816K| 816K| 1231K (0)| | 12 | TABLE ACCESS FULL | CATVEHIC | 1 | 196 | 196 |00:00:00.01 | 3 | 6 | | | | |* 13 | HASH JOIN | | 1 | 369 | 3886 |00:00:00.50 | 1195 | 1193 | 888K| 888K| 1225K (0)| | 14 | TABLE ACCESS FULL | MARQUE | 1 | 154 | 154 |00:00:00.01 | 3 | 6 | | | | |* 15 | HASH JOIN | | 1 | 369 | 3886 |00:00:00.42 | 1192 | 1187 | 870K| 870K| 1417K (0)| |* 16 | HASH JOIN | | 1 | 421 | 3914 |00:00:00.23 | 1003 | 1001 | 821K| 821K| 1420K (0)| |* 17 | TABLE ACCESS FULL| DONNEE_TX | 1 | 336 | 3892 |00:00:00.14 | 1003 | 1001 | | | | | 18 | REMOTE | TR_TRA_PERSON | 1 | 1066 | 1066 |00:00:00.01 | 0 | 0 | | | | |* 19 | TABLE ACCESS FULL | VEHICULE | 1 | 4116 | 4699 |00:00:00.10 | 189 | 186 | | | | | 20 | REMOTE | INFSAL | 1 | 9578 | 194K|00:00:47.21 | 0 | 0 | | | | | 21 | REMOTE | ETABSECT | 3886 | 1 | 3713 |00:00:06.26 | 0 | 0 | | | | | 22 | REMOTE | SOCIETE | 3886 | 1 | 3713 |00:00:05.88 | 0 | 0 | | | | | 23 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 1 | 28064 | 28064 |00:00:00.10 | 126 | 123 | | | | | 24 | TABLE ACCESS FULL | T_AFFECT4 | 1 | 526 | 526 |00:00:00.03 | 13 | 11 | | | | | 25 | REMOTE | INDIVIDU | 1 | 25491 | 10325 |00:00:00.06 | 0 | 0 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU")) 4 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") filter(("SOCIETE"."RAIS"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE')) 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter(("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ")) 10 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU")) filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN (TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),2),'YYYYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS",'YYYYMM'),1),'YYYYMM')) ELSE TO_NUMBER("INFSAL"."ANMOIS") END ) 11 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 13 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 15 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 16 - access("DONNEE_TX"."ID_CONDUCTEUR"="URANUS_TRA_PERSON"."CNR_EXTCODE") 17 - filter(("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201207' AND "DONNEE_TX"."CONSO_TOT_AVG">0)) 19 - filter(("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL AND "VEHICULE"."COD_CATEGORIE" IS NOT NULL))     75 ligne(s) sélectionnée(s).   SQL> spool off;

---

La différence est flagrante. La taille des buffer semble différente entre les deux serveurs est ce que cela ne peut pas venir de la?

D'avance merci pour votre aide.

Pour le même volume des données le temps d’accès à la table INFSAL est très différent. Faite une trace SQL étendue incluant les événements wait.
La table TR_TRA_PERSONNE est locale dans un cas et distante dans l’autre.

Je rejoins mnitu en pointant ceci

10g un peu plus d'une seconde pour générer 194,000 lignes

Code:

---

1 2	| 19 | REMOTE | INFSAL | 1 | 9578 | 194K|00:00:01.78

---

11g un peu plus de 47 secondes pour générer 194,000 lignes

Code:

---

1 2	| 20 | REMOTE | INFSAL | 1 | 9578 | 194K|00:00:47.21 |

---

Comment est fait cet accès à la table INFSAL? Les traces peuvent le montrer. Vous pouvez aussi utiliser le snapper de Tanel Poder pendant l'exécution de la requête.

Merci pour vos réponses a tous les deux.

Voila une trace snapper mais je ne suis pas sur que c'est ce que vous attendez.....

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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156

SQL> @c:\snapper ash 5 20 141; SP2-0734: commande inconnue au début de "-------..." - le reste de la ligne est ignoré.   Procédure PL/SQL terminée avec succès.     aucune ligne sélectionnée     SN --     Sampling SID 141 with interval 5 seconds, taking 20 snapshots...   -- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 40% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 1, end=2012-11-19 15:05:02, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 80% | 1h7twrj64r155 | SQL*Net message from dbli | Network 20% | 1h7twrj64r155 | ON CPU | ON CPU   -- End of ASH snap 2, end=2012-11-19 15:05:07, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 3, end=2012-11-19 15:05:12, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 4, end=2012-11-19 15:05:17, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 5, end=2012-11-19 15:05:22, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 6, end=2012-11-19 15:05:27, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 7, end=2012-11-19 15:05:32, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 8, end=2012-11-19 15:05:37, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 9, end=2012-11-19 15:05:42, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 10, end=2012-11-19 15:05:47, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 11, end=2012-11-19 15:05:52, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 60% | 1h7twrj64r155 | SQL*Net message from dbli | Network 20% | 1h7twrj64r155 | SQL*Net more data from db | Network 20% | 1h7twrj64r155 | ON CPU | ON CPU   -- End of ASH snap 12, end=2012-11-19 15:05:57, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 60% | 1h7twrj64r155 | SQL*Net message from dbli | Network 40% | 1h7twrj64r155 | ON CPU | ON CPU   -- End of ASH snap 13, end=2012-11-19 15:06:02, seconds=5, samples_taken=5     ----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 100% | 1h7twrj64r155 | SQL*Net message from dbli | Network   -- End of ASH snap 14, end=2012-11-19 15:06:07, seconds=5, samples_taken=5   <No active sessions captured during the sampling period>   -- End of ASH snap 15, end=2012-11-19 15:06:12, seconds=5, samples_taken=5   <No active sessions captured during the sampling period>   -- End of ASH snap 16, end=2012-11-19 15:06:17, seconds=5, samples_taken=5   <No active sessions captured during the sampling period>   -- End of ASH snap 17, end=2012-11-19 15:06:22, seconds=5, samples_taken=5   <No active sessions captured during the sampling period>   -- End of ASH snap 18, end=2012-11-19 15:06:27, seconds=5, samples_taken=5   <No active sessions captured during the sampling period>   -- End of ASH snap 19, end=2012-11-19 15:06:32, seconds=5, samples_taken=5 <No active sessions captured during the sampling period> -- End of ASH snap 20, end=2012-11-19 15:06:37, seconds=5, samples_taken=5   Procédure PL/SQL terminée avec succès.

---

La table Infsal est un synonym crée via un DBLINK.

Est-ce qu'il y a une différence importante dans l'exécution de la requête suivante sur les deux serveurs ?

Code:

---

1 2 3

Select t.indivu, t.anmois from infsal t

---

Exécutez-la 2 ou 3 fois sous sqlplus avec set autotrace traceonly (pour éviter qu'elle affiche les données)

Alors voici les traces sur mes deux serveurs

D'abord le serveur en 11G ou j'ai la lenteur. mais j'obtiens une erreur.

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

SQL> select t.individu,t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ---------------------------------------------------------- Error: cannot fetch plan for statement_id 'PLUS77884'     Statistiques ---------------------------------------------------------- 103 recursive calls 1 db block gets 19 consistent gets 4 physical reads 256 redo size 4580038 bytes sent via SQL*Net to client 157617 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> select t.individu,t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ---------------------------------------------------------- Error: cannot fetch plan for statement_id 'PLUS77884'     Statistiques ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 4580038 bytes sent via SQL*Net to client 157617 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> select t.individu,t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ---------------------------------------------------------- Error: cannot fetch plan for statement_id 'PLUS77884'     Statistiques ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 4580038 bytes sent via SQL*Net to client 157617 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> spool off;

---

Puis le vieux serveur en 10G

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

SQL> select t.individu, t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ----------------------------------------------------------   -------------------------------------------------- | Id | Operation | Name | Inst | -------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | | | 1 | TABLE ACCESS FULL | INFSAL | PAND | --------------------------------------------------   Note ----- - 'PLAN_TABLE' is old version - fully remote statement - rule based optimizer used (consider using cbo)     Statistiques ---------------------------------------------------------- 468 recursive calls 2 db block gets 76 consistent gets 7 physical reads 408 redo size 4580023 bytes sent via SQL*Net to client 157582 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> select t.individu, t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ----------------------------------------------------------   -------------------------------------------------- | Id | Operation | Name | Inst | -------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | | | 1 | TABLE ACCESS FULL | INFSAL | PAND | --------------------------------------------------   Note ----- - 'PLAN_TABLE' is old version - fully remote statement - rule based optimizer used (consider using cbo)     Statistiques ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 4580023 bytes sent via SQL*Net to client 157582 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> select t.individu, t.anmois from infsal t;   214371 ligne(s) sélectionnée(s).     Plan d'exécution ----------------------------------------------------------   -------------------------------------------------- | Id | Operation | Name | Inst | -------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | | | 1 | TABLE ACCESS FULL | INFSAL | PAND | --------------------------------------------------   Note ----- - 'PLAN_TABLE' is old version - fully remote statement - rule based optimizer used (consider using cbo)     Statistiques ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 4580023 bytes sent via SQL*Net to client 157582 bytes received via SQL*Net from client 14293 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 214371 rows processed   SQL> spool off

---

Par contre je n'ai pas l'impression qu'il y ai grande différence au niveau temps dans la réponse.

Je poste aussi les traces de la requete sur les deux serveurs. J'ai maintenant exactement les meme tables distantes des deux cotés les requetes sont donc les memes. Par contre les données du 10 G sont plus a jours que sur le 11G(Plus de lignes sur le 10 dans certaines tables mais pour le mois requeté le nombre de lignes renvoyées est le même).

Le serveur 11 G

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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180

SQL> SELECT 2 VEHICULE.PARC, 3 T_AFFECT4.CLIENT, 4 T_AFFECT2.FILIALE, 5 sum(DONNEE_TX.DISTANCE), 6 DONNEE_TX.CONSO_TOT_AVG, 7 sum(DONNEE_TX.CONSO_TOTAL), 8 DONNEE_TX.DATEJ, 9 to_char(DONNEE_TX.DATEJ,'WW'), 10 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), 11 ETABSECT.LIB, 12 SOCIETE_PAND.LIB_SOC, 13 DONNEE_TX.ID_CONDUCTEUR, 14 DONNEE_TX.SPEED_AVG, 15 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, 16 sum(DONNEE_TX.CONSO_IDLE), 17 sum(DONNEE_TX.CONSO_COND), 18 MARQUE.LIBELLE, 19 CATVEHIC.LIBELLE, 20 decode(INDIVIDU.SEXE,'F','Femme','Homme'), 21 to_char(DONNEE_TX.DATEJ,'YYYYMM'), 22 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) 23 FROM 24 VEHICULE, 25 t_AFFECT4, 26 t_AFFECT2, 27 DONNEE_TX, 28 TR_TRA_PERSON, 29 INDIVIDU, 30 ETABSECT, 31 SOCIETE_PAND, 32 MARQUE, 33 CATVEHIC, 34 VEHICULE_AFFECTATION, 35 INFSAL 36 WHERE 37 ( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID AND VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ AND VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ ) 38 AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 ) 39 AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 ) 40 AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID ) 41 AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE ) 42 AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE ) 43 AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE ) 44 AND ( INFSAL.NOBUL(+)=1 AND( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) AND to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) ) 45 AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR ) 46 AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) ) 47 AND ( INFSAL.SOCIETE=SOCIETE_PAND.SOCIETE(+) ) 48 AND 49 ( 50 ( 51 SOCIETE_PAND.LIB_SOC = 'GT BRETAGNE' 52 OR 53 T_AFFECT2.FILIALE = 'BRETAGNE' 54 ) 55 AND 56 DONNEE_TX.CONSO_TOT_AVG > 0 57 AND 58 DONNEE_TX.IND_CONF = '0' 59 AND 60 to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209' 61 ) 62 GROUP BY 63 VEHICULE.PARC, 64 T_AFFECT4.CLIENT, 65 T_AFFECT2.FILIALE, 66 DONNEE_TX.CONSO_TOT_AVG, 67 DONNEE_TX.DATEJ, 68 to_char(DONNEE_TX.DATEJ,'WW'), 69 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), 70 ETABSECT.LIB, 71 SOCIETE_PAND.LIB_SOC, 72 DONNEE_TX.ID_CONDUCTEUR, 73 DONNEE_TX.SPEED_AVG, 74 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, 75 MARQUE.LIBELLE, 76 CATVEHIC.LIBELLE, 77 decode(INDIVIDU.SEXE,'F','Femme','Homme'), 78 to_char(DONNEE_TX.DATEJ,'YYYYMM'), 79 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) 80 HAVING 81 sum(DONNEE_TX.DISTANCE) > 0;   1009 ligne(s) sélectionnée(s).     Plan d'exécution ---------------------------------------------------------- Plan hash value: 487837382   ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 53 | 30210 | 4124 (2)| 00:00:50 | | | |* 1 | FILTER | | | | | | | | | 2 | HASH GROUP BY | | 53 | 30210 | 4124 (2)| 00:00:50 | | | |* 3 | HASH JOIN OUTER | | 1049 | 583K| 4123 (2)| 00:00:50 | | | |* 4 | HASH JOIN | | 23 | 12236 | 4025 (2)| 00:00:49 | | | |* 5 | HASH JOIN | | 23 | 11822 | 4020 (2)| 00:00:49 | | | | 6 | TABLE ACCESS FULL | T_AFFECT2 | 37 | 592 | 3 (0)| 00:00:01 | | | |* 7 | HASH JOIN | | 629 | 305K| 4016 (2)| 00:00:49 | | | | 8 | NESTED LOOPS OUTER | | 296 | 136K| 3978 (2)| 00:00:48 | | | |* 9 | HASH JOIN RIGHT OUTER | | 296 | 126K| 3976 (2)| 00:00:48 | | | | 10 | TABLE ACCESS FULL | SOCIETE_PAND | 26 | 7254 | 3 (0)| 00:00:01 | | | |* 11 | HASH JOIN OUTER | | 296 | 46768 | 3973 (2)| 00:00:48 | | | |* 12 | HASH JOIN | | 296 | 32264 | 350 (2)| 00:00:05 | | | | 13 | TABLE ACCESS FULL | CATVEHIC | 196 | 4704 | 3 (0)| 00:00:01 | | | |* 14 | HASH JOIN | | 296 | 25160 | 346 (2)| 00:00:05 | | | | 15 | TABLE ACCESS FULL | MARQUE | 154 | 1694 | 3 (0)| 00:00:01 | | | |* 16 | HASH JOIN | | 296 | 21904 | 343 (2)| 00:00:05 | | | |* 17 | HASH JOIN | | 337 | 19546 | 287 (2)| 00:00:04 | | | |* 18 | TABLE ACCESS FULL| DONNEE_TX | 336 | 14784 | 274 (1)| 00:00:04 | | | |* 19 | TABLE ACCESS FULL| TR_TRA_PERSON | 854 | 11956 | 12 (0)| 00:00:01 | | | |* 20 | TABLE ACCESS FULL | VEHICULE | 4116 | 65856 | 56 (2)| 00:00:01 | | | | 21 | REMOTE | INFSAL | 9578 | 458K| 3622 (2)| 00:00:44 | MERCU~ | R->S | | 22 | REMOTE | ETABSECT | 1 | 35 | 1 (0)| 00:00:01 | MERCU~ | R->S | | 23 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 28064 | 712K| 38 (0)| 00:00:01 | | | | 24 | TABLE ACCESS FULL | T_AFFECT4 | 526 | 9468 | 5 (0)| 00:00:01 | | | | 25 | REMOTE | INDIVIDU | 25491 | 945K| 98 (2)| 00:00:02 | MERCU~ | R->S | -----------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(SUM("DONNEE_TX"."DISTANCE")>0) 3 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU"(+))) 4 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4") 5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2") filter("SOCIETE_PAND"."LIB_SOC"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE') 7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID") filter("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND "VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ") 9 - access("INFSAL"."SOCIETE"="SOCIETE_PAND"."SOCIETE"(+)) 11 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU"(+))) filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN (TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS"(+),'YYYYMM'),2),'YY YYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS"(+),'YYYYMM'),1),'YYYYMM')) ELSE TO_NUMBER("INFSAL"."ANMOIS"(+)) END ) 12 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE") 14 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE") 16 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID") 17 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE") 18 - filter("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201209' AND "DONNEE_TX"."CONSO_TOT_AVG">0) 19 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL) 20 - filter("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL AND "VEHICULE"."COD_CATEGORIE" IS NOT NULL)   Remote SQL Information (identified by operation id): ----------------------------------------------------   21 - SELECT "ANMOIS","NOBUL","SOCIETE","INDIVIDU","ETAB","SECTION" FROM "PANDORE"."INFSAL" "INFSAL" WHERE "NOBUL"=1 (accessing 'MERCURE.GT' )   22 - SELECT "SOCIETE","ETAB","SECTION","LIB" FROM "PANDORE"."ETABSECT" "ETABSECT" WHERE :1="SOCIETE" AND :2="ETAB" AND :3="SECTION" (accessing 'MERCURE.GT' )   25 - SELECT "INDIVIDU","NOM","SEXE","DNAI" FROM "PANDORE"."INDIVIDU" "INDIVIDU" (accessing 'MERCURE.GT' )     Note ----- - dynamic sampling used for this statement (level=2)     Statistiques ---------------------------------------------------------- 4 recursive calls 1 db block gets 1393 consistent gets 1000 physical reads 256 redo size 86937 bytes sent via SQL*Net to client 1153 bytes received via SQL*Net from client 69 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1009 rows processed   SQL> spool off;

---

Le serveur 10G

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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154

SQL> SELECT 2 VEHICULE.PARC, 3 T_AFFECT4.CLIENT, 4 T_AFFECT2.FILIALE, 5 sum(DONNEE_TX.DISTANCE), 6 DONNEE_TX.CONSO_TOT_AVG, 7 sum(DONNEE_TX.CONSO_TOTAL), 8 DONNEE_TX.DATEJ, 9 to_char(DONNEE_TX.DATEJ,'WW'), 10 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), 11 ETABSECT.LIB, 12 SOCIETE_PAND.LIB_SOC, 13 DONNEE_TX.ID_CONDUCTEUR, 14 DONNEE_TX.SPEED_AVG, 15 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, 16 sum(DONNEE_TX.CONSO_IDLE), 17 sum(DONNEE_TX.CONSO_COND), 18 MARQUE.LIBELLE, 19 CATVEHIC.LIBELLE, 20 decode(INDIVIDU.SEXE,'F','Femme','Homme'), 21 to_char(DONNEE_TX.DATEJ,'YYYYMM'), 22 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) 23 FROM 24 VEHICULE, 25 t_AFFECT4, 26 t_AFFECT2, 27 DONNEE_TX, 28 TR_TRA_PERSON, 29 INDIVIDU, 30 ETABSECT, 31 SOCIETE_PAND, 32 MARQUE, 33 CATVEHIC, 34 VEHICULE_AFFECTATION, 35 INFSAL 36 WHERE 37 ( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID AND VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ AND VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ ) 38 AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 ) 39 AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 ) 40 AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID ) 41 AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE ) 42 AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE ) 43 AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE ) 44 AND ( INFSAL.NOBUL(+)=1 AND( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) AND to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) ) 45 AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR ) 46 AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) ) 47 AND ( INFSAL.SOCIETE=SOCIETE_PAND.SOCIETE(+) ) 48 AND 49 ( 50 ( 51 SOCIETE_PAND.LIB_SOC = 'GT BRETAGNE' 52 OR 53 T_AFFECT2.FILIALE = 'BRETAGNE' 54 ) 55 AND 56 DONNEE_TX.CONSO_TOT_AVG > 0 57 AND 58 DONNEE_TX.IND_CONF = '0' 59 AND 60 to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209' 61 ) 62 GROUP BY 63 VEHICULE.PARC, 64 T_AFFECT4.CLIENT, 65 T_AFFECT2.FILIALE, 66 DONNEE_TX.CONSO_TOT_AVG, 67 DONNEE_TX.DATEJ, 68 to_char(DONNEE_TX.DATEJ,'WW'), 69 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME), 70 ETABSECT.LIB, 71 SOCIETE_PAND.LIB_SOC, 72 DONNEE_TX.ID_CONDUCTEUR, 73 DONNEE_TX.SPEED_AVG, 74 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 … 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 … 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, 75 MARQUE.LIBELLE, 76 CATVEHIC.LIBELLE, 77 decode(INDIVIDU.SEXE,'F','Femme','Homme'), 78 to_char(DONNEE_TX.DATEJ,'YYYYMM'), 79 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) 80 HAVING 81 sum(DONNEE_TX.DISTANCE) > 0;   1009 ligne(s) sélectionnée(s).     Plan d'exécution ----------------------------------------------------------   ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Inst |IN-OUT| ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 602 | 4189 | | | | 1 | FILTER | | | | | | | | 2 | HASH GROUP BY | | 2 | 602 | 4189 | | | | 3 | HASH JOIN OUTER | | 27 | 8127 | 4188 | | | | 4 | HASH JOIN | | 1 | 263 | 4088 | | | | 5 | NESTED LOOPS | | 1 | 249 | 4076 | | | | 6 | NESTED LOOPS | | 1 | 231 | 4075 | | | | 7 | HASH JOIN | | 8 | 1720 | 4074 | | | | 8 | HASH JOIN | | 496 | 93744 | 4036 | | | | 9 | TABLE ACCESS FULL | CATVEHIC | 196 | 4312 | 8 | | | | 10 | NESTED LOOPS OUTER | | 521 | 87007 | 4029 | | | | 11 | HASH JOIN RIGHT OUTER | | 521 | 68772 | 4026 | | | | 12 | TABLE ACCESS FULL | SOCIETE_PAND | 26 | 442 | 6 | | | | 13 | HASH JOIN OUTER | | 521 | 59915 | 4020 | | | | 14 | HASH JOIN | | 521 | 34386 | 273 | | | | 15 | TABLE ACCESS FULL | MARQUE | 154 | 1540 | 8 | | | | 16 | HASH JOIN | | 521 | 29176 | 265 | | | | 17 | TABLE ACCESS FULL | DONNEE_TX | 554 | 24376 | 212 | | | | 18 | TABLE ACCESS FULL | VEHICULE | 4950 | 59400 | 52 | | | | 19 | REMOTE | INFSAL | 9578 | 458K| 3746 | MERCU~ | R->S | | 20 | REMOTE | ETABSECT | 1 | 35 | 1 | MERCU~ | R->S | | 21 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 27553 | 699K| 37 | | | | 22 | TABLE ACCESS BY INDEX ROWID| T_AFFECT2 | 1 | 16 | 1 | | | | 23 | INDEX UNIQUE SCAN | T_AFFECT2_PK | 1 | | 0 | | | | 24 | TABLE ACCESS BY INDEX ROWID | T_AFFECT4 | 1 | 18 | 1 | | | | 25 | INDEX UNIQUE SCAN | T_AFFECT4_PK | 1 | | 0 | | | | 26 | TABLE ACCESS FULL | TR_TRA_PERSON | 854 | 11956 | 11 | | | | 27 | REMOTE | INDIVIDU | 25491 | 945K| 99 | MERCU~ | R->S | ----------------------------------------------------------------------------------------------------------   Remote SQL Information (identified by operation id): ----------------------------------------------------   19 - SELECT "ANMOIS","NOBUL","SOCIETE","INDIVIDU","ETAB","SECTION" FROM "PANDORE"."INFSAL" "INFSAL" WHERE "NOBUL"=1 (accessing 'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM' )   20 - SELECT "SOCIETE","ETAB","SECTION","LIB" FROM "PANDORE"."ETABSECT" "ETABSECT" WHERE :1="SOCIETE" AND :2="ETAB" AND :3="SECTION" (accessing 'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM' )   27 - SELECT "INDIVIDU","NOM","SEXE","DNAI" FROM "PANDORE"."INDIVIDU" "INDIVIDU" (accessing 'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM' )     Note ----- - 'PLAN_TABLE' is old version     Statistiques ---------------------------------------------------------- 1245 recursive calls 0 db block gets 11871 consistent gets 1521 physical reads 0 redo size 86916 bytes sent via SQL*Net to client 3058 bytes received via SQL*Net from client 69 SQL*Net roundtrips to/from client 62 sorts (memory) 0 sorts (disk) 1009 rows processed   SQL> spool off;

---

Encore merci de votre aide

Je résume

1. La différence de temps d'exécution provient de l'accès à la table INFSAL qui est de 1 seconde dans le serveur 10g alors qu'elle est de 47 secondes dans le serveur 11g

2. le snapper de tanel poder montre que la majeure partie du temps de la requête est consommée dans le rapatriement des données à partir du db link

Code:

---

1 2 3 4 5 6 7

----------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS ----------------------------------------------------------------------- 60% | 1h7twrj64r155 | SQL*Net message FROM dbli | Network 20% | 1h7twrj64r155 | SQL*Net more DATA FROM db | Network 20% | 1h7twrj64r155 | ON CPU | ON CPU

---

3. dans le serveur 11g le dynamic sampling a été utilisé indiquant une absence de statistique adéquate quelque part

Code:

---

1 2	- dynamic sampling used for this statement (level=2)

---

4. Enfin nous n'avons aucune indication sur la manière dont est accédée la table INFSAL dans la base distance. Si elle l'est via un FULL TABLE SCAN l'explication la plus plausible est que dans le serveur 10g ce FULL TABLE SCAN est fait via un "db file scattered read" alors que dans le serveur 11g il est fait via un "Direct path read" qui délaisse le passage par la mémoire (buffer cache) et procède directement à une lecture directe du disque (physical read). Afin de vérifier cela il faudra faire dérouler le select sur la table infsal dans sa base locale avec les mêmes clauses where (ou en s'en approchant) une fois dans le serveur 10g et une fois dans le serveur 11g et voir avec quelle manière se font ses probables FULL TABLE SCAN. Utilisez pour cela soit l'event 10046 soit plus simplement le snapper de tanel poder

@Mohamed

Pouvez vous m'indiquer comment uiliser snapper pour tracer l'event en question?

Je cherche mais sans grand resultat.

D'avance merci je continue a chercher :)

En espérant que ce qui suit est ce que vous attendiez....

La trace 10046 sur le serveur ou la table infsal est local

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 115 116 117 118 119 120 121 122

Dump file d:\oracle\oradata\trace\pand\pand_ora_2568_trace_infsal.trc Mon Nov 19 21:41:27 2012 ORACLE V9.2.0.4.0 - Production vsnsta=0 vsnsql=12 vsnxtr=3 Windows 2000 Version 5.2 Service Pack 2, CPU type 586 Oracle9i Release 9.2.0.4.0 - Production JServer Release 9.2.0.4.0 - Production Windows 2000 Version 5.2 Service Pack 2, CPU type 586 Instance name: pand   Redo thread mounted by this instance: 1   Oracle process number: 10   Windows thread id: 2568, image: ORACLE.EXE   ===================== PARSING IN CURSOR #1 len=85 dep=0 uid=33 oct=3 lid=33 tim=18446744070666057146 hv=1690126110 ad='1fbfa574' select individu,societe,etab,section,nobul from infsal where nobul=1 and societe='17' END OF STMT PARSE #1:c=15625,e=1829,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=3,tim=18446744070666057142 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 730 p1=1413697536 p2=1 p3=0 ===================== PARSING IN CURSOR #1 len=85 dep=0 uid=33 oct=3 lid=33 tim=18446744070666058052 hv=1690126110 ad='1fbfa574' select individu,societe,etab,section,nobul from infsal where nobul=1 and societe='17' END OF STMT PARSE #1:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=18446744070666058049 EXEC #1:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=18446744070666058142 WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #1: nam='db file sequential read' ela= 7046 p1=11 p2=1044 p3=1 WAIT #1: nam='db file sequential read' ela= 2920 p1=11 p2=2359 p3=1 WAIT #1: nam='db file sequential read' ela= 5035 p1=11 p2=18756 p3=1 WAIT #1: nam='db file sequential read' ela= 5105 p1=12 p2=43905 p3=1 WAIT #1: nam='db file sequential read' ela= 3743 p1=12 p2=43897 p3=1 WAIT #1: nam='db file sequential read' ela= 5965 p1=11 p2=12848 p3=1 WAIT #1: nam='db file sequential read' ela= 5389 p1=12 p2=50879 p3=1 WAIT #1: nam='db file sequential read' ela= 4515 p1=12 p2=50863 p3=1 WAIT #1: nam='db file sequential read' ela= 6163 p1=12 p2=58584 p3=1 WAIT #1: nam='db file sequential read' ela= 1455 p1=12 p2=58568 p3=1 WAIT #1: nam='db file sequential read' ela= 4469 p1=12 p2=58552 p3=1 WAIT #1: nam='db file sequential read' ela= 4445 p1=12 p2=74536 p3=1 WAIT #1: nam='db file sequential read' ela= 2667 p1=12 p2=74743 p3=1 WAIT #1: nam='db file sequential read' ela= 3760 p1=12 p2=102838 p3=1 WAIT #1: nam='db file sequential read' ela= 1474 p1=12 p2=103165 p3=1 WAIT #1: nam='db file sequential read' ela= 2094 p1=12 p2=103142 p3=1 WAIT #1: nam='db file sequential read' ela= 4878 p1=12 p2=103316 p3=1 WAIT #1: nam='db file sequential read' ela= 4631 p1=12 p2=103414 p3=1 WAIT #1: nam='db file sequential read' ela= 4986 p1=12 p2=43913 p3=1 WAIT #1: nam='db file sequential read' ela= 763 p1=12 p2=43909 p3=1 WAIT #1: nam='db file sequential read' ela= 840 p1=11 p2=18760 p3=1 WAIT #1: nam='db file sequential read' ela= 5072 p1=12 p2=50927 p3=1 WAIT #1: nam='db file sequential read' ela= 2370 p1=12 p2=58600 p3=1 WAIT #1: nam='db file sequential read' ela= 7644 p1=11 p2=37471 p3=1 WAIT #1: nam='db file sequential read' ela= 4457 p1=12 p2=75239 p3=1 WAIT #1: nam='db file sequential read' ela= 2588 p1=12 p2=74552 p3=1 WAIT #1: nam='db file sequential read' ela= 3557 p1=12 p2=74640 p3=1 WAIT #1: nam='db file sequential read' ela= 8715 p1=12 p2=58632 p3=1 WAIT #1: nam='db file sequential read' ela= 3915 p1=12 p2=74568 p3=1 WAIT #1: nam='db file sequential read' ela= 5065 p1=12 p2=103158 p3=1 WAIT #1: nam='db file sequential read' ela= 3997 p1=11 p2=2559 p3=1 WAIT #1: nam='db file sequential read' ela= 2352 p1=12 p2=49457 p3=1 WAIT #1: nam='db file sequential read' ela= 3882 p1=12 p2=30660 p3=1 WAIT #1: nam='db file sequential read' ela= 1849 p1=12 p2=30644 p3=1 WAIT #1: nam='db file sequential read' ela= 9681 p1=12 p2=50959 p3=1 WAIT #1: nam='db file sequential read' ela= 3848 p1=12 p2=58425 p3=1 WAIT #1: nam='db file sequential read' ela= 1960 p1=12 p2=58409 p3=1 WAIT #1: nam='db file sequential read' ela= 7148 p1=12 p2=74600 p3=1 WAIT #1: nam='db file sequential read' ela= 1036 p1=12 p2=103430 p3=1 WAIT #1: nam='db file sequential read' ela= 1810 p1=12 p2=30652 p3=1 WAIT #1: nam='db file sequential read' ela= 748 p1=12 p2=30648 p3=1 WAIT #1: nam='db file sequential read' ela= 4084 p1=11 p2=11496 p3=1 WAIT #1: nam='db file sequential read' ela= 4320 p1=12 p2=50720 p3=1 WAIT #1: nam='db file sequential read' ela= 2799 p1=12 p2=58457 p3=1 WAIT #1: nam='db file sequential read' ela= 4169 p1=12 p2=58441 p3=1 WAIT #1: nam='db file sequential read' ela= 6593 p1=12 p2=4468 p3=1 WAIT #1: nam='db file sequential read' ela= 836 p1=11 p2=12852 p3=1 WAIT #1: nam='db file sequential read' ela= 4843 p1=12 p2=4476 p3=1 WAIT #1: nam='db file sequential read' ela= 3759 p1=12 p2=4472 p3=1 WAIT #1: nam='db file sequential read' ela= 1818 p1=12 p2=10728 p3=1 WAIT #1: nam='db file sequential read' ela= 610 p1=12 p2=4480 p3=1 WAIT #1: nam='db file sequential read' ela= 682 p1=11 p2=12856 p3=1 WAIT #1: nam='db file sequential read' ela= 695 p1=12 p2=4484 p3=1 WAIT #1: nam='SQL*Net more data to client' ela= 74 p1=1413697536 p2=2041 p3=0 WAIT #1: nam='db file sequential read' ela= 4045 p1=12 p2=4492 p3=1 WAIT #1: nam='db file sequential read' ela= 713 p1=11 p2=12860 p3=1 WAIT #1: nam='db file sequential read' ela= 4269 p1=12 p2=17501 p3=1 WAIT #1: nam='db file sequential read' ela= 8291 p1=12 p2=3719 p3=1 WAIT #1: nam='db file sequential read' ela= 1893 p1=12 p2=3691 p3=1 WAIT #1: nam='db file sequential read' ela= 841 p1=12 p2=3723 p3=1 WAIT #1: nam='db file sequential read' ela= 2694 p1=12 p2=3653 p3=1 WAIT #1: nam='db file sequential read' ela= 4498 p1=12 p2=25472 p3=1 WAIT #1: nam='db file sequential read' ela= 3036 p1=12 p2=25468 p3=1 WAIT #1: nam='db file sequential read' ela= 4226 p1=12 p2=34551 p3=1 WAIT #1: nam='db file sequential read' ela= 1337 p1=12 p2=51404 p3=1 WAIT #1: nam='db file sequential read' ela= 7261 p1=12 p2=51388 p3=1 WAIT #1: nam='db file sequential read' ela= 12890 p1=11 p2=12864 p3=1 WAIT #1: nam='db file sequential read' ela= 6700 p1=12 p2=4473 p3=1 WAIT #1: nam='db file sequential read' ela= 717 p1=12 p2=4469 p3=1 WAIT #1: nam='db file sequential read' ela= 677 p1=12 p2=10732 p3=1 WAIT #1: nam='db file sequential read' ela= 859 p1=12 p2=10731 p3=1 WAIT #1: nam='db file sequential read' ela= 718 p1=12 p2=17509 p3=1 WAIT #1: nam='db file sequential read' ela= 862 p1=12 p2=3696 p3=1 WAIT #1: nam='db file sequential read' ela= 2380 p1=12 p2=3712 p3=1 WAIT #1: nam='db file sequential read' ela= 5349 p1=12 p2=3687 p3=1 WAIT #1: nam='db file sequential read' ela= 1955 p1=12 p2=3610 p3=1 WAIT #1: nam='db file sequential read' ela= 716 p1=12 p2=25476 p3=1 WAIT #1: nam='db file sequential read' ela= 1349 p1=11 p2=11500 p3=1 WAIT #1: nam='db file sequential read' ela= 1685 p1=12 p2=34555 p3=1 WAIT #1: nam='db file sequential read' ela= 2683 p1=12 p2=74755 p3=1 WAIT #1: nam='db file sequential read' ela= 5519 p1=12 p2=58489 p3=1 FETCH #1:c=15625,e=302242,p=80,cr=169,cu=0,mis=0,r=501,dep=0,og=3,tim=18446744070666360426 *** 2012-11-19 21:45:01.266 WAIT #1: nam='SQL*Net message from client' ela= 39451364 p1=1413697536 p2=1 p3=0 STAT #1 id=1 cnt=501 pid=0 pos=1 obj=29291 op='TABLE ACCESS BY INDEX ROWID INFSAL (cr=169 r=80 w=0 time=301513 us)' STAT #1 id=2 cnt=552 pid=1 pos=1 obj=29299 op='INDEX RANGE SCAN INFSAL_IDX (cr=13 r=13 w=0 time=64125 us)' ===================== PARSING IN CURSOR #1 len=55 dep=0 uid=33 oct=42 lid=33 tim=18446744070705812779 hv=4110456808 ad='1f22de98' alter session set events '10046 trace name context off' END OF STMT PARSE #1:c=0,e=208,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=18446744070705812775 EXEC #1:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=18446744070705812941

---

Ensuite sur le serveur 10G par contre la c'est trop gros donc je vous donne le lien Trace-10G

Et le fichier sur le 11G Trace-11G

Merci

La trace sur le serveur locale montre qu’il réponde assez rapidement (501 enregistrements en moins d'une séconde):

Code:

---

FETCH #1:c=15625,e=302242,p=80,cr=169,cu=0,mis=0,r=501,dep=0,og=3,tim=18446744070666360426

---

Les deux traces montre la trame répétés N (nombre des enregistrements ramenées /arraysize, 15 dans les deux cases) fois

Code:

---

1 2 3 4 5

WAIT #3: nam='SQL*Net message from client' ela= 14265 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503366283 WAIT #3: nam='SQL*Net message to client' ela= 10 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503366494 WAIT #3: nam='SQL*Net message to dblink' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503366550 WAIT #3: nam='SQL*Net message from dblink' ela= 5472 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503372054 FETCH #3:c=0,e=5721,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=0,tim=114503372161

---

On peut facilement constater que les FETCH prennent plus de temps sur le 11g que sur la 10g (e sur la ligne FETCH).
10g

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WAIT #2: nam='SQL*Net message from client' ela= 7079 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935627151 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935627282 WAIT #2: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935627318 WAIT #2: nam='SQL*Net message from dblink' ela= 419 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935627758 FETCH #2:c=0,e=573,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=5935627823 WAIT #2: nam='SQL*Net message from client' ela= 5953 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935633827 WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935633898 WAIT #2: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935633933 WAIT #2: nam='SQL*Net message from dblink' ela= 351 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935634308 FETCH #2:c=0,e=473,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=5935634356 WAIT #2: nam='SQL*Net message from client' ela= 5874 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935640269 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=10209 tim=5935640364 WAIT #2: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935640393 WAIT #2: nam='SQL*Net message from dblink' ela= 685 driver id=1413697536 #bytes=1 p3=0 obj#=10209 tim=5935641099 FETCH #2:c=0,e=1730,p=0,cr=0,cu=0,mis=0,r=7,dep=0,og=1,tim=5935642075

---

11g

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

WAIT #3: nam='SQL*Net message from client' ela= 13823 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503322184 WAIT #3: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503322349 WAIT #3: nam='SQL*Net message to dblink' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503322427 WAIT #3: nam='SQL*Net message from dblink' ela= 12762 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503335216 FETCH #3:c=0,e=13065,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=0,tim=114503335374 WAIT #3: nam='SQL*Net message from client' ela= 12950 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503348407 WAIT #3: nam='SQL*Net message to client' ela= 9 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503348635 WAIT #3: nam='SQL*Net message to dblink' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503348692 WAIT #3: nam='SQL*Net message from dblink' ela= 3066 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503351787 FETCH #3:c=0,e=3366,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=0,tim=114503351946 WAIT #3: nam='SQL*Net message from client' ela= 14265 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503366283 WAIT #3: nam='SQL*Net message to client' ela= 10 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503366494 WAIT #3: nam='SQL*Net message to dblink' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503366550 WAIT #3: nam='SQL*Net message from dblink' ela= 5472 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503372054 FETCH #3:c=0,e=5721,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=0,tim=114503372161 WAIT #3: nam='SQL*Net message from client' ela= 16116 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503388349 WAIT #3: nam='SQL*Net message to client' ela= 9 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=114503388589 WAIT #3: nam='SQL*Net message to dblink' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503388646 WAIT #3: nam='SQL*Net message from dblink' ela= 1530 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=114503390206 FETCH #3:c=0,e=1758,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=0,tim=114503390291

---

Et ça c’est du toujours à l’événement SQL*Net message from dblink (ela sur la ligne WAIT) qui est plus long sur le 11g.
Pour moi c’est de ce coté qu’il faut chercher, paramétrage SQL NET et réseau.

Bonjour

les fichiers sqlnet.ora des deux serveurs sont identiques

Code:

---

1 2 3 4 5 6 7 8 9 10

# sqlnet.ora Network Configuration File: C:\OraHome_1\network\admin\sqlnet.ora # Generated by Oracle configuration tools.   # This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.   SQLNET.AUTHENTICATION_SERVICES= (NTS)   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

---

Que puis je vérifier d'autre?

Quelle est la différence entre 'MERCURE.GT' et 'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM'

Code:

---

1 2 3 4 5 6 7

21 - SELECT "ANMOIS","NOBUL","SOCIETE","INDIVIDU","ETAB","SECTION" FROM "PANDORE"."INFSAL" "INFSAL" WHERE "NOBUL"=1 (accessing 'MERCURE.GT' )   10g 19 - SELECT "ANMOIS","NOBUL","SOCIETE","INDIVIDU","ETAB","SECTION" FROM "PANDORE"."INFSAL" "INFSAL" WHERE "NOBUL"=1 (accessing 'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM' )

---

J'ai oublié aussi de vous dire de tester votre requête avec le hint

Code:

---

1 2	/*+ driving_site(infsal) */

---

et vice-versa avec une table locale(/*+ driving_site(donnee_tx) */ dans le cas ou la performance ne s'améliore pas.

L'un est le nom du DBlink sur le serveur 10G

Code:

---

'MERCURE.REGRESS.RDBMS.DEV.US.ORACLE.COM

---

L'autre sur le serveur 11G

Code:

---

MERCURE.GT

---

Il y a la possibilité de tracer au niveau du SQL*NET

Alors j'ai testé la requete en ajoutant " /*+ driving_site(infsal) */"

Et la réponse en 10 secondes !!!!!

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

SELECT /*+ driving_site(infsal) */ VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, sum(DONNEE_TX.DISTANCE), DONNEE_TX.CONSO_TOT_AVG, sum(DONNEE_TX.CONSO_TOTAL), DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE_PAND.LIB_SOC, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, sum(DONNEE_TX.CONSO_IDLE), sum(DONNEE_TX.CONSO_COND), MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) FROM VEHICULE, t_AFFECT4, t_AFFECT2, DONNEE_TX, TR_TRA_PERSON, INDIVIDU, ETABSECT, SOCIETE_PAND, MARQUE, CATVEHIC, VEHICULE_AFFECTATION, INFSAL WHERE ( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID and VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ and VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ ) AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 ) AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 ) AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID ) AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE ) AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE ) AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE ) AND ( INFSAL.NOBUL(+)=1 and( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) and to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) ) AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR ) AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) ) AND ( INFSAL.SOCIETE=SOCIETE_PAND.SOCIETE(+) ) AND ( ( SOCIETE_PAND.LIB_SOC = 'GT BRETAGNE' OR T_AFFECT2.FILIALE = 'BRETAGNE' ) AND DONNEE_TX.CONSO_TOT_AVG > 0 AND DONNEE_TX.IND_CONF = '0' AND to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209' ) GROUP BY VEHICULE.PARC, T_AFFECT4.CLIENT, T_AFFECT2.FILIALE, DONNEE_TX.CONSO_TOT_AVG, DONNEE_TX.DATEJ, to_char(DONNEE_TX.DATEJ,'WW'), case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end, ETABSECT.LIB, SOCIETE_PAND.LIB_SOC, DONNEE_TX.ID_CONDUCTEUR, DONNEE_TX.SPEED_AVG, case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end, MARQUE.LIBELLE, CATVEHIC.LIBELLE, decode(INDIVIDU.SEXE,'F','Femme','Homme'), to_char(DONNEE_TX.DATEJ,'YYYYMM'), round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2) HAVING sum(DONNEE_TX.DISTANCE) > 0;

---

?? comment faire pour que ce comportement soit celui par défaut?

De plus je n'arrive plus a sortir le plan d'éxécution aves lse commandes suivantes.

Code:

---

1 2 3 4

ALTER session SET statistics_level=ALL; Exécution de la requete SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

---

J'obtiens le message d'erreur suivant;

Error: cannot fetch for statement_id 'PLUS109281'.

Citation:

---

Envoyé par mnitu

Il y a la possibilité de tracer au niveau du SQL*NET

---

Voila un bou de la trace ou je retourve des informations sur le serveur distand....
Mais bon il y avait une trentaine de fichier TRC.....

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

[20-NOV. -2012 11:07:06:127] nnftmlf_make_local_addrfile: construction of local names file failed [20-NOV. -2012 11:07:06:127] nnftmlf_make_system_addrfile: system names file is D:\app\administrateur\product\11.2.0\dbhome_1\network\admin\tnsnames.ora [20-NOV. -2012 11:07:06:127] nttbnd2addr: using host IP address: 128.1.0.8 [20-NOV. -2012 11:07:06:127] niotns: Not trying to enable dead connection detection. [20-NOV. -2012 11:07:06:127] niotns: Calling address: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=128.1.0.8)(PORT=1521))(CONNECT_DATA=(SID=PAND)(CID=(PROGRAM=d:\app\administrateur\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=HESTIA)(USER=Administrateur)))) [20-NOV. -2012 11:07:06:127] nttbnd2addr: using host IP address: 128.1.0.8 [20-NOV. -2012 11:07:06:127] nscall: connecting... [20-NOV. -2012 11:07:06:127] nsc2addr: (DESCRIPTION=(CONNECT_DATA=(SID=PAND)(CID=(PROGRAM=d:\app\administrateur\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=HESTIA)(USER=Administrateur)))(ADDRESS=(PROTOCOL=TCP)(HOST=128.1.0.8)(PORT=1521))) [20-NOV. -2012 11:07:06:127] nttbnd2addr: using host IP address: 128.1.0.8 [20-NOV. -2012 11:07:06:127] nsopen: opening transport... [20-NOV. -2012 11:07:06:127] nttcni: Tcp conn timeout = 60000 (ms) [20-NOV. -2012 11:07:06:127] nttcni: trying to connect to socket 2804. [20-NOV. -2012 11:07:06:142] nttcni: connected on ipaddr 128.1.0.72 [20-NOV. -2012 11:07:06:142] nttcon: set TCP_NODELAY on 2804 [20-NOV. -2012 11:07:06:142] nsopen: transport is open [20-NOV. -2012 11:07:06:142] nsnainit: inf->nsinfflg[0]: 0x21 inf->nsinfflg[1]: 0x21 [20-NOV. -2012 11:07:06:142] nsopen: global context check-in (to slot 0) complete [20-NOV. -2012 11:07:06:142] nscon: doing connect handshake... [20-NOV. -2012 11:07:06:142] nscon: sending NSPTCN packet [20-NOV. -2012 11:07:06:158] nscon: got NSPTRD packet [20-NOV. -2012 11:07:06:158] nscall: redirected [20-NOV. -2012 11:07:06:158] nstimarmed: no timer allocated [20-NOV. -2012 11:07:06:158] nsclose: closing transport [20-NOV. -2012 11:07:06:158] nsclose: global context check-out (from slot 0) complete [20-NOV. -2012 11:07:06:158] nscall: connecting... [20-NOV. -2012 11:07:06:158] nsc2addr: (ADDRESS=(PROTOCOL=tcp)(HOST=128.1.0.8)(PORT=1348)) [20-NOV. -2012 11:07:06:158] nttbnd2addr: using host IP address: 128.1.0.8 [20-NOV. -2012 11:07:06:158] nsopen: opening transport... [20-NOV. -2012 11:07:06:174] nttcni: Tcp conn timeout = 60000 (ms) [20-NOV. -2012 11:07:06:174] nttcni: trying to connect to socket 2804. [20-NOV. -2012 11:07:06:174] nttcni: connected on ipaddr 128.1.0.72 [20-NOV. -2012 11:07:06:174] nttcon: set TCP_NODELAY on 2804 [20-NOV. -2012 11:07:06:174] nsopen: transport is open [20-NOV. -2012 11:07:06:174] nsnainit: inf->nsinfflg[0]: 0x21 inf->nsinfflg[1]: 0x21 [20-NOV. -2012 11:07:06:174] nsopen: global context check-in (to slot 0) complete [20-NOV. -2012 11:07:06:174] nscon: doing connect handshake... [20-NOV. -2012 11:07:06:174] nscon: sending NSPTCN packet [20-NOV. -2012 11:07:06:174] nscon: got NSPTAC packet [20-NOV. -2012 11:07:06:174] nscon: connect handshake is complete [20-NOV. -2012 11:07:06:174] nscon: nsctxinf[0]=0x29, [1]=0x8 [20-NOV. -2012 11:07:06:174] nsnainconn: inf->nsinfflg[0]: 0x29 inf->nsinfflg[1]: 0x8 [20-NOV. -2012 11:07:06:174] nscontrol: Vect I/O support: 0[20-NOV. -2012 11:07:06:174] nszgclv: SQLNET.ALLOWED_LOGON_VERSION not found, using default. [20-NOV. -2012 11:08:03:722] sntpwrite: Attempting to write 2259 bytes to handle 2764 [20-NOV. -2012 11:08:03:722] sntpwrite: WriteFile returned 2259 bytes [20-NOV. -2012 11:08:03:722] sntpread: Attempting to read 8208 bytes from handle 2768 [20-NOV. -2012 11:08:03:722] sntpread: ReadFile returned 21 bytes

---