Discussion :

[bstevy]

Bonjour,

Je suis en train de developper une grosse requête et donc je cherche dès le début les moyens d'optimiser mon temps de traitement.
Néanmoins, j'ai un peu du mal à m'y retrouver entre par exemple les plans et le temps de traitement, ou la comparaison entre deux plans.


Pourriez vous m'aider à y voir plus clair ?



Par exemple, j'ai les deux requêtes et leur plan d'execution respectif:

Requete 1:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
  FROM contracts con
  inner join  contract_versions CV on con.con_number = CV.cv_con_number
WHERE 1 = 1
   AND CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)
   AND cv_number =
          (SELECT MAX (cv_number)
             FROM contract_versions
            WHERE NOT (cv_status_code = '09' OR cv_status_code = '9')
              AND cv_con_number = CV.cv_con_number)
   AND con_main_maturity > SYSDATE
;

Plan :

SELECT STATEMENT;  Optimizer=All_rows;  Cost=490,108;  Cardinality=8,791;  Bytes=3,446,072;  Cpu_cost=61,278,349,711;  Io_cost=487,170;  Time=5,882
  HASH JOIN;  Cost=490,108;  Cardinality=8,791;  Bytes=3,446,072;  Cpu_cost=61,278,349,711;  Io_cost=487,170;  Temp_space=41,542,000;  Access_predicates="con"."con_number"="cv_con_number";  Time=5,882
    HASH JOIN;  Cost=458,546;  Cardinality=128,203;  Bytes=39,999,336;  Cpu_cost=55,014,369,020;  Io_cost=455,909;  Temp_space=309,257,000;  Access_predicates="cv_number"="max(cv_number)" and "item_0"="cv_con_number";  Time=5,503
      VIEW VIEW SYS.VW_SQ_1;  Object_instance=7;  Cost=168,152;  Cardinality=8,138,236;  Bytes=211,594,136;  Cpu_cost=19,957,187,089;  Io_cost=167,195;  Time=2,018
        HASH GROUP BY;  Cost=168,152;  Cardinality=8,138,236;  Bytes=105,797,068;  Cpu_cost=19,957,187,089;  Io_cost=167,195;  Temp_space=261,923,000;  Time=2,018
          TABLE ACCESS FULL TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=6;  Cost=124,635;  Cardinality=10,870,145;  Bytes=141,311,885;  Cpu_cost=7,772,613,966;  Io_cost=124,262;  Filter_predicates="cv_status_code"<>'09' and "cv_status_code"<>'9';  Time=1,496
      TABLE ACCESS FULL TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=5;  Cost=125,698;  Cardinality=10,640,836;  Bytes=3,043,279,096;  Cpu_cost=29,954,993,186;  Io_cost=124,262;  Filter_predicates=To_number("cv_crc_code")<>15 and to_number("cv_crc_code")<>12 and to_number("cv_crc_code")<>16 and to_number("cv_crc_code")<>14 and to_number("cv_crc_code")<>13 and to_number("cv_crc_code")<>102;  Time=1,509
    TABLE ACCESS FULL TABLE JAPHONIE.CONTRACTS;  Optimizer=Analyzed;  Object_instance=1;  Cost=27,180;  Cardinality=553,902;  Bytes=44,312,160;  Cpu_cost=6,077,215,637;  Io_cost=26,889;  Filter_predicates="con"."con_main_maturity">sysdate@!;  Time=327

Requete 2:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
  FROM contracts con
  inner join  contract_versions CV on con.con_number = CV.cv_con_number
WHERE 1 = 1
   AND CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)
   AND con_main_maturity > SYSDATE
   AND exists   (   select 1
                    from contract_versions A
                    WHERE NOT (A.cv_status_code = '09' OR A.cv_status_code = '9')
                    AND A.cv_con_number = CV.cv_con_number
                    group by A.cv_con_number
                    having MAX (A.cv_number) = CV.cv_number
                    )

Plan 2:

