Você não precisa mais criar índices no banco de dados

Automatic Indexing

O título deste artigo é uma das promessas do novo recurso AUTOMATIC INDEXING do banco de dados Oracle 19c, ele promete automatizar a criação, eliminação e reorganização dos índices no banco de dados. Este recurso é uma evolução fantástica em termos de desempenho para o banco de dados pois uma grande parcela dos problemas de desempenho das instruções SQL estão relacionados a falta de índices ou índices inadequados, porem precisamos ter em mente que este recurso foi introduzido agora e vai evoluir nos próximos meses e anos, como todo novo recurso ele precisa ser utilizado considerando as boas práticas que um ambiente de produção necessita.

Neste artigo vamos ver na prática como funciona o recurso AUTOMATIC INDEXING, vamos fazer uma simulação bem simples para entender como funciona o mecanismo de automatização da criação de índices.

 
 

O recurso AUTOMATIC INDEXING esta disponível somente para bases Enterprise Edition em ambientes Engineered Systems, para efeitos didáticos vamos utilizar o parâmetro “_exadata_feature_on” para testar este recurso numa base Enterprise Edition que NÃO esta instalada num Exadata, esta opção não tem suporte da Oracle e não deve ser utilizada em ambientes de produção.

Para realizar a simulação vamos passar pelas seguintes etapas:

1) Configurar a base para realizar a simulação
2) Executar uma consulta no banco com filtro na cláusula WHERE
3) Consultar o relatório de índices automáticos
4) Consultar novamente o relatório de índices automáticos após 15 minutos
5) Executar a mesma consulta novamente

1) Configurar a base para realizar a simulação

Alem de criar a tabela que será utilizada na simulação, vamos desativar o recurso SQL PLAN BASELINE para simplificar o processo de utilização do novo índice, a utilização do recurso SQL PLAN BASELINE é uma boa prática e pode ser utilizado simultaneamente com o recurso AUTOMATIC INDEXING.
 
Para esta simulação vamos utilizar o usuário DBTW e através do pacote DBMS_AUTO_INDEX vamos configurar o recurso AUTOMATIC INDEXING para atuar somente neste esquema.

 

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
SQL > SELECT banner_full FROM v$version WHERE rownum < 2;

BANNER_FULL
---------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL >
SQL > ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES=FALSE;

Sessão alterada.

SQL >
SQL > EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'DBTW', allow => TRUE);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > COL parameter_name FOR A40
SQL > COL parameter_value FOR A15
SQL >
SQL > SELECT con_id, parameter_name, parameter_value
  2    FROM cdb_auto_index_config
  3   ORDER BY 1,2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          IMPLEMENT
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema IN (DBTW)
         3 AUTO_INDEX_SPACE_BUDGET                  50

8 linhas selecionadas.

SQL >
SQL >
SQL > CREATE TABLE dbtw067 AS SELECT rownum id, S.* FROM sh.sales S;

Tabela criada.

SQL >

 

Observe acima que a consulta na visão CDB_AUTO_INDEX_CONFIG mostra que o recurso AUTOMATIC INDEXING esta ativado na modalidade “IMPLEMENT”, isto significa que os índices identificados pelo AUTOMATIC INDEXING serão criados e estarão acessíveis para utilização pelo Otimizador.

2) Executar uma consulta no banco com filtro na cláusula WHERE

Quando executamos uma consulta na tabela criada para esta simulação, o Otimizador vai utilizar a operação TABLE ACCESS FULL pois não foi criado nenhum índice para esta 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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW067.1 */ *
  2    FROM dbtw067
  3   WHERE id=4921;

        ID    PROD_ID    CUST_ID TIME_ID  CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ---------- -------- ---------- ---------- ------------- -----------
      4921         25       6273 23/01/98          2        999             1      128,32

SQL >
SQL > COL sql_id       NEW_VALUE m_sql_id
SQL > COL child_number NEW_VALUE m_child_no
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW067.1%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
81y9auy1d4b52            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('81y9auy1d4b52',         0,'iostats last'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------

SQL_ID  81y9auy1d4b52, child number 0
-------------------------------------
SELECT /* DBTW067.1 */ *   FROM dbtw067  WHERE id=4921

Plan hash value: 979757283

------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      1 |00:00:00.42 |    5071 |   5067 |
|*  1 |  TABLE ACCESS FULL| DBTW067 |      1 |      1 |      1 |00:00:00.42 |    5071 |   5067 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=4921)


18 linhas selecionadas.

SQL >

 

3) Consultar o relatório de índices automáticos

