Oracle Invisible Index – 11g

janeiro 25, 2016 - Oracle Database, Performance, Segurança - Tagged: index, indexes, invisible, INVISIBLE INDEX, visible, visible index - no comments

A partir da versão do Oracle 11g, é possível criar e alterar os índices para INVISIBLE/VISIBLE.

Ao alterar um índice para invisible, por default o otimizador do Oracle não utiliza o mesmo para montar um plano de execução. A menos, que o parâmetro optimizer_use_invisible_indexes (default FALSE) esteja definido como TRUE a nível de instância ou de sessão. Neste último, o índice também será usado apenas a nível de sessão.

Com esta feature, é possível realizar a criação de um índice invisible em produção sem afetar os planos de execução existentes. Essa possibilidade é bastante útil quando, por exemplo, não temos um ambiente de testes e precisamos validar o impacto da criação do índice direto no ambiente de produção.

Abaixo vou exemplificar o uso desta feature:

Primeiramente iremos criar e popular uma tabela e realizar a criação do índice invisible, para que possamos realizar os testes com o uso da feature invisible index.

 SQL> conn lamim/lamim
SQL> create table tst_invisible_ix (
 
Connected.
SQL>
numero number
);
SQL> BEGIN
 
2    3
Table created.
 
    INSERT INTO tst_invisible_ix VALUES (i);
 
FOR i IN 1 .. 10000 LOOP
  END LOOP;
  COMMIT;
PL/SQL procedure successfully completed.
 
END;
/  2    3    4    5    6    7
SQL> SQL>
SQL>

Antes de efetivamente realizar os testes, irei realizar uma coleta de estatística da tabela recém criada.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tst_invisible_ix', cascade=> TRUE);
 
PL/SQL procedure successfully completed.

Agora vamos validar o comportamento do plano de execução com e sem um índice em modo invisible.

SQL>  show parameter invisible;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
 
SQL>  select * from tst_invisible_ix where numero=123;
 
    NUMERO
----------
       123
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 978504830
 
--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     4 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TST_INVISIBLE_IX |     1 |     4 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("NUMERO"=123)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

No exemplo acima é possível perceber que a consulta realiza um acesso full a tabela e não utiliza o índice criado com o invisible. Para testarmos a utilização do índice, podemos usar um hint na query ou alterar o parâmetro optimizer_use_invisible_indexes a nível de sessão, evitando assim que o índice impacte em outros processos e/ou aplicações.

SQL> select * from tst_invisible_ix where numero=123;
 
    NUMERO
----------
       123
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203
 
------------------------------------------------------------------------------------------
| Id  | Operation        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                       |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IX_TST_INVISIBLE_IX01 |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("NUMERO"=123)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Podemos notar que após a alteração do parâmetro a consulta não realiza mais um acesso full e passa a utilizar o índice. Lembrando que o uso do índice ocorre apenas na sessão atual, devido a alteração do parâmetro optimizer_use_invisible_indexes.

Para tornar o índice visível para todo o banco, basta executar o comando alter index index_name visible, da mesma forma se desejarmos alterar um índice já criado para invisivel, basta executar um alter index_name inivisible.

SQL> conn lamim/lamim
Connected.
SQL> alter index LAMIM.IX_TST_INVISIBLE_IX01 visible;

Index altered.

SQL> set autotrace on
SQL> select * from lamim.tst_invisible_ix where numero=123;

    NUMERO
----------
       123


Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203

------------------------------------------------------------------------------------------
| Id  | Operation        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                       |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IX_TST_INVISIBLE_IX01 |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("NUMERO"=123)


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Afim de validar se um índice está visivel ou invisível, podemos consultar a coluna visibility da dba/user_indexes.

SQL>  select index_name, visibility from dba_indexes where index_name='IX_TST_INVISIBLE_IX01';
 
INDEX_NAME                     VISIBILIT
------------------------------ ---------
IX_TST_INVISIBLE_IX01          VISIBLE

Fonte:

http://www.lamimdba.com.br/2016/01/oracle-invisible-index-11g.html

https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm

http://www.orafaq.com/wiki/Invisible_indexes

Autor: Jhonata Lamim

MBA em Banco de Dados Oracle, formado pelo Centro Universitário de Araraquara (UNIARA), graduado em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE).

Atua com Banco de Dados Oracle desde Junho de 2010. Atualmente é DBA Senior na Exímio Soluções em TI (www.eximioti.com.br)

Principais atividade:

Implementação, migração, gerenciamento e suporte a produtos Oracle (10g, 11g, 12c, RAC), multiplataforma;
Monitoramento de ambientes 24×7;
Backup e Recovery;
Performance e Tuning;
Alta disponibilidade (HA);
EM database/grid/cloud control 12c/13c;
Conversão de databases;
Standby database / Oracle Data Guard;
Migração de dados para Oracle;

Experiência:

DBA Oracle Teiko Soluções em TI – Jun/2010 – Abr/2018
DBA Oracle, Outsourcing – Marfrig Group – Set/2013 – Abr/2018
DBA Oracle, Outsourcing – Grupo Notre Dame – Intermédica – Mar/2017 – Abr/2018
DBA Oralce, Outsourcing – Hospital Beneficiência Portuguesa de São Paulo – Set/2015 – Abr/2018
DBA Oracle, Outsourcing – Fundação São Francisco Xavier – Set/2015 – Fev/2017
DBA Oracle, Outsourcing – Unimed Grande Florianopolis – Set/2014 – Jul/2016
DBA Oracle, Outsourcing – Hospital Moinhos de Vento – Set/2014 – Set/2015
DBA Oracle, Outsourcing – Santa Casa de Misericórdia de Porto Alegre – 2013

Certificações:

OCS 12C – Oracle Real Application Clusters 12c Certified Implementation Specialist
OCS 12C – Oracle Database 12c Certified Implementation Specialist
OCE 11G – Oracle Database 11g: Performance Tuning
OCA 11G – Oracle Certified Associate Administrator
OCP 11G/12C – Oracle Certified Professional Administrator
OCS 11G – Oracle Certified Specialist
OPNCS 11G – Oracle Partner Network Certified Specialist
OCS – Oracle Linux 6 Implementation Essentials

Linkedin: https://www.linkedin.com/in/jhonata-lamim-dba-oracle-61366484/