Ajuste de Cursores no Oracle Database para Performance Tuning

Sergio BenderSergio Bender
6 min read

Cursores no Oracle Database são estruturas de memória essenciais para processar consultas SQL, armazenando resultados e gerenciando o acesso aos dados. O ajuste de cursores é uma prática crítica em performance tuning, pois impactos no gerenciamento de cursores podem levar a erros como ORA-01000: maximum open cursors exceeded, consumo excessivo de memória no shared pool, e aumento no overhead de parsing. Esta documentação detalha os conceitos, parâmetros, estratégias de monitoramento e práticas de otimização para cursores, com foco em melhorar o desempenho do banco de dados.

Conceitos Fundamentais

  • Cursores Explícitos: Declarados em PL/SQL para processar resultados de consultas específicas.

  • Cursores Implícitos: Criados automaticamente pelo Oracle para instruções SQL.

  • Cursores Abertos: Cursores em uso por uma sessão, contabilizados contra o limite do parâmetro OPEN_CURSORS.

  • Cursores em Cache: Cursores mantidos na biblioteca de cursores (shared pool) para reutilização, reduzindo o soft parsing.

Problemas comuns relacionados a cursores incluem:

  • Excesso de cursores abertos, resultando em ORA-01000.

  • Alto soft parsing devido à baixa reutilização de cursores.

  • Fragmentação do shared pool por cursores mal gerenciados.

Parâmetros de Tuning

Os seguintes parâmetros do Oracle Database são cruciais para o ajuste de cursores:

  1. OPEN_CURSORS:

    • Define o número máximo de cursores abertos por sessão.

    • Valor padrão: 300 (varia por versão).

    • Um valor baixo causa ORA-01000, enquanto um valor muito alto aumenta o consumo de memória no PGA.

    • Tuning: Ajuste com base no uso máximo observado, com margem de 20-30%.

  2. SESSION_CACHED_CURSORS:

    • Controla quantos cursores uma sessão pode manter em cache para reutilização.

    • Valor padrão: 50.

    • Aumentar este parâmetro reduz o soft parsing, mas consome mais memória no shared pool.

    • Tuning: Valores entre 100 e 200 são comuns em sistemas com alto parsing.

  3. CURSOR_SHARING:

    • Determina se literais em consultas SQL são substituídos por variáveis de ligação.

    • Valores: EXACT (padrão), FORCE.

    • Configurar como FORCE promove reutilização de cursores, mas pode alterar planos de execução.

    • Tuning: Use FORCE em sistemas com muitas consultas semelhantes, após testes rigorosos.

  4. SHARED_POOL_SIZE:

    • Define o tamanho do shared pool, que armazena cursores em cache.

    • Tuning: Aumente se houver fragmentação ou falta de espaço para cursores.

Monitoramento para Tuning

Monitorar o uso de cursores é essencial para identificar gargalos e ajustar parâmetros de forma precisa. As seguintes visões e ferramentas são recomendadas:

  1. V$OPEN_CURSOR:

    • Lista cursores abertos por sessão.

    • Consulta para verificar cursores abertos:

        SELECT s.sid, s.username, COUNT(*) as open_cursors
  FROM v$open_cursor oc
  JOIN v$session s ON oc.sid = s.sid
  GROUP BY s.sid, s.username
  ORDER BY open_cursors DESC;

  2. V$SESSTAT e V$STATNAME:

    • Monitora o número de cursores abertos por sessão.

    • Exemplo:

        SELECT s.sid, s.username, st.value as open_cursors
  FROM v$sesstat st
  JOIN v$statname sn ON st.statistic# = sn.statistic#
  JOIN v$session s ON st.sid = s.sid
  WHERE sn.name = 'opened cursors current'
  ORDER BY st.value DESC;

  3. V$SYSSTAT:

    • Fornece estatísticas globais, como taxa de parsing.

    • Exemplo:

        SELECT name, value
  FROM v$sysstat
  WHERE name LIKE 'parse%';

    • Métricas-chave:

      • parse count (total): Alto valor indica necessidade de mais cache de cursores.

      • cursor cache hits: Baixa taxa sugere ajustes em SESSION_CACHED_CURSORS.

  4. AWR e ADDM:

    • Relatórios AWR identificam alto soft parsing, consumo de shared pool e problemas com cursores.

    • Verifique:

      • "Cursor Cache Hit Ratio": Taxa <90% indica necessidade de ajuste.

      • "Top SQL by Parse Calls": Identifica consultas que geram muitos cursores.

    • ADDM pode sugerir ajustes em OPEN_CURSORS ou SESSION_CACHED_CURSORS.

  5. SQL Tuning Advisor:

    • Analise consultas que consomem muitos cursores para otimização.

