REBUILD ou COALESCE, Qual a melhor opção?

rebuild index

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

Title of the document

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

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe um comentário

O seu endereço de e-mail não será publicado.