{"id":15,"date":"2015-10-31T21:14:51","date_gmt":"2015-10-31T23:14:51","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=15"},"modified":"2019-11-11T19:05:42","modified_gmt":"2019-11-11T22:05:42","slug":"por-que-utilizar-explain-plan-ou-autotrace-no-processo-de-tuning-pode-ser-uma-ma-ideia-2","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-utilizar-explain-plan-ou-autotrace-no-processo-de-tuning-pode-ser-uma-ma-ideia-2\/","title":{"rendered":"Por que utilizar “Explain Plan” ou “AUTOTRACE” no processo de “Tuning” pode ser uma m\u00e1 id\u00e9ia?"},"content":{"rendered":"

Os comandos do Oracle “Explain Plan” e “AUTOTRACE” s\u00e3o muito utilizados para gerar\u00a0o plano de execu\u00e7\u00e3o que o otimizador vai escolher para uma determinada instru\u00e7\u00e3o SQL. Porem nem sempre o plano de execu\u00e7\u00e3o gerado por estes comandos correspondem ao plano que realmente ser\u00e1 executado pelo otimizador do banco de dados, a \u00fanica maneira de descobrir qual plano de execu\u00e7\u00e3o ser\u00e1 utilizado para uma determinada instru\u00e7\u00e3o SQL \u00e9 obter o plano de execu\u00e7\u00e3o da vis\u00e3o din\u00e2minca V$SQL_PLAN usando o pacote DBMS_XPLAN.DISPLAY_CURSOR ou a sua pr\u00f3pria consulta personalizada.\u00a0<\/span><\/p>\n

 <\/p>\n

Isto ocorre devido ao fato de que os comandos “Explain Plan” e “AUTOTRACE” n\u00e3o passam pelo mesmo caminho de c\u00f3digo que o otimizador utiliza ao determinar um plano de execu\u00e7\u00e3o. Um dos exemplos mais simples desse comportamento \u00e9 quando utilizamos “Bind Variables” na instru\u00e7\u00e3o SQL. O comando “Explain Plan” ignora as “Binds” enquanto o otimizador as utiliza para determinar o melhor plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Demonstra\u00e7\u00e3o:<\/span><\/h2>\n

Vamos criar uma tabela para o nosso teste com um \u00edndice e coletar estat\u00edsticas:<\/span><\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
<\/div><\/td>
curso01@<\/span>LAB11 ><\/span> CREATE TABLE MY_OBJECTS AS<\/span> SELECT *<\/span> FROM DBA_OBJECTS;<\/span>
\n
\nTable created.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span> CREATE INDEX IDX_MY_OBJECTS ON MY_OBJECTS(<\/span>OBJECT_TYPE)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span> exec<\/span><\/a> dbms_stats.<\/span>GATHER_TABLE_STATS(<\/span>ownname =><\/span>'CURSO01'<\/span>,<\/span>tabname =><\/span>'MY_OBJECTS'<\/span>,<\/span>method_opt=><\/span>'FOR ALL INDEXED COLUMNS SIZE 254'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Agora vamos executar uma consulta utilizando “Bind variable”, em seguida vamos localizar o SQL_ID dessa consulta e listar o plano de execu\u00e7\u00e3o utilizando o pacote “DBMS_XPLAN”.<\/span><\/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
<\/div><\/td>
curso01@<\/span>LAB11 ><\/span> variable B1 VARCHAR2(<\/span>32<\/span>)<\/span>
\ncurso01@<\/span>LAB11 ><\/span>
\ncurso01@<\/span>LAB11 ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 'SYNONYM'<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span>
\ncurso01@<\/span>LAB11 ><\/span>
\ncurso01@<\/span>LAB11 ><\/span> SELECT \/* TESTE001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n                .<\/span>
\n                .<\/span>
\n                .<\/span>
\n\/<\/span>1cef5ab6_ProducerConsumerCons
\n\/<\/span>d2092352_ProducerConsumer1
\n\/<\/span>eb5dec48_ProducerConsumer2
\n\/<\/span>1f032_ProducerConsumerProduce
\n\/<\/span>a334bf37_ProducerConsumerCons
\n\/<\/span>10dcd7b1_ProducerConsumerProd
\n
\n33997<\/span> rows selected.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span>
\ncurso01@<\/span>LAB11 ><\/span> SELECT sql_id FROM gv$sql<\/span> WHERE sql_text LIKE '%TESTE001%'<\/span> and sql_text NOT LIKE '%gv$sql%'<\/span>;<\/span>
\n
\nSQL_ID
\n----------------<\/span>
\n3usu2v5hz4q4c
\n
\n1<\/span> row selected.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM TABLE (<\/span>DBMS_XPLAN.<\/span>display_cursor (<\/span>'3usu2v5hz4q4c'<\/span>,<\/span> NULL<\/span>,<\/span> 'TYPICAL'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  3usu2v5hz4q4c,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* TESTE001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 880823944<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name       |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>            |<\/span>       |<\/span>       |<\/span>   346<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span> 33981<\/span> |<\/span>  1128K|<\/span>   346<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Vamos pegar a mesma consulta e utilizar o comando “EXPLAIN PLAN” para verificar se o plano de execu\u00e7\u00e3o gerado \u00e9 igual ao anterior.<\/span><\/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
<\/div><\/td>
curso01@<\/span>LAB11 ><\/span> EXPLAIN PLAN FOR<\/span> SELECT OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nExplained.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM TABLE (<\/span>DBMS_XPLAN.<\/span>DISPLAY (<\/span>NULL<\/span>,<\/span> NULL<\/span>,<\/span> 'TYPICAL'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------<\/span>
\nPlan hash<\/span><\/a> value:<\/span> 1142617335<\/span>
\n
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name           |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>                |<\/span>  1929<\/span> |<\/span> 65586<\/span> |<\/span>    85<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> MY_OBJECTS     |<\/span>  1929<\/span> |<\/span> 65586<\/span> |<\/span>    85<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_MY_OBJECTS |<\/span>  1929<\/span> |<\/span>       |<\/span>     6<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\n14<\/span> rows selected.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Comparando o dois planos podemos verificar que o primeiro gerado pelo pacote\u00a0“DBMS_XPLAN” fez acesso a tabela\u00a0MY_OBJECTS atrav\u00e9s de FULL TABLE SCAN enquanto o segundo gerado pelo comando\u00a0“EXPLAIN PLAN” acessou os registros da tabela utilizando ROWID fornecido pela opera\u00e7\u00e3o de INDEX RANGE SCAN.<\/span><\/p>\n

Vamos fazer mais um teste, agora utilizando o comando AUTOTRACE e verificar o plano que ser\u00e1 gerado.<\/span><\/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
<\/div><\/td>
curso01@<\/span>LAB11 ><\/span> variable B1 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\ncurso01@<\/span>LAB11 ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 'SYNONYM'<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\ncurso01@<\/span>LAB11 ><\/span> set autotrace on
\ncurso01@<\/span>LAB11 ><\/span> SELECT \/* TESTE02 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nOBJECT_NAME
\n--------------------------------------------------------------------------------------------------------<\/span>
\nDUAL
\nMAP_OBJECT
\n.<\/span>
\n.<\/span>
\n.<\/span>
\n.<\/span>
\nJOB_HISTORY
\nCOE_CONFIGURATION
\nXMLROOT
\nCOE_NAMESPACES
\nCOE_DOM_HELPER
\nCOE_UTILITIES
\nCOE_TOOLS
\nCHANNELS
\nCOUNTRIES
\nTIMES
\nCOSTS
\nCUSTOMERS
\nPRODUCTS
\nPROMOTIONS
\nSALES
\n
\n33997<\/span> rows selected.<\/span>
\n
\n
\nExecution Plan
\n----------------------------------------------------------<\/span>
\nPlan hash<\/span><\/a> value:<\/span> 1142617335<\/span>
\n
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name           |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>                |<\/span>  1929<\/span> |<\/span> 65586<\/span> |<\/span>    85<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> MY_OBJECTS     |<\/span>  1929<\/span> |<\/span> 65586<\/span> |<\/span>    85<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_MY_OBJECTS |<\/span>  1929<\/span> |<\/span>       |<\/span>     6<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\n
\nStatistics
\n----------------------------------------------------------<\/span>
\n         35<\/span>  recursive calls
\n          0<\/span>  db block gets
\n       3524<\/span>  consistent gets
\n       1242<\/span>  physical reads
\n          0<\/span>  redo size
\n    1112471<\/span>  bytes sent via SQL*<\/span>Net to client
\n      25333<\/span>  bytes received via SQL*<\/span>Net from client
\n       2268<\/span>  SQL*<\/span>Net roundtrips to\/<\/span>from client
\n          8<\/span>  sorts (<\/span>memory)<\/span>
\n          0<\/span>  sorts (<\/span>disk)<\/span>
\n      33997<\/span>  rows processed
\n
\ncurso01@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

O resultado demonstra que o comando\u00a0AUTOTRACE, assim como o\u00a0comando\u00a0“EXPLAIN PLAN” acessaram a tabela da mesma forma,\u00a0atrav\u00e9s da opera\u00e7\u00e3o INDEX RANGE SCAN.<\/span><\/p>\n

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

Para evitar perda de tempo analisando um plano de execu\u00e7\u00e3o que n\u00e3o 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\u00e7\u00f5es SQL.<\/span><\/p>\n

 <\/p>\n

Refer\u00eancia:<\/span><\/h2>\n

http:\/\/kerryosborne.oracle-guy.com\/2008\/10\/explain-plan-lies\/<\/span>
\nhttps:\/\/iggyfernandez.wordpress.com\/2011\/12\/08\/the-twelve-days-of-sql-day-7\/<\/span>
\nhttp:\/\/tkyte.blogspot.com.br\/2007\/04\/when-explanation-doesn-sound-quite.html<\/span>
\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":"

Os comandos do Oracle “Explain Plan” e “AUTOTRACE” s\u00e3o muito utilizados para gerar\u00a0o plano de execu\u00e7\u00e3o que o otimizador vai escolher para uma determinada instru\u00e7\u00e3o SQL. Porem nem sempre o plano de execu\u00e7\u00e3o gerado por estes comandos correspondem ao plano que realmente ser\u00e1 executado pelo otimizador do banco de dados, a \u00fanica maneira de descobrir qual plano de execu\u00e7\u00e3o ser\u00e1<\/p>\n","protected":false},"author":2,"featured_media":538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[5,6,7,8,13,10,11,12],"class_list":["post-15","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","tag-autotrace","tag-cbo","tag-dbms_xplan","tag-explain-plan","tag-melhorar-desempenho","tag-otimizador","tag-plano-de-execucao","tag-tuning"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t