Índices Virtuais

O processo de “Tuning” de SQL normalmente requer o teste de estratégias alternativas de indexação para ver o efeito sobre os planos de execução. Adicionar índices extras para tabelas grandes pode levar uma quantidade considerável de tempo e espaço em disco. Os índices adicionais estão disponíveis para uso por outras sessões, o que pode afetar o desempenho de outras partes do aplicativo que você não está atualmente testando. Isso pode ser problemático quando você está tentando identificar problemas em um sistema de produção.

Comparado com os índices convencionais, um índice virtual não tem nenhum segmento associado, para que o tempo de criação e de espaço em disco associado são irrelevantes. Além disso, não é visto por outras sessões, para que ele não afete o funcionamento normal do sistema. Este artigo, apresen-tará um exemplo da utilização dos índices virtuais.

Primeiro, vamos criar e preencher uma tabela.

SQL> CREATE TABLE objects_tab

     AS SELECT * FROM all_objects;

SQL> ALTER TABLE objects_tab ADD (

     CONSTRAINT objects_tab_pk PRIMARY KEY (object_id));

 

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);

Vamos gerar o plano de execução.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan

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

Plan hash value: 2097082964

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |     1 |    92 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB    |     1 |    92 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | OBJECTS_TAB_PK |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=10)

Se a consulta for executada por uma coluna não indexada, o plano começa a realizar o “TABLE FULL SCAN”.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan

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

Plan hash value: 821620785

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

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

Para criar um índice virtual, basta adicionar a cláusula NOSEGMENT para a instrução CREATE INDEX.

SQL> CREATE INDEX objects_tab_object_name_vi

     ON objects_tab(object_name) NOSEGMENT;

Index Created.

Ao repetir a consulta anterior, podemos ver o índice virtual não é visível ao otimizador.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan

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

Plan hash value: 821620785

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

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

Para disponibilizar o índice virtual deve-se definir o parâmetro “ _USE_NOSEGMENT_INDEXES”.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered.

Ao repetirmos a consulta, vemos que o índice virtual é usado agora.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan

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

Plan hash value: 4006507992

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

| Id  | Operation                   | Name                       | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                            |    2 |   184 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB                |    2 |   184 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | OBJECTS_TAB_OBJECT_NAME_VI |    2 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_NAME"='USER_TABLES')

Os índices virtuais não aparecem na visão DBA_INDEXES, mas aprecem na visão DBA_OBJECTS.

SQL> SET AUTOTRACE OFF

SQL> SELECT index_name FROM user_indexes;

INDEX_NAME

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

OBJECTS_TAB_PK

1 row selected.

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME

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

OBJECTS_TAB_PK

OBJECTS_TAB_OBJECT_NAME_VI

2 rows selected.

Estatísticas podem ser geradas em índices virtuais da mesma forma como índices regulares, porém não serão geradas informações na view DBA_INDEXES.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

O banco de dados Oracle não permite criar outro índice virtual com a mesma relação de colunas, mas permite criar um índice real com a mesma relação de colunas.

SQL> CREATE INDEX objects_tab_object_name_vi2

     ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT

                                                        *

ERROR at line 1:

ORA-01408: such column list already indexed

SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.

Rubens Oliveira

DBA Oracle Consultor

O arquivo “alert.log”

Neste artigo apresentarei algumas características do arquivo “alert.log” do banco de dados Oracle, este arquivo registra cronologicamente as mensagens de  erros resultantes do funcionamento diário do banco de dados. Além disso, registra os arquivos de “traces” gerados para auxiliarem os DBAs a rastrearem as atividades dos usuários ou mesmo detalhamento dos erros ocorridos durante o processamento das aplicações.

-   Essas mensagens incluem também os “startups” e “shutdowns” da instância;

-   Mensagens para o console do operador;

-   Os nomes dos arquivos de rastreamento “trace files” ;

-   Resultados dos comandos SQL de “create”, “alter” e “drop” executados no banco de dados, nas “tablespaces” e nos segmentos de “rollback”;

