Gerenciando Desempenho do Banco de Dados - PARTE 2

Obtendo Estátisticas

É importante coletar estátisticas regularmente para que o banco de dados não perca desempenho e tambem para que o banco de estátisticas seja atualizado constantemente. Para atualizar as estátisticas pode-se usar o metodo automático ou manual com o comando ANALYSE, o pacote DBMS_STATS ou pelo Database Control.

Obter estátisticas faz com que o desempenho melhore, mas a propria obtenção poderá impor uma carga sobre o banco de dados (terá um impacto perceptivel quando o processos estiver carregando)

Para acessar pelo Database Control proceda com os seguintes passos
Administration > Schema > Tables > escolha a tabela > selecione a opção Gather Staticts na caixa Drop And Down Actions

Segue abaixo uma descrição dos parametros da procedure de estátisticas do Oracle
DBMS_STATS.GATHER_SCHEMA_STATS
- OWNNAME - Esquema a ser analisado
- ESTIMATE_PERCENT - Controla o quanto das tabelas será analisado (amostragem significativa)
- GRANULARITY - Refe-se a melhor forma de se analisar objetos que consistem de subobjetos
- BLOCK_SAMPLE - Determina se a amostragem da tabela deverá ser feita por linha ou por bloco. O padrão por linha é mais preciso só que é mais demorado
- CASCADE - Controla se objetos dependentes como indices deverão ser analisados
- DEGREE - Controla o numero de servidores da execução paralela a ser usado na tarefa
- METHOD_OPT - Controla para quais colunas deverão ser construido histogramas e quantos buckets eles devem ter. A configuração fornceda permite ao otimizador que ele decida.
- OPTIONS - Determina quais objetos analisar. A configuração data instrui o Oracle analisar estatística de todos objetos sem estátisticas e tambem de todos os objetos que o Oracle considera que esteja desatualizado.

Segue abaixo a chamada da procedure:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME => 'HR',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
GRANULARITY=>'DEFAULT',
BLOCK_SAMPLE => FALSE,
CASCADE=>TRUE,
DEGREE=>DBMS_STATS_DEFAULT_DEGREE,
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',
OPTIONS=>'GATHER_AUTO'
);
END;

O procedimentos DBMS_STATS.GATHER_STATS_JOB_PROC foi elaborado para ser executado através do scheduler. Ele faz o procedimento igual o anterior só que analisa todo o banco de dados além disso analisa os objetos que o Oracle julga ser mais importante.

MAINTENANCE_WINDOW_GROUP - Instrui o Oracle executar a tarefa todas as noites e fins de semana durante janelas pré configuradas pelos parametros WEEKEND_WINDOW e WEEKNIGHT_WINDOW.

Instance - V$INSTANCE, V$SYSSTAT - Preenchidas no modo: NOMOUNT
Control-File V$DATABASE, V$DATAFILE - Preechidas no modo: MOUNT
Dicionário de dados DBA_..., ALL_..., USER_... - Preenchidas no modo: OPEN

Visões de monitoração de desempenho
V$SYSSTAT -Mostra mais de 300 atividades de monitoração
SELECT NAME, VALUE FROM V$SYSSTAT;

V$SYSTEM_WAIT_CLASS - Resume varias categorias de problemas que podem fazer com que seções, ou todo o banco de dados rodem lentamente
SELECT WAIT_CLASS, TIME_WAITED
FROM V$SYSTEM_WAIT_CLASS
ORDER BY TIME_WAITED;

Visualizando Metricas de Desempenho com o Database Control
RELATED LINKS > ALL METRICS > Selecione a aréa desejada
Paging - Aumenta a medida que o servidor vai ficando sem memoria
Contagem de Sessões - contagem de sessões que estejam em espera e proque encontram em espera.
Numero de logins - Numero de logins e transações por segundo
Numero de leituras físicas - é a quantidade de REDO gerado por segundo.

Gerenciando Desempenho do Banco de Dados - PARTE 1

Objetos Invalidos
No Oracle objetos invalidos são marcados como invalid. O Oracle sempre tentará recompilar objetos PL/SQL e visões invalidadas automáticamente mas isso poderá não funcionar. Os erros mais comuns de erros de compilação são mudanças nas estruturas de objetos dependentes da PL/SQL. Para identificar objetos invalidados em seu banco de dados prossiga com o seguinte comando:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS WHERE STATUS='INVALID';

