Boas Praticas de Programação PL/SQL e um pouco de tunning Dia-a-Dia

Ola !!! Estou enviando este artigo de "Boas Praticas de programação PL/SQL" ou seja, são alguns assuntos que acho interessante e uso no meu dia-a-dia. Estes tópicos são algumas dicas de como programar ou melhorar a programação.

Espero que gostem e se possível mandem algumas dicas para incrementar meu artigo...

Introdução

Problemas com aplicações de baixa performance podem estar freqüentemente relacionados a consultas SQL mal estruturadas ou a um design de banco de dados ineficiente. A metodologia e tuning da Oracle, tradicionalmente é focada no design da aplicação e no tuning de consultas SQL antes mesmo de analisar quaisquer tipos de problemas relacionados à configuração do banco de dados.

A otimização de uma consulta constitui em determinar a melhor estratégia para executá-la no banco de dados. O otimizador do Oracle escolhe, por exemplo, se usará um índice ou não para uma consulta especifica e que técnicas de JOIN usar na junção de múltiplas tabelas. Estas decisões têm um impacto muito grande na performance de um SQL e por isso a otimização de uma consulta é essencial para qualquer aplicação e de extrema importância para a performance de um banco de dados relacional.

É muito importante que os desenvolvedores conheçam o otimizador do Oracle como também os conceitos relativos à tuning. Tal conhecimento irá ajudar a escrever consultas muito mais eficientes e rápidas. A proposta deste artigo é justamente fornecer uma base teórica e pratica dos principais conceitos relativos a tuning para que você já comece a escrever SQLs muito mais rápidos.

SQL Performance e Tuning

Conheça bem a aplicação como os dados contidos nela.

Informações idênticas quase sempre podem ser encontradas em diferentes fontes de dados. Se familiarize com estas fontes; você deve estar atento ao volume e a distribuição destes dados no banco de dados. Você também deve ter um profundo conhecimento do seu modelo de dados, como o relacionamento entre as entidades de negócios, antes de escrever seu SQL. Este entendimento vai ajudar a escrita de consultas mais eficientes para retirar dados de múltiplas tabelas.

Entenda o Otimizador

O otimizador determina a maneira mais eficiente de se rodar um SQL. Para executar qualquer SQL o Oracle tem que derivar um "plano de execução". O plano de execução de uma consulta é uma descrição de como o Oracle irá implementar a recuperação dos dados para satisfazer a um determinado SQL. Desde a versão 7 até a 9 o Oracle possui dois otimizadores que serão descritos a seguir:

Otimizador Baseado em Regra (Ruled Based Optimizer - RBO)

O RBO utiliza uma série de regras rígidas para determinar um plano de execução para cada SQL. Se você conhecer as regras você pode construir uma consulta SQL para acessar os dados da maneira desejada. O RBO não está sendo mais aperfeiçoado e foi descontinuado pela Oracle só sendo suportado até o Oracle 9i.

Otimizador Baseado em Custo (cost Based Optimizer - CBO)

Introduzido no Oracle 7, o CBO tentar achar o plano de execução que possui o menor custo para acessar os dados tanto para uma quantidade de trabalho especifica como para um tempo inicial de resposta mais rápido. Os Custos de diferentes planos são calculados e a opção que apresentar o menor custo de execução é escolhida. São coletadas estatísticas referentes às tabelas do banco de dados e estas são usadas para determinar um plano de execução ótimo.

O CBO não entende as características relacionadas a uma aplicação, como também não pode entender completamente o impacto de relacionamentos complexos nos joins de algumas tabelas. Ele apenas possui uma fonte de informações limitadas, baseada em estatísticas, para determinar o melhor plano de execução para cada consulta. Como o CBO assume alguns valores relativos ao custo, o plano escolhido pode não ser necessariamente o melhor plano de execução. Portanto você deve estar sempre preparado para otimizar estes SQLs em busca do plano ótimo quando preciso.

Entenda o que é seletividade

A seletividade é a primeira e mais importante medida do Otimizador Baseado em Custo. Ela representa uma fração de linhas de um conjunto resultante de uma tabela ou o resultado de um "join" ou um "group by". O CBO utiliza estatísticas para determinar a seletividade de um determinado predicado (clausula where ou having). A seletividade é diretamente ligada ao predicado da consulta como ID = 1245, ou uma combinação de predicados, como ID = 1245 AND STATUS=A. O propósito do predicado de uma consulta é limitar o escopo dela a um certo número de linhas em que estamos interessados. Portanto, a seletividade de um predicado indica quantas linhas de um conjunto vão ser filtradas por uma determinada condição.

A seletividade varia numa faixa de valores de 0.0 até 1.0 onde a seletividade de 0 indica que nenhuma linha será selecionada e 1 que todas as linhas serão selecionadas. A seletividade é igual ao numero de valores distintos que uma coluna possui. (1/NVD onde NVD significa o Numero de Valores Distintos).

