Testes de Desempenho com Result Cache no Oracle Database: Comparação e Análise

O Result Cache é uma poderosa funcionalidade do Oracle Database que permite armazenar em cache os resultados de consultas repetitivas, melhorando significativamente o desempenho de operações de leitura intensiva. Neste post, vamos explorar como configurar o Result Cache, criar funções que o utilizam e realizar testes de desempenho para comparar os resultados com e sem o uso do cache.

Configuração do Result Cache

result_cache_mode

MANUAL: Este modo requer que o uso do Result Cache seja especificado explicitamente utilizando a cláusula RESULT_CACHE nas definições de funções ou através da hint /*+ RESULT_CACHE */ em consultas SQL. O Oracle não armazena resultados em cache automaticamente, a menos que especificado.

FORCE: Neste modo, o Oracle tenta armazenar automaticamente todos os resultados de consultas elegíveis no Result Cache, mesmo sem a cláusula RESULT_CACHE ou hint. Essa opção pode ser mais agressiva e útil para maximizar o uso do Result Cache sem modificar o código existente.

SH@pdb> -- Configurar o modo do Result Cache para MANUAL
SH@pdb> ALTER SYSTEM SET result_cache_mode = MANUAL SCOPE=BOTH SID='*';

result_cache_max_size

O parâmetro result_cache_max_size define o tamanho máximo que o Result Cache pode ocupar na memória do banco de dados. Isso é especificado com uma unidade de medida, como M para megabytes ou G para gigabytes.

SH@pdb> -- Definir o tamanho máximo do Result Cache para 200MB
SH@pdb> ALTER SYSTEM SET result_cache_max_size = 200M SCOPE=BOTH SID='*';

result_cache_max_result

O parâmetro result_cache_max_result define o tamanho máximo permitido para um único resultado de consulta em relação ao tamanho total do Result Cache. Isso é especificado como uma porcentagem (por exemplo, 1 para 1%).

SH@pdb> -- Definir o tamanho máximo de um único resultado para 1% do tamanho total do cache
SH@pdb> ALTER SYSTEM SET result_cache_max_result = 1 SCOPE=BOTH SID='*';

Criando Funções no Schema SH

Criar duas funções no schema SH: uma utilizando o Result Cache e outra sem utilizá-lo. Ambas as funções calcularão a soma dos valores vendidos (amount_sold) na tabela sh.sales para um determinado mês.

Função com Result Cache

CREATE OR REPLACE FUNCTION sh.FNC_obter_vendas_mensais_cache(p_mes IN VARCHAR2)
RETURN NUMBER
RESULT_CACHE
IS
    v_soma_vendas NUMBER;
BEGIN
    SELECT SUM(amount_sold)
    INTO v_soma_vendas
    FROM sh.sales s
    JOIN sh.times t ON s.time_id = t.time_id
    WHERE t.calendar_month_desc = p_mes;

    RETURN v_soma_vendas;
END;
/

Função sem Result Cache

CREATE OR REPLACE FUNCTION sh.FNC_obter_vendas_mensais_sem_cache(p_mes IN VARCHAR2)
RETURN NUMBER
IS
    v_soma_vendas NUMBER;
BEGIN
    SELECT SUM(amount_sold)
    INTO v_soma_vendas
    FROM sh.sales s
    JOIN sh.times t ON s.time_id = t.time_id
    WHERE t.calendar_month_desc = p_mes;

    RETURN v_soma_vendas;
END;
/

Script para Teste de Desempenho

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.ENABLE(1000000);

DECLARE
    v_inicio NUMBER;
    v_soma_vendas NUMBER;
    v_l1 VARCHAR2(100) := '=================================================================================';
    v_l2 VARCHAR2(100) := '---------------------------------------------------------------------------------';
