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…
O que você ainda não sabe sobre exclusão de índices?
Uma das tarefas do administrador de banco de dados é verificar periodicamente se existem índices que foram criados mas não estão sendo utilizados nos planos de execução das instruções SQL, essa tarefa é necessária pois alem de liberar o espaço em disco utilizado pelo índice, também contribui para melhoria de desempenho das instruções DML (Delete, Insert e Update). Para realizar esta tarefa normalmente utilizamos o processo de monitoração de índices que indica quais índices estão sendo utilizados, mas existem alguns cuidados que precisam ser tomados para os índices indicados por este processo, pois ele não identifica a utilização dos índices acessados por recursive calls, ou seja não podemos excluir os índices que são utilizados em constraints (Primary Key, Unique Key e Foreign Key). Acredito que todas as informações apresentadas até aqui são do conhecimento da maioria das pessoas que trabalham com banco de dados Oracle, porem alem disto existem situações em que o Otimizador do Oracle apesar de não utilizar o índice no plano de execução, utiliza as estatísticas deste índice e quando ele é excluído o Otimizar passa a escolher outro plano de execução pois já não dispõe mais destas informações estatísticas. Neste artigo vamos fazer uma demonstração prática de uma destas situações e mostrar como podemos excluir o índice de forma que o Otimizador continue utilizando o mesmo plano de execução.
Para realizar esta demonstração vamos:
1) Criar 3 tabelas com os respectivos índices para simulação da consulta
2) Executar a consulta com todos os índices criados
3) Executar a consulta novamente após a exclusão de um índice não utilizado no plano de execução
4) Criar estatísticas estendidas para que o Otimizador mantenha o plano de execução original
1) Criar as tabelas da demonstração
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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production SQL > SQL > CREATE TABLE dbtw1 2 AS 3 SELECT rownum col1, 4 MOD(level, 20) col2, 5 MOD(level, 200) col3, 6 MOD(level,1000) col4, 7 lpad('x',60,'x') col5 8 FROM dual 9 CONNECT BY level <= 10000 10 / Tabela criada. SQL > SQL > CREATE TABLE dbtw2 2 AS 3 SELECT rownum col1, 4 MOD(level, 20) col2, 5 MOD(level, 200) col3, 6 MOD(level,1000) col4, 7 lpad('y',60,'y') col5 8 FROM dual 9 CONNECT BY level <= 10 10 / Tabela criada. SQL > SQL > CREATE TABLE dbtw3 2 AS 3 SELECT rownum col1, lpad('z',60,'z') col2 4 FROM dual 5 CONNECT BY level <= 10000 6 / Tabela criada. SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'dbtw1',method_opt=>'for all columns size 1'); Procedimento PL/SQL concluído com sucesso. SQL > EXEC dbms_stats.gather_table_stats(USER,'dbtw2',method_opt=>'for all columns size 1'); Procedimento PL/SQL concluído com sucesso. SQL > EXEC dbms_stats.gather_table_stats(USER,'dbtw3',method_opt=>'for all columns size 1'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > ALTER TABLE dbtw1 ADD CONSTRAINT dbtw1pk PRIMARY KEY(col1); Tabela alterada. SQL > ALTER TABLE dbtw2 ADD CONSTRAINT dbtw2pk PRIMARY KEY(col1); Tabela alterada. SQL > ALTER TABLE dbtw3 ADD CONSTRAINT dbtw3pk PRIMARY KEY(col1); Tabela alterada. SQL > SQL > CREATE INDEX dbtw1ix ON dbtw1(col2,col3); Índice criado. SQL > CREATE INDEX dbtw2ix ON dbtw2(col2,col3); Índice criado. SQL > |
2) Executar a consulta com todos os índices
Vamos executar uma consulta que faz um JOIN das 3 tabelas criadas e verificar que o plano de execução não utilizara os índices que foram criados.
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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > WITH visao AS ( 2 SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 4 WHERE t1.col2 = t2.col2 5 AND t1.col3 = t2.col3 6 AND t1.col1 = t3.col1) 7 select /* DBTW501 */ * from visao where rownum < 51; V1 V2 V3 ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > SQL > SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada 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 '%DBTW501%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 9bp6g7q3f7293 0 1 linha selecionada. SQL > SQL > -- Gera o relatório do plano de execução da consulta SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 9bp6g7q3f7293, child number 0 ------------------------------------- WITH visao AS ( SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 WHERE t1.col2 = t2.col2 AND t1.col3 = t2.col3 AND t1.col1 = t3.col1) select /* DBTW501 */ * from visao where rownum < :"SYS_B_0" Plan hash value: 137168799 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 50 |00:00:00.01 | 119 | |* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 119 | |* 2 | HASH JOIN | | 1 | 50 | 20650 | 9 (0)| 00:00:01 | 50 |00:00:00.01 | 119 | | 3 | TABLE ACCESS FULL | DBTW2 | 1 | 10 | 670 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | |* 4 | HASH JOIN | | 1 | 1000 | 204K| 7 (0)| 00:00:01 | 810 |00:00:00.01 | 117 | | 5 | TABLE ACCESS FULL| DBTW3 | 1 | 10000 | 634K| 5 (0)| 00:00:01 | 10000 |00:00:00.01 | 101 | | 6 | TABLE ACCESS FULL| DBTW1 | 1 | 10000 | 703K| 2 (0)| 00:00:01 | 810 |00:00:00.01 | 16 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<:SYS_B_0) 2 - access("T1"."COL2"="T2"."COL2" AND "T1"."COL3"="T2"."COL3") 4 - access("T1"."COL1"="T3"."COL1") 28 linhas selecionadas. SQL > |
No plano de execução podemos verificar que foram utilizadas duas operações HASH JOIN e foram acessados 119 buffers. As estimativas de cardinalidade das operações do plano de execução podem ser consideradas precisas. Para mais informações sobre cardinalidade, leia o artigo.
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:
3) Executar a consulta novamente excluindo um índice
Agora vamos excluir o índice DBTW1IX, executar a mesma consulta novamente e verificar como ficou o plano de execução.
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 | SQL > DROP INDEX dbtw1ix ; Índice eliminado. Decorrido: 00:00:00.32 SQL > SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SQL > WITH visao AS ( 2 SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 4 WHERE t1.col2 = t2.col2 5 AND t1.col3 = t2.col3 6 AND t1.col1 = t3.col1) 7 select /* DBTW501 */ * from visao where rownum < 51; V1 V2 V3 ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada 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 '%DBTW501%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 9bp6g7q3f7293 0 1 linha selecionada. SQL > SQL > -- Gera o relatório do plano de execução da consulta SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 9bp6g7q3f7293, child number 0 ------------------------------------- WITH visao AS ( SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 WHERE t1.col2 = t2.col2 AND t1.col3 = t2.col3 AND t1.col1 = t3.col1) select /* DBTW501 */ * from visao where rownum < :"SYS_B_0" Plan hash value: 3421279105 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 13 (100)| | 50 |00:00:00.01 | 131 | |* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 131 | |* 2 | HASH JOIN | | 1 | 25 | 5100 | 13 (8)| 00:00:01 | 50 |00:00:00.01 | 131 | |* 3 | HASH JOIN | | 1 | 25 | 3475 | 7 (0)| 00:00:01 | 500 |00:00:00.01 | 117 | | 4 | TABLE ACCESS FULL| DBTW2 | 1 | 10 | 670 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | 5 | TABLE ACCESS FULL| DBTW1 | 1 | 10000 | 703K| 5 (0)| 00:00:01 | 10000 |00:00:00.01 | 115 | | 6 | TABLE ACCESS FULL | DBTW3 | 1 | 10000 | 634K| 5 (0)| 00:00:01 | 810 |00:00:00.01 | 14 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<:SYS_B_0) 2 - access("T1"."COL1"="T3"."COL1") 3 - access("T1"."COL2"="T2"."COL2" AND "T1"."COL3"="T2"."COL3") 28 linhas selecionadas. SQL > |
Como podemos observar acima, após a exclusão do índice o plano de execução ficou diferente, tornou-se menos eficiente pois passou a acessar 131 buffers para trazer as mesmas 50 linhas. Podemos observar que na operação ID 3 HASH JOIN a cardinalidade estimada E-ROWS=25 foi muito diferente da cardinalidade real da operação A-ROWS=500, o Otimizador errou feio na estimativa pois não teve acesso as informações estatísticas do índice DBTW1IX que fornecia as estatísticas das colunas COL2 e COL3 da tabela DBTW1 utilizadas na cláusula WHERE para fazer a junção das tabelas.
4) Fornecer as estatísticas necessárias para manutenção do plano otimizado
Como vimos acima a exclusão do índice DBTW1IX eliminou também estatísticas muito importantes para que o Otimizador pudesse realizar o seu trabalho de forma adequada mantendo o desempenho da consulta. Para eliminar este índice que não esta sendo utilizado e preservar as estatísticas adequadas para o Otimizador podemos criar estatísticas estendidas das colunas referenciadas no índice.
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 | SQL > SELECT dbms_stats.create_extended_stats(NULL,'DBTW1','(col2,col3)') FROM dual; DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'DBTW1','(COL2,COL3)') -------------------------------------------------------------------------------------- SYS_STUIZDYGR9#LTMLSM7RCAPTD02 1 linha selecionada. SQL > SQL > SELECT column_name, num_distinct, histogram 2 FROM user_tab_col_statistics 3 WHERE TABLE_NAME = 'DBTW1'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- COL1 10000 NONE COL2 20 NONE COL3 200 NONE COL4 1000 NONE COL5 1 NONE 5 linhas selecionadas. SQL > SQL > SQL > col extension_name FOR a40 SQL > col extension FOR a40 SQL > SELECT extension_name, extension 2 FROM user_stat_extensions 3 WHERE TABLE_NAME = 'DBTW1'; EXTENSION_NAME EXTENSION ---------------------------------------- ---------------------------------------- SYS_STUIZDYGR9#LTMLSM7RCAPTD02 ("COL2","COL3") 1 linha selecionada. SQL > SQL > EXEC dbms_stats.gather_table_stats(NULL,'DBTW1',method_opt=>'for all columns size 1'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT column_name, num_distinct, histogram 2 FROM user_tab_col_statistics 3 WHERE TABLE_NAME = 'DBTW1'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- COL1 10000 NONE COL2 20 NONE COL3 200 NONE COL4 1000 NONE COL5 1 NONE SYS_STUIZDYGR9#LTMLSM7RCAPTD02 200 NONE 6 linhas selecionadas. SQL > |
Acima utilizamos o pacote DBMS_STATS e os procedimentos CREATE_EXTENDED_STATS e GATHER_TABLE_STATS para recriar as estatísticas que existiam em função do índice que foi eliminado. Na sequência vamos executar a mesma consulta novamente e verificar se o Otimizador volta a utilizar o melhor plano de execução. Para mais informações sobre estatísticas estendidas consulte o artigo.
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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SQL > WITH visao AS ( 2 SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 4 WHERE t1.col2 = t2.col2 5 AND t1.col3 = t2.col3 6 AND t1.col1 = t3.col1) 7 select /* DBTW502 */ * from visao where rownum < 51; V1 V2 V3 ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada 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 '%DBTW502%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 0tyzfn1f8n2q7 0 1 linha selecionada. SQL > SQL > -- Gera o relatório do plano de execução da consulta SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 0tyzfn1f8n2q7, child number 0 ------------------------------------- WITH visao AS ( SELECT t1.col5 v1,t2.col5 v2,t3.col2 v3 FROM dbtw1 t1,dbtw2 t2,dbtw3 t3 WHERE t1.col2 = t2.col2 AND t1.col3 = t2.col3 AND t1.col1 = t3.col1) select /* DBTW502 */ * from visao where rownum < :"SYS_B_0" Plan hash value: 137168799 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 50 |00:00:00.01 | 119 | |* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 119 | |* 2 | HASH JOIN | | 1 | 50 | 20650 | 9 (0)| 00:00:01 | 50 |00:00:00.01 | 119 | | 3 | TABLE ACCESS FULL | DBTW2 | 1 | 10 | 670 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | |* 4 | HASH JOIN | | 1 | 1000 | 204K| 7 (0)| 00:00:01 | 810 |00:00:00.01 | 117 | | 5 | TABLE ACCESS FULL| DBTW3 | 1 | 10000 | 634K| 5 (0)| 00:00:01 | 10000 |00:00:00.01 | 101 | | 6 | TABLE ACCESS FULL| DBTW1 | 1 | 10000 | 703K| 2 (0)| 00:00:01 | 810 |00:00:00.01 | 16 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<:SYS_B_0) 2 - access("T1"."COL2"="T2"."COL2" AND "T1"."COL3"="T2"."COL3") 4 - access("T1"."COL1"="T3"."COL1") 28 linhas selecionadas. SQL > |
Após a recriação das estatísticas das colunas referenciadas no índice que foi excluído, o Otimizador voltou a estimar a cardinalidade das operações com maior precisão e voltou a utilizar o mesmo plano de execução utilizado pela consulta antes da exclusão do índice.
Referências:
http://mytracelog.blogspot.com.br/2011/09/monitorando-o-uso-de-indice-no-oracle.html
