Performance Tuning

De acordo com Senegacnik (2004), há muitas definições do que é um Tuning de banco de dados. Se o tuning está acontecendo em uma transação OLTP (On-Line Transaction Processing) se deve pensar em vários usuários executando milhares ou milhões de pequenas transações por dia.

Caso a aplicação seja em um sistema DSS (Decision Support System) tem que trabalhar em ajuste de tempo de resposta. Sendo assim, é muito importante saber que tipo de aplicação em que esta funcionando o sistema.

O inicio no ajuste de desempenho é encontrar qual o problema que está tendo na base de dados. As áreas possíveis para ajuste de uma base de dados do Oracle, são: CPU; Memória, Input/Output; Rede; e Software. (SENEGACNIK, 2004a).

Para Senegacnik (2004a), a grande regra para ajuste da base de dados é nunca “mude mais um parâmetro ao mesmo tempo”. Isso se dá pelo fato que se mudar mais de um parâmetro de uma única vez, nunca saberá de qual dos parâmetros alterados que resultou a melhoria no desempenho do sistema, principalmente quando não se tem certeza do que fazer.

Há diversas maneiras de encontrar os problemas em potencial. O problema mais comum acontece quando em um único sistema o mesmo banco de dados possui aplicações que tem um ótimo desempenho, assim como também possui aplicações que possuem um mau desempenho.

Nesse caso, obviamente há um problema com essas aplicações e não na base de dados. Se o problema estiver em um aplicativo que esteja em uso há pouco tempo e inicialmente o desempenho era normal, mas com o passar do tempo seu desempenho esteja piorando, devem-se então procurar indicações de ajuste do SQL. Em 90% desse caso há um ou mais índices que falta e as consultas estão causando varreduras cheias da tabela. Também nos casos em que o crescimento diário das tabelas e índices, novamente no caso da utilização de uma aplicação nova, é grande as chances das estatísticas usadas pelo CBO (cost based optimizer) não estejam exatas. Se estiver sendo usado o CBO, então a analisa das tabelas e índices devem ser realizados regularmente.(ibidem)

O desenvolvimento do projeto do banco de dados também é muito importante no ciclo de desenvolvimento da aplicação. Nesse estagio são identificados às entidades, seus atributos e as relações entre si. Normalmente se faz o processo de normalização para conseguir a terceira forma normal.A não normalização correta da base de dados pode acarretar em problemas de desempenho.

Um outro problema do projeto é um modelo simples dos dados com triggers complexos da base de dados que usam muitos dos recursos. (SENEGACNIK, 2004a).

Fanderuff (2003) também relata que um banco de dados tem sua criação e configuração a partir de um projeto e de cálculos sobre o seu crescimento durante a sua utilização.Porem, a autora destaca que mesmo com todos os cuidados obtidos nessa fase há dois principais motivos para a queda do seu desempenho: a fragmentação dos dados com a sua utilização e não conseguir o comportamento do banco de dados esperado.

Quando uns destes casos acontecem, então há dois motivos para sua causa: recursos inadequados e projeto inadequado. Par a resolução desse problema, Fanderuff (2003) também cita a verificação da normalização do banco de dados, assim como verificar se os índices estão condizentes com a realidade e se os arquivos físicos estão corretamente separados e definidos.

Se o problema de desempenho na aplicação ainda persistir então há a possibilidade de existir problemas relacionados aos comandos SQL. Esta situação é a mais comum, sendo que quando as indicações críticas irão sendo ajustadas os problemas de desempenho desaparecerão.

(SENEGACNIK, 2004b).

Para a otimização (tuning) do banco de dados quando a causa é o código SQL, Fanderuff (2003) ressalta que são necessários os seguintes cuidados:

• Verificar se as áreas de paginação e SWAP estão de acordo com a realidade do banco de dados;

• Se o espaço em disco está sendo utilizado por completo ou se não existe espaço para execução de um processo do banco de dados; e

• Limitar o número de aplicações no banco de dados a somente ás necessárias.

Caso o banco de dados ainda não tenha o desempenho esperado, mesmo depois da verificação e dos ajustes dos itens acima citados, então há a necessidade de otimização do banco de dados.(ibidem).

Senegacnik (2004b) cita dois tipos de otimização, sendo uma baseada em regras (RBO - Rule Based Optimizer) e outra baseada em custos (CBO - Cost Based Optimizer).

No caso da utilização do CBO é possível que a causa do problema sejam estatísticas recolhidas no começo da fase da produção e que não foram atualizadas. Sendo assim, a primeira regra do CBO é manter as estatísticas atualizadas. Um problema das versões anteriores do Oracle está na não possibilidade de recolher as estatísticas de acordo com o número de mudanças na tabela.

Essa otimização é dirigida por estatísticas e suas decisões são baseadas no calculo de custo dos objetos nas declarações SQL, tendo uma melhor performance que o RBO. Os dados estatísticos são obtidos através do comando Analyze, juntamente com o Estimate, que reúne uma amostra das informações do objeto no qual se baseiam suas estatísticas. (SENEGACNIK, 2004b).

Antes da versão 7.0 o Oracle tinha somente o RBO que, por sua vez, é dirigida pela sintaxe, não considerando dados estatísticos e suas decisões não são baseadas em cálculos de custo. Nessa RBO, a posição dos nomes das tabelas, assim como as restrições da cláusula where, são fatores importantes para a otimização do banco de dados. (ibidem).

