Verificar se Indices devem passar por Rebuild

(1) -- CRIAR A TABELA ONDE ARMAZENAMENTOS AS INFORMAÇÕES

CREATE TABLE MMED_INDICE_STATUS (INDEX_NAME VARCHAR(100),PCT_DELETED NUMBER,PCT_USED NUMBER,BLOCK NUMBER,DTEXECUAO DATE DEFAULT SYSDATE) ;

ALTER TABLE MMED_INDICE_STATUS ADD CONSTRAINT PK_MMED_IND_STATUS PRIMARY KEY (INDEX_NAME,DTEXECUAO) ;


(2) -- RODAR A QUERY ABAIXO E PEGAR O RESULTADO DELA PARA EXECUAR

SELECT COMM
FROM(
SELECT 1 AS ID,
ROWNUM AS ID2,
'ANALYZE INDEX ' ||INDEX_NAME || ' VALIDATE STRUCTURE ; ' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'

UNION ALL

SELECT 2 AS ID,
ROWNUM ID2,
'INSERT INTO MMED_INDICE_STATUS SELECT NAME,TRUNC(100*(NVL(DEL_LF_ROWS,1)/NVL(LF_ROWS,1))) PCT_DELETED, PCT_USED ,BLOCKS ,SYSDATE FROM INDEX_STATS ;' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'
ORDER BY ID2,ID
)

(3) -- EXECUTAR O RESULTADO DO PASSO 3 E DAR COMMIT .

COMMIT ;



(4) -- VERIFICAR OS INDICES COM MAIS DE 30 % DE FRAGMENTAÇÃO

SELECT DISTINCT A.* ,B.TABLE_NAME
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100
ORDER BY PCT_DELETED DESC


(5) -- AJUSTA OS INDICES QUE FORAM ENCONTADOS COM FRAGMENTAÇÃO MAIOR QUE 20 %

-- RODAR A QUERY ABEIXO E DEPOIS EXECUTAR O RESULTADO

SELECT DISTINCT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD TABLESPACE TSP_CLINICA_INDEX ; ' AS RESULTADO
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100

Views importantes

Segue uma lista de Views importantes que podem ser utilizadas mesmo com o banco de dados não estando no estado de open .

Views que podem ser utilizadas no estado de NOMOUNT

V$PARAMETER : Mostra informações sobre os parametros de inicialização do sistema.
V$SGA : Informações sobre a SGA da Instancia.
V$OPTION : opções da instancia ,exemplo se está em um ambiente em cluster ou se está utilizando ASM.
V$SESSION : informações sobre as sessões existentes na Instancia.
V$VERSION : Informações sobre a versões dos aplicativos que juntos formas o seu RDBMS.
V$INSTANCE : Informações básicas da instancia oracle.

Views que podem ser utilizados no estado de MOUNT.

V$CONTROLFILE : informações sobre os arquivos de controle.
V$DATAFILE : informações sobre os data files componentes das Tablespaces.
V$LOGFILE : informações dos arquivos de Redo Log que estiverem online.

Documentação Oracle

Muitos não conhecem mas a oracle tem um site oficial e aberto com toda documentação disponível sobre os seus produtos.Vejo alguns DBA reclamarem que este site não é tão atualizado como o MetaLink mas acredito que seja uma boa fonte de ganho de conhecimento.Segue a dica :

http://www.oracle.com/technetwork/indexes/documentation/index.html

Dicas Para Otimização de Queries

Pessoal ,Segue algumas dicas que devemos sempre pensar quando motamos um novo código Sql ,principalmente nas aplicações.

(1) Entenda como utilizar o Parce do Oracle.Isso Economiza Shared Pool

SELECT sysdate from dual ; é diferente de select sysdate from dual ;

Select * from Emp where id_emp = :pvar1 é diferente de Select * from Emp where id_emp = :pvar2

(2) Conheca os dados da sua aplicacao no momento da criacao de seu Sql.Seja seletivo na informacao que você ira trazer na sua consulta Sql.

(3) Conheça as Colunas candidatas a índices são apenas aquelas que são mencionadas após WHERE e AND, em uma cláusula SQL;

(4) não use colunas como índice se esta é sempre mencionada através do uso de uma função (exemplo, FLOOR ou ABS), ou manipulacão de string e conversão (exemplo, SUBSTR ou TO_NUMBER);

Exemplo: SELECT nm FROM empregado WHERE SUBSTR(nome,1,5) = ‘BRUNO’;

(5) Durante o processo de seleção das colunas para criaçao de um índices compostos, verifique aquelas colunas que são utilizadas no WHERE e AND juntas durante a aplicação em muitas situaçoes. Se elas são utilizadas separadamente no WHERE e AND tanto quanto em conjunto, dois índices simples pode ser melhor;

(6) Muito CUIDADO com Inner e Left Joins de suas SQL !!! Um query mal montada não tem indice que resolva !!!