Entenda o que são Variáveis de Ligação (Bind Variables)

As variáveis de ligação (bind) permitem que uma instrução SQL seja preparada uma única vez pelo banco de dados e executada inúmeras vezes, mesmo com valores diferentes para estas variáveis. Esta economia na fase de preparação a cada execução representa um ganho de eficiência (tempo e recursos) na aplicação e no servidor de banco de dados.

Além disso, variáveis de ligação facilitam a validação de tipo de dados dos valores de entrada fornecidos dinamicamente e evitam os riscos de vulnerabilidade de segurança e integridade existentes quando se constrói uma instrução SQL por concatenação de strings (Select Dinâmico). Assim, este recurso traz também robustez e segurança à execução de SQL nas aplicações.

Portanto, há grande importância e vantagens no uso de SQL preparados e variáveis de ligação (bind) nas aplicações interagindo com bancos de dados, especialmente quando envolvem valores dinâmicos e parâmetros fornecidos pelo usuário, de forma que este recurso deve ser utilizado sempre, tratando-se de boa prática de programação, portanto minha dica é que usem e abusem de bind variables !!!

Escreva SQL´s idênticos em suas aplicações

Tire toda a vantagem do uso de variáveis de ligação (Bind Variables), stored procedures e packages quando possível. Os benefícios de SQLs idênticos incluem a redução de uso de memória no servidor do banco de dados como a execução de consultas mais rápidas, pois não é necessária a fase de "parse" durante a execução do comando. Por exemplo, estes SQLs não são iguais:

Exemplos:

select * from employee where empid = 10;
select * from employee where empid = 20;

Usando uma Bind Variable chamada i_empid, a consulta ficaria assim:

select * from employee where empid = :i_empid;

Para que os comandos se tornem iguais você deve sempre usar variáveis no lugar de dados fixos, como mostra o exemplo acima.

Use índices nas tabelas cuidadosamente

Para tirar vantagem dos índices, escreva seu SQL de uma maneira que ele faça uso dele. O otimizador do Oracle não usará o acesso através de um índice simplesmente porque ele existe em uma coluna; o meio de acesso tem que ser provido pelo seu SQL !

Tenha certeza de ter criado todos os índices necessários nas tabelas, mas tome cuidado com o excesso de índices, pois eles podem degradar a performance de DMLs na tabela. Então como escolher que colunas indexar?

Use índices em colunas que são freqüentemente usados na clausula WHERE de consultas da aplicação ou de consultas usadas por usuários finais.

Indexe as colunas que são freqüentemente usadas para juntar (JOIN) as tabelas nas diferentes consultas. Prefira fazer JOIN pelas chaves primarias e chaves estrangeiras. Use índices apenas em colunas que possuem uma baixa porcentagem de linhas com valores iguais.

Não use índices em colunas que são usadas apenas com funções e operadores na clausula WHERE.

Não indexe colunas que são freqüentemente modificadas ou quando a eficiência ganha através da criação de um índice não valha a pena devido à perda de performance em operações de INSERT, UPDATE e DELETE. Com a criação do índice, estas operações perderão em performance devido à necessidade de manter o índice correto.

Índices únicos (UNIQUE) são melhores que os não únicos devido a melhor seletividade. Use índices únicos em chaves primárias e índices não únicos em chaves estrangeiras (FOREIGN KEY) e colunas usadas freqüentemente nas clausulas WHERE.

Use o "Explain Plan" quando possível - SEMPRE !!!

Se familiarize com a ferramenta EXPLAIN PLAN e use-a para otimizar seu SQL. O Explain Plan irá te ajudar a descobrir, através do plano de execução da consulta, os meios de acesso que o Oracle está utilizando para acessar as tabelas do banco de dados.

