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…
Por que 100 linhas a mais numa tabela podem prejudicar o desempenho de uma consulta?
Você acredita que uma consulta numa tabela com mais de 300 mil linhas possa ter seu plano de execução alterado pelo Otimizador quando inserimos mais 100 linhas na tabela? Neste artigo vamos ver uma simulação onde isto ocorre, explicar por que ocorre e quais são as alternativas para evitar este tipo de situação.
Nossa simulação utilizará uma consulta muito simples que faz a junção em duas tabelas, uma com 300 mil linhas e outra com 1 milhão de linhas e como resultado da consulta temos 62 linhas, o plano de execução original utiliza a operação NESTED LOOPS JOIN. Após inserirmos 100 linhas na tabela de 300 mil linhas o Otimizador utilizará um novo plano de execução que passará a utilizar a operação HASH JOIN, com esta mudança no plano de execução o tempo de resposta da consulta vai aumentar 128%.
Para realizar esta simulação vamos:
1) Criar 2 tabelas com um índice numa das tabelas
2) Verificar o custo atribuído pelo Otimizador para acessar uma linha da tabela
3) Executar a consulta que faz junção das duas tabelas e verificar a estatísticas de performance
4) Executar a operação INSERT para adicionar mais 100 linhas numa das tabelas
5) Verificar o custo atribuído pelo Otimizador para acessar uma linha da tabela após a operação de INSERT
6) Executar a consulta de junção novamente e verificar o novo plano de execução gerado pelo Otimizador
1) Criar as tabelas da simulaçã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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Release 11.2.0.4.0 - 64bit Production SQL > SQL > DROP TABLE dbtw002 purge; Tabela eliminada. SQL > SQL > CREATE TABLE dbtw002 2 AS 3 SELECT rownum col1, 4 mod(rownum,100) col2, 5 'dbtimewizard.com.br' col3 6 FROM dual 7 CONNECT BY level <= 302355; Tabela criada. SQL > SQL > SQL > create index dbtw002_idx on dbtw002(col1); Índice criado. SQL > SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'DBTW002',method_opt=>'for all columns size auto',cascade=>true,force=>true); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > select blevel, leaf_blocks, clustering_factor from dba_indexes where index_name='DBTW002_IDX'; BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ---------- ----------- ----------------- 1 672 1380 SQL > SQL > SQL > DROP TABLE dbtw004 purge; Tabela eliminada. SQL > SQL > CREATE TABLE dbtw004 2 AS 3 SELECT rownum col1, 4 mod(rownum,10000) col2, 5 '*DBTIMEWIZARD*' col3 6 FROM dual 7 CONNECT BY level <= 1000000; Tabela criada. SQL > SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'DBTW004',method_opt=>'for all columns size auto',cascade=>true,force=>true); Procedimento PL/SQL concluído com sucesso. SQL > |
Observando acima a consulta na visão DBA_INDEXES verificamos que o índice criado tem BLEVEL=1 e LEAF_BLOCKS=672, isto significa que o índice tem somente um bloco (ROOT_BLOCK) que endereça os 672 LEAF BLOCKS, consequentemente para obter o ROW_ID de uma linha na tabela o banco vai acessar 2 blocos (ROOT BLOCK e 1 LEAF BLOCK).
2) Verificar o CUSTO para acessar uma linha da tabela
Vamos realizar uma consulta na tabela onde criamos o índice e selecionar apenas uma linha para verificar quantos blocos serão acessados pelo banco e qual o custo atribuído pelo Otimizador.
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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* DBTW111 */ * 2 FROM dbtw002 3 WHERE col1 = 51; COL1 COL2 COL3 ---------- ---------- ------------------- 51 51 dbtimewizard.com.br 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 '%DBTW111%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2wj8c06ccrcuy 0 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')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('2wj8c06ccrcuy', 0,'typical iostats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2wj8c06ccrcuy, child number 0 ------------------------------------- SELECT /* DBTW111 */ * FROM dbtw002 WHERE col1 = :"SYS_B_0" Plan hash value: 3681438309 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| DBTW002 | 1 | 1 | 28 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | DBTW002_IDX | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1"=:SYS_B_0) 19 linhas selecionadas. SQL > |
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:
Verificando o plano de execução acima constatamos que a operação ID 2 (INDEX RANGE SCAN) acessa 3 Blocos no Buffer Cache ( 1 ROOT BLOCK, 1 LEAF BLOCK e como o índice criado não é UNIQUE um terceiro bloco para ver se existe mais alguma linha com o valor especificado na cláusula WHERE). O custo atribuído pelo Otimizador para esta operação foi 1 e para recuperar a linha na tabela será necessário acessar mais um bloco com custo de operação igual a 1, portanto teremos um custo total de 2 para recuperar 1 linha da tabela.
3) Executar a consulta de junção das tabelas
Agora vamos executar uma consulta com a junção das duas tabelas e verificar as estatísticas no 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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SELECT /* DBTW112 */ * 2 FROM dbtw004 A, 3 dbtw002 B 4 WHERE A.col1=B.col1 5 AND A.col2 in (51,151); COL1 COL2 COL3 COL1 COL2 COL3 ---------- ---------- -------------- ---------- ---------- ------------------- 51 51 *DBTIMEWIZARD* 51 51 dbtimewizard.com.br 151 151 *DBTIMEWIZARD* 151 51 dbtimewizard.com.br 10051 51 *DBTIMEWIZARD* 10051 51 dbtimewizard.com.br 10151 151 *DBTIMEWIZARD* 10151 51 dbtimewizard.com.br . . . 300151 151 *DBTIMEWIZARD* 300151 51 dbtimewizard.com.br 62 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 '%DBTW112%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 5v1qcshkcgm74 0 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')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('5v1qcshkcgm74', 0,'typical iostats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5v1qcshkcgm74, child number 0 ------------------------------------- SELECT /* DBTW112 */ * FROM dbtw004 A, dbtw002 B WHERE A.col1=B.col1 AND A.col2 in (:"SYS_B_0",:"SYS_B_1") Plan hash value: 1144161524 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1386 (100)| | 62 |00:00:00.07 | 4146 | | 1 | NESTED LOOPS | | 1 | 200 | 10400 | 1386 (2)| 00:00:01 | 62 |00:00:00.07 | 4146 | | 2 | NESTED LOOPS | | 1 | 200 | 10400 | 1386 (2)| 00:00:01 | 62 |00:00:00.07 | 4104 | |* 3 | TABLE ACCESS FULL | DBTW004 | 1 | 200 | 4800 | 1125 (2)| 00:00:01 | 200 |00:00:00.07 | 4025 | |* 4 | INDEX RANGE SCAN | DBTW002_IDX | 200 | 1 | | 1 (0)| 00:00:01 | 62 |00:00:00.01 | 79 | | 5 | TABLE ACCESS BY INDEX ROWID| DBTW002 | 62 | 1 | 28 | 2 (0)| 00:00:01 | 62 |00:00:00.01 | 42 | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("A"."COL2"=:SYS_B_0 OR "A"."COL2"=:SYS_B_1)) 4 - access("A"."COL1"="B"."COL1") 24 linhas selecionadas. SQL > |
Analisando o plano de execução verificamos que a consulta retornou 62 linhas, o plano de execução escolhido pelo Otimizador faz a junção das duas tabelas utilizando a operação NESTED LOOPS e o tempo de duração da consulta foi 7 centésimos de segundo.
4) Executar a operação INSERT
Abaixo vamos inserir mais 100 linhas na tabela DBTW002 e verificar o BLEVEL do índice.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL > insert into dbtw002 select rownum+302355, mod(rownum,100), 'dbtimewizard.com.br' from dual connect by level <=100; 100 linhas criadas. SQL > SQL > commit; Commit concluído. SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'DBTW002',method_opt=>'for all columns size auto',cascade=>true,force=>true); Procedimento PL/SQL concluído com sucesso. SQL > SQL > select blevel, leaf_blocks, clustering_factor from dba_indexes where index_name='DBTW002_IDX'; BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ---------- ----------- ----------------- 2 673 1381 SQL > |
Consultando a estrutura do índice após a inserção das 100 linhas verificamos que o BLEVEL foi alterado para 2, ou seja a estrutura do índice agora é formada por 1 ROOT BLOCK, 2 BRANCH BLOCKS e 673 LEAF BLOCKS.
5) Verificar o CUSTO para acessar uma linha da tabela após INSERT
Vamos realizar a consulta selecionando uma linha da tabela DBTW002 novamente e verificar qual foi o custo atribuído pelo Otimizador para a consulta após a inserção de 100 linhas na tabela.
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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* DBTW113 */ * 2 FROM dbtw002 3 WHERE col1 = 51; COL1 COL2 COL3 ---------- ---------- ------------------- 51 51 dbtimewizard.com.br 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 '%DBTW113%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ g74180qy0pmtp 0 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')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('g74180qy0pmtp', 0,'typical iostats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g74180qy0pmtp, child number 0 ------------------------------------- SELECT /* DBTW113 */ * FROM dbtw002 WHERE col1 = :"SYS_B_0" Plan hash value: 3681438309 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 5 | | 1 | TABLE ACCESS BY INDEX ROWID| DBTW002 | 1 | 1 | 28 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | |* 2 | INDEX RANGE SCAN | DBTW002_IDX | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1"=:SYS_B_0) 19 linhas selecionadas. SQL > |
Observe no plano de execução acima que operação ID 2 (INDEX RANGE SCAN) agora passou a acessar 4 blocos no Buffer Cache e o custo que era igual a 1 antes do INSERT das 100 linhas, agora é 3 e o custo total da consulta passou de 2 para 4.
6) Executar a consulta de junção das tabelas novamente
Para finalizar vamos executar a mesma consulta de junção das tabelas após a inserção das 100 linhas e verificar qual foi o impacto desta alteração no 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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > SELECT /* DBTW114 */ * 2 FROM dbtw004 A, 3 dbtw002 B 4 WHERE A.col1=B.col1 5 AND A.col2 in (51,151); COL1 COL2 COL3 COL1 COL2 COL3 ---------- ---------- -------------- ---------- ---------- ------------------- 51 51 *DBTIMEWIZARD* 51 51 dbtimewizard.com.br 151 151 *DBTIMEWIZARD* 151 51 dbtimewizard.com.br 10051 51 *DBTIMEWIZARD* 10051 51 dbtimewizard.com.br . . . 300151 151 *DBTIMEWIZARD* 300151 51 dbtimewizard.com.br 62 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 '%DBTW114%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 61j8yfydn1ud4 0 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')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('61j8yfydn1ud4', 0,'typical iostats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 61j8yfydn1ud4, child number 0 ------------------------------------- SELECT /* DBTW114 */ * FROM dbtw004 A, dbtw002 B WHERE A.col1=B.col1 AND A.col2 in (:"SYS_B_0",:"SYS_B_1") Plan hash value: 248469155 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 1531 (100)| | 62 |00:00:00.16 | 5428 | |* 1 | HASH JOIN | | 1 | 200 | 10400 | 1531 (2)| 00:00:01 | 62 |00:00:00.16 | 5428 | |* 2 | TABLE ACCESS FULL| DBTW004 | 1 | 200 | 4800 | 1125 (2)| 00:00:01 | 200 |00:00:00.07 | 4020 | | 3 | TABLE ACCESS FULL| DBTW002 | 1 | 302K| 8270K| 404 (1)| 00:00:01 | 302K|00:00:00.03 | 1408 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."COL1"="B"."COL1") 2 - filter(("A"."COL2"=:SYS_B_0 OR "A"."COL2"=:SYS_B_1)) 22 linhas selecionadas. SQL > |
Analisando o plano de execução verificamos que a operação de junção escolhida pelo Otimizador passou a ser um HASH JOIN, o Otimizador escolheu este plano pois o custo do plano utilizando a operação NESTED LOOPS passou de 1386 (antes do INSERT) para 1786 (após o INSERT), a diferença de 400 foi adicionada ao custo total pois o custo para acessar uma linha no indice passou de 1 para 3 conforme demonstrado nas etapas 2 e 5. Como o custo total do plano com HASH JOIN foi de 1531 o Otimizador escolheu este plano pois o valor é menor que 1786.
Observamos também que o tempo de reposta da consulta com o novo plano de execução aumentou para 16 centésimos de segundo, um aumento considerável de 128%. Estamos falando aqui de um aumento de 9 centésimos de segundos em uma consulta, isso parece insignificante, mas temos que lembrar que algumas consultas podem executar milhões de vezes num banco e se uma destas apresentar este tipo de situação podemos ter um grande impacto no banco.
CONCLUSÃO
Por incrível que possa parecer o tipo de tabela utilizada nesse artigo é muito comum nos banco de dados, são as famosas tabelas de parâmetros das aplicações que são alteradas com pouca frequência, mas que podem apresentar uma situação semelhante a que utilizamos nesta simulação. Para prevenir este tipo de problema existem algumas opções, podemos utilizar o SQL PLAN MANAGEMENT que vai controlar as mudanças nos plano de execução e impedir uma regressão de performance, podemos fixar as estatísticas das tabelas impedindo que sejam atualizadas, podemos utilizar SQL PROFILE para fixar o plano de execução. Você pode escolher qual a melhor opção para as bases que administra.
