Pular para o conteúdo

Context Switching em PL/SQL: Como Evitar a Armadilha de Performance Mais Comum

Context Switching em PL/SQL: Como Evitar a Armadilha de Performance Mais Comum

Context Switching

Introdução

Confesso que até pouco tempo o context switching era algo desconhecido para mim, porém com a constante demanda por melhoria de perfomance e o grande aumento na quantidade de dados acabei por me deparar com este conceito tão importante para os desenvolvedores SQL e PL/SQL. O context switching é um dos fatores mais negligenciados que impactam a performance de aplicações Oracle. Muitos desenvolvedores escrevem código funcionalmente correto, mas acabam criando gargalos desnecessários devido ao overhead das trocas de contexto entre os engines SQL e PL/SQL.

O que é Context Switching?

Context switching ocorre quando há transferência de controle entre os engines SQL e PL/SQL do Oracle. Cada vez que o Oracle precisa alternar entre processar uma instrução SQL e executar código PL/SQL, acontece uma troca de contexto que gera overhead computacional.

Como explica a documentação oficial da Oracle: “Esta transferência de controle é chamada de context switch, e cada uma dessas trocas incorre em overhead que diminui a performance geral dos seus programas”.

Quando Acontece o Context Switching?

Chamadas de Função em Queries SQL

SQL
-- EXEMPLO PROBLEMÁTICO
SELECT employee_id, 
       get_employee_bonus(employee_id) bonus
FROM employees;

-- Function que causa context switching
FUNCTION get_employee_bonus(p_emp_id NUMBER) RETURN NUMBER IS
    l_bonus NUMBER;
BEGIN
    SELECT bonus INTO l_bonus 
    FROM bonus_table 
    WHERE employee_id = p_emp_id;
    RETURN l_bonus;
END;

Loops com Comandos SQL Individuais

SQL
-- EXEMPLO PROBLEMÁTICO
DECLARE
    CURSOR c_emp IS SELECT employee_id FROM employees;
BEGIN
    FOR rec IN c_emp LOOP
        UPDATE employees 
        SET last_updated = SYSDATE 
        WHERE employee_id = rec.employee_id;
    END LOOP;
END;

Processamento row-by-row

SQL
-- EXEMPLO PROBLEMÁTICO
DECLARE
    TYPE t_emp IS TABLE OF employees%ROWTYPE;
    l_employees t_emp;
BEGIN
    SELECT * BULK COLLECT INTO l_employees FROM employees;
    
    FOR i IN 1..l_employees.COUNT LOOP
        INSERT INTO audit_table VALUES (l_employees(i).employee_id, SYSDATE);
    END LOOP;
END;

Impacto na performance

Apesar do overhead ser geralmente muito pequeno, o context switching acontece para cada linha. No caso de 10.000 linhas, significa que você chamará a função PL/SQL 10.000 vezes. Se o overhead para cada chamada é 0.01 segundo, você terminará com 100 segundos de overhead e aumento do consumo de CPU.

Estratégias para Reduzir Context Switching

Use BULK COLLECT e FORALL

SQL
-- SOLUÇÃO OTIMIZADA
DECLARE
    TYPE t_emp_id IS TABLE OF employees.employee_id%TYPE;
    l_emp_ids t_emp_id;
    
    CURSOR c_emp IS SELECT employee_id FROM employees;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp BULK COLLECT INTO l_emp_ids LIMIT 1000;
        
        FORALL i IN 1..l_emp_ids.COUNT
            UPDATE employees 
            SET last_updated = SYSDATE 
            WHERE employee_id = l_emp_ids(i);
        
        EXIT WHEN c_emp%NOTFOUND;
    END LOOP;
    CLOSE c_emp;
END;

Substitua Funções por SQL Puro

SQL
-- Em vez de usar função
SELECT employee_id, 
       get_employee_bonus(employee_id) bonus
FROM employees;

-- Use JOIN ou subconsulta
SELECT e.employee_id, 
       NVL(b.bonus, 0) bonus
FROM employees e
LEFT JOIN bonus_table b ON e.employee_id = b.employee_id;

Use PRAGMA UDF (Oracle 12c+)

O pragma UDF informa ao compilador que a unidade PL/SQL é uma função definida pelo usuário, e que é usada principalmente em instruções SQL, o que pode melhorar sua performance.

SQL
-- SOLUÇÃO OTIMIZADA COM PRAGMA UDF
FUNCTION get_employee_bonus(p_emp_id NUMBER) RETURN NUMBER IS
    PRAGMA UDF;  -- Reduz overhead do context switching
    l_bonus NUMBER;
BEGIN
    SELECT bonus INTO l_bonus 
    FROM bonus_table 
    WHERE employee_id = p_emp_id;
    RETURN l_bonus;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;

O impacto na performance é uma pena, pois é muito benéfico encapsular lógica de negócio em um único lugar com PL/SQL. Declarar que a função PL/SQL é uma função definida pelo usuário com a opção pragma UDF reduziu o tempo de execução para 0.08 segundos – removendo a maior parte do context switching.

Use WITH FUNCTION (Oracle 12c+)

SQL
-- SOLUÇÃO MODERNA
WITH
FUNCTION get_employee_bonus(p_emp_id NUMBER) RETURN NUMBER IS
BEGIN
    RETURN (SELECT NVL(bonus, 0) FROM bonus_table WHERE employee_id = p_emp_id);
END;

SELECT employee_id, 
       get_employee_bonus(employee_id) bonus
FROM employees;

