Por que utilizar “Explain Plan” ou “AUTOTRACE” no processo de “Tuning” pode ser uma má idéia?

Explain Plan or Autotrace

Os comandos do Oracle “Explain Plan” e “AUTOTRACE” são muito utilizados para gerar o plano de execução que o otimizador vai escolher para uma determinada instrução SQL. Porem nem sempre o plano de execução gerado por estes comandos correspondem ao plano que realmente será executado pelo otimizador do banco de dados, a única maneira de descobrir qual plano de execução será utilizado para uma determinada instrução SQL é obter o plano de execução da visão dinâminca V$SQL_PLAN usando o pacote DBMS_XPLAN.DISPLAY_CURSOR ou a sua própria consulta personalizada. 

 

Isto ocorre devido ao fato de que os comandos “Explain Plan” e “AUTOTRACE” não passam pelo mesmo caminho de código que o otimizador utiliza ao determinar um plano de execução. Um dos exemplos mais simples desse comportamento é quando utilizamos “Bind Variables” na instrução SQL. O comando “Explain Plan” ignora as “Binds” enquanto o otimizador as utiliza para determinar o melhor plano de execução.

Demonstração:

Vamos criar uma tabela para o nosso teste com um índice e coletar estatísticas:

1
2
3
4
5
6
7
8
9
10
11
12
13
curso01@LAB11 > CREATE TABLE MY_OBJECTS AS SELECT * FROM DBA_OBJECTS;

Table created.

curso01@LAB11 > CREATE INDEX IDX_MY_OBJECTS ON MY_OBJECTS(OBJECT_TYPE);

Index created.

curso01@LAB11 > exec dbms_stats.GATHER_TABLE_STATS(ownname =>'CURSO01',tabname =>'MY_OBJECTS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

curso01@LAB11 >

Agora vamos executar uma consulta utilizando “Bind variable”, em seguida vamos localizar o SQL_ID dessa consulta e listar o plano de execução utilizando o pacote “DBMS_XPLAN”.

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
curso01@LAB11 > variable B1 VARCHAR2(32)
curso01@LAB11 >
curso01@LAB11 > exec :B1 := 'SYNONYM';

PL/SQL procedure successfully completed.

curso01@LAB11 >
curso01@LAB11 >
curso01@LAB11 > SELECT /* TESTE001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;
                .
                .
                .
/1cef5ab6_ProducerConsumerCons
/d2092352_ProducerConsumer1
/eb5dec48_ProducerConsumer2
/1f032_ProducerConsumerProduce
/a334bf37_ProducerConsumerCons
/10dcd7b1_ProducerConsumerProd

33997 rows selected.

curso01@LAB11 >
curso01@LAB11 > SELECT sql_id FROM gv$sql WHERE sql_text LIKE '%TESTE001%' and sql_text NOT LIKE '%gv$sql%';

SQL_ID
----------------
3usu2v5hz4q4c

1 row selected.

curso01@LAB11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('3usu2v5hz4q4c', NULL, 'TYPICAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  3usu2v5hz4q4c, child number 0
-------------------------------------
SELECT /* TESTE001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1

Plan hash value: 880823944

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   346 (100)|          |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS | 33981 |  1128K|   346   (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_TYPE"=:B1)


19 rows selected.

curso01@LAB11 >

Vamos pegar a mesma consulta e utilizar o comando “EXPLAIN PLAN” para verificar se o plano de execução gerado é igual ao anterior.

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
curso01@LAB11 > EXPLAIN PLAN FOR SELECT OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

Explained.

curso01@LAB11 > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY (NULL, NULL, 'TYPICAL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 1142617335

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |  1929 | 65586 |    85   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |  1929 | 65586 |    85   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_MY_OBJECTS |  1929 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:B1)

14 rows selected.

curso01@LAB11 >

Comparando o dois planos podemos verificar que o primeiro gerado pelo pacote “DBMS_XPLAN” fez acesso a tabela MY_OBJECTS através de FULL TABLE SCAN enquanto o segundo gerado pelo comando “EXPLAIN PLAN” acessou os registros da tabela utilizando ROWID fornecido pela operação de INDEX RANGE SCAN.

Vamos fazer mais um teste, agora utilizando o comando AUTOTRACE e verificar o plano que será gerado.

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
curso01@LAB11 > variable B1 VARCHAR2(32);
curso01@LAB11 > exec :B1 := 'SYNONYM';

PL/SQL procedure successfully completed.

curso01@LAB11 > set autotrace on
curso01@LAB11 > SELECT /* TESTE02 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------
DUAL
MAP_OBJECT
.
.
.
.
JOB_HISTORY
COE_CONFIGURATION
XMLROOT
COE_NAMESPACES
COE_DOM_HELPER
COE_UTILITIES
COE_TOOLS
CHANNELS
COUNTRIES
TIMES
COSTS
CUSTOMERS
PRODUCTS
PROMOTIONS
SALES

33997 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1142617335

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |  1929 | 65586 |    85   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |  1929 | 65586 |    85   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_MY_OBJECTS |  1929 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:B1)


Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
       3524  consistent gets
       1242  physical reads
          0  redo size
    1112471  bytes sent via SQL*Net to client
      25333  bytes received via SQL*Net from client
       2268  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
      33997  rows processed

curso01@LAB11 >

O resultado demonstra que o comando AUTOTRACE, assim como o comando “EXPLAIN PLAN” acessaram a tabela da mesma forma, através da operação INDEX RANGE SCAN.

Conclusão:

Para evitar perda de tempo analisando um plano de execução que não corresponde ao plano real que o otimizador cria e utiliza, passe a utilizar o pacote DBMS_XPLAN no seu trabalho de ajuste de desempenho das instruções SQL.

 

Referência:

http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/
https://iggyfernandez.wordpress.com/2011/12/08/the-twelve-days-of-sql-day-7/
http://tkyte.blogspot.com.br/2007/04/when-explanation-doesn-sound-quite.html
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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