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. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)