Aumentando a performance de procedimentos através da redução de trocas de contexto

Por Paulo Alexandre Petruzalek
Postado en julho 2012

Introdução

De modo geral, performance costuma ser uma preocupação diária tanto de DBAs como de desenvolvedores de banco de dados. O objetivo deste artigo é apresentar algumas técnicas para melhoria de performance dentro do escopo do profissional desenvolvedor, através da redução de trocas de contexto entre SQL e PL/SQL, o que costuma ser uma alteração relativamente simples na forma de codificar que pode acarretar em ganhos bastante significativos.

Conceitos básicos

O banco de dados Oracle trabalha internamente com duas engines distintas para o processamento de comandos: uma responsável pelo processamento de querys em SQL e outra pelo processamento de blocos PL/SQL.

No nosso dia a dia é muito comum fazermos uma mistura destes dois tipos de código, seja quando dentro de um bloco PL/SQL chamamos uma query em SQL ou quando dentro de uma query fazemos referência a uma função em PL/SQL.

Quando temos esta combinação de SQL com PL/SQL o banco necessita realizar internamente o chaveamento entre as duas engines, fenômeno conhecido tecnicamente como troca de contexto. O problema é que em cada troca de contexto temos um custo em termos de tempo. Embora pequeno isoladamente, se executado dentro de um loop para o processamento de milhares ou mesmo milhões de registros, o impacto destas trocas acaba se tornando bastante significativo.

Portanto, otimizar as trocas de contexto é um ponto crucial para obter uma boa performance de um banco de dados Oracle. Abaixo iremos discutir como melhorar a performance de procedimentos reduzindo o número de trocas de contexto.

Reduzindo o número de trocas de contexto

Existem diversas técnicas para reduzir trocas de contexto. Dentro do escopo deste artigo vou tratar principalmente da troca de contexto envolvendo atribuição de valores, pois acredito que seja uma das formas mais simples de ser evitada. A prática a que estou me referindo, especificamente, é a de atribuir valores a uma variável utilizando um select from dual ao invés de uma atribuição direta. Veja o exemplo:

SQL> declare
  2  x number;
  3  begin
  4  -- incorreto
  5  select 1 into x from dual;
  6  
  7  -- correto
  8  x := 1;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Esta prática também é válida para chamadas de funções:

SQL> declare
  2  v_data date;
  3  begin
  4  -- incorreto                        
  5  select sysdate into v_data from dual;
  6  
  7  -- correto
  8  v_data := sysdate;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Historicamente falando, em especial se tratando da chamada de funções via querys, esta é uma prática que vem desde os tempos remotos quando o PL/SQL ainda não possuia implementações nativas para a maioria das funções SQL. Então criou-se o costume de fazer chamadas de função usando a tabela dual e, como velhos hábitos são difíceis de derrubar, o banco evoluiu mas a prática continuou a mesma. O fato é que houve um esforço imenso da Oracle para disponibilizar todas as funções SQL no contexto PL/SQL. Pela minha experiência pessoal, no Oracle 11g R2 só houve um caso onde não existia a função no contexto PL/SQL – a função REVERSE – onde foi necessário manter a chamada pela tabela dual:

