Aumentando a performance da aplicação através da otimização de SQL

Olá pessoal! A primeira matéria que escrevi foi um pouco avançada, então resolvi explicar alguns passos separados. Vamos considerar que a área de tuning possui seis divisões. A ordem em que cada tópico aparece deve ser mantida para prevenir um resultado inesperado. Por exemplo, não é muito bom aumentar o buffer cache se você pode reduzir I/O reescrevendo o SQL.

1. Modelagem do Banco de Dados (Se não for muito tarde): Uma má performance geralmente é resultado de uma modelagem ruim. É importante se preocupar com a modelagem, pelo menos até a 3FN (Terceira Forma Normal).
2. Tuning de Aplicação: Na última matéria eu coloquei que, “de acordo com Craig Mullins, quase 80% dos problemas de performance em banco de dados são causados por códigos SQL mal elaborados”. Reduzir o tempo dos SQL e o custo é importantíssimo para eliminarmos uma boa porcentagem desses problemas. Falarei sobre esse tópico com mais detalhes. Pra quem quiser conhecer um pouco mais sobre Craig Mullins é só acessar: http://www.craigsmullins.com/
3. Tuning de Memória: Defina corretamente o buffer do Banco de Dados (shared_pool, buffer cache, log buffer, etc.), observando o buffer hit ratios (Você pode encontrar scripts para tuning de memória no site Orafaq). Coloque os objetos grandes e muito acessados em memória para prevenir freqüente reloads.
4. Tuning de I/O Físico: Arquivos de Banco de Dados precisam estar com o tamanho correto e em um local que prove um mínimo de I/O. Verifique também freqüentes sorts de disco, full table scans, falta de índices, row chaining, fragmentação de dados, etc.
5. Elimine Contenção de Dados: Estude os eventos que acontecem no banco relativo à locks, latches e wait com cuidado e elimine-os quando possível.
6. Tuning de Sistema Operacional: Monitore e defina com cuidado os parâmetros de CPU, I/O e memória de forma a melhorar a performance do Banco.

Um pouco mais sobre Tuning de Aplicação

A otimização consiste em trazer a informação para o usuário com o menor custo e tempo possíveis. É encontrar um caminho mais eficiente de processar a mesma requisição.

Quando você está escrevendo um novo comando SQL, ou otimizando um comando existente na aplicação, o tuning de aplicação pode melhorar o tempo de resposta da CPU, reduzir I/O e os recursos gastos com memória.

A metodologia de tuning de SQL envolve identificar as querys que consomem mais recursos, e então otimizá-las. Em geral, um número pequeno de querys são responsáveis pela maior parte das atividades que ocorrem no Banco de Dados. Além disso, tente entender completamente a aplicação, focando sua atenção nos SQL ou tabelas onde os benefícios do tuning excederão os custos. Para isso, é importante observar alguns passos:

• Somente otimize o SQL depois que tiver certeza que o código está correto;

• Garanta que os comandos sejam escritos absolutamente iguais para facilitar a reutilização no banco de dados. O re-parsing no banco de dados pode ser evitado para cada uso subseqüente.

Ex: SELECT * FROM EMP;

É diferente de: Select * From Emp; SELECT * from EMP;

• Procure trazer do banco de dados somente as informações necessárias. Se você não precisa da informação contida em uma coluna específica, não é preciso trazê-la.
• Mantenha sempre atualizado as estatísticas das tabelas;
• Identifique problemas potenciais antes de mandá-los para produção. Isso pode ser feito utilizando o “Explain Plan”, tentando diminuir ao máximo o custo do SQL;
• Procure otimizar primeiro os SQL mais críticos; Não gaste tempo otimizando códigos que nunca ou raramente serão usados;
• Use índices, mas não os crie em demasia. Muitos índices podem resultar em um efeito adverso na performance. Lembre-se de escolher o tipo de índice mais adequado a cada situação. O critério básico para escolha de índices é a seletividade. Quando o banco de dados resolve uma consulta, freqüentemente, ele precisa percorrer mais registros do que aqueles realmente retomados pela consulta. Os registros percorridos que forem rejeitados representam o trabalho perdido. Quanto menor for o trabalho perdido, mais perto estaremos da performance ótima para resolver a consulta. Portanto, o melhor índice para uma consulta é aquele que apresenta a maior seletividade.
• Construa os índices a partir das restrições dos selects (cláusula WHERE); Lembre-se que as comparações usando “<>”, “NOT”, “NULL”, “LIKE” podem invalidar o índice.
• Evite full table scans se o resultado pode ser recuperado via índice, a menos que a tabela seja pequena e o custo diminua. Quando a tabela é pequena, o trabalho envolvido em acessar o índice, pegar o endereço e acessar a tabela é maior que o esforço de ler a tabela inteira.
• Divida as queries complexas em queries menores. Evite lógicas complexas de negócio no SQL. A lógica deve ficar no código fonte. Lembre-se que: SQL não é uma linguagem procedural.
• Use os comandos que produzem sort (Group By, Order By, Distinct, etc.) somente quando necessário. No sort, o banco recupera todas as informações necessárias primeiro e depois as ordena.
• Os comandos “EXIST” e “NOT EXIST” possuem menor custo do que os comandos “IN” e “NOT IN” na maior parte dos casos. Quando a maioria dos filtros estiver na sub-query o comando “IN” se torna mais eficiente.
• Toda vez que houver função na coluna, o índice não será usado. Isto inclui as funções do Oracle.

Use: WHERE cargo = rtrim(1);

Ao invés de: WHERE rtrim(cargo) = 1;

• Evite comparar dados incompatíveis. O Oracle converte automaticamente os campos char e number.

Ex: Levando em consideração que o campo “matricula” é numérico e “codigo” é literal, temos:

WHERE matricula = ‘1234’ WHERE codigo = 1234

Note que o tipo de dados está incompatível, porém esse tipo de comparação não dá erro. Implicitamente o Oracle faz:

WHERE matricula = to_number(‘1234’) WHERE codigo = to_char(1234)

• Procure usar “EXISTS” ao invés de “DISTINCT”.

Use: SELECT dept_no, dept_name FROM dept d WHERE EXISTS (SELECT ‘X’ FROM emp e WHERE e.dept_no = d.dept_no);

Ao invés de: SELECT DISTINCT dept_no, dept_name FROM dept d, emp e WHERE d.dept_no = e.dept_no;

Além desses passos, existem outras formas de tuning de aplicação. Basta pesquisar mais a fundo! Agradeço a todos pelos e-mails que recebi essa semana. Na próxima coluna falarei sobre as responsabilidade do DBA.