“Otimização de Consultas de Aplicações T-SQL em

Uma Abordagem Prática Sobre Otimização de Banco de
Dados Alterando a Área de Memória no Sistema Gerenciador
de Banco de Dados Oracle
Hugo F. Carrara1, Lucas V. Souza1, Miguel J. das Neves2
Graduando em Tecnologia em Banco de Dados – Faculdade de Tecnologia de Bauru
(FATEC)
Bauru – SP – Brasil
2
Professor do Curso de Tecnologia em Banco de Dados – Faculdade de Tecnologia de
Bauru (FATEC)
Bauru – SP – Brasil
1
[email protected], [email protected], [email protected]
Abstract. Because of the big amount of data generated by the companies that
use information systems, database is essential. Data usually need to be
accessed constantly, so the results availability are even not satisfactory. This
context brings us the question of performance related to find information.
Performance problems are not related to infrastructure, operating systems or
hardware. Adjusting and optimizing the memory structure becomes an
important factor and can obtain a considerable gain of performance. This
article rates how to improve the performance of a database management
system, suggesting possible changes that can improve databases performance
changing values of memory areas.
Resumo. Devido ao grande volume de dados que são gerados pelas Empresas
que utilizam Sistemas de Informação, é fundamental o papel do Banco de
Dados. Geralmente os dados precisam ser acessados a todo instante, logo, a
disponibilidade dos resultados nem sempre são satisfatórias. Nesse contexto,
entra a questão do desempenho ao se buscar informações. Muitos problemas
de desempenho não estão relacionados à infraestrutura, sistemas
operacionais ou mesmo ao hardware. Ajustar e otimizar a estrutura de
memória torna-se fator importante, podendo-se ter um ganho de performance
considerável. Este artigo avalia como melhorar o desempenho do Sistema
Gerenciador de Banco de Dados, sugerindo possíveis alterações que possam
levar a um ganho de desempenho modificando os valores das áreas de
memória.
1. Introdução
Cada vez mais empresas buscam em sistemas informatizados uma ferramenta que
forneça apoio na melhoria dos processos e suporte a tomada de decisões.
O conceito de tuning parte do princípio de sintonizar ou ajustar algo para que
funcione melhor, partindo desse conceito, tuning é a otimização das configurações dos
componentes que fazem parte de todo o sistema envolvido no armazenamento e
gerenciamento dos dados, seja esse ajuste na parte de hardware ou software.
Quando se fala em tuning, refere-se genericamente em ganho de performance,
porém, não necessariamente essa performance corresponde a tempo de resposta, neste
caso, pode-se considerar alguns outros aspectos tais como um consumo menor de
memória no servidor, a utilização mais otimizada do disco ou até mesmo suportar uma
quantidade maior de usuários.
Considerando o processo de aumento de desempenho como um todo, são vários
os profissionais envolvidos, colaborando cada qual em sua área para a sintonia do
projeto, pode-se dizer então que o processo de tuning envolve várias competências e
tecnologias que partirá desde o design da aplicação, passará pela análise e
desenvolvimento quanto a programação, a análise e estruturação da base de dados e por
fim, seguirá a parte de infra estrutura.
Dentro desse contexto, estão os Sistemas de Gerenciamento de Banco de Dados
(SGBD’s) armazenando e gerenciando informações, disponibilizando-as de forma
rápida e eficaz, contudo, SGBD´s não são ferramentas auto suficientes, se considerado
as consultas que serão realizadas dentro do banco de dados, necessitando ser
configurado e ajustados de forma particular para cada tipo de dado a ser armazenado.
Exposto esse motivo, despende-se esforços no sentido de aperfeiçoar seu funcionamento
interno, melhorando a organização das informações e como serão obtidas.
Algumas questões devem ser consideradas no processo de otimização
envolvendo o Banco de Dados, levado em conta que o processo de tuning exige a
definição detalhada de passos a serem seguidos, bem como elaborada toda uma
documentação de quais serão os procedimentos aplicados.
No âmbito do banco de dados, o profissional responsável pelo tuning é o
Administrador de Banco de Dados (Databases Administrator ou DBA), é ele quem irá
determinar os objetivos a serem alcançados, baseando-se em algumas questões que
serão objeto desta pesquisa, por exemplo, o porquê realizar o processo de otimização? O
quanto de tuning deve ser feito? Essas questões podem ser respondidas com a
interpretação de relatórios gerados a partir de ferramentas existentes no próprio SGDB.
Conforme Gehrke e Ramakrishnan (2008), para obter o melhor resultado
possível em se tratando de desempenho, é necessário a sintonização continuada do
banco de dados. Após a fase final do projeto de banco de dados, seu uso real fornece
informações detalhadas que podem ser utilizadas a fim de melhorar o desempenho em
relação ao desempenho.
Este trabalho tem por objetivo apresentar os benefícios alcançados com a
otimização de desempenho em banco de dados e as vantagens que ela pode trazer para
as empresas e profissionais de tecnologia da informação nos seus negócios.
2. Gerenciadores de Banco de Dados
Segundo Andrade (2005), possuir um ambiente dinâmico é principal problema
relacionado aos bancos de dados. Pode-se considerar também o crescimento constante
da população de dados resultando em mudanças de suas configurações.
Algumas ferramentas são importantes para a coleta de estatísticas para análise do
desempenho do banco de dados com o objetivo definir novas configurações dependendo
do resultado obtido em seus relatórios. A coleta de estatísticas no Oracle, é um dos
recursos mais importantes para quem está monitorando, otimizando o desempenho e
resolvendo problemas.
Segundo Date (2004), visto que o usuário não determina como e sim o que ele
pretende receber, ou seja, não especificam um procedimento, o processo em torno dos
dados armazenados para atender a requisição do usuário é feito automaticamente.
A otimização pode ser dividida em três seguimentos principais que segundo
Tramontina (2008) são: (1) refinamento do esquema das relações e consultas feitas na
base de dados, (2) configuração do sistema operacional em uso e (3) configuração dos
parâmetros do SGBD, esse último segmento relacionado ao estudo deste trabalho.
3. Estrutura de Memória Oracle
A arquitetura Oracle está dividida em duas estruturas de memórias, uma delas é a
Program Global Area que é privada e alocada para um único processo e dependente da
configuração da conexão do banco de dados ou servidor compartilhado/dedicado (Bryla
e Loney, 2008).
A outra estrutura é chamada de System Global Area (SGA) é um grupo de
estruturas de memória compartilhadas, utilizadas para armazenar e acessar informações
do banco de dados que são divididas pelos processos e usuários da instância do banco,
contendo dados e informações para o controle da instancia do Oracle. Em casos em que
diversos usuários estão conectados simultaneamente à mesma instância, os dados na
SGA da instância são compartilhados entre os usuários (Bryla e Loney, 2008).
Dentro da SGA está a shared pool e dentro dela, outras duas divisões de
memória (library cache e data dictionary cache) compartilhada pelos usuários da
instância de banco de dados que fazem parte da arquitetura como um todo. No processo
de inicialização de uma instância Oracle, a memória é dimensionada para a SGA com
base nos valores definidos no arquivo de parâmetros de inicialização ou codificados no
software Oracle. Quando uma instância é inicializada, a memória é alocada pelo Oracle,
e ao ser finalizada, esta memória é liberada [Bryla e Loney 2008].
3.1 Conceitos em Shared Pool
Segundo Mauro Pichiliani (2013), shared pool é uma área de memória constituída por
dois subcaches principais: o cache de biblioteca (library cache), utilizado para
armazenar os comandos Structured Query Language (SQL) e Procedural Language /
Structured Query Language (PL/SQL) executados recentemente no banco de dados; e o
cache de dicionário de dados (data dictionary cache), que armazena um subconjunto de
colunas das tabelas do dicionário de dados após serem lidos no buffer cache.
Estes comandos SQL podem ser solicitados por processos do usuário ou, no caso
de stored procedures, lidos do dicionário de dados. O shared pool é dimensionado pelo
parâmetro de inicialização SHARED_POOL_SIZE. Para visualizar o tamanho atual é
utilizado o comando SHOW PARAMETER SHARED_POOL_SIZE.
Relacionado a desempenho, um dos principais componentes do SGA é o library
cache que é o responsável pelo gerenciamento do acesso ao cache de instruções SQL,
procedures, funcitions, packs entre outros. [Bryla e Loney 2008].
Esse componente é o responsável por armazenar em sua memória as últimas
instruções SQL que foram utilizadas, e conforme vão deixando de ser utilizadas,
fazendo a renovação por outras instruções mais recentes, ou seja, quando uma nova
instrução é executada, esse componente elimina a primeira instrução que foi utilizada
com o objetivo de liberar o espaço de memória para a alocação de uma instrução mais
recente. Quando uma instrução SQL é executada e está na área de memória do library
cache, pode-se dizer que tal instrução recebe o valor de hit, e quando não se encontra na
área de memória, recebe o valor de miss, que é exatamente o que deve ser evitado, para
verificação do percentual de hit é necessário observar a coluna GETHITRATIO da view
V$LIBRARYCACHE [Pichiliani 2013].
O objetivo da otimização do componente library cache é aumentar a quantidade
de instruções que serão encontradas (hit) na área de memória e diminuir ou eliminar os
casos em que não serão encontradas (miss). Existem algumas recomendações em relação
a melhoria de performance do espaço de memória do library como a utilização de um
código genérico, com a utilização de variáveis que poderão ser reutilizados mudando-se
somente alguns parâmetros. Outra recomendação importante é fazer uso de módulos, ou
seja, procedures e functions.
A utilização de stored procedures (procedimentos armazenados) melhora o
desempenho de consultas em um ambiente de rede, visto que os mesmos já estão précompilados e contém um plano de execução guardado na memória [Gunderloy e Jorden
2001].
A análise de alguns parâmetros é determinante para na oimização do library
cache, um dos principais fatores de análise é o tamanho reservado para o shared pool,
que por padrão é oito megabytes. Existe também uma parte da memória que pode ser
reservada para o shared pool para armazenar instruções e grandes compilações, a
recomendação é que se destine dez por cento do tamanho total do shared pool.
4. Metodologia
Foi utilizado para realização deste trabalho o SGBD, Oracle 11g Release 2 na sua versão
Standard Edition para sistemas trinta e dois bits, que pertence a Oracle Corporation
sendo executado sobre a plataforma de Sistema Operacional Windows Server 2008
sistemas trinta e dois bits.
Paralelamente ao SGBD foram utilizados os programas SQL Plus, Oracle SQL
Developer, Apex, bem como ferramentas de geração de relatórios e views provenientes
do próprio SGBD Oracle. Para a realização dos testes foi utilizado o aplicativo
Benchmark Factory na versão freeware pertencente a Quest Software capaz de simular
acesso simultâneo bem como uma grande quantidade de consultas permitindo a
avaliação do comportamento da performance do SGBD diante dessas cargas.
4.1. Resultados Obtidos
Para este trabalho foram elaborados testes simulando ambientes de trabalho com
manipulação e acessos simultâneos de vinte usuários com as configurações padrão dos
parâmetros de inicialização e após mensuração e análise, a realização novos testes com
alteração nos valores de memória dos parâmetros. Como citado anteriormente, o parâmetro
de inicialização SHARED_POOL_SIZE vem configurado com oito megabytes, os testes
iniciais mostram o resultados com o valor padrão e a seguir com o valor de oitenta
megabytes, valor escolhido de forma empírica.
4.2. Testes com valor padrão do parâmetro SHARED_POOL_SIZE
A tabela e no gráfico a seguir os resultados dos testes com o valor padrão do parâmetro
de inicialização.
Tabela 1. Resultados dos testes com valor padrão
SHARED_POOL SIZE (Fonte: simulação no software
Benchmark Factory freeware).
Descrição
Resultados
Transações por segundo
1.091
Tempo médio de resposta (em segundos)
0.036
Tempo médio das transações (em segundos)
0.040
Número de execuções
202
Número de linhas
247
Erros
0
do
de
Nota-se na tabela 1 que ao passar uma carga máxima de vinte usuários ao
software de Benchmark e com o parâmetro de inicialização da área de memória shared
pool em seu valor padrão, obtivemos 1.091 transações por segundo em um tempo médio
de resposta de 0,036 segundos e numa média de 0,040 de segundos no total das
transações, executando 202 operações, em 247 linhas e tudo a 0 erros.
Imagem 1. Média do tempo de resposta em milissegundos (Fonte:
simulação no software de Benchmark Factory freeware).
Notamos que na imagem 1, representação do ambiente descrito na tabela 1, o
tempo de resposta se inicia acima de 400 milissegundos, e ao passo que as instruções
estão sendo gravadas na área de memória denominada shared pool esse tempo diminui.
Nesta simulação a porcentagem de hit cache foi de 44,56 %, esta informação
representa que menos que a metade das instruções SQL solicitadas foram encontradas
em memória cache.
4.3. Testes com valor alterado do parâmetro SHARED_POOL_SIZE
A tabela e no gráfico a seguir os resultados dos testes com o valor de 80 megabytes para
parâmetro de inicialização.
Tabela 2. Resultados dos testes com valor
SHARED_POOL SIZE (Fonte: simulação no
Benchmark Factory freeware).
alterado
software
Descrição
Resultados
Transações por segundo
1.093
Tempo médio de resposta (em segundos)
0.008
Tempo médio das transações (em segundos)
0.012
Número de execuções
202
Número de linhas
247
Erros
0
do
de
Nota-se na tabela 2 que ao passar uma carga máxima de vinte usuários ao
software de Benchmark e com o parâmetro de inicialização da área de memória shared
pool em seu valor 80 megabytes, obtivemos 1.093 transações por segundo em um tempo
médio de resposta de 0,008 segundos e numa média de 0,012 de segundos no total das
transações, executando 202 operações, em 247 linhas e tudo a 0 erros.
Imagem 2. Média do tempo de resposta em milissegundos (Fonte:
simulação no software de Benchmark Factory freeware).
Nota-se na imagem 2, representação do ambiente descrito na tabela 2, o tempo
de resposta se inicia com abaixo dos 120 milissegundos e ao passo que as instruções
estão sendo gravadas na área de memória denominada shared pool esse tempo diminui.
Nesta simulação a porcentagem de hit cache atingiu de 61,75 %, ou seja, como a
memória destinada a shared pool está maior, as instruções SQL permanecem por mais
tempo e são encontradas em memória cache, evitando o acesso e leitura de disco físico e
consequentemente uma diminuição no tempo médio de resposta.
4.4. Comparação dos ambientes
A tabela a seguir mostra o comparativo dos resultados dos testes 1 e 2.
Tabela 3. Comparação dos resultados dos testes 1 e 2 (Fonte: simulação no
software de Benchmark Factory freeware).
Descrição
Teste 1
Teste 2
Transações por segundo
1.091
1.093
Tempo médio de resposta (em segundos)
0.036
0.008
Tempo médio das transações (em segundos)
0.040
0.012
Número de execuções
202
202
Número de linhas
247
247
Erros
0
0
No comparativo dos resultados se observa que o redimensionamento da área de
memória shared pool resultou em um melhor desempenho. Também foi possível
observar uma diminuição considerável no tempo médio de resposta.
5. Conclusão
A realização deste estudo demonstrou a necessidade de mensuração, análise e
acompanhamento do desempenho do banco de dados, fatos esses possíveis através de
ferramentas disponibilizadas pelo próprio SGBD onde interpretação correta dessas
informações pode resultar em um ganho significativo de desempenho através de um
ajuste em um parâmetro de inicialização.
Em grandes ambientes empresarias em que a informação se torna cada vez mais
fator essencial para o gerenciamento, segundos podem fazer a diferença no suporte à
tomada de decisões na área de negócios.
6. Referências
Andrade, L. D. (2005) “Otimização de Consultas de Aplicações T-SQL em Ambiente
SQL Server 2000”. Salvador, Bahia.
Bryla, B. e Loney, K. (2008) “Oracle Database 11g DBA Handbook”. Oracle Press.
Date, C. J. (2004), “Introdução a Sistemas de Banco de Dados, Campus”, 8ª edição.
Gehrke, J.; Ramakrishna, R. (2008) “Sistemas de Gerenciamento de Banco de Dados”.
São Paulo, São Paulo.
Gunderloy, M.; Jorden, J. L. (2001) “Dominando o SQL Server 2000”. São Paulo, São
Paulo.
Pichiliani, M. “Curso Oracle: Otimização de Desempenho” Disponível em:
http://www.devmedia.com.br/curso/curso-de-oracle-otimizacao-de-desempenho/390.
Último acesso em: 20 abr. 2014.
Tramontina, G. B. Database Tuning: “Configurando o Interbase e o PostgreSQL”
Disponível
em:
http://www.ic.unicamp.br/~geovane/mo410-091/Ch20ConfigInterbasePosgres-art.pdf . Último acesso em 29 mar. 2014.