-   Erros quando as visões materializadas são atualizadas (operações de “refresh”).

Além das mensagens:

·         ORA-00600  (erros internos)

·         ORA-01578  (erros de corrupção nos blocos)

·         ORA-00060  (erros de “deadlocks”)

Outras mensagens comuns no arquivo “alert.log”.

ARCx: Media recovery disabled

Esta mensagem é gravada no arquivo “alert.log” se o processo ARCH “archive process” é iniciado com o banco de dados no modo “no archive mode” (com o arquivamento automático desabilitado).

Infelizmente é possível que o processo ARCH esteja em memória sem função alguma, além de apenas estar ocupando a memória quando o banco de dados está no modo “no archive mode”.

O processo ARCH pode ser interrompido dinamicamente, usando o comando:

SQL> alter system archive log stop;

Ignoring SIGALARM

Essa mensagem é gravada no arquivo “alert.log” quando um processo espera por um semáforo e seu tempo limite se expira.

Thread 1 cannot allocate new log, sequence 1558 Checkpoint not complete

Esta mensagem de erro é gravada no arquivo “alert.log” se um processo de “checkpoint” não consegue gravar todos os blocos utilizados “dirty blocks”  no redo log.

Normalmente, esta mensagem é um sinal de que o tamanho dos “redologs” estão pequenos ou que deveria haver mais membros disponíveis.

Lendo um arquivo “alert.log”

O arquivo “alert.log” é um arquivo de texto que pode ser aberto com qualquer editor de texto. O diretório onde ele fica gravado pode ser determinado através da inicialização do parâmetro “background_dump_dest”.

Para verificar o caminho onde encontra-se o arquivo, deve-se executar a instrução abaixo:

SQL> select value from v$parameter where name = 'background_dump_dest';

Se o parâmetro “background_dump_dest” não for especificado o Oracle irá gravar o arquivo “alert.log dentro do caminho  $ORACLE_HOME/RDBMS/diretório de trace.

Lendo um arquivo “alert.log” através de uma tabela externa

Os passos abaixo permitem criar uma tabela externa ao banco de dados para que se possa verificar um arquivo de “alert.log” quando se estiver conectado no banco de dados.

--Cria um diretório no banco de dados para acesso ao arquivo “alert.log”

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

--Cria uma tabela externa apontando diretamente para o arquivo “alert.log”

create table 
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Para verificar as informações importantes de alertas do arquivo “alert.log” só é necessário estar conectado ao 

banco via SQL*Plus e executar o comando abaixo.

SQL> select msg from alert_log where msg like 'ORA-00600%';

ORA-00600: internal error code, arguments: [17034], [2940981512], [0],[],[],[],[],[]
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8],[],[],[],[],[]
ORA-00600: internal error code, arguments: [4400], [48],[],[],[]

Rotacionando o arquivo “alert.log”

Também é perfeitamente possível excluir ou renomear, o arquivo “alert.log”, (por exemplo, se ele atinge um determinado tamanho). Pode-se renomeá-lo e o banco de dados simplesmente recria um novo arquivo de “alert.log” para que na próxima ocorrência comece a atualizá-lo.

No Linux ou no Unix existe um utilitário chamado “logrotate” para automatizar essa tarefa.

Para verificar os detalhes acesse o link: http://linuxcommand.org/man_pages/logrotate8.html, neste link tudo está bem explicado, os autores capricharam na explicação (por Erik Troan - ewt@redhat.com  e        Preston Brown - pbrown@redhat.com).

Escrevendo suas próprias mensagens próprias no arquivo “alert.log”

Existe uma procedure chamada “kdswrt”, esta procedure não documentada pela Oracle, mas ela permite escrever as próprias mensagens no arquivo “alert.log”, abaixo segue a sintaxe:

SQL> execute sys.dbms_system.ksdwrt(2, 'Minha propria mensagem !!');

PL/SQL procedure successfully completed.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com