Quantas vezes você encontrou uma consulta usando um índice X quando você queria que ela usasse o índice Y, ou uma consulta realizando "Nested loop" para fazer um "join" entre duas tabelas quando um "Hash Join" realizaria esse trabalho muito mais rápido. Ou uma instância em que a aplicação de…
Por que utilizar “Explain Plan” ou “AUTOTRACE” no processo de “Tuning” pode ser uma má idéia?
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:
Ajudo DBAs e analistas de sistema a se destacarem em suas empresas
e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:
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
