AWR - SQL Costs

Segue abaixo o sql para extrair do Awr as instruções mais impactantes:

spool INSTRUCOES_IMPACTANTES.LOG
col c1 heading ‘SQLID’ format a13
col c2 heading ‘Cost’ format 9,999,999
col c3 heading ‘SQL Text’ format a200

select p.sql_id c1,
p.cost c2,
DBMS_LOB.SUBSTR(s.sql_text,4000,1) c3
from dba_hist_sql_plan p,
dba_hist_sqltext s
where p.id = 0
and p.sql_id = s.sql_id
and p.cost is not null
order by p.cost desc;

spool off

SendMail - Configurações.

Segue abaixo configurações do SendMail do Linux.

Removendo o SendMail do Startup do RedHat
chkconfig --del sendmail

Adicionando o SendMail no Startup do RedHat
chkconfig --add sendmail

Verificando Status do SendMail
chkconfig --list sendmail

AWR configurações básicas

Awr - Statistics Level

Verificar parametro STATISTICS_LEVEL:
show parameter STATISTICTS_LEVEL

Parametros aceitos:

Typical (DEFAULT) - Força obtenção de todas estátisticas necessárias para o ajuste normal sem se coletar nehuma estátistica cuja obtenção teria um impacto adverso sobre o desempenho.

Basic - Desabilita praticamente todas as estátisticas, sem nenhum beneficio apreciavel em termos de desempenho

All - Coleta estátisticas extremamente detalhadas sobre a execução de instruções SQL, mas podem causar uma ligeira queda de desempenho quando estiverem sendo coletadas.

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

As estátisticas são armazenas em memoria, e isso é executado a cada 60 minutos escrito em disco. O processo que escreve é o processo MONITOR DE GERENCIABILIDADE ou MMON.

O MMON tem acesso direto a estruturas de memoria que compoe a SGA.

O AWR se localiza no tablespace SYSAUX e não pode ser movido para nenhum outro lugar.

Relátorios ADDM são mantidos por 30 dias.

Scripts em Tablespaces

Segue abaixo script para verificação de tamanho de tablespace

select tablespace_name,
sum_bytes_alloc "ALOCADO (MB)",
sum_bytes_livre "LIVRE (MB)",
decode(trunc((sum_bytes_livre/sum_bytes_alloc)*100),'','SEM ESPACO LIVRE',
trunc((sum_bytes_livre/sum_bytes_alloc)*100)) "PCT LIVRE"
from
(select tablespace_name ,
trunc(sum(bytes)/1024/1024) as sum_bytes_alloc
from dba_data_files
group by tablespace_name) XX,
(select tablespace_name Y ,
trunc(sum(bytes)/1024/1024) as sum_bytes_livre
from dba_free_space
group by tablespace_name) XY
where XX.tablespace_name = XY.Y (+);

Abaixo um script para mostrar a alocação de espaço livre por datafile classificado por file system.

set linesize 120
set pagesize 200
break on tablespace_name
col file_name for a62
col tablespace_name for a30
select a.tablespace_name,
a.file_name,
trunc(a.bytes/1024/1024) "Alocados (Mb)",
trunc(sum(b.bytes)/1024/1024) "Livres (Mb)"
from dba_data_files a, dba_free_space b
where a.file_id = b.file_id
and a.tablespace_name not in ('TOOLS','USERS','SYSTEM','RBS','RBSBIG','INDX','UNDOTBS','XDB','DRSYS','EXAMPLE')
group by a.file_name, a.tablespace_name, a.bytes
order by tablespace_name, substr (file_name,1,17);

Os scripts acima são muito uteis quando você necessita de uma verificação rapida ou um ambiente que você não possui o acesso ao Enterprise Manager. Um exemplo bem pratico é você estar no servidor de produção em um ambiente UNIX ou Linux, onde não possui ambiente gráfico.

Envio de email com erros ORA do AlertLog

Script para enviar email dos ORA através do mailx do Linux.

#!/bin/ksh
data=`date +%a' '%d/%m/%y`
cd $ORACLE_BASE/ORCL/admin/bdump
#
# Carregando o .profile do usuario
#
. /home/oracle/.profile
#
echo "Erros ORA- no Alert" > "Erro_Ora"+data".err"
echo "-----------------------------------------------------------------" >> Erro_Ora.err
MAIL_SUB=">>> ALERT - VERIFICACAO DE ERROS - BANCO: ORCL<<<"
if test -a alert_ORCL_080109.log
then
grep -B 2 ORA- alert_ORCL_080109.log >> Erro_Ora.err
if [ `cat Erro_Ora.errwc -l` -gt 2 ]
then
ALERT_RENAME=alert_ORCL_080109.log_`date +%Y%m%d'_'%H%M%S`
echo " " >> Erro_Ora.err
echo "-------------------------------------------------------------------------------------------" >> Erro_Ora.err
echo "O alert foi renomeado para" $ALERT_RENAME "e movido para pasta alert_hist/" >> Erro_Ora.err
echo "Os arquivos .trc foram movidos para a pasta trace_hist/" >> Erro_Ora.err
echo "-------------------------------------------------------------------------------------------" >> Erro_Ora.err
mailx -r lucianoalvarenga@gmail.com -s "$MAIL_SUB" lucianoalvarenga@gmail.com <>
# mv alert_ORCL_080109.log /$ORACLE_BASE/NEWTST/admin/bdump/alert_hist/$ALERT_RENAME
cp alert_ORCL_080109.log /$ORACLE_BASE/NEWTST/admin/bdump/alert_hist/$ALERT_RENAME
# mv *.trc /$ORACLE_BASE/ORCL/admin/bdump/trace_hist
export ORACLE_SID=ORCL
echo "/ as SYSDBA
alter system switch logfile;
exit" sqlplus
fi
fi

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