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…
Você não precisa mais criar índices no banco de dados
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 > |
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:
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