O recurso AUTOMATIC INDEXING executa um processo a cada 15 minutos que realiza algumas tarefas:
1) Identifica potenciais índices candidatos com base nas colunas que são utilizadas como filtro na cláusula WHERE.
2) Cria os índices candidatos como invisíveis para serem utilizados em planos de execução. O nome dos índices incluem o prefixo “SYS_AI”.
3) Testa as instruções SQL com os índices automáticos invisíveis para verificar se houve melhora no desempenho. Se resultado for positivo os índices são alterados para visíveis, caso contrário os índices automáticos são marcados como “UNUSUABLE” e removidos posteriormente.
 
A consulta abaixo foi realizada logo após a execução da consulta da etapa anterior e antes do processo descrito acima ser executado, por esta razão podemos observar que o relatório não registra ainda nenhum índice candidato.

 

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
SET LONG 1000000 PAGESIZE 0

SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() FROM dual;

SET pagesize 100

=========================================================================================================================================

SQL > SET LONG 1000000 PAGESIZE 0
SQL >
SQL > SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() FROM dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 20-NOV-2020 10:49:48
 Activity end                 : 20-NOV-2020 10:50:06
 Executions completed         : 1
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 Indexes created             : 0
 Space used                  : 0 B
 Indexes dropped             : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
No errors found.
--------------------------------------------------------------------------------


SQL >

 

4) Consultar novamente o relatório de índices automáticos após 15 minutos

 

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
SQL > SET LONG 1000000 PAGESIZE 0
SQL >
SQL > SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() FROM dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 20-NOV-2020 11:04:50
 Activity end                 : 20-NOV-2020 11:05:21
 Executions completed         : 1
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 0
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 17,83 MB (17,83 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (5599x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 5599x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------
| Owner | Table   | Index                | Key | Type   | Properties |
----------------------------------------------------------------------
| DBTW  | DBTW067 | SYS_AI_d4ch93a7aqtxt | ID  | B-TREE | NONE       |
----------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : DBTW

 SQL ID               : 81y9auy1d4b52

 SQL Text             : SELECT /* DBTW067.1 */ * FROM dbtw067 WHERE id=4921

 Improvement Factor   : 5599x


Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  858450                        11531
 CPU Time (s):      160498                        401
 Buffer Gets:       11202                         4
 Optimizer Cost:    1405                          4
 Disk Reads:        10157                         2
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        2                             1


PLANS SECTION
--------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 979757283

-----------------------------------------------------------------------
| Id | Operation           | Name    | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT    |         |      |       | 1405 |          |
|  1 |   TABLE ACCESS FULL | DBTW067 |    1 |    34 | 1405 | 00:00:01 |
-----------------------------------------------------------------------

- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1043730720

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    1 |    34 |    4 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DBTW067              |    1 |    34 |    4 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_d4ch93a7aqtxt |    1 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4921)


Notes
-----
- Dynamic sampling used for this statement ( level = 11 )


-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
No errors found.
--------------------------------------------------------------------------------


SQL >
SQL > SET pagesize 100

 

O novo relatório gerado mostra que o recurso AUTOMATIC INDEXING criou um índice e testou a execução da consulta utilizando este índice, o plano de execução alternativo com a utilização do índice apresenta um custo e um tempo de execução (ELAPSED TIME) muito menor.

 

5) Executar a mesma consulta novamente

 

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
SQL > SELECT /* DBTW067.2 */ *
  2    FROM dbtw067
  3   WHERE id=4866;

        ID    PROD_ID    CUST_ID TIME_ID  CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ---------- -------- ---------- ---------- ------------- -----------
      4866         25       7849 16/01/98          3        999             1      126,55

SQL >
SQL > COL sql_id       NEW_VALUE m_sql_id
SQL > COL child_number NEW_VALUE m_child_no
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW067.2%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
5gcm57nxn3k1b            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('5gcm57nxn3k1b',         0,'iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  5gcm57nxn3k1b, child number 0
-------------------------------------
SELECT /* DBTW067.2 */ *   FROM dbtw067  WHERE id=4866

Plan hash value: 1043730720

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DBTW067              |      1 |      1 |      1 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_d4ch93a7aqtxt |      1 |      1 |      1 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=4866)


19 linhas selecionadas.

SQL >

 

Verificando o plano de execução da consulta podemos constatar que o Otimizador utilizou o índice criado e o tempo de execução caiu para 1 centésimo de segundo, contra 42 centésimo de segundo da primeira execução onde não foi utilizado o índice.

 

Referências

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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