BEGIN
    -- Executar sem Result Cache Funcao
    DBMS_OUTPUT.PUT_LINE(v_l1);
    DBMS_OUTPUT.PUT_LINE('Executando sem Result Cache Funcao:');
    DBMS_OUTPUT.PUT_LINE(v_l2);

    v_inicio := dbms_utility.get_time;
    FOR i IN 1..1000000 LOOP
        v_soma_vendas := sh.FNC_obter_vendas_mensais_sem_cache('Jan-2023');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Tempo sem Result Cache Funcao:   ' || TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos');

    -- Executar com Result Cache Funcao
    DBMS_OUTPUT.PUT_LINE(v_l1);
    DBMS_OUTPUT.PUT_LINE('Executando com Result Cache Funcao:');
    DBMS_OUTPUT.PUT_LINE(v_l2);

    v_inicio := dbms_utility.get_time;
    FOR i IN 1..1000000 LOOP
        v_soma_vendas := sh.FNC_obter_vendas_mensais_cache('Jan-2023');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Tempo com Result Cache Funcao:   ' || TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos');

    -- Executar consulta SQL com Result Cache Hint
    DBMS_OUTPUT.PUT_LINE(v_l1);
    DBMS_OUTPUT.PUT_LINE('Executando consulta SQL com Result Cache Hint:');
    DBMS_OUTPUT.PUT_LINE(v_l2);

    v_inicio := dbms_utility.get_time;
    FOR i IN 1..1000000 LOOP
        SELECT /*+ RESULT_CACHE */ SUM(amount_sold)
        INTO v_soma_vendas
        FROM sh.sales s
        JOIN sh.times t ON s.time_id = t.time_id
        WHERE t.calendar_month_desc = 'Jan-2023';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Tempo com Result Cache Hint:    ' || TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos');

    -- Executar consulta SQL sem Result Cache Hint
    DBMS_OUTPUT.PUT_LINE(v_l1);
    DBMS_OUTPUT.PUT_LINE('Executando consulta SQL sem Result Cache Hint:');
    DBMS_OUTPUT.PUT_LINE(v_l2);

    v_inicio := dbms_utility.get_time;
    FOR i IN 1..1000000 LOOP
        SELECT SUM(amount_sold)
        INTO v_soma_vendas
        FROM sh.sales s
        JOIN sh.times t ON s.time_id = t.time_id
        WHERE t.calendar_month_desc = 'Jan-2023';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Tempo sem Result Cache Hint:    ' || TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos');

    DBMS_OUTPUT.PUT_LINE(v_l1);
END;
/

Resultados Obtidos (um milhão de vezes):

Cada teste foi realizado com uma bateria de um milhão de consultas, e podemos ver uma diferença significativa nos tempos de execução entre as funções e consultas com e sem o uso do Result Cache.

Sem Result Cache na Função: O tempo de execução foi de aproximadamente 28,92 segundos. Isso é esperado, já que cada execução da função precisa recalcular a soma dos valores vendidos.
Com Result Cache na Função: O tempo de execução caiu drasticamente para apenas 0,58 segundos. Isso ocorre porque, após a primeira execução, os resultados são armazenados no cache, eliminando a necessidade de recalcular os valores.
Consulta SQL com Result Cache Hint: O tempo de execução foi de 14,02 segundos. Embora o Result Cache tenha sido usado, o overhead de processar a hint e validar os resultados em cache ainda existe.
Consulta SQL sem Result Cache Hint: O tempo de execução foi de 22,73 segundos, que é mais rápido do que a função sem cache, mas significativamente mais lento do que a função com cache.

O uso do Result Cache pode reduzir significativamente o tempo de execução de consultas repetitivas, especialmente em cenários de leitura intensiva. Com as configurações adequadas e a implementação correta, o Result Cache é uma ferramenta poderosa para melhorar o desempenho do seu Oracle Database.

Configurar os parâmetros como result_cache_mode, result_cache_max_size e result_cache_max_result permite otimizar o uso do Result Cache, controlando o comportamento de armazenamento em cache, o tamanho máximo permitido para resultados individuais e o modo de funcionamento geral. Experimente ajustar esses parâmetros de acordo com as necessidades específicas do seu ambiente para obter os melhores resultados de desempenho e eficiência operacional.

🤬🤬🤬🤬 #OracleResultCache #DBA #Performance #ChiquinhaNãoVaiComOsOutros #DicasDeDBA #OProfessorGirafalesResponde #EngenheiroDeObraPronta #ConsultasRápidas #CaceteDeAgulha #GuinaNãoTinhaDó #BóBó #DonaFlorindaMandou #VidaLoka #SobrevivendoNoInferno #ManoBrownInsights #DBAProblemático 🤬🤬🤬🤬

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.