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