Análise de SQL com SQLTXTRACT – Criação do Teste

Vamos verificar, em um formato passo a passo, como descobrir com a ajuda do SQLTXTRACT, porque um determinado SQL, que estava sendo executado em um tempo satisfatório pra o cliente, passou de repente a apresentar um péssimo desempenho.

Antes de iniciar esta análise, precisamos criar um teste que simule de forma satisfatória este problema.

Vamos conhecer nosso ambiente de testes. Trata-se de uma tabela (T) com um índice (IDX_T, na coluna OBJECT_TYPE), com pouco mais de um milhão de linhas, criada a partir de duplicações da tabela ALL_OBJECTS.

SQL> SET AUTOTRACE ON
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1006733

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

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

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
      17248  consistent gets
	  0  physical reads
	  0  redo size
	545  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

Esta tabela possui na coluna OBJECT_TYPE alguns valores raros, como TABLE.

Repare que o índice é utilizado para o acesso a esta tabela quando pesquisamos na coluna OBJECT_TYPE, utilizando o método INDEX RANGE SCAN, o que é apropriado para um pequeno número de linhas como o valor em questão.

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

COUNT(OBJECT_TYPE)
------------------
	      2704

Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    13 |	1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |    13 |	       |	  |
|*  2 |   INDEX RANGE SCAN| IDX_T |	1 |    13 |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 22  consistent gets
	  0  physical reads
	732  redo size
	553  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

Mas esta coluna também tem um valor que representa praticamente metade da tabela, SYNONYM.

Repare que o mesmo INDEX RANGE SCAN é utilizado, quando um INDEX FAST FULL SCAN provavelmente seria mais apropriado.

Veja também que o número estimado de BYTES é o mesmo do SELECT anterior, o que evidencia que o CBO está tomando decisões erradas.

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

COUNT(OBJECT_TYPE)
------------------
	    482729

Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    13 |	1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |    13 |	       |	  |
|*  2 |   INDEX RANGE SCAN| IDX_T |	1 |    13 |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='SYNONYM')

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
       3612  consistent gets
       1244  physical reads
     150920  redo size
	554  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

A vasta maioria dos casos de decisões erradas do CBO ocorrem porque ele recebe informações erradas.

Vamos atualizas as informações que o CBO tem dos objetos utilizados por este SQL, via uma coleta de estatísticas completa.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', ESTIMATE_PERCENT=>NULL, CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>

Vamos refazer nossos SQLs, começando pelo acesso completo à tabela.

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1006733

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

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

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
      17248  consistent gets
	762  physical reads
	  0  redo size
	545  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

Dessa vez tivemos uma estimativa de ROWS idêntica à realidade.

Mas vamos ao SQL do valor TABLE.

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

COUNT(OBJECT_TYPE)
------------------
	      2704

Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    10 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |    10 |	       |	  |
|*  2 |   INDEX RANGE SCAN| IDX_T |  2704 | 27040 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 14  consistent gets
	  0  physical reads
	  0  redo size
	553  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

Continuamos com o INDEX RANGE SCAN, o que continua apropriado, e a estimativa de ROWS também está perfeita.

E agora, o SQL do valor SYNONYM.

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

COUNT(OBJECT_TYPE)
------------------
	    482729

Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation	      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    10 |  1065	(1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    10 | 	   |	      |
|*  2 |   INDEX FAST FULL SCAN| IDX_T |   482K|  4714K|  1065	(1)| 00:00:01 |
-------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
       3997  consistent gets
	  0  physical reads
	  0  redo size
	554  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

Desta vez, conhecendo totalmente os objetos, o CBO decidiu corretmente por um INDEX FAST FULL SCAN.

Vamos atrabalhar o CBO novamente, apagando todas as linhas com o valor SYNONYM, e executando novamente os dois SQLs.

SQL> DELETE FROM T WHERE OBJECT_TYPE = 'SYNONYM';

482729 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3753722170

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |	  |   482K|  4714K|  1879   (1)| 00:00:01 |
|   1 |  DELETE 	  | T	  |	  |	  |	       |	  |
|*  2 |   INDEX RANGE SCAN| IDX_T |   482K|  4714K|  1879   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='SYNONYM')

Statistics
----------------------------------------------------------
	293  recursive calls
     561748  db block gets
       2031  consistent gets
      10422  physical reads
  217985676  redo size
	871  bytes sent via SQL*Net to client
	843  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  4  sorts (memory)
	  0  sorts (disk)
     482729  rows processed

SQL> COMMIT;

Commit complete.

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

COUNT(OBJECT_TYPE)
------------------
	      2704

Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    10 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |    10 |	       |	  |
|*  2 |   INDEX RANGE SCAN| IDX_T |  2704 | 27040 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 14  consistent gets
	  0  physical reads
	  0  redo size
	553  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

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

COUNT(OBJECT_TYPE)
------------------
		 0

Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation	      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    10 |  1065	(1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    10 | 	   |	      |
|*  2 |   INDEX FAST FULL SCAN| IDX_T |   482K|  4714K|  1065	(1)| 00:00:01 |
-------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       3997  consistent gets
	  0  physical reads
	  0  redo size
	551  bytes sent via SQL*Net to client
	543  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

O CBO continua decidindo por um INDEX FAST FULL SCAN, mesmo para retornar zero linhas, pois novamente, está com informações imprecisas.

Mas saber disso executando o SQL é fácil. Na próxima postagem desta série, vamos fazer a análise como um pobre DBA que só tem a seguinte informação: de manhã estava rápido, aí ficou lento!

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.