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…
Por que o operador NOT EQUAL prejudica performance?
As instruções SQL que utilizam NOT EQUAL na cláusula WHERE em base de dados Oracle não utilizam índice no plano de execução para acessar as linhas da tabela, em algumas situações essa limitação do Otimizador pode prejudicar o desempenho das instruções SQL. Nesse artigo vamos entender porque essa limitação existe e verificar quais alternativas podemos utilizar em nossa instrução SQL para evitar que ela prejudique a performance.
Como trabalha o Otimizador
O Otimizador (CBO) é um código muito complexo que tem de lidar com inúmeros cenários diferentes para determinar o plano de execução mais eficiente possível.
O objetivo principal do Otimizador é determinar um plano de execução que consiga processar a instrução SQL o mais rápido possível, porem o tempo que ele gasta para determinar esse plano esta incluso no tempo total que a instrução SQL vai levar para realizar a tarefa solicitada ao Banco de dados, logo esse trabalho tem que ser realizado num intervalo de tempo muito pequeno, caso contrário ele pode ser o responsável pelo tempo insatisfatório para execução da instrução SQL.
Para realizar esta tarefa complexa com extrema precisão e rapidez o Otimizador utiliza vários tipos de atalhos e suposições. No entanto, esses atalhos às vezes podem ser problemáticos se não forem reconhecidos e tratados de forma adequada.
Um desses pequenos atalhos digno de nota é a forma como o Otimizador trata os operadores NOT EQUAL (e NOT IN).
Normalmente, quando temos uma situação em que dizemos que queremos o que não seja igual a alguma coisa especifica (NOT EQUAL), estamos sugerindo basicamente que estamos interessado em tudo exceto aquela coisa especifica.
Por exemplo quando temos uma condição como:
WHERE frutas <> ‘LARANJA’
Essa condição nos diz que estamos interessados em todas as frutas existentes na tabela exceto a LARANJA.
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:
Numa situação como essa, em que estamos interessados na maioria dos registros de uma tabela, sabemos que o Otimizador do Oracle prefere fazer o acesso a tabela através de uma operação FULL TABLE SCAN, pois ela é mais eficiente do que utilizar um índice para retornar a maioria dos registros da tabela.
Porem existem situações em que utilizamos o operador NOT EQUAL e a quantidade de linhas retornada é muito pequena considerando a quantidade total de registros da tabela, nesses casos a utilização de um índice seria mais eficiente.
Simulando uma consulta com NOT EQUAL
A seguir vamos criar uma tabela com índice e coletar as estatísticas, na sequência fazer uma consulta nesta tabela utilizando o operador NOT EQUAL para verificar se o Otimizador vai utilizar o índice para acessar a tabela.
Vamos criar uma tabela com 3 valores distintos (P, T e F) sendo que um deles (P) tem uma quantidade de registros muito superior aos outros dois:
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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 1 row selected. SQL > SQL > SQL > create table dbtw01 (id , result, text ) 2 as 3 select rownum, decode (mod(rownum, 30000), 0, 'F', 1, 'T', 'P'), 'DBTimeWizard - Performance and Tuning' 4 from dual connect by level <= 100000; Table created. SQL > SQL > create index dbtw01_idx on dbtw01(result); Index created. SQL > SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 254'); PL/SQL procedure successfully completed. SQL > SQL > SQL > SELECT result , count(*) 2 FROM dbtw01 3 GROUP BY result; R COUNT(*) - ---------- P 99993 T 4 F 3 3 rows selected. SQL > |
Vamos executar uma consulta onde vamos selecionar todos os registros diferentes de “P”:
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 | SQL > ALTER SESSION SET statistics_level=ALL; Session altered. SQL > SQL > select /* tst101 */ * 2 from dbtw01 3 where result != 'P'; ID R TEXT ---------- - ------------------------------------- 1 T DBTimeWizard - Performance and Tuning 30000 F DBTimeWizard - Performance and Tuning 30001 T DBTimeWizard - Performance and Tuning 60000 F DBTimeWizard - Performance and Tuning 60001 T DBTimeWizard - Performance and Tuning 90000 F DBTimeWizard - Performance and Tuning 90001 T DBTimeWizard - Performance and Tuning 7 rows selected. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > 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 ------------- ------------ dqak0zs4mj0w2 0 1 row selected. SQL > SQL > SQL > 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 */ * from dbtw01 where result != :"SYS_B_0" Plan hash value: 658621498 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 699 | |* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 7 | 7 |00:00:00.01 | 699 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RESULT"<>:SYS_B_0) 18 rows selected. SQL > |
Verificamos que apesar da consultar retornar somente 7 linhas o Otimizador escolheu fazer uma operação de FULL TABLE SCAN para acessar essas linhas na tabela.
Contornando a limitação do operador NOT EQUAL
Agora vamos reescrever a cláusula WHERE de outra forma, mas como na consulta anterior, indicando ao Otimizador que queremos valores diferentes de “P”:
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 | SQL > select /* tst102 */ * 2 from dbtw01 3 where result > 'P' 4 or result < 'P'; ID R TEXT ---------- - ------------------------------------- 30000 F DBTimeWizard - Performance and Tuning 60000 F DBTimeWizard - Performance and Tuning 90000 F DBTimeWizard - Performance and Tuning 1 T DBTimeWizard - Performance and Tuning 30001 T DBTimeWizard - Performance and Tuning 60001 T DBTimeWizard - Performance and Tuning 90001 T DBTimeWizard - Performance and Tuning 7 rows selected. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > 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 ------------- ------------ 4uf7c3a4vdmmc 0 1 row selected. SQL > SQL > SQL > 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 */ * from dbtw01 where result > :"SYS_B_0" or result < :"SYS_B_1" Plan hash value: 3833081860 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 12 | | 1 | CONCATENATION | | 1 | | 7 |00:00:00.01 | 12 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 3 | 3 |00:00:00.01 | 6 | |* 3 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 3 | 3 |00:00:00.01 | 3 | | 4 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 3 | 4 |00:00:00.01 | 6 | |* 5 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 4 | 4 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("RESULT"<:SYS_B_1) 5 - access("RESULT">:SYS_B_0) filter(LNNVL("RESULT"<:SYS_B_1)) 25 rows selected. SQL > |
Reescrevendo a consulta com os operadores “>” “OR” “<” verificamos que o Otimizador calculou que seria mais eficiente utilizar índice, nesse caso ele utilizou as estatísticas existentes na base sobre a tabela pois no código do Otimizador não existe atalhos para esses tipos de operadores. Se observarmos a quantidade de Buffers lidos na primeira consulta (699) e compararmos com a quantidade de Buffers lidos na consulta acima (12), podemos afirmar que essa consulta que utilizou índice é muito mais eficiente que a primeira.
Segunda alternativa ao operador NOT EQUAL
Outra alternativa para reescrever a cláusula WHERE seria utilizar o operador IN ():
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 | SQL > select /* tst103 */ * 2 from dbtw01 3 where result IN ('F','T'); ID R TEXT ---------- - ------------------------------------- 30000 F DBTimeWizard - Performance and Tuning 60000 F DBTimeWizard - Performance and Tuning 90000 F DBTimeWizard - Performance and Tuning 1 T DBTimeWizard - Performance and Tuning 30001 T DBTimeWizard - Performance and Tuning 60001 T DBTimeWizard - Performance and Tuning 90001 T DBTimeWizard - Performance and Tuning 7 rows selected. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%tst103%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ b11d2xwtzxjjw 0 1 row selected. SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /* tst103 */ * from dbtw01 where result IN (:"SYS_B_0",:"SYS_B_1") Plan hash value: 2152084041 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 12 | | 1 | INLIST ITERATOR | | 1 | | 7 |00:00:00.01 | 12 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 2 | 6 | 7 |00:00:00.01 | 12 | |* 3 | INDEX RANGE SCAN | DBTW01_IDX | 2 | 6 | 7 |00:00:00.01 | 5 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(("RESULT"=:SYS_B_0 OR "RESULT"=:SYS_B_1)) 21 rows selected. SQL > |
Verificando o resultado na log acima notamos que o Otimizador utilizou o índice novamente pois eliminamos a limitação NOT EQUAL ao reescrever a consulta utilizando o operador IN ().
Terceira alternativa ao operador NOT EQUAL
Outra alternativa que temos para reescrever a cláusula WHERE seria utilizar o UNION ALL:
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 | SQL > select /* tst104 */ * 2 from dbtw01 3 where result > 'P' 4 union all 5 select * 6 from dbtw01 7 where result < 'P'; ID R TEXT ---------- - ------------------------------------- 1 T DBTimeWizard - Performance and Tuning 30001 T DBTimeWizard - Performance and Tuning 60001 T DBTimeWizard - Performance and Tuning 90001 T DBTimeWizard - Performance and Tuning 30000 F DBTimeWizard - Performance and Tuning 60000 F DBTimeWizard - Performance and Tuning 90000 F DBTimeWizard - Performance and Tuning 7 rows selected. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%tst104%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 3rtvk926fj5zu 0 1 row selected. SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /* tst104 */ * from dbtw01 where result > :"SYS_B_0" union all select * from dbtw01 where result < :"SYS_B_1" Plan hash value: 2290229173 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 12 | | 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 12 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 4 | 4 |00:00:00.01 | 7 | |* 3 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 4 | 4 |00:00:00.01 | 3 | | 4 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 3 | 3 |00:00:00.01 | 5 | |* 5 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 3 | 3 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("RESULT">:SYS_B_0) 5 - access("RESULT"<:SYS_B_1) 24 rows selected. SQL > |
Novamente vamos obter o mesmo resultado com um plano de execução eficiente utilizando o índice.
Conclusão
Devido a limitação de tempo para determinar o plano de execução de uma instrução SQL, o Otimizador recorre a algumas regras pré-definidas que não são verdadeiras em todas as situações, essa limitação leva o Otimizador em alguns momentos a produzir planos de execução ineficientes, conhecer quais são esses atalhos e suposições é muito importante pois podemos avaliar se eles são validos ou não nas instruções SQL das aplicações e quando identificamos que eles não são validos podemos buscar alternativas de código SQL para contornar essas limitações.
Referências
http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/