SELECT STATEMENT;  Optimizer=All_rows;  Cost=1,391,280;  Cardinality=1;  Bytes=366;  Cpu_cost=48,060,754,096;  Io_cost=1,388,976;  Time=16,696
  FILTER;  Filter_predicates= exists (select 0 from "japhonie"."contract_versions" "contract_versions" where "cv_con_number"=:b1 and "cv_status_code"<>'09' and "cv_status_code"<>'9' group by "cv_con_number" having max("cv_number")=:b2)
    HASH JOIN;  Cost=305,320;  Cardinality=729,676;  Bytes=267,061,416;  Cpu_cost=40,080,213,886;  Io_cost=303,398;  Temp_space=50,963,000;  Access_predicates="con"."con_number"="cv_con_number";  Time=3,664
      TABLE ACCESS FULL TABLE JAPHONIE.CONTRACTS;  Optimizer=Analyzed;  Object_instance=1;  Cost=27,180;  Cardinality=553,902;  Bytes=44,312,160;  Cpu_cost=6,077,215,637;  Io_cost=26,889;  Filter_predicates="con"."con_main_maturity">sysdate@!;  Time=327
      TABLE ACCESS FULL TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=5;  Cost=125,708;  Cardinality=10,640,836;  Bytes=3,043,279,096;  Cpu_cost=30,167,809,902;  Io_cost=124,262;  Filter_predicates=To_number("cv_crc_code")<>15 and to_number("cv_crc_code")<>12 and to_number("cv_crc_code")<>16 and to_number("cv_crc_code")<>14 and to_number("cv_crc_code")<>13 and to_number("cv_crc_code")<>102;  Time=1,509
    FILTER;  Filter_predicates=Max("cv_number")=:b1
      SORT GROUP BY NOSORT;  Cost=2;  Cardinality=1;  Bytes=13;  Cpu_cost=11,027;  Io_cost=2;  Time=1
        TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=6;  Cost=2;  Cardinality=1;  Bytes=13;  Cpu_cost=11,027;  Io_cost=2;  Filter_predicates="cv_status_code"<>'09' and "cv_status_code"<>'9';  Time=1
          INDEX RANGE SCAN INDEX JAPHONIE.CV_CON_FK_I;  Optimizer=Analyzed;  Search_columns=1;  Cost=1;  Cardinality=1;  Cpu_cost=6,529;  Io_cost=1;  Access_predicates="cv_con_number"=:b1;  Time=1

Les deux requêtes me donnent le même résultat.
Mais les informations que je trouve dans le plan ne me sont pas très claires.

Par exemple :
La premiere met entre 1min30 et 2min30 à me retourner un résultat, avec un cout dans le plan de 490,108 et 3 fullscan
La deuxième me donne un résultat en 20s, mais le plan affiche un cout de 1,391,280 avec seuleument 2 fullscan.

De ce fait, je me demande ce qu'il faut privilégier (privilegier le cout ou le nombre de full scan), et quelle information il est utile de regarder dans le plan (le cout, Bytes, time ?)


Pourriez vous m'aidez à y voir plus clair svp ?


Steven

[pachot]

Bonjour,

Ces plan ne sont pas très lisibles et n'ont pas l'info la plus important: le nombre de blocs lus.
Voici comment obtenir le plan le plus lisible:
http://www.dbi-services.com/index.ph...execution-plan

Sinon, Il y a probablement moyen de ne pas aller lire plusieurs fois la table contract_versions en utilisant une fonction analytique.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from (
SELECT con.*,cv.*, MAX (case when cv_status_code = '09' OR cv_status_code = '9' then cv_number end) OVER (partition by CV.cv_con_number) max_cv_number  FROM contracts con
  inner join  contract_versions CV on con.con_number = CV.cv_con_number
WHERE 1 = 1
   AND CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)
   AND cv_number =
          (SELECT MAX (cv_number)
             FROM contract_versions
            WHERE NOT (cv_status_code = '09' OR cv_status_code = '9')
              AND cv_con_number = CV.cv_con_number)
   AND con_main_maturity > SYSDATE
) where max_cv_number=cv_number

;

à vérifier, j'ai copié/collé vite fait pour donner l'idée...

Cordialement,
Franck.

[bstevy]

Je vois l'idée pour l'optimisation. Mais je vais devoir dédoublonner derrière...



Je vais regarder ton lien sur les plans un peu plus tard, et je te ferais un retour merci en tout cas pour cette info.

[mnitu]

Utilisez last/first dans leur version agrégé. Cherchez des exemples sur ce forum.

[bstevy]

Je suis partie sur une autre méthode finalement, car j'ai un ETL derrière donc je réalise la partie extraction avec Oracle, et tout ce qui est dédoublonnage et dénormalisation, je le fais avec informatica.

Cette méthode me convient bien.

Merci pour vos réponses respectives.