Segue aqui como executar o EXPLAIN na mão mas temos muitas ferramentas de programação, boas ferramentas por sinal, são elas (SQL DEVELOPER (QUEST), SQL DEVELOPER (ORACLE), TOAD (QUEST).

Primeiro precisamos criar a Plan_TAble, será nesta tabela que ficarão as informações:

create table PLAN_TABLE (
        statement_id    varchar2(30),
        timestamp       date,
        remarks         varchar2(80),
        operation       varchar2(30),
        options         varchar2(30),
        object_node     varchar2(128),
        object_owner    varchar2(30),
        object_name     varchar2(30),
        object_instance numeric,
        object_type     varchar2(30),
        optimizer       varchar2(255),
        search_columns  number,
        id              numeric,
        parent_id       numeric,
        position        numeric,
        cost            numeric,
        cardinality     numeric,
        bytes           numeric,
        other_tag       varchar2(255),
        partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
        other           long,
        distribution    varchar2(30));

Ou pode usar o script da Oracle o utlxplan.sql que fica em $ORACLE_HOME/rdbms/admin.

grant insert,delete,select,update on plan_table to public ;

Depois de criado basta rodarmos a nossa query que queremos analisar antes de colocar em produção (TODAS)...

EXPLAIN PLAN set STATEMENT_ID=TESTE FOR --Este é o comando e depois vem a query
SELECT dist.distributor_id,
       dist.city,
       dist.state,
       dist.zip_code,
       district.name,
       emp.last_name
 FROM distributor dist, district, employee emp
WHERE emp.employee_id = dist.manager_id
  AND district.district_id = dist.district_id;

Após executar a sua query, o sqlplus irá voltar a mensagem explain (explicado), aí basta rodar o select:

SELECT LPAD(  ,2*(LEVEL-1))||
OPERATION||   ||OPTIONS ||  ||OBJECT_NAME|| ||
DECODE(ID, 0,COST =  ||POSITION) "QUERY PLAN" 
FROM PLAN_TABLE
START WITH ID = 0 AND 
STATEMENT_ID = TESTE
CONNECT BY PRIOR ID = PARENT_ID AND
STATEMENT_ID = TESTE
ORDER BY ID

Ou pode usar o script da Oracle utlxplp.sql que fica no mesmo local que o primeiro.Além disso tem as ferramentas que fazer o explian para te auxiliar.

A Clausula WHERE é crucial

As seguintes cláusulas no WHERE não farão uso do índice mesmo que ele esteja disponível:

       A.COL1 > A.COL2 
         A.COL1 < A.COL2
         A.COL1 >= A.COL2
         A.COL1 <= A.COL2
         COL1 IS NULL
         COL1 IS NOT NULL.

Um índice não guarda o ROWID de colunas que possuem valores nulos. Qualquer consulta em linhas que possuam valores nulos o índice não pode ser utilizado.

      COL1 NOT IN (value1, value2 )
         COL1 != expression
         COL1 LIKE %teste

Neste caso, o uso do "%" no inicio da string acaba por suprimir a parte por onde coluna é indexada e por isso o índice não é usado. Por outro lado, COL1 LIKE teste% ou COL1 LIKE teste%teste% faz uso do índice resultando em uma busca por faixas limites.

NOT EXISTS subconsulta
         expression1 = expression2. 

Quaisquer expressões, funções ou cálculos envolvendo colunas indexadas não farão uso do índice se ele existir. No exemplo a seguir, o uso da função SQL UPPER vai impedir do índice ser usado provocando assim uma consulta FULL TABLE SCAN.

SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) like SALES%;

Use o WHERE ao invés de HAVING para filtrar linhas

Evite o uso da clausula HAVING junto com GROUP BY em uma coluna indexada. Neste caso o índice não é utilizado. Além disso, exclua as linhas indesejadas na sua consulta utilizando a clausula WHERE ao invés do HAVING. Se a tabela EMP possuísse um índice na coluna DEPTID, a seguinte consulta não faria uso dele:

SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;

Entretanto, a mesma consulta pode ser escrita para explorar o índice:

SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;

Especifique as colunas principais do índice na clausula WHERE

Em um índice composto a consulta apenas utilizará o índice se as principais colunas do índice estiverem especificada na clausula WHERE. A seguinte consulta usará o índice composto baseado na chave primária (PART_NUM, PRODUCT_ID) da tabela PARTS.

SELECT SN_NUM
FROM PARTS
WHERE PART_NUM = 100;

Enquanto esta consulta não se beneficiará do uso do índice composto:

SELECT SN_NUM
FROM PARTS
WHERE PRODUCT_ID = 5555;

A mesma consulta pode ser reescrita para tirar vantagem do índice. Nesta consulta o valor assumido para a coluna PART_NUM é sempre maior que zero.

SELECT SN_NUM
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;

Compare o INDEX SCAN com o FULL TABLE SCAN

Se você estiver selecionando mais de 15 % das linhas de uma tabela, um FULL TABLE SCAN é geralmente mais rápido do que o acesso pelo índice. Quando o acesso por índice causar lentidão ao invés de apresentar um ganho de performance pode utilizar algumas técnicas para eliminar o uso do índice:

SELECT EMP_NAME
FROM EMP
WHERE SALARY+0 = 50000;

A consulta a seguir não utilizará o índice mesmo que exista um na coluna SS# da tabela EMP:

SELECT EMP_NAME
FROM EMP
WHERE SS# ||   = 111-22-333;

Um índice também não é usado se o Oracle tiver que realizar uma conversão implícita de dados. Para o exemplo a seguir, SALARY é uma coluna numérica na tabela EMP e uma string é convertida num valor numérico:

SELECT EMP_NAME
FROM EMP
WHERE SALARY = 50000;

Quando a porcentagem de linhas acessadas é menor que 15 % da tabela, então o uso do índice vai será bem mais performático.