Sendo que DBA_OBJECTS refere-se a todos objetos do banco de dados, ALL_OBJECTS refere-se a todos objetos que você tem acesso e USER_OBJECTS os objetos do seu usuário. Preparando Objetos Ex.
ALTER PROCEDURE HR.ADD_REG COMPILE;
ALTER VIEW RNAME COMPILE;

Consultando dependencias de Objetos
Ex.
SELECT REFERENCED_NAME, REFERENCED_OWNER, REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME='TABELA_NOME';

Índices Inutilizáveis O indice para ser preparado necessita explicitamente ser reparado antes que possa ser usado.
Em caso de indices com falha o Oracle não vai ser comportar como versões anteriores. Ele simplismente vê o indice com problema e ignora e faz o Table Scan da tabela. Em versões anteriores a 10G ele apresentava um problema no indice e só poderia continuar quando o indice fosse reparado.

Para manter a compatilidade a Oracle criou um parametro para se caso você quiser que o Oracle comporte-se como versões anteriores
SKIP_UNUSABLE_INDEXES
Para detectar indices com falha consulte a visão DBA_INDEXES
SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE STATUS='UNUSABLE';

Para reconstruir o indice use o comando REBUILD. Esse comando tem diversas opções entre elas TABLESPACE, ONLINE E NOLOGGING.

TABLESPACE - poderá alterar seu indice para outro tablespace.

ONLINE - quando você está criando um indice instruções DML ficam bloqueadas. Isso poderá ser emitido com o comando ONLINE

NOLOGGING - instrui o Oracle não gravar REDO da criação do indice, com isso a criação irá ser bem mais rapida.

ex.
ALTER INDEX IDX_TBPESSOAS REBUILD ONLINE NOLLOGING;


Estátisticas do Otimizador
A escolha do plano de execução é de importancia vital para o desempenho. Em um banco de dados Oracle, o comportamento padrão é que os planos de execução sejam desenvolvidos dinamicamente pelo otimizador. A principal estátistica é a de objeto e as mesmas poderam ser obtidas através das seguintes views

DBA_TABLES
- Numero de linhas da tabela
- Numero de blocos (usados e não usados)
- Quantidade de blocos livres nos blocos usados
- Extenssão média de cada linha
- Numero de linhas "encadeadas" (que ocupam mais de um extend)

DBA_TAB_COLUMNS
- Numero de valores distintos
- Os valores mais alto e baixo
- Numero de NULLS
- A extensão médias das colunas

DBA_INDEXES
- Profundidade da árvore de indices
- Numero de valores chaves distintos
- O fator de clustering - em que medida a ordem natural das linhas segue a ordem das chaves de indice.

INDEXES_STATS
- Numero de entrada do indice que referem a linhas existentes e excluídas

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

Na parte 2 irei abordar os seguintes temas:
- Obtendo estátisticas
- Metricas de desempenho
- Visualizando métricas com o Database Control

Ora-12560 Erro na criação da Instance com o DBCA

Boa tarde. Hoje estarei postando um problema que ocorre em algumas versões em Windows 2003 Server ao criar um Instance. Ao finalizar o assistente do DBCA você irá se deparar com a seguinte situação (em algumas versões de SO e Oracle). Ora-12560

Esse problema pode ser ocasionado através de n problemas Listener mau configurado ou o seu DB_DOMAIN configurado de maneira incorreta.

No meu caso ele não conseguia inicializar a instalação do Oracle pois quando ele cria a instance no services.msc (Painel de Controle/Ferramentas Adminstrativas/Serviços). Ele dá um erro que está em duplicidade, vai para o proximo passo de fazer o start do serviço para que atravez de INITDB[[sid]].ora ele consiga instalar a nova instance.

Antes de dar o OK na mensagem verifica para ver se o serviço foi criado, e estiver criar e o serviço estiver parado. Alguma coisa no sistema operacional impede que o Oracle inicialize o serviço.

Para criar o banco de dados nesse caso iremos criar ele passo passo através da ajuda do DBCA.

1º Passo:

Crie todas as etapas pelo DBCA, na hora que você for finalizar o processo, mande ele salvar o script em alguma pasta para você.

2º Passo