Estratégias de Performance Tuning

  1. Ajuste de OPEN_CURSORS:

    • Use as consultas de monitoramento para determinar o uso máximo de cursores.

    • Exemplo: Se o pico for 250 cursores, configure:

        ALTER SYSTEM SET open_cursors = 350 SCOPE=BOTH;

    • Monitore o impacto no PGA e ajuste gradualmente.

  2. Otimização de SESSION_CACHED_CURSORS:

    • Aumente se o AWR indicar alta taxa de soft parsing.

    • Exemplo:

        ALTER SYSTEM SET session_cached_cursors = 150 SCOPE=BOTH;

    • Verifique a taxa de "cursor cache hits" no AWR após o ajuste.

  3. Uso de CURSOR_SHARING = FORCE:

    • Aplique em sistemas com consultas repetitivas usando literais.

    • Exemplo:

        ALTER SYSTEM SET cursor_sharing = 'FORCE' SCOPE=BOTH;

    • Execute testes para garantir que os planos de execução não sejam impactados negativamente.

  4. Gerenciamento do Shared Pool:

    • Monitore fragmentação com:

        SELECT name, value
  FROM v$sgastat
  WHERE pool = 'shared pool' AND name LIKE '%free memory%';

    • Aumente SHARED_POOL_SIZE se necessário:

        ALTER SYSTEM SET shared_pool_size = 500M SCOPE=BOTH;

  5. Otimização de Consultas:

    • Use variáveis de ligação para reduzir parsing:

        SELECT * FROM employees WHERE employee_id = :emp_id;

    • Evite literais fixos, que criam novos cursores:

        -- Evitar:
  SELECT * FROM employees WHERE employee_id = 100;

  6. Fechamento de Cursores:

    • Em PL/SQL, garanta que cursores explícitos sejam fechados:

        DECLARE
    CURSOR c IS SELECT * FROM employees;
    v_employee employees%ROWTYPE;
  BEGIN
    OPEN c;
    FETCH c INTO v_employee;
    CLOSE c;
  END;

  7. Diagnóstico de Vazamentos de Cursores:

    • Identifique sessões com alto uso de cursores:

        SELECT s.sid, s.username, c.value as open_cursors, q.sql_text
  FROM v$session s
  JOIN v$sesstat c ON s.sid = c.sid
  JOIN v$statname n ON c.statistic# = n.statistic#
  LEFT JOIN v$sql q ON s.sql_id = q.sql_id
  WHERE n.name = 'opened cursors current'
    AND c.value > 200
  ORDER BY c.value DESC;

    • Corrija aplicações que não fecham cursores adequadamente.

Exemplo Prático: Tuning de Cursores

Cenário: Relatórios AWR mostram alta taxa de soft parsing e erros ORA-01000. O valor atual de OPEN_CURSORS é 300, e SESSION_CACHED_CURSORS é 50.

  1. Verificar Uso de Cursores:

     SELECT s.sid, s.username, COUNT(*) as open_cursors
 FROM v$open_cursor oc
 JOIN v$session s ON oc.sid = s.sid
 GROUP BY s.sid, s.username
 HAVING COUNT(*) > 200
 ORDER BY open_cursors DESC;

    Resultado: Sessões atingem ~280 cursores.

  2. Ajustar OPEN_CURSORS:

     ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

  3. Analisar Soft Parsing no AWR:

    • Se "Parse Calls" for alto, aumente SESSION_CACHED_CURSORS:

        ALTER SYSTEM SET session_cached_cursors = 150 SCOPE=BOTH;

  4. Otimizar Consultas:

    • Identifique consultas com literais fixos:

        SELECT sql_text, parse_calls
  FROM v$sql
  WHERE parse_calls > 100
  ORDER BY parse_calls DESC;

    • Modifique para usar variáveis de ligação.

  5. Monitorar Resultados:

    • Gere um novo relatório AWR e verifique:

      • Aumento no "Cursor Cache Hit Ratio".

      • Redução em "Parse Calls".

      • Eliminação de erros ORA-01000.

Boas Práticas de Tuning

  • Baseie Ajustes em Dados: Use AWR, ADDM e visões dinâmicas para determinar valores apropriados para OPEN_CURSORS e SESSION_CACHED_CURSORS.

  • Evite Valores Excessivos: Configurações muito altas (ex.: OPEN_CURSORS > 5000) podem mascarar vazamentos de cursores e aumentar o consumo de memória.

  • Teste em Homologação: Aplique mudanças em um ambiente de teste antes de produção.

  • Monitore Continuamente: Configure alertas para uso elevado de cursores e revise relatórios AWR regularmente.

  • Integre com Outras Otimizações: Combine o ajuste de cursores com gerenciamento de índices, estatísticas atualizadas e tuning de consultas.

Ferramentas de Suporte

  • AWR: Identifica problemas de parsing e uso de cursores.

  • ADDM: Sugere ajustes em parâmetros e otimizações.

  • SQL Tuning Advisor: Recomenda melhorias em consultas que geram muitos cursores.

  • Enterprise Manager: Facilita monitoramento e ajustes via interface gráfica.

O ajuste de cursores é uma prática fundamental para otimizar o desempenho do Oracle Database. Configurações inadequadas podem levar a gargalos de performance, enquanto ajustes bem planejados melhoram a eficiência do parsing e reduzem o consumo de recursos. Combine monitoramento rigoroso, ajustes baseados em evidências e otimizações na aplicação para alcançar um ambiente estável e eficiente.

0
Subscribe to my newsletter

Read articles from Sergio Bender directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Oracletunning

Written by

Sergio Bender
Sergio Bender