Explications sur trois requêtes et trois plans

**bstevy** · 11/06/2015, 12h37

Bonjour à tous,

J'aurais besoin de vos lumières pour comprendre ce qu'il se passe avec trois de mes requêtes qui donnent le même résultat mais dont le code est différent, et donc en conséquence, le plan d'éxecution.

En fait, le plan de correspond pas au temps de traitement, et donc, j'aimerai savoir si vous pouviez m'en expliquer la raison, ou me dire ce que je pourrais regarder d'autre.

requête 1 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT COUNT (1)
  FROM contracts con, contract_versions CV
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_CONSTRUCTION_TIME > sysdate - 455
              AND cv_con_number = CV.cv_con_number)
   AND con.con_number = CV.cv_con_number
   AND con_main_maturity > SYSDATE
;

plan1 :

SELECT STATEMENT;  Optimizer=All_rows;  Cost=272,888;  Cardinality=1;  Bytes=55;  Cpu_cost=21,337,555,528;  Io_cost=271,865;  Time=3,275
  SORT AGGREGATE;  Cardinality=1;  Bytes=55
    NESTED LOOPS
      NESTED LOOPS;  Cost=272,888;  Cardinality=747;  Bytes=41,085;  Cpu_cost=21,337,555,528;  Io_cost=271,865;  Time=3,275
        HASH JOIN;  Cost=265,224;  Cardinality=12,768;  Bytes=510,720;  Cpu_cost=21,273,232,081;  Io_cost=264,204;  Temp_space=30,557,000;  Access_predicates="cv_number"="max(cv_number)" and "item_0"="cv_con_number";  Time=3,183
          VIEW VIEW SYS.VW_SQ_1;  Object_instance=6;  Cost=122,957;  Cardinality=804,012;  Bytes=20,904,312;  Cpu_cost=1,856,347,958;  Io_cost=122,868;  Time=1,476
            HASH GROUP BY;  Cost=122,957;  Cardinality=804,012;  Bytes=16,884,252;  Cpu_cost=1,856,347,958;  Io_cost=122,868;  Temp_space=29,533,000;  Time=1,476
              TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=5;  Cost=117,732;  Cardinality=814,425;  Bytes=17,102,925;  Cpu_cost=1,032,030,669;  Io_cost=117,683;  Filter_predicates="cv_status_code"<>'09' and "cv_status_code"<>'9';  Time=1,413
                INDEX RANGE SCAN INDEX JAPHONIE.CONTRACT_VERSIONS_TMP1;  Optimizer=Analyzed;  Search_columns=1;  Cost=740;  Cardinality=823,782;  Cpu_cost=53,178,366;  Io_cost=738;  Access_predicates="cv_construction_time">sysdate@!-455;  Time=9
          TABLE ACCESS FULL TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=4;  Cost=127,290;  Cardinality=10,917,642;  Bytes=152,846,988;  Cpu_cost=16,944,868,409;  Io_cost=126,478;  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,528
        INDEX UNIQUE SCAN INDEX (UNIQUE) JAPHONIE.CON_PK;  Optimizer=Analyzed;  Search_columns=1;  Cost=1;  Cardinality=1;  Cpu_cost=2,706;  Io_cost=1;  Access_predicates="con"."con_number"="cv_con_number";  Time=1
      TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACTS;  Optimizer=Analyzed;  Object_instance=1;  Cost=1;  Cardinality=1;  Bytes=15;  Cpu_cost=5,038;  Io_cost=1;  Filter_predicates="con_main_maturity">sysdate@!;  Time=1

Temps d'execution moyen = 48s

Req 2:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
with contract_versions_max AS (
    SELECT cv_con_number
    FROM contract_versions
    WHERE cv_status_code not in ('09','9')
    and CV_CONSTRUCTION_TIME > sysdate - 455
    group by cv_con_number
    having max(CV_CRC_CODE) keep (dense_rank first order by CV_NUMBER desc) not IN (12, 13, 14, 15, 16, 102)
)   
select count(*)
from contracts con
where con_main_maturity > SYSDATE
and con_number in (select cv_con_number from contract_versions_max)
;

Plan 2:

