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?
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
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.
sh@LAB11 > SELECT * FROM V$VERSION where rownum < 2;
sh@LAB11 > create index CUSTOMERS_POSTAL_CODE_BIX on CUSTOMERS(CUST_POSTAL_CODE);
sh@LAB11 > select index_name,
4 visibility,
5 to_char(last_analyzed, ' dd/MON/yyyy') last_analyzed
6 from user_indexes
7 where table_name = 'CUSTOMERS'
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.
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;
sh@LAB11 > VARIABLE SYS_B_1 number;
sh@LAB11 > EXEC :SYS_B_1 := 59411;
sh@LAB11 > select /* tst101 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME
3 where CUST_POSTAL_CODE = :SYS_B_1;
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 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 > SELECT sql_id, child_number
488qzbmpb9c1v 0
sh@LAB11 > SELECT *
select /* tst101 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME from
customers where CUST_POSTAL_CODE = :SYS_B_1
| 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 |
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=:SYS_B_1)
sh@LAB11 > @desc CUSTOMERS
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.
sh@LAB11 > VARIABLE SYS_B_1 varchar2(5);
sh@LAB11 > EXEC :SYS_B_1 := '59411';
sh@LAB11 > select /* tst102 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME
dz494vttb896c 0
select /* tst102 */ CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME from
Plan hash value: 1026787069
| 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 |
2 - access("CUST_POSTAL_CODE"=:SYS_B_1)
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.
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".
http://intermediatesql.com/oracle/how-oracle-implicit-type-conversion-works-part-1/
http://intermediatesql.com/oracle/how-oracle-implicit-type-conversion-works-part-2/
Como pode um plano de execução de mudar de repente, quando ninguém fez qualquer alteração no banco de dados? Por nenhuma mudança, queremos dizer que não houve alterações na estrutura das tabelas, não foram adicionados ou alterados índices, nenhuma mudança relativa a "Bind Peeking", sem mudanças de parâmetros do banco,…