O que você ainda não sabe sobre exclusão de índices?

Exclusão í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.

 

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

 

 
promocao2u

video#001
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *