{"id":203,"date":"2015-12-27T22:02:19","date_gmt":"2015-12-28T00:02:19","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=203"},"modified":"2019-11-11T19:03:25","modified_gmt":"2019-11-11T22:03:25","slug":"plano-de-execucao-com-custo-baixo-tem-melhor-desempenho-mito-ou-fato","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/plano-de-execucao-com-custo-baixo-tem-melhor-desempenho-mito-ou-fato\/","title":{"rendered":"Plano de execu\u00e7\u00e3o com custo baixo tem melhor desempenho, Mito ou Fato?"},"content":{"rendered":"

Alguns profissionais acreditam que se um plano de execu\u00e7\u00e3o tem um custo baixo ele ser\u00e1 melhor que um plano de execu\u00e7\u00e3o cujo custo seja maior, essa afirma\u00e7\u00e3o \u00e9 um mito ou fato? <\/span><\/p>\n

Nesse artigo vamos fazer uma simula\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL que vai ser executada duas vezes com planos de execu\u00e7\u00e3o diferentes e custos diferentes e vamos comparar os tempos de execu\u00e7\u00e3o para responder esta quest\u00e3o.<\/span><\/p>\n

Plano de execu\u00e7\u00e3o “A”<\/span><\/h2>\n

Para essa simula\u00e7\u00e3o vamos utilizar o “SAMPLE SCHEMA SH<\/a><\/strong>” que faz parte da instala\u00e7\u00e3o do banco de dados Oracle, vamos executar a mesma consulta duas vezes e na segunda execu\u00e7\u00e3o vamos observar que o otimizador vai utilizar um novo plano devido a “feature Cardinality feedback”<\/span>.<\/p>\n

 <\/p>\n

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
<\/div><\/td>
appuser@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.19<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nappuser@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nappuser@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nappuser@<\/span>LAB11 ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSession altered.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> select \/* QRY-TST *\/<\/span> c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME,<\/span> sum(<\/span>QUANTITY_SOLD)<\/span>  QUANTITY_SOLD
\n  2<\/span>    from CUSTOMERS c,<\/span> SALES s
\n  3<\/span>   where c.<\/span>CUST_ID =<\/span> s.<\/span>CUST_ID
\n  4<\/span>     and c.<\/span>CUST_GENDER =<\/span> 'F'<\/span>
\n  5<\/span>     and c.<\/span>CUST_LAST_NAME like '%Bishop%'<\/span>
\n  6<\/span>    group by c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           QUANTITY_SOLD
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span> -------------<\/span>
\n    100376<\/span> Dylan                Bishop                                               4<\/span>
\n    100842<\/span> Hankil               Bishop                                               4<\/span>
\n    100232<\/span> Chloe                Bishop                                               5<\/span>
\n    100025<\/span> Brayden              Bishop                                               2<\/span>
\n    100188<\/span> Hankil               Bishop                                               3<\/span>
\n    100915<\/span> Andrew               Bishop                                               3<\/span>
\n    100896<\/span> Noah                 Bishop                                               4<\/span>
\n    100218<\/span> Andrew               Bishop                                               3<\/span>
\n    100642<\/span> Aidan                Bishop                                               4<\/span>
\n    100854<\/span> Joshua               Bishop                                               3<\/span>
\n    100813<\/span> Andrew               Bishop                                               5<\/span>
\n    100927<\/span> Dakota               Bishop                                               1<\/span>
\n    100383<\/span> Dakota               Bishop                                               3<\/span>
\n
\n13<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nappuser@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nappuser@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%QRY-TST%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n801jbjyrx5dzz            0<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'801jbjyrx5dzz'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  801jbjyrx5dzz,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* QRY-TST *\/<\/span> c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME,<\/span>
\nsum(<\/span>QUANTITY_SOLD)<\/span>  QUANTITY_SOLD   from CUSTOMERS c,<\/span> SALES s  where
\nc.<\/span>CUST_ID =<\/span> s.<\/span>CUST_ID    and c.<\/span>CUST_GENDER =<\/span> :<\/span>"SYS_B_0"<\/span>    and
\nc.<\/span>CUST_LAST_NAME like :<\/span>"SYS_B_1"<\/span>   group by c.<\/span>CUST_ID,<\/span>
\nc.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME
\n
\nPlan hash<\/span><\/a> value:<\/span> 2222044125<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> Pstart|<\/span> Pstop |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT         |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   954<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    5928<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY           |<\/span>           |<\/span>      1<\/span> |<\/span>    916<\/span> |<\/span> 36640<\/span> |<\/span>   954<\/span>   (<\/span>4<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>12<\/span> |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    5928<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   HASH<\/span><\/a> JOIN<\/span><\/a>              |<\/span>           |<\/span>      1<\/span> |<\/span>    916<\/span> |<\/span> 36640<\/span> |<\/span>   953<\/span>   (<\/span>4<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>12<\/span> |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    5928<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    JOIN<\/span><\/a> FILTER CREATE    |<\/span> :<\/span>BF0000   |<\/span>      1<\/span> |<\/span>    916<\/span> |<\/span> 20152<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>       |<\/span>       |<\/span>     20<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1457<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL    |<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>    916<\/span> |<\/span> 20152<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>       |<\/span>       |<\/span>     20<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1457<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>    VIEW                  |<\/span> VW_GBC_5  |<\/span>      1<\/span> |<\/span>   7059<\/span> |<\/span>   124K|<\/span>   548<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>     14<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    4471<\/span> |<\/span>
\n|<\/span>   6<\/span> |<\/span>     HASH<\/span><\/a> GROUP BY        |<\/span>           |<\/span>      1<\/span> |<\/span>   7059<\/span> |<\/span> 56472<\/span> |<\/span>   548<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>     14<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    4471<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>      JOIN<\/span><\/a> FILTER USE<\/span>     |<\/span> :<\/span>BF0000   |<\/span>      1<\/span> |<\/span>    918K|<\/span>  7178K|<\/span>   525<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>    111<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    4471<\/span> |<\/span>
\n|<\/span>   8<\/span> |<\/span>       PARTITION RANGE<\/span><\/a> ALL|<\/span>           |<\/span>      1<\/span> |<\/span>    918K|<\/span>  7178K|<\/span>   525<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>    111<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    4471<\/span> |<\/span>
\n|*<\/span>  9<\/span> |<\/span>        TABLE ACCESS FULL |<\/span> SALES     |<\/span>     28<\/span> |<\/span>    918K|<\/span>  7178K|<\/span>   525<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>    111<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    4471<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"ITEM_1"<\/span>)<\/span>
\n   4<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE :<\/span>SYS_B_1 AND "C"<\/span>.<\/span>"CUST_GENDER"<\/span>=:<\/span>SYS_B_0)<\/span>)<\/span>
\n   9<\/span> -<\/span> filter(<\/span>SYS_OP_BLOOM_FILTER(<\/span>:<\/span>BF0000,<\/span>"S"<\/span>.<\/span>"CUST_ID"<\/span>)<\/span>)<\/span>
\n
\n
\n32<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span>
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando\u00a0o plano de execu\u00e7\u00e3o acima podemos verificar que a consulta selecionou 13 registros (A-Rows) e o tempo de execu\u00e7\u00e3o foi de 6 cent\u00e9simos de segundo (A-Time) com um custo de 954 (Cost), tamb\u00e9m podemos observar que para esse SQL_ID “801jbjyrx5dzz” tem somente um plano de execu\u00e7\u00e3o cujo HASH \u00e9 “2222044125” e CHILD NUMBER 0. Outro ponto importante a se observar \u00e9 o numero de “Buffers” de memoria visitados que nesse caso foi 5.928.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

    \n
  1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/span><\/strong><\/li>\n
  2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/span><\/strong><\/li>\n
  3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/span><\/strong><\/li>\n
  4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/span><\/strong><\/li>\n<\/ol>\n

    Plano de execu\u00e7\u00e3o “B”<\/span><\/h2>\n

    Vamos executar novamente a mesma consulta e observar o que acontece:<\/span><\/p>\n

     <\/p>\n

    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
    <\/div><\/td>
    appuser@<\/span>LAB11 ><\/span> select \/* QRY-TST *\/<\/span> c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME,<\/span> sum(<\/span>QUANTITY_SOLD)<\/span>  QUANTITY_SOLD
    \n  2<\/span>    from CUSTOMERS c,<\/span> SALES s
    \n  3<\/span>   where c.<\/span>CUST_ID =<\/span> s.<\/span>CUST_ID
    \n  4<\/span>     and c.<\/span>CUST_GENDER =<\/span> 'F'<\/span>
    \n  5<\/span>     and c.<\/span>CUST_LAST_NAME like '%Bishop%'<\/span>
    \n  6<\/span>    group by c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME;<\/span>
    \n
    \n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           QUANTITY_SOLD
    \n----------<\/span> --------------------<\/span> ----------------------------------------<\/span> -------------<\/span>
    \n    100376<\/span> Dylan                Bishop                                               4<\/span>
    \n    100842<\/span> Hankil               Bishop                                               4<\/span>
    \n    100232<\/span> Chloe                Bishop                                               5<\/span>
    \n    100025<\/span> Brayden              Bishop                                               2<\/span>
    \n    100188<\/span> Hankil               Bishop                                               3<\/span>
    \n    100915<\/span> Andrew               Bishop                                               3<\/span>
    \n    100896<\/span> Noah                 Bishop                                               4<\/span>
    \n    100218<\/span> Andrew               Bishop                                               3<\/span>
    \n    100642<\/span> Aidan                Bishop                                               4<\/span>
    \n    100854<\/span> Joshua               Bishop                                               3<\/span>
    \n    100813<\/span> Andrew               Bishop                                               5<\/span>
    \n    100927<\/span> Dakota               Bishop                                               1<\/span>
    \n    100383<\/span> Dakota               Bishop                                               3<\/span>
    \n
    \n13<\/span> rows selected.<\/span>
    \n
    \nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.40<\/span>
    \nappuser@<\/span>LAB11 ><\/span>
    \nappuser@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
    \nappuser@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
    \nappuser@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
    \n  2<\/span>    FROM v$sql<\/span>
    \n  3<\/span>   WHERE sql_text LIKE '%QRY-TST%'<\/span>
    \n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
    \n
    \nSQL_ID        CHILD_NUMBER
    \n-------------<\/span> ------------<\/span>
    \n801jbjyrx5dzz            0<\/span>
    \n801jbjyrx5dzz            1<\/span>
    \n
    \nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span>
    \nappuser@<\/span>LAB11 ><\/span>
    \nappuser@<\/span>LAB11 ><\/span>
    \nappuser@<\/span>LAB11 ><\/span> SELECT *<\/span>
    \n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
    \nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
    \nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'801jbjyrx5dzz'<\/span>,<\/span>         1<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
    \n
    \nPLAN_TABLE_OUTPUT
    \n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
    \nSQL_ID  801jbjyrx5dzz,<\/span> child number 1<\/span>
    \n-------------------------------------<\/span>
    \nselect \/* QRY-TST *\/<\/span> c.<\/span>CUST_ID,<\/span> c.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME,<\/span>
    \nsum(<\/span>QUANTITY_SOLD)<\/span>  QUANTITY_SOLD   from CUSTOMERS c,<\/span> SALES s  where
    \nc.<\/span>CUST_ID =<\/span> s.<\/span>CUST_ID    and c.<\/span>CUST_GENDER =<\/span> :<\/span>"SYS_B_0"<\/span>    and
    \nc.<\/span>CUST_LAST_NAME like :<\/span>"SYS_B_1"<\/span>   group by c.<\/span>CUST_ID,<\/span>
    \nc.<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME
    \n
    \nPlan hash<\/span><\/a> value:<\/span> 3219243479<\/span>
    \n
    \n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
    \n|<\/span> Id  |<\/span> Operation                     |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>    Time<\/span><\/a>   |<\/span> Pstart|<\/span> Pstop |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
    \n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
    \n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT              |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   563<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.39<\/span> |<\/span>   18088<\/span> |<\/span>
    \n|<\/span>   1<\/span> |<\/span>      HASH<\/span><\/a> GROUP BY                |<\/span>              |<\/span>      1<\/span> |<\/span>     14<\/span> |<\/span>   560<\/span> |<\/span>   563<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.39<\/span> |<\/span>   18088<\/span> |<\/span>
    \n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS                |<\/span>              |<\/span>      1<\/span> |<\/span>     14<\/span> |<\/span>   560<\/span> |<\/span>   562<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.39<\/span> |<\/span>   18088<\/span> |<\/span>
    \n|<\/span>   3<\/span> |<\/span>    NESTED LOOPS               |<\/span>              |<\/span>      1<\/span> |<\/span>     14<\/span> |<\/span>   560<\/span> |<\/span>   562<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>   7059<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.38<\/span> |<\/span>   11029<\/span> |<\/span>
    \n|<\/span>   4<\/span> |<\/span>     VIEW                      |<\/span> VW_GBC_5     |<\/span>      1<\/span> |<\/span>     14<\/span> |<\/span>   252<\/span> |<\/span>   548<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>   7059<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.37<\/span> |<\/span>    4471<\/span> |<\/span>
    \n|<\/span>   5<\/span> |<\/span>          HASH<\/span><\/a> GROUP BY            |<\/span>              |<\/span>      1<\/span> |<\/span>     14<\/span> |<\/span>   112<\/span> |<\/span>   548<\/span>   (<\/span>6<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>       |<\/span>       |<\/span>   7059<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.37<\/span> |<\/span>    4471<\/span> |<\/span>
    \n|<\/span>   6<\/span> |<\/span>       PARTITION     RANGE<\/span><\/a> ALL     |<\/span>              |<\/span>      1<\/span> |<\/span>    918K|<\/span>  7178K|<\/span>   525<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>    918K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span> |<\/span>    4471<\/span> |<\/span>
    \n|<\/span>   7<\/span> |<\/span>        TABLE ACCESS FULL      |<\/span> SALES        |<\/span>     28<\/span> |<\/span>    918K|<\/span>  7178K|<\/span>   525<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>07<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>    918K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>    4471<\/span> |<\/span>
    \n|*<\/span>  8<\/span> |<\/span>     INDEX UNIQUE SCAN         |<\/span> CUSTOMERS_PK |<\/span>   7059<\/span> |<\/span>      1<\/span> |<\/span>       |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/span>          |<\/span>       |<\/span>       |<\/span>   7059<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    6558<\/span> |<\/span>
    \n|*<\/span>  9<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> CUSTOMERS    |<\/span>   7059<\/span> |<\/span>      1<\/span> |<\/span>    22<\/span> |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>       |<\/span>       |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    7059<\/span> |<\/span>
    \n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
    \n
    \nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
    \n---------------------------------------------------<\/span>
    \n
    \n   8<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"ITEM_1"<\/span>)<\/span>
    \n   9<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE :<\/span>SYS_B_1 AND "C"<\/span>.<\/span>"CUST_GENDER"<\/span>=:<\/span>SYS_B_0)<\/span>)<\/span>
    \n
    \nNote
    \n-----<\/span>
    \n   -<\/span> cardinality feedback used for<\/span> this statement
    \n
    \n
    \n35<\/span> rows selected.<\/span>
    \n
    \nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span>
    \nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

     <\/p>\n

    A primeira observa\u00e7\u00e3o sobre a execu\u00e7\u00e3o da consulta acima\u00a0\u00e9 que o plano de execu\u00e7\u00e3o foi alterado, agora o HASH \u00e9 “3219243479” e CHILD NUMBER 1, se observarmos na se\u00e7\u00e3o “Note” linha 83 vamos encontrar o motivo pelo qual o plano de execu\u00e7\u00e3o foi alterado: “cardinality feedback used for this statement<\/em><\/span>“, isto significa que o Otimizador percebeu que as estimativas de linhas por opera\u00e7\u00e3o na primeira execu\u00e7\u00e3o n\u00e3o estavam corretas e utilizou as informa\u00e7\u00f5es da primeira execu\u00e7\u00e3o para melhorar as estimativas na segunda execu\u00e7\u00e3o. Alem disso podemos observar que a consulta selecionou os mesmos 13 registros (A-Rows) da primeira execu\u00e7\u00e3o, porem o tempo de execu\u00e7\u00e3o aumentou para 39 cent\u00e9simos de segundo (A-Time) e o custo diminuiu para 563 (Cost). O aumento do tempo de execu\u00e7\u00e3o se deve ao fato de que houve um aumento no n\u00famero de “Buffers” em memoria visitados para execu\u00e7\u00e3o da consulta.<\/span><\/p>\n

     <\/p>\n

    Conclus\u00e3o<\/span><\/h2>\n

    \"resumo<\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n

    Observando o quadro acima podemos concluir que o plano com menor custo 563 (B) n\u00e3o \u00e9 o melhor plano de execu\u00e7\u00e3o pois o seu tempo de execu\u00e7\u00e3o foi cinco vezes maior que o plano “A” com custo 954, Isto ocorre porque o otimizador faz uma s\u00e9rie de estimativas e suposi\u00e7\u00f5es para criar o plano de execu\u00e7\u00e3o e em alguns casos essas estimativas ou suposi\u00e7\u00f5es n\u00e3o s\u00e3o t\u00e3o precisas como deveriam ser. Assim podemos concluir que trata-se de um mito a afirma\u00e7\u00e3o de que um plano de execu\u00e7\u00e3o com o menor custo sempre ser\u00e1 o melhor.<\/span><\/p>\n

    Refer\u00eancias<\/span><\/h2>\n

    https:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e10831\/overview.htm#sthref6<\/a><\/p>\n

     
    \n 
    \n    \"Promo-D75L\"<\/a><\/p>\n

    \"video-02\"<\/a>
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n <\/p>\n","protected":false},"excerpt":{"rendered":"

    Alguns profissionais acreditam que se um plano de execu\u00e7\u00e3o tem um custo baixo ele ser\u00e1 melhor que um plano de execu\u00e7\u00e3o cujo custo seja maior, essa afirma\u00e7\u00e3o \u00e9 um mito ou fato? Nesse artigo vamos fazer uma simula\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL que vai ser executada duas vezes com planos de execu\u00e7\u00e3o diferentes e custos diferentes e vamos comparar os<\/p>\n","protected":false},"author":2,"featured_media":204,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[6,59,42,60,46,36,57,58,10,29,11,45,61],"class_list":["post-203","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","tag-cbo","tag-cost","tag-cost-base-optimizer","tag-custo","tag-dbms_xplan-display_cursor","tag-execution-plan","tag-mito","tag-myth","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-sql-statement-analysis","tag-tempo-de-reposta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=203"}],"version-history":[{"count":22,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/203\/revisions"}],"predecessor-version":[{"id":2107,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/203\/revisions\/2107"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/204"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}