SELECT STATEMENT;  Optimizer=All_rows;  Cost=117,770;  Cardinality=1;  Bytes=28;  Cpu_cost=1,794,244,483;  Io_cost=117,684;  Time=1,414
  SORT AGGREGATE;  Cardinality=1;  Bytes=28
    NESTED LOOPS
      NESTED LOOPS;  Cost=117,770;  Cardinality=1;  Bytes=28;  Cpu_cost=1,794,244,483;  Io_cost=117,684;  Time=1,414
        VIEW VIEW SYS.VW_NSO_1;  Object_instance=5;  Cost=117,768;  Cardinality=1;  Bytes=13;  Cpu_cost=1,773,380,806;  Io_cost=117,683;  Time=1,414
          FILTER;  Filter_predicates=To_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>12 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>13 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>14 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>15 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>16 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>102
            SORT GROUP BY;  Cost=117,768;  Cardinality=1;  Bytes=25;  Cpu_cost=1,773,380,806;  Io_cost=117,683;  Time=1,414
              TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=4;  Cost=117,732;  Cardinality=814,423;  Bytes=20,360,575;  Cpu_cost=1,032,030,412;  Io_cost=117,683;  Filter_predicates="cv_status_code"<>'09' and "cv_status_code"<>'9';  Time=1,413
                INDEX RANGE SCAN INDEX JAPHONIE.CONTRACT_VERSIONS_TMP1;  Optimizer=Analyzed;  Search_columns=1;  Cost=740;  Cardinality=823,780;  Cpu_cost=53,178,306;  Io_cost=738;  Access_predicates="cv_construction_time">sysdate@!-455;  Time=9
        INDEX UNIQUE SCAN INDEX (UNIQUE) JAPHONIE.CON_PK;  Optimizer=Analyzed;  Search_columns=1;  Cost=1;  Cardinality=1;  Cpu_cost=2,706;  Io_cost=1;  Access_predicates="con_number"="cv_con_number";  Time=1
      TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACTS;  Optimizer=Analyzed;  Object_instance=2;  Cost=1;  Cardinality=1;  Bytes=15;  Cpu_cost=5,038;  Io_cost=1;  Filter_predicates="con_main_maturity">sysdate@!;  Time=1

Temps d'execution moyen = 1min15

requete 3:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
with contract_versions_max AS (
    SELECT cv_con_number
    FROM contract_versions
    WHERE cv_status_code not in ('09','9')
    and CV_CONSTRUCTION_TIME > sysdate - 455
    group by cv_con_number
    having max(CV_CRC_CODE) keep (dense_rank first order by CV_NUMBER desc) not IN (12, 13, 14, 15, 16, 102)
)   
select count(*)
from contracts con
where con_main_maturity > SYSDATE
and exists (select 1 from contract_versions_max where cv_con_number = con_number)
;

plan 3:

SELECT STATEMENT;  Optimizer=All_rows;  Cost=741,956;  Cardinality=1;  Bytes=15;  Cpu_cost=11,428,402,608;  Io_cost=741,408;  Time=8,904
  SORT AGGREGATE;  Cardinality=1;  Bytes=15
    FILTER;  Filter_predicates= exists (select 0 from "japhonie"."contract_versions" "contract_versions" where "cv_con_number"=:b1 and "cv_construction_time">sysdate@!-455 and "cv_status_code"<>'09' and "cv_status_code"<>'9' group by "cv_con_number" having to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>12 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>13 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>14 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>15 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>16 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>102)
      TABLE ACCESS FULL TABLE JAPHONIE.CONTRACTS;  Optimizer=Analyzed;  Object_instance=2;  Cost=27,179;  Cardinality=484,538;  Bytes=7,268,070;  Cpu_cost=6,042,594,233;  Io_cost=26,889;  Filter_predicates="con_main_maturity">sysdate@!;  Time=327
      FILTER;  Filter_predicates=To_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>12 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>13 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>14 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>15 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>16 and to_number(max("cv_crc_code") keep (dense_rank first  order by internal_function("cv_number") desc ))<>102
        SORT GROUP BY NOSORT;  Cost=2;  Cardinality=1;  Bytes=25;  Cpu_cost=11,307;  Io_cost=2;  Time=1
          TABLE ACCESS BY INDEX ROWID TABLE JAPHONIE.CONTRACT_VERSIONS;  Optimizer=Analyzed;  Object_instance=4;  Cost=2;  Cardinality=1;  Bytes=25;  Cpu_cost=11,307;  Io_cost=2;  Filter_predicates="cv_construction_time">sysdate@!-455 and "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

Temps d'execution moyen = 15s

Donc, si vous avez le courrage de vous penchez dessus, vous verrez que la requete 3 à le plus gros "cost" et aussi le plus petit temps d'execution. L'inverse pour la requête 2.

Si vous pouviez m'expliquer ce qui semble etre pour un paradoxe, je suis tout ouie.

Merci d'avance pour vos réponses.

Steven

**mnitu** · 11/06/2015, 14h40

A part quelques exceptions concernant des requêtes très simple ça n'a pas de sens de comparer le coût de deux requêtes différentes.

Consider Cost or Time - trying to compare the COST of two queries

Et Jonathan Lewis nuance cette idée d'une meilleur façon.

The cost of a query represents the optimizer’s estimate of how long it will take that query to run – so it is perfectly valid to compare the cost of two queries to see which one the optimizer thinks will be faster but, thanks to limitations and defects in the optimizer it may not be entirely sensible to do so.

/Edit
Essayez de formater vos requête avec dbms_xplan.display sinon il sont presque illisibles.

Explications sur trois requêtes et trois plans

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager