Tuning de intruções SQL: Prestar atenção ao plano de execução é o primeiro passo

Olá,

Em algum momento, todo DBA vai passar por aquela experiência de tentar melhorar a performance de uma instrução SQL e acreditar que o que foi feito até então vai resolver o problema de performance. Mas aí, após fazer as melhorias e executar o SQL, percebe-se que o mesmo plano de execução de baixa performance continua sendo utilizado. O objetivo desse artigo é chamar a atenção para que prestemos realmente mais atenção ao plano de execução gerado por uma instrução SQL antes de quebrar a cabeça com outras tentativas.

Avaliando a consulta abaixo na qual a performance não estava aceitável, é possível perceber alguns filtros e um JOIN entre as tabelas T1 e T2.

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND B.VALUE = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

Analisando estruturalmente as tabelas, verifiquei que as colunas que estão fazendo JOIN (B.VALUE = A.EMP_CODE)  são de tipos de dados e tamanhos diferentes.

SQL> desc T1
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 EMP_CODE                         NOT NULL NUMBER(15)
 NAME                                      VARCHAR2(255)
 HASH                                      VARCHAR2(32)

SQL> desc T2
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 DEP_CODE                        NOT NULL NUMBER(20)
 VALUE                                    VARCHAR2(4000)

Verificando os dados da tabela T2, verifiquei que não seria possível criar um índice na coluna VALUE pois o erro "ORA-01450: maximum key length (string) exceeded" seria emitido devido a limitação existente no Oracle do tamanho máximo de valores indexados de acordo com o tamanho do bloco de dados usado pela tablespace da tabela (8 KB neste caso). Bom, a idéia então foi criar um índice baseado em função na qual a mesma limitaria o tamanho da coluna VARCHAR2(4000) para o tamanho da coluna numérica da tabela T1 (EMP_CODE). Como o tamanho da coluna EMP_CODE da tabela T1 é NUMBER(15), irei criar um índice de função SUBSTR(VALUE,1,15) na tabela T2. Como na instrução SQL existem outros filtros, irei criar 2 índices conforme demonstrado abaixo.

SQL> create index idx_name_hash on t1 (name,hash) tablespace tbs_indx noparallel;
SQL> create index idx_substr_value_depcode on t2 (substr(value,1,15),dep_code) tablespace tbs_indx noparallel;

Após criados os índices e alterada a instrução SQL para utilizar a função SUBSTR, segue abaixo o plano de execução gerado:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926

11 linhas selecionadas.

Decorrido: 00:00:42

Plano de Execução
----------------------------------------------------------
Plan hash value: 540149683

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   1 |  COUNT                        |                            |       |       |            |          |
|*  2 |   HASH JOIN                   |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                         |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH              |     3 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T2                         |  5251K|    88M|   103K  (1)| 00:20:42 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."EMP_CODE"=TO_NUMBER(SUBSTR("VALUE",1,15)))
   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   5 - filter("B"."DEP_CODE"=13)

Foi possível verificar acima que a instrução SQL executou em 42 segundos, o que é péssimo, e que o índice IDX_NAME_HASH foi utilizado. No entanto, o índice de função criado na tabela T2 não foi usado e por isso está sendo realizado uma operação de TABLE ACCESS FULL na mesma em cerca de 5 milhões de linhas ao realizar uma operação de HASH JOIN. A questão é: porque o otimizador não utilizou o índice de função? Após alguns testes utilizando HINTS para forçar o uso do índice entre outras tentativas, consegui a tempo perceber que a resposta estava no próprio plano de execução na seção "Predicate Information".

É possível perceber que o Oracle implicitamente converteu o tipo de dado utilizando a função TO_NUMBER de modo a fazer o JOIN com a coluna EMP_CODE que é NUMBER(15), o que é compreensível. Neste caso, o problema pode ser resolvido com 2 opções: criar o índice como TO_NUMBER(SUBSTR(VALUE,1,15)) na tabela T1 ou utilizar a função TO_CHAR na coluna EMP_CODE como demonstrado abaixo:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = TO_CHAR(A.EMP_CODE)
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169

11 linhas selecionadas.

Decorrido: 00:00:00.09

Plano de Execução
----------------------------------------------------------
Plan hash value: 424040904

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   1 |  COUNT                        |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                           |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH                |     3 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2                           |     1 |    27 |     5   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_SUBSTR_VALUE_DEPCODE     |     4 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   6 - access(SUBSTR("VALUE",1,15)=TO_CHAR("A"."EMP_CODE") AND "B"."DEP_CODE"=13)

Pronto. Agora a instrução SQL executou em menos de 1 segundo com a utilização do índice IDX_SUBSTR_VALUE_DEPCODE.