Após ele criar o script localize a pasta SCRIPT dentro do seu sistema operacional e localize o arquivo. Ex. C:\oracle\product\10.2.0\admin\DBTESTE\scripts

Abra o arquivo .bat no notepad ou algum editor de texto de sua preferencia sendo o nome da instance.

No meu exemplo o arquivo se encontrava assim:

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\adump

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\bdump

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\cdump

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\dpdump

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\pfile

mkdir C:\oracle\product\10.2.0\admin\DBTESTE\udump

mkdir C:\oracle\product\10.2.0\db_1\cfgtoollogs\dbca\DBTESTE

mkdir C:\oracle\product\10.2.0\db_1\dbs

mkdir C:\oracle\product\10.2.0\oradata

mkdir C:\oracle\product\10.2.0\oradata\DBTESTE

mkdir D:\oracle\product\10.2.0\oradata\DBTESTE

mkdir E:\oracle\product\10.2.0\oradata\DBTESTE

set ORACLE_SID=DBTESTE

C:\oracle\product\10.2.0\db_1\bin\oradim.exe -new -sid DBTESTE -startmode manual -spfile

C:\oracle\product\10.2.0\db_1\bin\oradim.exe -edit -sid DBTESTE -startmode auto -srvcstart system

C:\oracle\product\10.2.0\db_1\bin\sqlplus /nolog @C:\oracle\product\10.2.0\admin\DBTESTE\scripts\DBTESTE.sql

3º Passo

Ao abrir copie os comandos MKDIR e vá jogando no shell para executar os comando e criar a estrutura de pastas seguindo a OFA (Oracle Flexible Architect). Verifique se as mesmas pastas foram criadas

4º Passo

Na primeira linha do Oradim apague o inicio pois como o Oracle já foi instalado o Oradim pode ser acessado de qualquer lugar. Ele vai solicitar a você que defina a senha do usuário SYS, SYSTEM e SYSMAN:

Ex.

oradim.exe -new -sid DBTESTE -startmode manual -spfile

Nesse passo ele fez a criação do serviço, se você entrar em Painel de Controle/Ferramentas Administrativas/Serviços você ira ver o serviço criado para o seu banco de dados.

5º Passo

Na segunda linha do OraDim faça o mesmo procedimento para configurar o serviço

Ex.

oradim.exe -edit -sid DBTESTE -startmode auto -srvcstart system

Nesse passo ele tenta inicilizar o serviço. É ai que mora o problema, esse comando não consegue inicializar você precisa ir até Serviços e inicializa-lo manualmente. Ao iniciar prossiga para a etapa 6

6º Etapa

Abra uma sessão shell (DOS) e digite

SET ORACLE_SID=[[SID]]

Sendo [[sid]] o nome da instance. Nesse comando você definiu uma variavel de ambiente que será utilizada até você fechar a janela na sequencia retire o inicio da ultima linha do script deixando da seguinte forma:

sqlplus /nolog @C:\oracle\product\10.2.0\admin\DBTESTE\scripts\DBTESTE.sql

Ao fazer o mesmo ele ira começar a criar seu banco de dados normalmente. Para configurar o Enterprise Manager posteriormente entre no DBCA e selecione a opção "Configure Database" e selecione o banco de dados que deseja configurar.

Um abraço e até a proxima

SQL - Testes

Vou postar hoje um teste de SQL simples, perguntinhas básicas, mas que se não tomado o certo cuidado pode prejudicar na hora do teste.

Questões

1- Quais destas instruções falharão porque o nome da tabela não é valido? (Marque duas respostas)
A - CREATE TABLE "SELECT"(COL1 DATE);
B - CREATE TABLE "LOWER CASE"(COL1 DATE);
C - CREATE TABLE NUMBER1(COL1 DATE);
D - CREATE TABLE 1NUMBER(COL1 DATE);
E - CREATE TABLE UPDATE(COL1 DATE);

2 - Vários tipos de objetos compartilham do mesmo namespace e, portanto, não podem ter o mesmo nome do mesmo esquema. Qual dos seguintes tipos de objetos não se encontra, no mesmo tablespace dos outros?
A - INDICE
B - PROCEDIMENTO PL/SQL
C - SINONIMO
D - TABELA
E - VISAO