SQL> set serveroutput on
SQL> declare
  2  x varchar2(5) := '12345';
  3  begin
  4  x := reverse(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
x := reverse(x);
     *
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00201: identifier 'REVERSE' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

Alterando a linha 4 para fazer o select into:

SQL> 4 select reverse(x) into x from dual;;
SQL> l
  1  declare
  2  x varchar2(5) := '12345';
  3  begin
  4  select reverse(x) into x from dual;
  5  dbms_output.put_line(x);
  6* end;
SQL> /
54321

PL/SQL procedure successfully completed.

Portanto, sempre que possível, devemos buscar fazer as chamadas de função com atribuições no contexto PL/SQL pois, atualmente, as situações onde as funções não existem são exceções e não a regra.

Abaixo, veremos um exemplo prático para demonstrar a diferença de performance entre os dois tipos de abordagem. Primeiro, vamos fazer um loop de 10000 iterações fazendo a atribuição do retorno da função SYSDATE a uma variável do tipo DATE utilizando um select into from dual.

SQL> declare
  2  t number := dbms_utility.get_time();
  3  x date;
  4  begin
  5  for r in (select rownum from dual connect by level <= 10000)
  6  loop
  7  select sysdate into x from dual;
  8  end loop;
  9  dbms_output.put_line('hsecs=' || to_char(dbms_utility.get_time() - t));
 10  end;
 11  /
hsecs=87

PL/SQL procedure successfully completed.

Agora, vamos modificar o bloco acima para trocar o select into por uma atribuição direta, alterando apenas a linha 7:

SQL> 7 x := sysdate;;
SQL> l
  1  declare
  2  t number := dbms_utility.get_time();
  3  x date;
  4  begin
  5  for r in (select rownum from dual connect by level <= 10000)
  6  loop
  7  x := sysdate;
  8  end loop;
  9  dbms_output.put_line('hsecs=' || to_char(dbms_utility.get_time() - t));
 10* end;
SQL> /
hsecs=12

PL/SQL procedure successfully completed.

Podemos ver que a diferença é espetacular: o bloco com atribuição direta rodou em 12 centésimos de segundo contra 87 centésimos da versão com o select into, ou seja, 8 vezes mais rápido! Como o código é idêntico, com exceção da atribuição, podemos dizer seguramente que esta diferença se dá devido a quantidade de trocas de contexto envolvida em cada um dos blocos.

A exceção: scalar subquery caching

Como de praxe, para toda regra existe uma exceção, e neste caso não poderia ser diferente. Para não ser injusto com o select from dual vou apresentar aqui uma técnica muito interessante que tem o efeito exatamente contrário da situação descrita anteriormente: o uso do select from dual pode ajudar a reduzir as trocas de contexto em chamadas de função, desde que seja aplicado na situação correta. O segredo está num recurso interno do Oracle chamado subquery caching, ou seja, quando fazemos uma subquery dentro de um select, o Oracle é capaz de identificar resultados repetidos desta subquery e guardá-los num cache para serem reaproveitados sem a necessidade de executar a mesma subquery para várias linhas diferentes do mesmo result set.

O segredo, então, está em fazer com que o Oracle trate uma chamada simples de função como uma subquery, para que o resultado da função entre no cache de subquerys e a função seja chamada apenas uma vez para cada conjunto diferente de parâmetros. Esta técnica é denominada scalar subquery caching. Tudo vai ficar mais claro com o exemplo abaixo, mas primeiro, vamos preparar o cenário.

O objetivo é criar uma tabela grande para que os resultados sejam mensuráveis. Também é importante que nesta tabela existam vários valores repetidos para que a técnica de cache funcione. Como exemplo, escolhi fazer uma cópia da view ALL_OBJECTS com apenas as colunas OWNER e OBJECT_NAME múltiplas vezes até criar uma tabela com cerca de 1 milhão de linhas. Estarei utilizando a coluna OWNER no nosso teste porque ela possui muitas repetições, o que a torna uma ótima candidata para o cache.

SQL> create table t nologging as 
  2  select owner, object_name                    
  3    from all_objects, 
  4         (select 1 from dual connect by level <= 100); 
 
Table created. 
  
SQL> select count(*) from t; 
 
  COUNT(*) 
---------- 
   1204400 

Agora que nossa tabela de exemplo está com mais de 1 milhão de linhas vamos aos testes. Primeiramente, vou criar uma cópia desta tabela vazia para servir de benchmark e também para podermos desconsiderar a influência do db block cache nos próximos passos.

SQL> set timing on
SQL> create table t1 nologging as 
  2  select * from t; 
 
Table created. 
 
Elapsed: 00:00:02.11

Em seguida, vamos criar três cópias desta tabela passando a função LOWER na coluna OWNER. Estou fazendo cada operação três vezes para diminuir a influência de eventos pontuais que possam mascarar os resultados do teste.

SQL> create table t2 nologging as 
  2  select lower(owner) owner, object_name from t;  
 
Table created. 
 
Elapsed: 00:00:03.09 

SQL> create table t3 nologging as 
  2  select lower(owner) owner, object_name from t; 
 
Table created. 
 
Elapsed: 00:00:03.04 

SQL> create table t4 nologging as 
  2  select lower(owner) owner, object_name from t; 
 
Table created. 
 
Elapsed: 00:00:03.07 

Nota-se que ficou nitidamente mais lento em função do processamento da coluna OWNER. Agora vamos tentar minimizar este impacto utilizando a técnica de scalar subquery caching:

SQL> create table t5 nologging as 
  2  select (select lower(owner) from dual) owner, object_name from t; 
 
Table created. 
 
Elapsed: 00:00:02.64 

SQL> create table t6 nologging as 
  2  select (select lower(owner) from dual) owner, object_name from t; 
 
Table created. 
 
Elapsed: 00:00:02.62 

SQL> create table t7 nologging as 
  2  select (select lower(owner) from dual) owner, object_name from t; 
 
Table created. 
 
Elapsed: 00:00:02.63 

Utilizando-se a técnica de scalar subquery caching o mesmo create table executou cerca de 15% mais rápido, pois o Oracle não precisa mais calcular a função LOWER para todas as linhas da tabela: ele irá apenas chamar a função LOWER para os valores distintos de OWNER. Esta técnica tem ainda mais importância quando se tratam de funções codificadas em PL/SQL, o que implicaria em uma troca de contexto para cada linha da query. Usando a scalar subquery caching você estará também evitando a troca de contexto, além de todo e qualquer processamento adicional que incorre da chamada de função.

Claro que toda técnica tem seus prós e contras. No caso da scalar subquery caching, não seria apropriado utilizá-la caso o comportamento esperado da função não seja determinístico, ou seja, quando para um dado conjunto de parâmetros de chamada não há garantia de que a função retorne o mesmo resultado. Neste caso, utilizar a scalar subquery caching pode causar o retorno de dados incorretos, pois apenas a primeira chamada da função será executada.

Conclusão

Adotar um padrão de codificação que engloba evitar chamadas de função ou atribuições através da tabela dual é uma boa prática de programação que se empregada desde os primeiros momentos do projeto pode significar um ganho significativo de performance e uma redução da necessidade de refactoring. A troca de contexto entre as engines SQL e PL/SQL tem um impacto bastante significativo na performance de functions, stored procedures e blocos anônimos e, portanto, deve ser um objetivo de design minimizá-la desde o começo.

A exceção a esta regra é quando a chamada de função utilizando uma subquery na tabela dual é utilizada como recurso de otimização, técnica conhecida como scalar subquery caching. Nesta situação o processo é inverso: ao invés de induzir trocas de contexto a chamada de função é armazenada no cache de subquerys e menos chamadas de função são necessárias para produzir o mesmo result set, ocasionando ganhos significativos de performance.




Postado por Paulo Alexandre Petruzalek. Oracle Advanced PL/SQL Developer Certified Professional, Oracle PL/SQL Developer Certified Associate, Oracle Database 11g Administrator Certified Associate, Oracle Database SQL Certified Expert, OPN Certified Specialist.