Oracle Database

Quanto o EXPLAIN PLAN está errado

Posted by

DISPLAY_CURSOR

É impressionante a quantidade de DBAs que ao investigar a causa de lentidão de um SQL, olham apenas o Plano de Execução através de AUTOTRACE / EXPLAIN PLAN / Toad / SQL Developer (e o que mais utilizar 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.

O EXPLAIN PLAN pode estar errado por vários motivos, e um deles são estatísticas erradas.

Para mostrar isto, primeiramente, vamos criar uma tabela simples para o teste.

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Tabela criada.

SQL> INSERT INTO T SELECT * FROM T;

73624 linhas criadas.

SQL> INSERT INTO T SELECT * FROM T;

147248 linhas criadas.

SQL> INSERT INTO T SELECT * FROM T;

294496 linhas criadas.

SQL> INSERT INTO T SELECT * FROM T;

588992 linhas criadas.

SQL> COMMIT;

Commit concluido.

SQL>

E vamos verificar qual a real cardinalidade (quantidade de linhas retornada) de dois SELECTs nesta tabela.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1177984

SQL> SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE';

  COUNT(*)
----------
      2032

SQL>

Ok, como esta tabela não tem estatísticas, estas serão coletadas (e jogadas fora) no momento do SELECT, por conta do Dynamic Sampling (parâmetro optimizer_dynamic_sampling com o valor 2, que é o padrão). Este valor padrão utiliza um sample_size de apenas 64 blocos, ou seja, é uma coleta imperfeita. Vamos verificar o que o EXPLAIN PLAN nos diz.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM T;

Explicado.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   331   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T	  | 73624 |   331   (0)| 00:00:01 |
-------------------------------------------------------------------

9 linhas selecionadas.

SQL>

Errou feio (de 1177984 para 73624), mas o problema é o EXPLAIN PLAN / DBMS_XPLAN.DISPLAY não nos diz que este erro existe. É nessa hora que um HASH_JOIN pode facilmente virar um NESTED LOOP, por conta de estatísticas erradas. É aí também que o DBA coloca uma HINT, a execução fica ótima, mas o problema fundamental não foi resolvido, só escondido, esperando para acontecer de outra forma, quando os dados mudarem.

Vamos ver se o outro SELECT também erra.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE';

Explicado.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    10 |   332   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |    10 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T	  |  3201 | 32010 |   332   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='TABLE')

14 linhas selecionadas.

SQL>

Também errou (de 2032 para 3201), mais ou menos 50% de erro. Agora veremos as estatísticas reais de execução do primeiro SELECT, com o DBMS_XPLAN.DISPLAY_CURSOR.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1177984

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	48k1njhd4ras3, child number 0
-------------------------------------
SELECT COUNT(*) FROM T

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   1 |00:00:00.87 |   19171 |  19161 |
|   1 |  SORT AGGREGATE    |	  |	 1 |	  1 |	   1 |00:00:00.87 |   19171 |  19161 |
|   2 |   TABLE ACCESS FULL| T	  |	 1 |   1182K|	1177K|00:00:00.86 |   19171 |  19161 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


18 linhas selecionadas.

SQL> SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE';

  COUNT(*)
----------
      2032

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	66u32qs43gq61, child number 0
-------------------------------------
SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE'

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   1 |00:00:00.14 |   19171 |  19161 |
|   1 |  SORT AGGREGATE    |	  |	 1 |	  1 |	   1 |00:00:00.14 |   19171 |  19161 |
|*  2 |   TABLE ACCESS FULL| T	  |	 1 |	317 |	2032 |00:00:00.14 |   19171 |  19161 |
----------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


23 linhas selecionadas.

SQL>

As colunas mais importantes aqui não a E-Rows e a A-Rows, respetivamente “Linhas Estimadas” (é o mesmo que a coluna Rows no EXPLAIN PLAN), e “Linhas Reais”. Este é um dos pontos fundamentais que você deve procurar quando analisar um plano de execução: diferenças entre E-Rows e A-Rows, o que indica que suas estatísticas estão erradas, o que pode facilmente levar o CBO a uma decisão incorreta quanto ao método de acesso.

Se as estatísticas estiverem perfeitas, o Plano de Execução do EXPLAIN PLAN será perfeito.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T');

Procedimento PL/SQL concluido com sucesso.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM T;

Explicado.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |  5410   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T	  |  1177K|  5410   (1)| 00:00:01 |
-------------------------------------------------------------------

9 linhas selecionadas.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE';

Explicado.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    10 |  5414   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |    10 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T	  |  2032 | 20320 |  5414   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='TABLE')

14 linhas selecionadas.

SQL>

E agora, como estará a relação de E-Rows x A-Rows? Também perfeita.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1177984

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	48k1njhd4ras3, child number 0
-------------------------------------
SELECT COUNT(*) FROM T

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   1 |00:00:00.28 |   19171 |  19167 |
|   1 |  SORT AGGREGATE    |	  |	 1 |	  1 |	   1 |00:00:00.28 |   19171 |  19167 |
|   2 |   TABLE ACCESS FULL| T	  |	 1 |   1177K|	1177K|00:00:00.28 |   19171 |  19167 |
----------------------------------------------------------------------------------------------


14 linhas selecionadas.

SQL> SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE';

  COUNT(*)
----------
      2032

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	66u32qs43gq61, child number 0
-------------------------------------
SELECT COUNT(*) FROM T WHERE OBJECT_TYPE = 'TABLE'

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation	   | Name | Starts | E-Rows | A-Rows |	 A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	   1 |00:00:00.14 |   19171 |  19161 |
|   1 |  SORT AGGREGATE    |	  |	 1 |	  1 |	   1 |00:00:00.14 |   19171 |  19161 |
|*  2 |   TABLE ACCESS FULL| T	  |	 1 |   2032 |	2032 |00:00:00.14 |   19171 |  19161 |
----------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='TABLE')


19 linhas selecionadas.

SQL>

4 comments

    1. Um LOB, por mais que seja, é apenas um um registro, uma linha (Rows). Onde pode ter diferença será no Bytes, irei fazer um teste a respeito.
      Imagino que não é sua escolha, mas lembrando das três pragas (ou Gremlins) do DBA Oracle: DBLinks, Triggers, e LOBs.

      Responder

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.