Fanderuff (2003) também cita que é importante verificar qual o plano de execução que uma declaração SQL realiza em um banco de dados, onde se pode utilizar o recurso Explain Plan, o qual ilustra a tabela ou índice e a ordem em que estas serão acessadas com a declaração. Este procedimento é extremamente útil para se verificar a forma como o Oracle resolveu a execução de um comando e então interferir para sua melhora de performance.

Urmam (1999) destaca que é através desse plano de execução que a base de dados processará a instrução SQL efetivamente, incluindo tabelas e índices que necessitam ter acesso, além, também dos métodos de acesso utilizados, entre outras características.

2.4.2.1. Métodos de Acesso

Essa forma que o Oracle resolve a execução de um comando para adotar uma tabela é apresentada de seis formas distintas, sendo essas apresentadas a seguir, de acordo com Fanderuff (2003):

• Full Table Scan: Todas as linhas de uma tabela descrita na declaração são retornadas através da pesquisa completa da mesma (do começo ao fim). No RBO pode ainda ser feito por índices e, no CBO, o otimizador é obrigado a fazer a leitura completa em busca de estatísticas para constatar que o pequeno número de linhas possui o menor custo para Full;

• Index Unique Scan: Localiza através de um único índice uma única chave;

• Index Range Scan: Acessa coluna com múltiplos valores, sendo utilizado range (>,>,<>, between, etc). A comparação = (igual) não é utilizada no range;

• Index Full Scan: Ao invés de realizar um range, todo o índice é lido. No CBO, pode ser determinado através da estatística, se é ou não uma boa idéia;

• Index Fast Full Scan: é o scan de todos os blocos de índices e os registros não são retornados de forma odenada; e

• Rowid: Método mais rápido de acesso. O Oracle recupera um bloco especifico e extrai somente os registros desejados.

2.4.2.2. Join

Quando duas ou mais tabelas são unidas o Oracle cria um Result Set (conjunto de resultados) temporário, sendo guardada na tablespace temporary, contendo a combinação de linhas. Há cinco métodos para essa junção, sendo principais os métodos nested loop, sort merge join e hash join .(FANDERUFF, 2003).

• Sort Merge Join: É utilizado quando as tabelas têm números de linhas semelhantes e a maioria das linhas são retornadas. O seu funcionamento é baseado na classificação de ambas as tabelas pela coluna de junão. Tem alto custom de operação. Os resultados são vistos após a realização de todo o processo de vinculação;

• Nested Loops: É utilizado quando a tabela principal é menor e quando um único índice é definido na coluna de junção. O seu funcionamento consiste na leitura de uma linha da tabela principal e é verificada na outra tabela. O retorno das linhas é quase imediato, não necessitando da total vinculação do processo;

• Hash Join: Somente utilizado quando o parâmetro hash_join_enabled é true, sendo disponível para o CBO. O hash join tem como funcionamento a divisão de duas tabelas em partições e a criação de uma memória na base da tabela hash, sendo utilizado para mapear as colunas de junção sem a necessidade de classificar/intercalar. É recomendado para situações onde o tamanho de uma tabela é imensamente superior ao tamanho da outra tabela;

• Cluster Join: Utilizado para acesso em tabelas fisicamente clusterizados (método para acessar uma tabela sem a utilização de um índice). As linhas das colunas unidas são alinhadas dentro do mesmo bloco de dados aumentando a eficiência, visto a necessidade de um único I/O do banco de dados; e

• Index Join: Se todos os dados de retorno da declaração estiverem em um único índice é dispensada a estrutura subjacente da tabela.

Fanderuff (2003) ainda relata dicas para aprimorar a execução de comandos SQL:

• Utilizar IN ao NOT (o NOT não permite a utilização de índices);

• Utilizar <= e >= ao NOT (o NOT não permite a utilização de índices);

• Não realizar cálculos na clásula WHERE ou em colunas indexadas;

• Não utilizar índice quando 20% das linhas serão retornadas em uma consulta; e

• Utilizar subquerys (subconsultas) somente quando não houver outra solução.

A Figura XX mostra a utilização da instrução SQL Explain Plan, baseado em Urman (1999).

Select Count(P.CD_INSTITUICAO) From PONTOS_APOIOS P, INSTITUICOES I Where P.CD_INSTITUICAO = I.CD_INSTITUICAO And I.CD_MATRIZ = 2;

Select lpad(' ', 2 * (LEVEL - 1)) || operation || ' ' || options ||

object_name || ' ' || decode(id, 0, 'Cost = ' || position) “Execution Plan”

from plan_table start with id=0 and

timestamp = (select max(timestamp) from plan_table where id=0) connect by prior id = parent_id

and prior nvl(statement_id, 'SQL_001') = nvl(statement_id, 'SQL_001') and prior timestamp <= timestamp

order by id, position;

Execution Plan

--- SELECT STATEMENT Cost = SORT AGGREGATE NESTED LOOPS

TABLE ACCESS BY INDEX ROWIDINSTITUICOES INDEX RANGE SCANIX_01_INSTITUICOES INDEX RANGE SCANIX_01_PONTOS_APOIOS EXPLAIN PLAN SET STATEMENT_ID = 'SQL_001' FOR

Cabeçalho

Comando SQL para análise

Comando SQL do Explain Plan

Resultado para análise

Figura 6. Análise de Explain Plan Fonte: Adaptado de Urman (1999)