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…
REBUILD ou COALESCE, Qual a melhor opção?
Existe uma discussão antiga entre os administradores de banco de dados Oracle quanto a necessidade de fazer periodicamente a reorganização dos índices das tabelas, neste artigo vamos abordar esse assunto com o foco no impacto desta atividade na performance das consultas no banco. Esse assunto também é muito controverso com relação a reorganizar os índices para liberar espaço em disco, mas não vamos abordar aqui este aspecto.
Do ponto de vista de performance não precisamos fazer REBUILD dos índices, essa é uma operação onerosa que impacta drasticamente a performance das aplicações durante a execução do processo e o beneficio que ela propicia pode ser obtido pela operação COALESCE que é mais leve e não causa impacto de performance nas aplicações durante sua execução.
Neste artigo vamos demonstrar porque precisamos fazer periodicamente a operação COALESCE nos índices das tabelas que tem por características uma grande quantidade de exclusão de linhas ao longo do tempo e como a falta dessa manutenção nos índices pode impactar a performance das aplicações.
Para realizar esta simulação vamos:
1) Criar uma tabela com um índice para simulação da consulta
2) Excluir uma grande quantidade de linhas da tabela
3) Executar a consulta na tabela e verificar a estatísticas de performance
4) Executar a operação COALESCE no índice
5) Executar a consulta novamente e verificar o impacto do COALESCE nas estatísticas
1) Criar a tabela 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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > SQL > CREATE TABLE dbtw001 2 AS 3 SELECT rownum col1, 4 lpad('x',5,'x') col2 5 FROM dual 6 CONNECT BY level <= 1000000 7 / Tabela criada. SQL > SQL > create index dbtw001_idx on dbtw001(col1); Índice criado. SQL > SQL > analyze index dbtw001_idx validate structure; Índice analisado. SQL > SQL > select lf_rows, lf_blks, del_lf_rows from index_stats; LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 1000000 2226 0 SQL > |
Após a criação do índice da tabela foi executado o comando ANALYZE e a consulta na estrutura do índice indica que ele tem 1.000.000 linhas e 2.226 LEAF BLOCKS para armazenar essas linhas.
2) Excluir as linhas da tabela
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:
Vamos excluir agora 700.000 linhas da tabela e verificar como fica a estrutura do í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 | SQL > DELETE dbtw001 WHERE col1 BETWEEN 100001 AND 500000; 400000 linhas deletadas. SQL > SQL > DELETE dbtw001 WHERE col1 BETWEEN 600001 AND 900000; 300000 linhas deletadas. SQL > SQL > COMMIT; Commit concluído. SQL > SQL > analyze index dbtw001_idx validate structure; Índice analisado. SQL > SQL > select lf_rows, lf_blks, del_lf_rows from index_stats; LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 1000000 2226 700000 SQL > SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'DBTW001',method_opt=>'for all columns size auto',cascade=>true,force=>true); Procedimento PL/SQL concluído com sucesso. SQL > |
Consultando a estrutura do índice após a exclusão das linhas observamos que a quantidade de LEAF BLOCKS continua igual, ou seja, 2.226 LEAF BLOCKS. Devido a grande quantidade de linhas excluídas da tabela, agora temos na estrutura do índice uma quantidade enorme de LEAF BLOCKS vazios.
3) Executar a consulta e verificar as estatísticas
Agora vamos executar uma consulta na tabela que vai utilizar o índice criado e em seguida vamos 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 /* DBTW501 */ col1, col2 2 FROM dbtw001 3 WHERE col1 BETWEEN 599999 AND 900010; COL1 COL2 ---------- ----- 599999 xxxxx 600000 xxxxx 900001 xxxxx 900002 xxxxx 900003 xxxxx 900004 xxxxx 900005 xxxxx 900006 xxxxx 900007 xxxxx 900008 xxxxx 900009 xxxxx 900010 xxxxx 12 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 ------------- ------------ d7dm563jz7rbs 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 ('d7dm563jz7rbs', 0,'typical iostats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------- SQL_ID d7dm563jz7rbs, child number 0 ------------------------------------- SELECT /* DBTW501 */ col1, col2 FROM dbtw001 WHERE col1 BETWEEN :"SYS_B_0" AND :"SYS_B_1" Plan hash value: 3268869844 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 12 |00:00:00.01 | 676 | |* 1 | FILTER | | 1 | | | | | 12 |00:00:00.01 | 676 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW001 | 1 | 1176 | 12936 | 8 (0)| 00:00:01 | 12 |00:00:00.01 | 676 | |* 3 | INDEX RANGE SCAN | DBTW001_IDX | 1 | 1176 | | 5 (0)| 00:00:01 | 12 |00:00:00.01 | 673 | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:SYS_B_1>=:SYS_B_0) 3 - access("COL1">=:SYS_B_0 AND "COL1"<=:SYS_B_1) 22 linhas selecionadas. SQL > |
Analisando o plano de execução verificamos que o Oracle teve que ler 673 blocos de índice para retornar somente 12 linhas, fica claro que os blocos vazios estão sendo acessados inutilmente, ou seja, existe um consumo desnecessário de recursos do banco causando perda de performance na consulta.
4) Executar a operação COALESCE no índice
Para otimizar a estrutura do índice vamos executar o comando COALESCE e verificar como ela vai ficar.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL > alter index dbtw001_idx coalesce; Índice alterado. SQL > SQL > analyze index dbtw001_idx validate structure; Índice analisado. SQL > SQL > select lf_rows, lf_blks, del_lf_rows from index_stats; LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 300000 668 0 SQL > |
Consultando a estrutura do índice após o comando COALESCE verificamos que o número de LEAF BLOCKS diminuiu de 2.226 para 668, ou seja todos os blocos vazios foram excluídos da estrutura do índice e aqueles que continham poucas linhas foram unidos.
5) Executar a consulta novamente e verificar se houve melhoria
Com o índice reestruturado vamos executar a consulta novamente e verificar se haverá alguma melhoria no acesso aos blocos do í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 61 62 63 64 65 66 67 68 69 | SQL > SELECT /* DBTW502 */ col1, col2 2 FROM dbtw001 3 WHERE col1 BETWEEN 599999 AND 900010; COL1 COL2 ---------- ----- 599999 xxxxx 600000 xxxxx 900001 xxxxx 900002 xxxxx 900003 xxxxx 900004 xxxxx 900005 xxxxx 900006 xxxxx 900007 xxxxx 900008 xxxxx 900009 xxxxx 900010 xxxxx 12 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 ------------- ------------ 5fxp0gakr1h9u 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 ('5fxp0gakr1h9u', 0,'typical iostats last')) PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5fxp0gakr1h9u, child number 0 ------------------------------------- SELECT /* DBTW502 */ col1, col2 FROM dbtw001 WHERE col1 BETWEEN :"SYS_B_0" AND :"SYS_B_1" Plan hash value: 3268869844 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 12 |00:00:00.01 | 8 | |* 1 | FILTER | | 1 | | | | | 12 |00:00:00.01 | 8 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW001 | 1 | 1176 | 12936 | 8 (0)| 00:00:01 | 12 |00:00:00.01 | 8 | |* 3 | INDEX RANGE SCAN | DBTW001_IDX | 1 | 1176 | | 5 (0)| 00:00:01 | 12 |00:00:00.01 | 5 | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:SYS_B_1>=:SYS_B_0) 3 - access("COL1">=:SYS_B_0 AND "COL1"<=:SYS_B_1) 22 linhas selecionadas. SQL > |
Verificando o plano de execução acima podemos constatar que o plano de execução permanece o mesmo e que foram selecionadas as mesmas 12 linhas, porem o numero de blocos acessados na leitura do índice diminuiu de 673 para 5, tornando a consulta mais eficiente.
CONCLUSÃO
Para tabelas que tem um grande volume de exclusão de linhas ao longo do tempo, é uma boa prática agendar uma rotina periódica para fazer a operação COALESCE nos índices relacionados a essas tabelas.
Referência
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_1009.htm#SQLRF00805
