12C : A simple approach to index monitoring

Index tracking is useful to know if the index is being used or not and then delete it.

In this post I’ll be creating a table called techdatabasket_customers, an index called techdatabasket_index and later activating index monitoring through the command “ALTER INDEX <index_name> MONITORING USAGE;” to finally use the view dba_object_usage (In previous versions of 12.1, the view was called V$OBJECT_USAGE) to get the information in the USED field with the value “YES” meaning that the index is being used and “NO” meaning that the index is not being used.

• Creating the table

SQL> CREATE TABLE techdatabasket_customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);
  2    3    4    5 
Table created.

• Creating the index

SQL> CREATE INDEX techdatabasket_index
 ON techdatabasket_customers (customer_id, customer_name)
 COMPUTE STATISTICS;
 2 3
 Index created.
 

• Enabling index monitoring

SQL>
SQL> ALTER INDEX techdatabasket_index MONITORING USAGE;

Index altered.

• Checking whether the index is being used or not

SQL> SET PAGES 2000
SET LINES 2000
SELECT *
FROM   dba_object_usage
WHERE  index_name = '&INDEX_NAME';SQL> SQL>   2    3 
Enter value for index_name: techdatabasket_index
old   3: WHERE    index_name = '&INDEX_NAME'
new   3: WHERE    index_name = 'TECHDATABASKET_INDEX'

OWNER                                                                 INDEX_NAME                                                  TABLE_NAME                                                               MON USE START_MONITORING    END_MONITORING
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- --- ------------------- -------------------
SYS                                                                 TECHDATABASKET_INDEX                                              TECHDATABASKET_CUSTOMERS                                                       YES NO  02/18/2018 11:50:35

• Disabling Index Monitoring

SQL>
SQL> ALTER INDEX techdatabasket_index NOMONITORING USAGE;

Index altered.

O monitoramento de index é útil para saber se o índice está sendo usado ou não para posteriormente deletá-lo.

Nest post estarei criando uma tabela chamada techdatabasket_customers, um índice chamado techdatabasket_index e posteriormente ativando o monitoramento do índice através do comando “ALTER INDEX <index_name> MONITORING USAGE;” para por fim utilizar a view dba_object_usage (Nas versões anteriores da 12.1, a view se chamava V$OBJECT_USAGE ) para obter as informações no campo USED com o valor “YES” significando que o índice está sendo utilizado e “NO” significando que o índice não está sendo utilizado.

• Criação da tabela

SQL> CREATE TABLE techdatabasket_customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);
  2    3    4    5 
Table created.

• Criação do index

SQL> CREATE INDEX techdatabasket_index
ON techdatabasket_customers (customer_id, customer_name)
COMPUTE STATISTICS;
2 3
Index created.

• Ativando o monitoramento do index

SQL>
SQL> ALTER INDEX techdatabasket_index MONITORING USAGE;

Index altered.

• Verificando se o índice está sendo utilizado ou não

SQL> SET PAGES 2000
SET LINES 2000
SELECT *
FROM   dba_object_usage
WHERE  index_name = '&INDEX_NAME';SQL> SQL>   2    3 
Enter value for index_name: techdatabasket_index
old   3: WHERE    index_name = '&INDEX_NAME'
new   3: WHERE    index_name = 'TECHDATABASKET_INDEX'

OWNER                                                                 INDEX_NAME                                                  TABLE_NAME                                                               MON USE START_MONITORING    END_MONITORING
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- --- ------------------- -------------------
SYS                                                                 TECHDATABASKET_INDEX                                              TECHDATABASKET_CUSTOMERS                                                       YES NO  02/18/2018 11:50:35

• Desativando o monitoramento do índice

SQL>
SQL> ALTER INDEX techdatabasket_index NOMONITORING USAGE;

Index altered.

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)