O Oracle 12c introduziu uma nova funcionalidade onde você pode declarar funções/procedimentos dentro de uma query SQL com a ajuda da cláusula WITH. O benefício desta abordagem é reduzir o context switching entre os engines SQL e PL/SQL.

Comparação Prática de Performance

Para exemplificar na prática o resultado de cada um, vamos comparar três abordagens diferentes para atualizar 100.000 registros na tabela employees:

Método 1: Loop Tradicional (LENTO)

SQL
-- PROBLEMÁTICO: Context switching para cada linha
DECLARE
    CURSOR c_emp IS SELECT employee_id FROM employees;
BEGIN
    FOR rec IN c_emp LOOP
        UPDATE employees 
        SET last_updated = SYSDATE 
        WHERE employee_id = rec.employee_id;
    END LOOP;
    COMMIT;
END;

Resultado: 45 segundos | 100.000 context switches

Método 2: BULK COLLECT com LIMIT (RÁPIDO)

SQL
-- OTIMIZADO: Processamento em lotes
DECLARE
    TYPE t_emp_id IS TABLE OF employees.employee_id%TYPE;
    l_emp_ids t_emp_id;
    CURSOR c_emp IS SELECT employee_id FROM employees;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp BULK COLLECT INTO l_emp_ids LIMIT 1000;
        
        FORALL i IN 1..l_emp_ids.COUNT
            UPDATE employees 
            SET last_updated = SYSDATE 
            WHERE employee_id = l_emp_ids(i);
        
        EXIT WHEN c_emp%NOTFOUND;
    END LOOP;
    CLOSE c_emp;
    COMMIT;
END;

Resultado: 3 segundos | 100 context switches (100.000 ÷ 1000)

Método 3: SQL Puro (MAIS RÁPIDO)

SQL
-- IDEAL: Sem context switching
UPDATE employees SET last_updated = SYSDATE;
COMMIT;

Resultado: 0.8 segundos | 0 context switches

Como Identificar Context Switching

1. Use TKPROF para Análise

  • Ativar trace
SQL
ALTER SESSION SET SQL_TRACE = TRUE;

  • Executar código suspeito
  • Analisar arquivo trace com TKPROF

2. Monitore com V$SESSTAT

SQL
SELECT s.name, m.value
FROM v$mystat m, v$statname s
WHERE m.statistic# = s.statistic#
AND s.name LIKE '%SQL%PL/SQL%';

3. Sinais de Alerta

  • Função simples executando devagar
  • Alto CPU usage sem I/O intensivo
  • Muitas chamadas de função em SELECT
  • Loops com comandos SQL individuais

Melhores Práticas

FAÇA:

  • Use BULK COLLECT e FORALL para processamento em lote
  • Prefira SQL puro quando possível
  • Implemente PRAGMA UDF em funções SQL
  • Processe dados em chunks (LIMIT 1000)
  • Use WITH FUNCTION para lógica complexa

NÃO FAÇA:

  • Loops com comandos SQL individuais
  • Funções desnecessárias em SELECT
  • Processamento row-by-row
  • Ignorar análise de performance
  • Commits dentro de loops

Conclusão

O context switching é uma armadilha comum que pode degradar significativamente a performance de aplicações Oracle. Context switching excessivo pode levar a problemas de performance porque cada troca entre SQL e PL/SQL tem um custo. Se a função é simples e chamada frequentemente dentro de uma query, pode causar lentidão perceptível.

A melhor maneira de reduzir context switches e melhorar a performance é usar SQL baseado em conjuntos. Falhando isso, podemos processar várias linhas de uma vez usando BULK COLLECT e FORALL.

Implementar essas técnicas pode resultar em melhorias de performance de 10x a 100x, especialmente em aplicações que processam grandes volumes de dados.

Com isso, espero ter ajudado no entendimento deste conceito tão importante, onde com este post busco pegar um compilado de conteúdo de várias fontes distintas para unificar e tentar demonstrar como é possível com “pequenas” mudanças obter grandes resultados no processamento dos dados.

Referências

Author

Marcel S. Santana é formado em Análise e Desenvolvimento de Sistemas pela FATEC, com MBA em Engenharia de Software SOA pela FIAP. Possui mais de 12 anos de experiência em desenvolvimento de sistemas e suporte ao cliente, atuando tanto no backend quanto no frontend, com foco em banco de dados Oracle e tecnologias como PL/SQL, JavaScript, HTML, Oracle Forms, entre outras. Nos últimos 8 anos, tem se dedicado à Oracle, trabalhando com o Oracle Retail Fiscal Management (ORFM), com forte atuação na melhoria contínua do produto, suporte e implantação em novos clientes. Seu trabalho envolve otimização de processos, garantindo eficiência e inovação na utilização da solução.

Comentário(s) da Comunidade

  3. Antonelli
    Comentar

    Gostei pra caramba do seu texto.Aproveintando a deicha gostaria de perguntar a sua opiniao se vc acha que ainda tem mercado para quem desenvolve em PLSQL?

    1. Marcel
      Comentar

      Olá Antonelli,

      Eu ainda vejo bastante mercado de PL/SQL, baseado no que tenho acompanhado no LinkedIn por exemplo. É claro que com a chegada da IA, muita coisa esta mudando e essa área não esta isenta disso, porém ainda acredito que vai levar um tempo para uma mudança tão ampla. Além disso, considero que até mesmo por conta da IA, a área de dados vai continuar sendo uma das mais relevantes por conta da importância que isso tem para o uso dela; logo, é cedo para estimar, mas acredito sim que ainda existe mercado.

Prestigie o autor e deixe o seu comentário:

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Marcações: