Plano de execução com custo baixo tem melhor desempenho, Mito ou Fato?

Mito Custo versus Desempenho

Alguns profissionais acreditam que se um plano de execução tem um custo baixo ele será melhor que um plano de execução cujo custo seja maior, essa afirmação é um mito ou fato?

Nesse artigo vamos fazer uma simulação de uma instrução SQL que vai ser executada duas vezes com planos de execução diferentes e custos diferentes e vamos comparar os tempos de execução para responder esta questão.

Plano de execução “A”

Para essa simulação vamos utilizar o “SAMPLE SCHEMA SH” que faz parte da instalação do banco de dados Oracle, vamos executar a mesma consulta duas vezes e na segunda execução vamos observar que o otimizador vai utilizar um novo plano devido a “feature Cardinality feedback”.

 

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
appuser@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Elapsed: 00:00:00.19
appuser@LAB11 >
appuser@LAB11 > set echo off;
appuser@LAB11 > set tab off;
appuser@LAB11 > set lines 300 pages 100;
appuser@LAB11 >
appuser@LAB11 > ALTER SESSION SET statistics_level=ALL;

Session altered.

Elapsed: 00:00:00.00
appuser@LAB11 > ALTER SESSION SET current_schema=SH;

Session altered.

Elapsed: 00:00:00.00
appuser@LAB11 >
appuser@LAB11 > select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD)  QUANTITY_SOLD
  2    from CUSTOMERS c, SALES s
  3   where c.CUST_ID = s.CUST_ID
  4     and c.CUST_GENDER = 'F'
  5     and c.CUST_LAST_NAME like '%Bishop%'
  6    group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           QUANTITY_SOLD
---------- -------------------- ---------------------------------------- -------------
    100376 Dylan                Bishop                                               4
    100842 Hankil               Bishop                                               4
    100232 Chloe                Bishop                                               5
    100025 Brayden              Bishop                                               2
    100188 Hankil               Bishop                                               3
    100915 Andrew               Bishop                                               3
    100896 Noah                 Bishop                                               4
    100218 Andrew               Bishop                                               3
    100642 Aidan                Bishop                                               4
    100854 Joshua               Bishop                                               3
    100813 Andrew               Bishop                                               5
    100927 Dakota               Bishop                                               1
    100383 Dakota               Bishop                                               3

13 rows selected.

Elapsed: 00:00:00.07
appuser@LAB11 >
appuser@LAB11 > column sql_id new_value m_sql_id
appuser@LAB11 > column child_number new_value m_child_no
appuser@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%QRY-TST%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
801jbjyrx5dzz            0

Elapsed: 00:00:00.03
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('801jbjyrx5dzz',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  801jbjyrx5dzz, child number 0
-------------------------------------
select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME,
sum(QUANTITY_SOLD)  QUANTITY_SOLD   from CUSTOMERS c, SALES s  where
c.CUST_ID = s.CUST_ID    and c.CUST_GENDER = :"SYS_B_0"    and
c.CUST_LAST_NAME like :"SYS_B_1"   group by c.CUST_ID,
c.CUST_FIRST_NAME, CUST_LAST_NAME

Plan hash value: 2222044125

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |       |   954 (100)|          |       |       |     13 |00:00:00.06 |    5928 |
|   1 |  HASH GROUP BY           |           |      1 |    916 | 36640 |   954   (4)| 00:00:12 |       |       |     13 |00:00:00.06 |    5928 |
|*  2 |   HASH JOIN              |           |      1 |    916 | 36640 |   953   (4)| 00:00:12 |       |       |     13 |00:00:00.06 |    5928 |
|   3 |    JOIN FILTER CREATE    | :BF0000   |      1 |    916 | 20152 |   405   (1)| 00:00:05 |       |       |     20 |00:00:00.01 |    1457 |
|*  4 |     TABLE ACCESS FULL    | CUSTOMERS |      1 |    916 | 20152 |   405   (1)| 00:00:05 |       |       |     20 |00:00:00.01 |    1457 |
|   5 |    VIEW                  | VW_GBC_5  |      1 |   7059 |   124K|   548   (6)| 00:00:07 |       |       |     14 |00:00:00.05 |    4471 |
|   6 |     HASH GROUP BY        |           |      1 |   7059 | 56472 |   548   (6)| 00:00:07 |       |       |     14 |00:00:00.05 |    4471 |
|   7 |      JOIN FILTER USE     | :BF0000   |      1 |    918K|  7178K|   525   (2)| 00:00:07 |       |       |    111 |00:00:00.05 |    4471 |
|   8 |       PARTITION RANGE ALL|           |      1 |    918K|  7178K|   525   (2)| 00:00:07 |     1 |    28 |    111 |00:00:00.05 |    4471 |
|*  9 |        TABLE ACCESS FULL | SALES     |     28 |    918K|  7178K|   525   (2)| 00:00:07 |     1 |    28 |    111 |00:00:00.05 |    4471 |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="ITEM_1")
   4 - filter(("C"."CUST_LAST_NAME" LIKE :SYS_B_1 AND "C"."CUST_GENDER"=:SYS_B_0))
   9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))


32 rows selected.

Elapsed: 00:00:00.03
appuser@LAB11 >

 

Analisando o plano de execução acima podemos verificar que a consulta selecionou 13 registros (A-Rows) e o tempo de execução foi de 6 centésimos de segundo (A-Time) com um custo de 954 (Cost), também podemos observar que para esse SQL_ID “801jbjyrx5dzz” tem somente um plano de execução cujo HASH é “2222044125” e CHILD NUMBER 0. Outro ponto importante a se observar é o numero de “Buffers” de memoria visitados que nesse caso foi 5.928.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

  1. Como verificar a ordem que as operações são realizadas
  2. Como interpretar os valores estatísticos estimados
  3. Como interpretar os valores estatísticos coletados durante a execução
  4. Como interpretar as seções não estatísticas do plano de execução

Plano de execução “B”

Vamos executar novamente a mesma consulta e observar o que acontece:

 

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
appuser@LAB11 > select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD)  QUANTITY_SOLD
  2    from CUSTOMERS c, SALES s
  3   where c.CUST_ID = s.CUST_ID
  4     and c.CUST_GENDER = 'F'
  5     and c.CUST_LAST_NAME like '%Bishop%'
  6    group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME                           QUANTITY_SOLD
---------- -------------------- ---------------------------------------- -------------
    100376 Dylan                Bishop                                               4
    100842 Hankil               Bishop                                               4
    100232 Chloe                Bishop                                               5
    100025 Brayden              Bishop                                               2
    100188 Hankil               Bishop                                               3
    100915 Andrew               Bishop                                               3
    100896 Noah                 Bishop                                               4
    100218 Andrew               Bishop                                               3
    100642 Aidan                Bishop                                               4
    100854 Joshua               Bishop                                               3
    100813 Andrew               Bishop                                               5
    100927 Dakota               Bishop                                               1
    100383 Dakota               Bishop                                               3

13 rows selected.

Elapsed: 00:00:00.40
appuser@LAB11 >
appuser@LAB11 > column sql_id new_value m_sql_id
appuser@LAB11 > column child_number new_value m_child_no
appuser@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%QRY-TST%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
801jbjyrx5dzz            0
801jbjyrx5dzz            1

Elapsed: 00:00:00.03
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('801jbjyrx5dzz',         1,'typical iostats last'))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  801jbjyrx5dzz, child number 1
-------------------------------------
select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME,
sum(QUANTITY_SOLD)  QUANTITY_SOLD   from CUSTOMERS c, SALES s  where
c.CUST_ID = s.CUST_ID    and c.CUST_GENDER = :"SYS_B_0"    and
c.CUST_LAST_NAME like :"SYS_B_1"   group by c.CUST_ID,
c.CUST_FIRST_NAME, CUST_LAST_NAME

Plan hash value: 3219243479

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |       |   563 (100)|          |       |       |     13 |00:00:00.39 |   18088 |
|   1 |  HASH GROUP BY                |              |      1 |     14 |   560 |   563   (6)| 00:00:07 |       |       |     13 |00:00:00.39 |   18088 |
|   2 |   NESTED LOOPS                |              |      1 |     14 |   560 |   562   (6)| 00:00:07 |       |       |     13 |00:00:00.39 |   18088 |
|   3 |    NESTED LOOPS               |              |      1 |     14 |   560 |   562   (6)| 00:00:07 |       |       |   7059 |00:00:00.38 |   11029 |
|   4 |     VIEW                      | VW_GBC_5     |      1 |     14 |   252 |   548   (6)| 00:00:07 |       |       |   7059 |00:00:00.37 |    4471 |
|   5 |      HASH GROUP BY            |              |      1 |     14 |   112 |   548   (6)| 00:00:07 |       |       |   7059 |00:00:00.37 |    4471 |
|   6 |       PARTITION RANGE ALL     |              |      1 |    918K|  7178K|   525   (2)| 00:00:07 |     1 |    28 |    918K|00:00:00.18 |    4471 |
|   7 |        TABLE ACCESS FULL      | SALES        |     28 |    918K|  7178K|   525   (2)| 00:00:07 |     1 |    28 |    918K|00:00:00.08 |    4471 |
|*  8 |     INDEX UNIQUE SCAN         | CUSTOMERS_PK |   7059 |      1 |       |     0   (0)|          |       |       |   7059 |00:00:00.01 |    6558 |
|*  9 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |   7059 |      1 |    22 |     1   (0)| 00:00:01 |       |       |     13 |00:00:00.01 |    7059 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("C"."CUST_ID"="ITEM_1")
   9 - filter(("C"."CUST_LAST_NAME" LIKE :SYS_B_1 AND "C"."CUST_GENDER"=:SYS_B_0))

Note
-----
   - cardinality feedback used for this statement


35 rows selected.

Elapsed: 00:00:00.02
appuser@LAB11 >

 

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

 

Conclusão

resumo das estatisticas

 

 

 

 

Observando o quadro acima podemos concluir que o plano com menor custo 563 (B) não é o melhor plano de execução pois o seu tempo de execução foi cinco vezes maior que o plano “A” com custo 954, Isto ocorre porque o otimizador faz uma série de estimativas e suposições para criar o plano de execução e em alguns casos essas estimativas ou suposições não são tão precisas como deveriam ser. Assim podemos concluir que trata-se de um mito a afirmação de que um plano de execução com o menor custo sempre será o melhor.

Referências

https://docs.oracle.com/cd/E18283_01/server.112/e10831/overview.htm#sthref6

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *