Por que minha consulta não esta usando o índice?

Cost Base Optimizer

Existem muitas razões pelas quais o Otimizador do Oracle Database escolhe não utilizar um índice de uma tabela, algumas são bem conhecidas outras nem tanto, neste artigo vamos analisar uma consulta que aparentemente deveria utilizar um índice porem o Otimizador resolve fazer um “FULL TABLE SCAN” na tabela, esse caso especifico é bem comum e muitas vezes perdemos muito tempo para identificar a sua causa.

Nesta consulta tudo parece indicar que a utilização do índice seria uma boa escolha para o Otimizador, ela possui um filtro na cláusula WHERE cuja coluna da tabela possui um índice, esse por sua vez tem bom valor de “DENSITY” próxima de zero (0.001605136), no filtro da consulta não foi especificado uma função, porem o Otimizador escolhe um plano que não utiliza o índice, o que esta impedindo o Otimizador de utilizar o índice?

Descrição da simulação

Para analisar este caso vamos fazer duas simulações utilizando o “SAMPLE SCHEMA SH” para isso vamos criar um índice que seria utilizado pela consulta, pois originalmente o “SCHEMA SH” não possui esse índice, na primeira simulação vamos constatar que o Otimizador não vai utilizar o índice, vamos fazer uma análise do plano e identificar a causa que esta impedindo o Otimizador de utilizar o índice e na sequência executar a segunda simulação onde o Otimizador vai utilizar o índice.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução

Criando o índice

O primeiro passo em nossa simulação é a criação do índice da coluna “CUST_POSTAL_CODE” na tabela “CUSTOMERS” que será utilizado na consulta.

 

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
sh@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

sh@LAB11 > create index CUSTOMERS_POSTAL_CODE_BIX on CUSTOMERS(CUST_POSTAL_CODE);

índice criado.

sh@LAB11 > select index_name,
  2         num_rows,
  3         distinct_keys,
  4         visibility,
  5         to_char(last_analyzed, ' dd/MON/yyyy') last_analyzed
  6    from user_indexes
  7   where table_name = 'CUSTOMERS'
  8   order by 1;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS VISIBILIT LAST_ANALYZE
------------------------------ ---------- ------------- --------- ------------
CUSTOMERS_GENDER_BIX                    5             2 VISIBLE    25/AGO/2013
CUSTOMERS_MARITAL_BIX                  18            11 VISIBLE    25/AGO/2013
CUSTOMERS_PK                        55500         55500 VISIBLE    25/AGO/2013
CUSTOMERS_POSTAL_CODE_BIX           55500           623 VISIBLE    02/FEV/2016
CUSTOMERS_YOB_BIX                      75            75 VISIBLE    25/AGO/2013

sh@LAB11 >

 

Consulta que não utiliza o índice

Agora vamos executar a nossa consulta que supostamente deveria utilizar o índice criado, mas que por algum motivo não vai utiliza-lo.

 

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 >
sh@LAB11 > VARIABLE SYS_B_1     number;
sh@LAB11 >
sh@LAB11 > EXEC    :SYS_B_1     := 59411;

Procedimento PL/SQL concluído com sucesso.

