Este é um post disponível para assinantes MVPEste post também está disponível para assinantes da SQL Magazine DIGITAL
ou para quem possui Créditos DevMedia. Clique aqui para saber mais!
ou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Artigo SQL Magazine 73 - Oracle Performance Diagnostics & Tuning
Este artigo trata do método de diagnóstico de problemas de desempenho em banco de dados Oracle baseado em tempo.
[fechar]
Você não gostou da qualidade deste conteúdo?
(opcional) Você gostaria de comentar o que não lhe agradou?
SQL Magazine 73
[Artigo disponível no Leitor Digital DevMedia. Clique aqui para acessá-lo]
> Clique aqui para ler todos os artigos da SQL Magazine 73
[Artigo disponível no Leitor Digital DevMedia. Clique aqui para acessá-lo]
> Clique aqui para ler todos os artigos da SQL Magazine 73
Oracle
Oracle Performance Diagnostics & Tuning
Um assunto de grande interesse tratando-se do SGBD Oracle é o famoso Tuning, ou como prefiro chamar, Performance Diagnostics & Tuning. Prefiro utilizar este nome porque mais importante que o ajuste é o diagnóstico.
Os métodos antigos
Antes de lembrar dos antigos métodos de Performance Diagnostics & Tuning, gostaria de dizer aos leitores que trago uma boa notícia: Performance Diagnostics & Tuning é fácil.
Gosto de dizer isso a quem me pergunta sobre Performance Diagnostics & Tuning, pois este assunto foi por muito tempo envolto em grande mistério, sendo dominado apenas pelos mais experientes profissionais em banco de dados Oracle.
Esta imagem do Performance Diagnostics & Tuning foi fixada, na minha opinião, pelos antigos métodos adotados (entre meados dos anos 80 e início dos anos 90) pelos próprios DBAs, e que eram recomendados em manuais e livros, e praticados pelos próprios consultores da Oracle.
O antigo método consistia geralmente em seguir um Checklist, onde o DBA deveria verificar vários itens, e vários indicadores conhecidos como Hit Ratios (Buffer Cache Hit Ratio e Library Hit Ratio eram os principais, dentre muitos).
Esta lista de itens que eram verificados deve ser bem conhecida para a maioria dos leitores: o consumo de processamento, memória, I/O e rede do sistema operacional, o uso da SGA e PGA, a fragmentação de tabelas e índices, a quantidade de sessões e cursores, eram alguns entre centenas de outros indicadores.
O jogo era o seguinte: verificar a maior quantidade possível de indicadores, elaborar uma teoria sobre a lentidão baseada neles, e alterar algo que teoricamente melhore um ou mais destes indicadores, e medir novamente para ver se as alterações surtiam o efeito esperado. Mais do que isto, o DBA esperava que suas alterações não piorassem ainda mais o cenário.
Em particular, o Buffer Cache Hit Ratio (que é a proporção de leituras que são feitas no Cache de dados da instância Oracle, ao invés do disco físico do sistema operacional) era mais atentamente observado pelos DBAs. Geralmente, se este estava abaixo de 90%, o DBA aumentava o parâmetro DB_CACHE_SIZE até onde era possível, e verificava se o número de leituras nos discos diminuía. Esta técnica, embora muito empregada até hoje, é fundamentalmente falha, pois o que o Oracle considera uma leitura física, pode não ser para o sistema operacional, pois este tem seu próprio Cache para o filesystem, assim como os discos rígidos, ou até mesmo o Storage pode ter o seu.
Uma alternativa a este método, executada por DBAs menos experientes (ou mais desesperados) era analisar os parâmetros atuais do Oracle (são centenas, como você deve saber), identificar um ou mais deles que teoricamente tem relação com a lentidão verifica, e então alterá-los, e observar o resultado.
Um método comum também era o de, através de utilitários do sistema operacional (como o top no Linux, ou topas no AIX), observar qual processo de usuário consumia mais recursos, identificar o SQL que este está fazendo, e tentar elaborar alguma melhoria nele.
Outra técnica de Performance Diagnostics & Tuning muito empregada era adicionar hardware: se o banco de dados era considerado lento, aumentava-se a memória, e a SGA (Nota DevMan 1) (e/ou a PGA, que é a área de memória dos processos de usuário), ou até mesmo a quantidade de processadores, até que o desempenho ficasse satisfatório. Infelizmente, para infelicidade de quem comprou e pagou pelo upgrade, nem sempre isto acontecia. Aliás, há casos onde aumentar os recursos de hardware piorou o cenário.
Nota DevMan 1. SGA
System Global Area (SGA) é uma terminologia usada no SGBD Oracle que representa uma área de memória compartilhada responsável por armazenar todas as informações referentes aos processos do Oracle. Essa área é dividida em várias outras áreas de memória que cada instância do banco de dados ocupa no SGA.
Citei todos estes métodos no tempo passado, mas vejo todos muito utilizados até hoje. Reconheço que todos estes métodos, que fique bem claro, funcionavam e ainda funcionam, e que os Hit Ratios não são de maneira alguma inúteis, apenas incompletos. O grande problema é que todos estes métodos dependiam completamente da experiência do profissional, e de alguma sorte.
O método correto
Antes de executar um ajuste no SGBD Oracle, em resposta a um problema de desempenho, devemos ter um diagnóstico correto. Sem um correto diagnóstico, não faz sentido prosseguir.
O desempenho de processos computacionais é medido por velocidade. Quanto mais rápido, melhor o desempenho. E quando se trata de velocidade, o desempenho só pode ser medido pelo TEMPO. O tempo é a única constante onde podemos nos basear sobre o desempenho de um processo computacional.
Portanto, para um correto diagnóstico de um problema de desempenho, deve-se saber onde o tempo é gasto.
Finalmente, para saber-se onde o tempo é gasto no Oracle, não há uma forma mais direta que observar os Wait Events (Eventos de Espera).
Os Wait Events são eventos de espera por recursos computacionais, físicos (discos rígidos, memória, rede, etc.) ou lógicos (latches, locks, etc.). Estes recursos são limitados, e são os únicos limitadores do desempenho de um comando SQL. Se não houvesse espera por recursos computacionais, todos os comandos SQLs seriam finalizados instantaneamente.
Os Wait Events do Oracle são implementados pela OWI, a Oracle Wait Interface. Esta implementação mede as esperas diretamente do kernel do Oracle, e não é nova, foi introduzida na versão 7.0.12. Desde então, os Wait Events monitorados pela OWI cresceram consideravelmente, de 104 Wait Events na 7.0.12, para 220 no 8i, 400 no 9i, e mais de 800 no 10g. Este progresso mostra como a própria Oracle acredita e investe neste método de diagnóstico de problemas de performance.
Sabem aquela telinha bonita de desempenho do Enterprise Manager, com um gráfico legal? Pois é, aqueles gráficos nada mais são do que os Wait Events do Banco de Dados. Mas, como eu prefiro o bom e velho SQL*Plus, este artigo irá demonstrar como encontrar os gargalos do banco de dados sem a ajuda do Enterprise Manager.
Traçando um paralelo da OWI com a vida real, pense em quanto tempo você leva para ir de São Paulo ao Rio de Janeiro, de avião. Embora o tempo de vôo leve menos que 30 minutos, no tempo total a viagem completa pode levar umas 6 horas: é necessário ir até o aeroporto, comprar a passagem, fazer o Check In, despachar a bagagem, etc.
Prosseguindo com a metáfora, o avião é o comando SQL, enquanto todas as outras tarefas necessárias para se completar a viagem são os Waits Events.
Então, se você precisa otimizar este tempo de 6 horas, onde é mais fácil e produtivo otimizar? O processo pode ser otimizado em vários pontos: comprando-se previamente a passagem, levando apenas bagagem de mão, fazendo outro caminho para chegar ao aeroporto, etc. Mas otimizar o tempo de vôo é o mais difícil (e caro, pode ser necessário trocar o avião), e o que surtirá menos efeito no tempo total da viagem. Mesmo se comprarmos um Concorde, o tempo total da viagem diminuiria de 6 horas para 5 horas e 45 minutos.
Dynamic Performance Views
As principais Dynamic Performance Views (“Visualizações Dinâmicas de Desempenho”) que você utilizará para fazer um diagnóstico de desempenho com a OWI serão as descritas a seguir:
• V$SESSION_WAIT: Esta é a principal View da OWI, e mostra com detalhes os Waits Events das sessões atuais. O nível de detalhe é excelente, e é isto que permite um diagnóstico preciso do gargalo. Por exemplo, no evento db file sequential read, é exibido exatamente em qual arquivo está ocorrendo a leitura. Como esta View é muito precisa, e só tem com conteúdo o que estava acontecendo exatamente no momento de sua consulta, geralmente é necessário executá-la várias vezes seguidas, para poder observar o gargalo em tempo real;
"
Este é um post disponível para assinantes MVP
Oracle Performance Diagnostics & Tuning
Um assunto de grande interesse tratando-se do SGBD Oracle é o famoso Tuning, ou como prefiro chamar, Performance Diagnostics & Tuning. Prefiro utilizar este nome porque mais importante que o ajuste é o diagnóstico.
Os métodos antigos
Antes de lembrar dos antigos métodos de Performance Diagnostics & Tuning, gostaria de dizer aos leitores que trago uma boa notícia: Performance Diagnostics & Tuning é fácil.
Gosto de dizer isso a quem me pergunta sobre Performance Diagnostics & Tuning, pois este assunto foi por muito tempo envolto em grande mistério, sendo dominado apenas pelos mais experientes profissionais em banco de dados Oracle.
Esta imagem do Performance Diagnostics & Tuning foi fixada, na minha opinião, pelos antigos métodos adotados (entre meados dos anos 80 e início dos anos 90) pelos próprios DBAs, e que eram recomendados em manuais e livros, e praticados pelos próprios consultores da Oracle.
O antigo método consistia geralmente em seguir um Checklist, onde o DBA deveria verificar vários itens, e vários indicadores conhecidos como Hit Ratios (Buffer Cache Hit Ratio e Library Hit Ratio eram os principais, dentre muitos).
Esta lista de itens que eram verificados deve ser bem conhecida para a maioria dos leitores: o consumo de processamento, memória, I/O e rede do sistema operacional, o uso da SGA e PGA, a fragmentação de tabelas e índices, a quantidade de sessões e cursores, eram alguns entre centenas de outros indicadores.
O jogo era o seguinte: verificar a maior quantidade possível de indicadores, elaborar uma teoria sobre a lentidão baseada neles, e alterar algo que teoricamente melhore um ou mais destes indicadores, e medir novamente para ver se as alterações surtiam o efeito esperado. Mais do que isto, o DBA esperava que suas alterações não piorassem ainda mais o cenário.
Em particular, o Buffer Cache Hit Ratio (que é a proporção de leituras que são feitas no Cache de dados da instância Oracle, ao invés do disco físico do sistema operacional) era mais atentamente observado pelos DBAs. Geralmente, se este estava abaixo de 90%, o DBA aumentava o parâmetro DB_CACHE_SIZE até onde era possível, e verificava se o número de leituras nos discos diminuía. Esta técnica, embora muito empregada até hoje, é fundamentalmente falha, pois o que o Oracle considera uma leitura física, pode não ser para o sistema operacional, pois este tem seu próprio Cache para o filesystem, assim como os discos rígidos, ou até mesmo o Storage pode ter o seu.
Uma alternativa a este método, executada por DBAs menos experientes (ou mais desesperados) era analisar os parâmetros atuais do Oracle (são centenas, como você deve saber), identificar um ou mais deles que teoricamente tem relação com a lentidão verifica, e então alterá-los, e observar o resultado.
Um método comum também era o de, através de utilitários do sistema operacional (como o top no Linux, ou topas no AIX), observar qual processo de usuário consumia mais recursos, identificar o SQL que este está fazendo, e tentar elaborar alguma melhoria nele.
Outra técnica de Performance Diagnostics & Tuning muito empregada era adicionar hardware: se o banco de dados era considerado lento, aumentava-se a memória, e a SGA (Nota DevMan 1) (e/ou a PGA, que é a área de memória dos processos de usuário), ou até mesmo a quantidade de processadores, até que o desempenho ficasse satisfatório. Infelizmente, para infelicidade de quem comprou e pagou pelo upgrade, nem sempre isto acontecia. Aliás, há casos onde aumentar os recursos de hardware piorou o cenário.
Nota DevMan 1. SGA
System Global Area (SGA) é uma terminologia usada no SGBD Oracle que representa uma área de memória compartilhada responsável por armazenar todas as informações referentes aos processos do Oracle. Essa área é dividida em várias outras áreas de memória que cada instância do banco de dados ocupa no SGA.
Citei todos estes métodos no tempo passado, mas vejo todos muito utilizados até hoje. Reconheço que todos estes métodos, que fique bem claro, funcionavam e ainda funcionam, e que os Hit Ratios não são de maneira alguma inúteis, apenas incompletos. O grande problema é que todos estes métodos dependiam completamente da experiência do profissional, e de alguma sorte.
O método correto
Antes de executar um ajuste no SGBD Oracle, em resposta a um problema de desempenho, devemos ter um diagnóstico correto. Sem um correto diagnóstico, não faz sentido prosseguir.
O desempenho de processos computacionais é medido por velocidade. Quanto mais rápido, melhor o desempenho. E quando se trata de velocidade, o desempenho só pode ser medido pelo TEMPO. O tempo é a única constante onde podemos nos basear sobre o desempenho de um processo computacional.
Portanto, para um correto diagnóstico de um problema de desempenho, deve-se saber onde o tempo é gasto.
Finalmente, para saber-se onde o tempo é gasto no Oracle, não há uma forma mais direta que observar os Wait Events (Eventos de Espera).
Os Wait Events são eventos de espera por recursos computacionais, físicos (discos rígidos, memória, rede, etc.) ou lógicos (latches, locks, etc.). Estes recursos são limitados, e são os únicos limitadores do desempenho de um comando SQL. Se não houvesse espera por recursos computacionais, todos os comandos SQLs seriam finalizados instantaneamente.
Os Wait Events do Oracle são implementados pela OWI, a Oracle Wait Interface. Esta implementação mede as esperas diretamente do kernel do Oracle, e não é nova, foi introduzida na versão 7.0.12. Desde então, os Wait Events monitorados pela OWI cresceram consideravelmente, de 104 Wait Events na 7.0.12, para 220 no 8i, 400 no 9i, e mais de 800 no 10g. Este progresso mostra como a própria Oracle acredita e investe neste método de diagnóstico de problemas de performance.
Sabem aquela telinha bonita de desempenho do Enterprise Manager, com um gráfico legal? Pois é, aqueles gráficos nada mais são do que os Wait Events do Banco de Dados. Mas, como eu prefiro o bom e velho SQL*Plus, este artigo irá demonstrar como encontrar os gargalos do banco de dados sem a ajuda do Enterprise Manager.
Traçando um paralelo da OWI com a vida real, pense em quanto tempo você leva para ir de São Paulo ao Rio de Janeiro, de avião. Embora o tempo de vôo leve menos que 30 minutos, no tempo total a viagem completa pode levar umas 6 horas: é necessário ir até o aeroporto, comprar a passagem, fazer o Check In, despachar a bagagem, etc.
Prosseguindo com a metáfora, o avião é o comando SQL, enquanto todas as outras tarefas necessárias para se completar a viagem são os Waits Events.
Então, se você precisa otimizar este tempo de 6 horas, onde é mais fácil e produtivo otimizar? O processo pode ser otimizado em vários pontos: comprando-se previamente a passagem, levando apenas bagagem de mão, fazendo outro caminho para chegar ao aeroporto, etc. Mas otimizar o tempo de vôo é o mais difícil (e caro, pode ser necessário trocar o avião), e o que surtirá menos efeito no tempo total da viagem. Mesmo se comprarmos um Concorde, o tempo total da viagem diminuiria de 6 horas para 5 horas e 45 minutos.
Dynamic Performance Views
As principais Dynamic Performance Views (“Visualizações Dinâmicas de Desempenho”) que você utilizará para fazer um diagnóstico de desempenho com a OWI serão as descritas a seguir:
• V$SESSION_WAIT: Esta é a principal View da OWI, e mostra com detalhes os Waits Events das sessões atuais. O nível de detalhe é excelente, e é isto que permite um diagnóstico preciso do gargalo. Por exemplo, no evento db file sequential read, é exibido exatamente em qual arquivo está ocorrendo a leitura. Como esta View é muito precisa, e só tem com conteúdo o que estava acontecendo exatamente no momento de sua consulta, geralmente é necessário executá-la várias vezes seguidas, para poder observar o gargalo em tempo real;
"
A exibição deste artigo foi interrompida.
Este é um post disponível para assinantes MVPEste post também está disponível para assinantes da SQL Magazine DIGITAL
ou para quem possui Créditos DevMedia. Clique aqui para saber mais!
ou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Ricardo Portilho Proni
Com 20 anos de experiência profissional, Ricardo Portilho Proni é Oracle ACE e já trabalhou em grande parte dos maiores bancos de dados Oracle e MySQL do Brasil. É certificado em Oracle, MySQL, SQL Server, DB2, Sybase e WebSphere. Arquiteto na UOLDIVEO e Instrutor da Nerv Informática Ltda, também é ...
O que você achou deste post?
5 COMENTÁRIOS
Aderman Adão Polidoro Junior
http://www.devmedia.com.br/articles/viewcomp.asp?comp=8439
Boa Noite, este link que foi passado sobre tuning foi excluído, tem como disponibilizá-lo para continuidade do artigo sobre tunning
[há +1 ano] -
Responder
Devmedia - Equipe De Moderação
o link é http://www.devmedia.com.br/articles/viewcomp.asp?comp=16297
[há +1 ano] -
Responder
Flávio Ferreira Burgos
Muito boa matéria, especialmente pela abordagem via SQL*Plus porque uma vez que o DBA entende como o Oracle Database mensura e armazena informações tão relevantes, uma aborgagem via Oracle Enterprise Manager ou outras ferramentas gráficas será um caminho natural devido a sua praticidade, mas é fundamental que o profissional compreenda o que está avaliando.
Parabéns!
Parabéns!
[há +1 ano] -
Responder
Hugo Viana Da Silva Junior
Parabéns, Ricardo!
Artigo muito bem escrito e didático.
Artigo muito bem escrito e didático.
[há +1 ano] -
Responder
Cursos relacionados
Publicidade
