Banco de dados

AULA 06
Tuning de Banco de Dados
INTRODUÇÃO AO OWI
Na aula anterior...
Começamos a estudar OWI, vimos que todo o nosso problema associado a lentidão está sempre
ligado a gargalos em banco de dados.
Para cada gargalo existe um evento de espera específico que deve ser analisado para que
possamos conhecer o que realmente está impactando na performance do nosso ambiente.
Falamos sobre o evento de espera chamado Buffer Busy Waits e vimos que está diretamente
ligado aos chamados hotblocks.
Continuando OWI
Hoje continuaremos trabalhando com eventos de espera suas causas e a forma de tentar evitálos.
Na aula de hoje vamos imaginar uma situação onde uma usuária nos liga dizendo que ao chegar
para trabalhar o sistema dela está muito lento. Você então pergunta para ela onde
especificamente ela percebeu o problema.
A usuária então te fala que existem diversos processos com problema.
Você que já está ficando experiente em resolver problemas de performance já percebeu que na
reclamação dela existe uma palavra chave: DIVERSOS.
Sendo assim você decide que não é uma boa alternativa pensar em problema isolado e analisar
trace.
Você então prefere pedir para que ela rode novamente o processo e vai investigar o ambiente
como um todo.
MAS COMO FAZER ISSO??
OWI no ambiente real
Localizando gargalos
Lembra que na aula anterior nós falamos a respeito da query que ajudava a localizar gargalos?
Pois bem, mãos a obra...
Você então roda a query abaixo e percebe um novo evento de espera acontecendo para várias
sessões no banco de dados.
SELECT i.instance_name, w.event, COUNT (*), SUM (seconds_in_wait)
FROM gv$instance i, gv$session_wait w
WHERE i.inst_id = w.inst_id
AND event not like 'Streams AQ%'
AND event not like 'queue messages%'
AND event not like 'SQL*Net%'
AND (w.inst_id, SID) IN (SELECT inst_id, SID FROM gv$session WHERE status = 'ACTIVE'
AND username IS NOT NULL)
GROUP BY i.instance_name,eventORDER BY 3 DESC;
Eventos de espera mais comuns
db file scattered read
Você acaba de descobrir que um novo evento de espera acontece no seu banco de dados. Ele
se chama db file scattered read. Então você precisa inicialmente entender o que este evento
tem para te falar.
Vamos a ele?
O evento db file scattered read, significa que que o processo do usuário (a sessão) está lendo
buffers na SGA (buffer cache) porém está tendo que esperar muito por leitura física no disco
I/O.
De base desta informação veja o leque de informações que se abre para que possamos
analisar.
Pense comigo...
Eventos de espera mais comuns
db file scattered read
O processo de usuário está lendo do buffer mas está demorando esperando buscar no disco.
Só por esta informação percebemos que a taxa de acertos da SGA, está ruim.
A taxa de acerto que eu chamo é quando dados são procurados e eles estão em memória,
não necessitando buscar em disco.
Outra coisa que podemos analisar é que poderemos ter algum problema em disco causando
esta lentidão.
Porém aqui cabe um parênteses importante. Falar em problema de disco significa ter que
envolver pessoal de storage, SO... E seria muito chato depois de tudo descobrir que talves
uma simples configuração de banco de dados melhorasse não é mesmo?
Sendo assim vamos seguir entendendo o evento de espera que é bem melhor para nós.
Eventos de espera mais comuns
db file scattered read
O termo scattered read quer dizer leitura dispersa, ou seja lendo em vários locais da memória
ao mesmo tempo, onde pode quase sempre ter que buscar em disco.
O grande vilão deste evento de espera são as leituras em FULL table SCAN, ou seja varredura
da tabela toda para se buscar os dados ou ou índices mal criados que geram leitura full scan
também.
Quando um full scan ocorre normalmente os blocos lidos não estão fisicamente adjacentes,
em seu armazenamento no disco, ou seja ordenados na ordem de leitura. Sendo assim torna a
leitura mais demorada.
Sempre que encontrarmos este evento de espera é interessante que se analise as queries das
sessões onde ele está ocorrendo.
Lembra que temos uma query qye traz quais sessões tem o evento?
Vamos usá-la para verificar o SID e SERIAL# e identificar as queries rodando e pegar quais tem
o evento.
Eventos de espera mais comuns
db file scattered read
Sabendo qual o SID da sessão e o SERIAL# podemos facilmente descobrir o
que a sessão está fazendo utilzando uma nova query, simples porém
objetiva:
select * from v$sql
where address = (select sql_address from v$session where sid =26 and serial#=32145);
O sid e serial você informa de acordo com o descoberto pela query da aula
passada.
Sabendo qual a query poderemos facilmente gerar um plano de execução
da query.
Mas o que é de fato este plano de execução???
Eventos de espera mais comuns
Um plano de execução.
O plano de execução de uma query será objeto de um capítulo específico do curso, porém é
interessante fazer um breve comentário a respeito dele para que possamos entender o que
ele traz o que é.
Um plano de execução é como um plano de vôo, que diz qual a melhor forma de se chegar a
algum lugar. No Banco de Dados ele mostra qual a melhor forma de se acessar os dados e
trazê-los para a requisição do usuário.
Se engana quem pensa que este plano de execução é sempre a melhor forma de acessar os
dados. Muitas das vezes uma tarefa do tuning é fazer mudar o plano de execução.
Mas para sabermos como a nossa query em questão está sendo executada precisamos saber
o plano de execução.
Para isso existe uma forma bem simples para isso utilizando o comando EXPLAIN PLAN.
Eventos de espera mais comuns
Utilizando o comando EXPLAIN PLAN.
O comando explain é utilizando da seguinte forma:
No SQLPLUS faça:
SQL> EXPLAIN PLAN FOR
SELECT ... (aqui coloca a query completa)
Depois para saber o plano de execução utiliza-se:
SQL> set pages 1000
set lines 120
select * from table(dbms_xplan.display)
/
Eventos de espera mais comuns
Identificando a query.
Na query, ou melhor, no plano de execução procure pela palavra FULL ela pode aparecer em
full table ou full index.
De posse desta informação e com o evento de espera que conhecemos hoje, já podemos
verificar que provavelmente ele é proveniente de uma query mal feita.
Não se engane porém achando que todo FULL é ruim; se fosse assim era muito fácil de banir
ele do banco de dados.
Antes de condenar o FULL scan verificamos o tamanho da tabela, a quantidade de linhas que
serão lidas. Quando o FULL ocorre em uma tabela muito pequena em quantidade de linhas
com certeza ele não será impactante para o ambiente e o sistema escolheu certo em fazê-lo.
Isso é facilmente identificado com um select count(*) from tabela;
Então a grande lição de hoje é: antes de condenar procure ouvir os dois lados SEMPRE!
Bem mas se percebermos que o FULL não deveria ocorrer e que seria muito melhor com a
utilização de um índice devemos então quem sabe criá-lo.
Eventos de espera mais comuns
Estimando criação de um índice.
A criação de um índice requer espaço em storage e deve ser analisada.
Pode nem ser o foco do nosso curso mas acho muito interessante explicar e o DBA saber.
Se precisarmos estimar o espaço na criação de um índice a package abaixo poderá ajudar da
seginte forma:
Declare
u_bytes number;
a_bytes number;
Begin
dbms_space.create_index_cost (ddl => 'CREATE UNIQUE INDEX OWNER.NOME_INDICE ON OWNER.NOME_TABELA'
||'(COLUNAS INDEXADAS SEPARADAS POR VIRGULA) tablespace TBSPC_INDEX',
used_bytes => u_bytes,
alloc_bytes=> a_bytes);
dbms_output.put_line ('Used Bytes = '|| u_bytes);
dbms_output.put_line ('Allocated Bytes = '|| a_bytes);
end;
/
Eventos de espera mais comuns
Estimando criação de um índice.
Como resultado temos algo parecido com:
Used Bytes = 2514576775 bytes ou 2,34Gb à ocupará este valor em bytes para dados
Allocated Bytes = 4160749568 ou 3,87Gb à ocupará este valor dentro do tablespace de índice (valor alocado).
Valor alocado será diferente de acordo com a configuração do tablespace.
Assim poderemos prever o tamanho de espaço necessário para a criação de um índice.
Eventos de espera mais comuns
db file scattered read
Voltando ao evento de espera encontrado ainda poderemos analisar um parâmetro que
normalmente é setado automaticamente pelo banco de dados a partir da versão 10 mas que
poderemos alterá-lo e com isso aumentar ou diminuir a incidência de full tables scan.
Estou falando do parâmetro DB_FILE_MULTIBLOCK_READ_COUNT, que basicamente dirá para
o banco quantos blocos poderão ser lidos ao mesmo tempo.
Quando maior este número mais interessante se torna um full table scan, quanto menor
menos interessante seria para o otimizador escolher fazer FULL SCAN.
Existe uma regra para calculo de um bom valor deste parâmetro que falaremos no momento
oportuno.
Por uma questão de teste poderíamos fazer a alteração dele com alter session apenas na
sessão para verificar a performance da query.
ATENÇÃO!!
Dica
Para a aula de hoje cabe uma dica muito interessante e importante, sempre que analisar uma
query por um problema de lentidão e pensar em alteração de parâmetro de inicialização,
como no caso citado anteriormente, pense no banco de dados como um todo e lembre-se que
parâmetros de inicialização influenciam o banco de dados como um todo.
Se for mesmo muito necessária a alteração do parâmetro, por uma determinação do
fabricante da aplicação ou algo do gênero, o mesmo poderá ser feito através de trigger de
logon onde seta o parâmetro apenas para a sessão de usuário determinado, ou até mesmo
inserir na aplicação o alter session de alteração na aplicação antes da query.
Não deixe que para resolver um problema crie-se outros.
Tuning banco de dados
Próxima aula...
Na próxima aula vamos dar prosseguimento ao estudo dos eventos de espera
suas causas e algumas formas de evitá-los.
AULA 06
Tuning de Banco de Dados
FIM