sh@LAB11 >
sh@LAB11 > select /* tst101 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME
  2    from customers
  3   where CUST_POSTAL_CODE = :SYS_B_1;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
     44116 Abner                Robbinette
     44117 Abraham              Sadworth
     49102 Bailey               Thompson
     49147 Barnaby              Malone
     49114 Baird                Rogers
     49125 Baldwin              Ball
     49136 Barlow               Charron
     .....
     .....
     .....
    103296 Trinity              Moy

320 linhas selecionadas.

sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
488qzbmpb9c1v            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst101 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME   from
customers  where CUST_POSTAL_CODE = :SYS_B_1

Plan hash value: 2008213504

--------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |    320 |00:00:00.02 |    1478 |   1454 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    320 |00:00:00.02 |    1478 |   1454 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=:SYS_B_1)


19 linhas selecionadas.

sh@LAB11 >
sh@LAB11 > @desc CUSTOMERS
 Nome                                Nulo?    Tipo
 ----------------------------------- -------- ------------------------
 CUST_ID                             NOT NULL NUMBER
 CUST_FIRST_NAME                     NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                      NOT NULL VARCHAR2(40)
 CUST_GENDER                         NOT NULL CHAR(1)
 CUST_YEAR_OF_BIRTH                  NOT NULL NUMBER(4)
 CUST_MARITAL_STATUS                          VARCHAR2(20)
 CUST_STREET_ADDRESS                 NOT NULL VARCHAR2(40)
 CUST_POSTAL_CODE                    NOT NULL VARCHAR2(10)
 CUST_CITY                           NOT NULL VARCHAR2(30)
 CUST_CITY_ID                        NOT NULL NUMBER
 CUST_STATE_PROVINCE                 NOT NULL VARCHAR2(40)
 CUST_STATE_PROVINCE_ID              NOT NULL NUMBER
 COUNTRY_ID                          NOT NULL NUMBER
 CUST_MAIN_PHONE_NUMBER              NOT NULL VARCHAR2(25)
 CUST_INCOME_LEVEL                            VARCHAR2(30)
 CUST_CREDIT_LIMIT                            NUMBER
 CUST_EMAIL                                   VARCHAR2(30)
 CUST_TOTAL                          NOT NULL VARCHAR2(14)
 CUST_TOTAL_ID                       NOT NULL NUMBER
 CUST_SRC_ID                                  NUMBER
 CUST_EFF_FROM                                DATE
 CUST_EFF_TO                                  DATE
 CUST_VALID                                   VARCHAR2(1)

sh@LAB11 >

 

Verificando o plano de execução acima, observamos que o Otimizador utilizou a operação “TABLE ACCESS FULL” para acessar os registros da tabela “CUSTOMERS”, logo abaixo temos a seção “Predicate Information”, nela podemos observar que o Otimizador utilizou implicitamente a função “TO_NUMBER” para filtra os registros.

Por que o Otimizador utilizou a função “TO_NUMBER” na coluna “CUST_POSTAL_CODE” inviabilizando a utilização do índice?

A resposta está na definição da bind variable “:SYS_B_1” no inicio da simulação, a definição dessa variável é numérica, porem se verificarmos na descrição dos tipos de colunas da tabela, a coluna “CUST_POSTAL_CODE” é um “VARCHAR2(10)”, portanto para manter a compatibilidade de tipo na comparação da igualdade o Otimizador utilizou a função “TO_NUMBER” para converter o conteudo da coluna “CUST_POSTAL_CODE” para numérico o que permitirá a comparação com a “bind variable” que também é numérica.

Consulta que utiliza o índice

Na sequência vamos executar a consulta novamente só que desta vez vamos alterar a “bind variable” para o tipo “VARCHAR2” que é o mesmo tipo da coluna “CUST_POSTAL_CODE” na tabela “CUSTOMERS” e verificar se o Otimizador vai alterar o plano de execução para utilizar o índice criado.

 

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
69
70
71
72
73
74
75
76
77
78
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 >
sh@LAB11 > VARIABLE SYS_B_1     varchar2(5);
sh@LAB11 >
sh@LAB11 > EXEC    :SYS_B_1     := '59411';

Procedimento PL/SQL concluído com sucesso.

sh@LAB11 >
sh@LAB11 >
sh@LAB11 >
sh@LAB11 > select /* tst102 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME
  2    from customers
  3   where CUST_POSTAL_CODE = :SYS_B_1;

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
     44116 Abner                Robbinette
     44117 Abraham              Sadworth
     49102 Bailey               Thompson
     49147 Barnaby              Malone
     49114 Baird                Rogers
     49125 Baldwin              Ball
     .....
     .....
     .....
    103296 Trinity              Moy

320 linhas selecionadas.

sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dz494vttb896c            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /* tst102 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME   from
customers  where CUST_POSTAL_CODE = :SYS_B_1

Plan hash value: 1026787069

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |      1 |        |    320 |00:00:00.01 |     328 |    653 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS                 |      1 |     89 |    320 |00:00:00.01 |     328 |    653 |
|*  2 |   INDEX RANGE SCAN          | CUSTOMERS_POSTAL_CODE_BIX |      1 |     89 |    320 |00:00:00.01 |      25 |      7 |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("CUST_POSTAL_CODE"=:SYS_B_1)


20 linhas selecionadas.

sh@LAB11 >

 

Analisando o plano de execução acima observamos que o índice “CUSTOMERS_POSTAL_CODE_BIX” foi utilizado e como resultado tivemos uma melhoria de desempenho da ordem de 78% (Redução de leitura de Buffers de 1478 para 328), na seção “Predicate Information” verificamos que o Otimizador não utiliza função para conversão de tipo de dados pois a “bind variable” informada é do mesmo tipo que a coluna no filtro da cláusula WHERE.

 

Conclusão

Apesar de toda a evolução do Otimizador do banco de dados Oracle (CBO), existem situações em que ele consegue evitar que uma instrução SQL apresente um erro, mas não é capaz de criar um plano de execução com bom desempenho. No nosso exemplo simulamos uma situação em que a aplicação estava passando para a consulta um tipo de dado incompatível com o tipo de dado na coluna da tabela e para resolver o problema simplesmente alteramos o tipo de dado enviado pela aplicação, mas essa solução nem sempre é viável pois as vezes a aplicação pertence a terceiros e sua correção pode levar meses ou anos, nesse caso uma alternativa seria criar um “function-based index”.

Referências

http://intermediatesql.com/oracle/how-oracle-implicit-type-conversion-works-part-1/

http://intermediatesql.com/oracle/how-oracle-implicit-type-conversion-works-part-2/

 

Mídia social

 

Deixe uma resposta

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