3 - Qual dos seguintes não é suportado pelo Oracle como um tipo de dados inteiro?
A - CHAR
B - FLOAT
C - INTEGER
D - STRING

4 - Você precisa registrar valores de data e hora com uma precisão de um segundo. Qual seria um tipo de dados adequado para armazenar essas informações em uma unica coluna?
A - DATE
B - TIMESTAMP
C - DATE OU TIMESTAMP
D - Você precisa desenvolver seu tipo de dados personalizado, porque os internos armazenam a data ou a hora

5 - Quais tipos de restrições requerem um indice? (Marque todas que se aplicarem)
A - CHECK
B - NOT NULL
C - PRIMARY KEY
D - UNIQUE

6 - Uma determinada transação consiste de duas instruções. A primeira tem sucesso, mas a segunda (que atualiza diversas linhas) falha no meio do caminho devido a uma violação de restrição. O que acontecerá? (marque a resposta correta)
A - A transação inteira será descartada
B - A segunda instrução será descartada completamente, e a primeira escrita em disco
C - A segunda instrução será descartada completamente, e a primeira permanecerá sem ser e escrita em disco
D - Somente a atualização que causou a violação será descartada, tudo o mais será escrito em disco
E - Somente a atualização que causou a violação será descartada, tudo o mais será escrito em disco

7 - Qual das seguintes está correta sobre indices? (Marque a resposta correta)
A - Um índice pode se basear em multiplas colunas de uma tabela, mas as colunas devem ser do mesmo tipo de dados
B - Um índice pode se basear em múltiplas colunas de uma tabela, mas as colunas devem ser adjacentes e especificadas na ordem em que foram definidas na tabela
C - Um indice não pode ter o mesmo nome de uma tabela, a não ser que o indice e a tabela estejam em esquemas separados
D - Nenhuma das alternativas anteriores está correta.

8 - Para quais propositos você poderia optar em criar visões? (Marque duas respostas)
A - Para melhorar a segurança
B - Para apresentar dados de uma forma mais simples
C - Para melhorar o desempenho
D - Para salvar conjuntos de resultados de consultas frequentemente executadas

9 - Você insere uma linha usando uma sequencia INV_NOS e depois descarta a inserçãom da seguinte forma:
ocp10g> insert into invoces values(inv_nos.nextval,1,sysdate,150);
1 row created
ocp10g> rollback;
Rollback complete;
Antes dessa transação a sequencia estava no valor 10. Qual será o próximo valor emitido por ela?
A - 10
B - 11
C - 12
D - Depende de como a sequencia foi criada


=================================
=================================
=================================


RESPOSTAS
1 - D e E
2 - A
3 - D
4 - C
5 - C e D
6 - C
7 - D
8 - A e B
9 - D

Watcher - Procedure para envio de Emails

Olá a todos!!!
Hoje vou postar uma procedure que envia email's apartir do Oracle. Sua configuração é muito simples.

Para fazer a configuração você ira precisar de um servidor de SMTP caso seu servidor seja Microsoft, caso contrario é só apontar o servidor como localhost. O segundo passo é configurar o email que você vai mandar as mensagens, você pode usar qualquer email valido por exemplo silviosantos@sbt.com.br ou tomkite@oracle.com ou até mesmo luciano@oracle.com.br rs...

Para isso vamos utilizar um pacote de utilitários disponivel no Oracle chamado UTL_SMTP (Utilitários - Implementação do Simple Mail Transfer Protocol)

Segue abaixo o codigo fonte da procedure:

CREATE OR REPLACE PROCEDURE WATCHER(
v_ToAddr IN VARCHAR2,
v_Assunto IN VARCHAR2,
v_corpo IN VARCHAR2
) AS
v_FromAddr VARCHAR2(25) := 'luciano@oracle.com.br'; --Email que voce quer usar de envio v_Message VARCHAR2(200);
v_MailHost VARCHAR2(50) := '10.42.2.17'; --Ip do servidor de SMTP
v_MailConnection UTL_SMTP.Connection;

BEGIN
/* Autor: Luciano Alvarenga Maciel Pires
Descrição: Envio de emails apartir de uma procedure */

