quinta-feira, 15 de dezembro de 2011

Índices Invisíveis no Oracle 11g


Neste artigo abordarei sobre índices invisíveis no Oracle 11g, importante dispositivo que permite testar e validar a utilização e necessidade de um índice num ambiente produtivo de banco de dados.

O Oracle 11g permite que os índices sejam marcados como invisíveis. Os índices invisíveis são mantidos como qualquer outro índice, mas eles são ignorados pelo otimizador, a menos que o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES esteja definido como TRUE. Os índices podem ser criados como invisíveis, usando a palavra-chave INVISIBLE, e sua visibilidade pode ser alterada usando o comando ALTER INDEX.

Exemplo de criação de um índice invisível:

CREATE INDEX ON () INVISIBLE;

Ativação/desativação de um índice:

ALTER INDEX INVISIBLE;
ALTER INDEX VISIBLE;

O script a abaixo cria e preenche uma tabela, em seguida, cria um índice invisível.

Criação da tabela “TEST_TAB”:

CREATE TABLE TEST_TAB(id  NUMBER);


Preenchimento de dados na tabela “TEST_TAB”.

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO TEST_TAB VALUES (i);
  END LOOP;
  COMMIT;
END;
/


Criação do índice invisível, “TEST_TAB_ID” para a tabela “TEST_TAB”.

CREATE INDEX TEST_TAB_ID ON TEST_TAB(id) INVISIBLE;


Atualização das estatísticas da tabela “TEST_TAB” e do índice “TEST_TAB_ID”.

EXEC DBMS_STATS.gather_table_stats(TEST, 'TEST_TAB', cascade=> TRUE);

Para demonstrar como um índice invisível funciona, vamos executar uma consulta usando a coluna indexada, definida na cláusula WHERE.

SET AUTOTRACE ON

SELECT * FROM test_tab WHERE id = 999;

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------==--------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     3 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Pode-se verificar que o índice foi ignorado, pois a operação de “TABLE ACCESS FULL” é apresentada no plano de execução da consulta, essa operação realiza a varredura completa da tabela desprezando o índice da tabela.

Ao definir o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE e ativando a “visibilidade” do índice, o otimizador utiliza o índice durante a execução da consulta, conforme demonstrado abaixo.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SELECT * FROM test_tab WHERE id = 999;

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

Porém, ao tornar o índice visível, mas com o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES definido como FALSE, essa instrução ainda indicará ao otimizador que o índice ainda disponível para uso.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

ALTER INDEX test_tab_id VISIBLE;

SELECT * FROM test_tab WHERE id = 9999;

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

Para resolver esse impasse deve-se modificar o índice para INVISIBLE.

Índices invisíveis podem ser uteis para processos com necessidades específicas de indexação, onde a presença dos índices pode afetar negativamente outras áreas funcionais. Eles também são úteis para testar o impacto de descartar um índice.

A visibilidade dos índices pode ser implementada configurando o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE, para todo o banco de dados através da configuração do arquivo “spfile” ou do “init.ora” ou ainda pode ser implementado através da sessão de um usuário. A ativação ou desativação desse parâmetro é dinâmica.

Para verificar os estados de visibilidade de um índice deve-se verificar a coluna “VISIBILITY” das visões [DBA | ALL | USER] _INDEXES.



Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com

segunda-feira, 5 de dezembro de 2011

Oracle Standby Database 2/2


Na segunda parte do artigo sobre Oracle Standby, descreverei os procedimentos para o gerenciamento do banco de dados.

Seguem abaixo os passos necessários para o gerenciamento de um “Standby Database”.


PARA A VERIFICAÇÃO DO STANDBY

1.   Conectar-se na base de dados primário (origem) para gerar novos “archives”.
Executar:  alter system archive log current;
alter system switch logfile;
   archive log list;

2.   Para verificar a atualização do “standby”

Executar: select sequence#, first_time, next_time
                        from v$archived_log order by sequence#;

3.   Para verificar os processos e status do “standby”

Executar: select process, status from v$managed_standby; 

4.   Para a verificação automática executar foi criado um script shell [verify_sync_stb.sh], fonte disponível no final do artigo.

5.   Para a remoção dos  “archives logs” já aplicados foi criado um script shell [clear_archives.sh], fonte disponível no final do artigo.


PARA A ATIVAÇÃO DO STANDBY EM MODO READ-ONLY

1.   Verificar se o banco de dados “standby” está no ar.
Caso esteja deve-se baixá-lo utilizando o comando abaixo:
Executar: shutdown immediate;

2.   Na seqüência, levantar o banco de dados “standby”
Executar:  startup nomount;
alter database mount standby database;
alter database open read only;

3.   Para verificar o status do “standby” executar a consulta abaixo:
Executar: select name,created,log_mode,database_role from v$database;



