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

fevereiro 7, 2016 V. J.A. Execution Plan, Índice

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.

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

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”.