Bind Variables – Parametro Cursors_Sharing

Estou trabalhando em um banco de dados onde as consultas que são executadas com os valores fixos nos selects. Fazendo um levantamento nas querys que mais consomem pelo AWR, consegui levantar uma consulta 25 vezes, com 25 vezes levantadas em memória pois o CURSOR_SHARING está setado para EXACT.

O parâmetro Cursor_Sharing aceita 3 valores, sendo eles: FORCE, SIMILAR e EXACT

EXACT – é o padrão para banco de dados Oracle. O parâmetro EXACT envia ao otimizador do Oracle que toda e qualquer instrução deve ser igual inclusive os valores literais. Esse parâmetro é mais utilizado em ambientes que as instruções executadas não possui valores similares.

FORCE – O parâmetro troca as variáveis literais por valores bind, em sua execução, fazendo com que o HARD-PARSE se transforme em SOFT. O valor FORCE no parametro CURSOR_SHARING, gera apenas 1 plano de execução.

SIMILAR – O parâmetro troca as variáveis literais por valores bind, em sua execução, fazendo com que o HARD-PARSE se transforme em SOFT. O valor SIMILAR no parametro CURSOR_SHARING, gera mais de um plano de execução (se a tabela estiver com a coleta de estatísticas atualizada).

----TESTES-----

Meu primeiro passo é testar a performance de cada um desses comandos, vou habilitar o trace para buscar o HARD PARSE e SOFT PARSE de cada uma das consultas executadas. Meu primeiro desafio encontrado foi que o Trace não funcionava!! Tentei de tudo, fiquei até 23hs no serviço incomodando muita gente. Quase indo embora executei o comando:

SHOW PARAMETERS TRACE

E me trouxe uma peça importante:

trace_enabled boolean FALSE

Achei que esse parâmetro tinha alguma coisa a ver com minha dificuldade. Hoje confirmei com meu outro amigo DBA Rubens (FCESP) que esse parâmetro habilitar a geração do trace.Após o trace a recompensa! Achei que a diferença ia ser muito baixa fiz os testes rodando o mesmo conjunto de SQL (utilizando variáveis bindadas), mas para minha felicidade foram altas.

Segue abaixo os valores encontrados:

 

EXACT

 

FORCE

 

SIMILAR

 

Conclusão:

Para alteração nesse parâmetro faça o levantamento de instruções SQL sendo executadas no banco de dados e verifique a possibilidade de alteração para Bind Variables. Se verificado que não há possibilidade de alteração devido tempo (o principal fator), averigúe a possibilidade de alteração do parâmetro fazendo antes um levantamento no ambiente.

 

Avisos dos Advisores na pagina principal do DbConsole

Consultando os avisos do Advisors gerados na pagina principal do DBConsole.

--ALERTAS AINDA NAO RESOLVIDOS
SELECT REASON,
OBJECT_TYPE TYPE,
OBJECT_NAME NAME
FROM DBA_OUTSTANDING_ALERTS;

--ALERTAS RESOLVIDOS NO DBA_OUTSTANDING_ALERTS
SELECT REASON,
OBJECT_TYPE TYPE,
OBJECT_NAME NAME
FROM DBA_ALERT_HISTORY;

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