PARA A ATIVAÇÃO DO STANDBY EM MODO READ-WRITE

1.   Verificar as atualizações do banco de dados e ver se todos os “archives” pendentes foram aplicados.
Executar: select thread#, sequence#, first_change#, next_change# from v$log_history;

2.   Na sequência baixar o “standby” utilizando o comando abaixo:
Executar: shutdown immediate;

3.   Levantar o banco de dados “standby” usando os comandos abaixo:
Executar:  startup nomount;
alter database mount standby database;
recover standby database until cancel;
alter database activate standby database;
shutdown immediate;


4.   Levantar o “standby” :
Executar : startup;

Atenção:  A partir desse momento o banco de dados deixará de ser STANDBY e torna-se o banco de dados PRIMÁRIO.

5.   Realizar a validação do status do banco de dados
Executar: select name,created,log_mode,database_role from v$database;

6.   Levantar o serviço de listener.
Executar: lsnrctl start

Deve-re realizar um re-apontamento do arquivo TNSNAMES.ora na rede para que as aplicações possam conectar-se ao novo banco de dados primário.




Script - verify_sync_stb.sh

Script que realiza a verificação do sincronismo do standby, ao término, é enviado – via e-mail, um log contendo o tempo de início e fim do processo e também os processos de “recover” no “standby”.

Abaixo segue o código fonte:


. $HOME/.profile

export ORACLE_SID=basestd
export ORACLE_HOME=/u01/app/oracle/ora11g

ARQLOG=/home/oracle/verify_arch_updt.log

sqlplus sys/@base    as sysdba @$HOME/checksync.sql
sqlplus sys/@basestd as sysdba @$HOME/getsync.sql

cat $HOME/sync.txt > $ARQLOG

# Envia email informando status da execucao
#
MAIL_SUB="VERIFICACAO DO SINCRONISMO DO STANDBY: BASESTD"
MAIL_FILE=/home/oracle/logs/verf_mail.txt
mailx -r << seu_e-mail@empresa.com >> -s "$MAIL_SUB" << seu_e-mail@empresa.com >>  < $ARQLOG
#
# Renomeia arquivo de log
#

cd $HOME
mv sync.txt  $HOME/logs/sync_stb.`date +%d%m%H%M`


-- Conteúdo do script checksync.sql

prompt set echo off feedback off verify off trimspool on pagesize 0 linesize 255
prompt spool $HOME/sync.txt
prompt
select 'select ''   Standby Database Seq# '||sequence#||' First Time: '||to_char(first_time,'dd/mm/yyyy hh24:mi:ss')||'''||CHR(10)||'||chr(10)||
       '       ''Production Database Seq# ''||sequence#||'' First Time: ''||to_char(first_time,''dd/mm/yyyy hh24:mi:ss'')||CHR(10)||CHR(10)||'||chr(10)||
       '       ''Diferenca de Sincronizacao (minutos): ''||round((first_time-to_date('''||to_char(first_time,'yyyymmddhh24miss')||''',''yyyymmddhh24miss''))*2
4*60)'||chr(10)||
       '  from v$log_history'||chr(10)||
       ' where sequence#=(select max(sequence#) from v$log_history)'||chr(10)||
       '/'
  from v$log_history
 where sequence# = (select max(sequence#) from v$log_history)
/

select 'set lines 200 pages 200' from dual;

select 'select process, status from v$managed_standby;' from dual;

prompt exit
spool off
exit



Script - clear_archives.sh

Script que realiza a remoção de “archives” já aplicados no banco “standby”.

Abaixo segue o código fonte:


. $HOME/.profile

export ORACLE_SID=basestd
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle/ora11g

#----------------------------------------------------------
#Verifica archives a eliminar
#----------------------------------------------------------
/u01/app/oracle/ora11g/bin/sqlplus "sys/@basestd as sysdba"   @/u01/app/oracle/ora11g/bin/clear_archives.sql

-- Conteúdo do script clear_archives.sql

set head off
set pagesize 0
set feedback off
set linesize 200
set trimspool on
set echo off
spool /u01/app/oracle/ora11g/bin/clear_lista_archives.sql
select '!rm '||name from v$archived_log
where trunc(completion_time) < trunc(sysdate-1)
and   APPLIED = 'YES' and name <> 'BASESTD'
/

select 'exit;' from dual
/
spool off
@/u01/app/oracle/ora11g/bin/clear_lista_archives.sql
exit

  
Com os procedimentos acima descritos, já é possível gerenciar seu banco de dados “standby”.

Os scripts de automação, poderão ser colocado na “crontab” do usuário “oracle” para execução automatica, permitindo um controle mais efetivo do gerenciamento.



Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com