Quanto o EXPLAIN PLAN está errado – Bind Peeking

01

Um dos motivos que faz com que Desenvolvedores não considerem o Plano de Execução é que muitas vezes este não condiz com a realidade: vê-se um custo baixo mas um tempo real de execução longo, e vice-versa.

E ao investigar a causa de lentidão de um SQL, muitos DBAs olham apenas o Plano de Execução através de AUTOTRACE / EXPLAIN PLAN / Toad / SQL Developer (ou qualquer outro recurso que na verdade utiliza a PLAN_TABLE).

Um plano é um plano. Quais seus planos para amanhã? Acordar, escovar os dentes, etc. Geralmente dá certo. Esta é a mesma ideia dos Planos de Execução: eles mostram o que o CBO planeja fazer, baseado em estatísticas.

Limitações do Explain Plan:
– É o Plano, não a Execução;
Não utiliza Bind Peeking / Adaptive Cursor Sharing;
– Todas Variáveis Bind são consideradas VARCHAR2;
– Depende do ambiente de execução (Trigger de logon que altera parâmetros?);
– O AUTOTRACE TRACEONLY não executa SELECTs, mas cuidado, pois ele executa INSERT / UPDATE / DELETE.

O recurso de Bind Peeking / Adaptive Cursor Sharing faz com que o CBO considere o conteúdo da variável BIND para o plano de execução.

Abaixo um exemplo mostrando o mesmo SELECT e seu Plano de Execução (extraído via AUTOTRACE), primeiramente executada com literais, e depois com variáveis BIND.

Primeiramente, a criação de duas tabelas para o teste, com um índice na coluna OBJECT_ID, do tipo NUMBER, e a coleta de estatísticas.

[ricardo@Melquior ~]$ rlwrap sqlplus SCOTT/TIGER

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 18:11:47 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE T1 AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> CREATE INDEX T1_IDX1 ON T1(OBJECT_ID);

Index created.

SQL> CREATE TABLE T2 AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> CREATE INDEX T2_IDX1 ON T2(OBJECT_ID);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');

PL/SQL procedure successfully completed.

SQL>

E agora, a visualização do Plano de Execução com literais. Veja a estimativa de Rows e Cost.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 300
SQL> SELECT T1.OBJECT_ID, T2.OBJECT_NAME FROM T1, T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID AND T2.OBJECT_TYPE = 'SYNONYM' AND T1.OBJECT_ID BETWEEN 1000 AND 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 697438891

------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |	       |   190 |  8740 |   149	 (0)| 00:00:01 |
|*  1 |  HASH JOIN			     |	       |   190 |  8740 |   149	 (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2      |   190 |  7790 |   138	 (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN		     | T2_IDX1 |  4372 |       |    11	 (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN		     | T1_IDX1 |  4372 | 21860 |    11	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T2"."OBJECT_TYPE"='SYNONYM')
   3 - access("T2"."OBJECT_ID">=1000 AND "T2"."OBJECT_ID"<=10000)    4 - access("T1"."OBJECT_ID">=1000 AND "T1"."OBJECT_ID"<=10000) Note -----    - this is an adaptive plan SQL> VARIABLE vSTART NUMBER

E agora, compare Rows e Cost com a análise do mesmo SQL, mas com variáveis BIND.

SQL> VARIABLE vSTART NUMBER
SQL> VARIABLE vEND NUMBER
SQL> EXEC :vSTART := 1000

PL/SQL procedure successfully completed.

SQL> EXEC :vEND := 10000

PL/SQL procedure successfully completed.

SQL> SELECT T1.OBJECT_ID, T2.OBJECT_NAME FROM T1, T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID AND T2.OBJECT_TYPE = 'SYNONYM' AND T1.OBJECT_ID BETWEEN :vSTART and :vEND;

Execution Plan
----------------------------------------------------------
Plan hash value: 444482522

-------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	|     8 |   368 |    14   (0)| 00:00:01 |
|*  1 |  FILTER 			      | 	|	|	|	     |		|
|*  2 |   HASH JOIN			      | 	|     8 |   368 |    14   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2	|     8 |   328 |    12   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN		      | T2_IDX1 |   336 |	|     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN		      | T1_IDX1 |   187 |   935 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:VEND)>=TO_NUMBER(:VSTART))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T2"."OBJECT_TYPE"='SYNONYM')
   4 - access("T2"."OBJECT_ID">=TO_NUMBER(:VSTART) AND
	      "T2"."OBJECT_ID"<=TO_NUMBER(:VEND))    5 - access("T1"."OBJECT_ID">=TO_NUMBER(:VSTART) AND
	      "T1"."OBJECT_ID"<=TO_NUMBER(:VEND)) Note -----    - this is an adaptive plan SQL>

4 comments

Leave a Reply

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.