v_Message := 'From: ' v_FromAddr CHR(10)
'Subject: ' v_Assunto CHR(10)
v_corpo;
-- Abrindo conexão com o servidor de email
--Aqui você tem a possibilidade de trocar a porta do servidor de SMTP
--em servidores UNIX coloque LOCALHOST
v_MailConnection := UTL_SMTP.OPEN_CONNECTION(v_MailHost,25);
-- Usando SMTP para enviar o email.
UTL_SMTP.HELO(v_MailConnection, v_MailHost);
UTL_SMTP.MAIL(v_MailConnection, v_FromAddr);
UTL_SMTP.RCPT(v_MailConnection, v_ToAddr);
UTL_SMTP.DATA(v_MailConnection, v_Message);
-- Fechar a conexão
UTL_SMTP.QUIT(v_MailConnection);
DBMS_OUTPUT.PUT_LINE('Email enviado com sucesso');
END WATCHER;

Para chamar a procedure basta executar o seguinte comando:

DECLARE
V_TOADDR VARCHAR2(200);
V_ASSUNTO VARCHAR2(200);
V_CORPO VARCHAR2(200);
BEGIN
WATCHER( V_TOADDR => 'remetente@oracle.com.br', V_ASSUNTO => 'Assunto do email', V_CORPO => 'Olá como vai??? Estou enviando esse email através do Oracle!' );
END;

Qualquer duvida ou sugestão me contatem por email.

FlashBack Recovery

Para aquelas cagadinhas diarias, segue abaixo um comando bem interessante, utilizanda ontem por um amigo e hoje por mim, devido um membro da minha equipe ter efetuado um Update incorreto...
Segue abaixo o comando para ser guardado com grande carinho rs...

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, rowid, owner, object_name, object_typeFROM tVERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

SELECT DISTINCT TBLOGACOESUSUARIAS.* FROM TBLOGACOESUSUARIASAS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '20' MINUTE

Antes de inicializar o comando tenha certeza que eles estará atualizando os registros atingidos.

SCRIPT EXP (Export DB)

Nossa fiquei super contente com a ultima aula de Oracle 10g, o professor Marcos a cada dia se supera mais...

Vou disponibilizar aqui um script que eu montei para fazer o EXP do banco de dados da empresa a qual eu trabalho. Tem algumas coisas que tenho certeza que podem ser melhoradas, aceito sugestões, amanhã estarei fazendo uma procedure para importação de arquivos para uma tabela, com recursos de envio de email. Disponibilizarei assim que finalizar...


CLS
@ECHO OFF
ECHO. *********************************
ECHO. ** Backup - DACSISTEMAS
ECHO. ** Luciano Alvarenga M Pires
ECHO. ** AGENDAMENTO: SABADO
ECHO. ** Backup do BD Full aos sabados
ECHO. *********************************
ECHO.
SET FILE_DATE=%DATE%
SET FILE_DATE=%FILE_DATE:SEG=%
SET FILE_DATE=%FILE_DATE:TER=%
SET FILE_DATE=%FILE_DATE:QUA=%
SET FILE_DATE=%FILE_DATE:QUI=%
SET FILE_DATE=%FILE_DATE:SEX=%
SET FILE_DATE=%FILE_DATE:SAB=%
SET FILE_DATE=%FILE_DATE:DOM=%
SET FILE_DATE=%FILE_DATE:/=%
SET FILE_DATE=%FILE_DATE: =%
SET DIA=%FILE_DATE:~0,2%
SET MES=%FILE_DATE:~2,2%
SET ANO=%FILE_DATE:~4,4%
SET FILE_DATE=%ANO%%MES%%DIA%
ECHO %FILE_DATE%
DEL D:\BKP_DACSISTEMAS\DACSISTEMAS_SAB%FILE_DATE%.DMP
EXP system/SENHA@DATABASE FULL=Y BUFFER=50000 FILE=D:\BKP_DACSISTEMAS\DACSISTEMAS_SAB.DMP FEEDBACK=1000

Nessa procedure o que importa mesmo é o ultimo comando que monta o codigo de todo o banco de dados em um arquivo de dump, segundo o professor é de fundamental importancia ter esses arquivos. Não é um dos melhores tipos de backups disponiveis no Oracle, mas é um recurso muito interessante. Caso alguem venha ter duvida sobre os comandos acima me manda um email que eu explico melhor lucianoalvarenga@gmail.com ou luciano.a.m.pires@vivo.com.br.

[ ]'s