Pular para o conteúdo

Result Cache em Functions: O reaproveitamento de resultados no Oracle

Result Cache em Functions: O reaproveitamento de resultados no Oracle

Quem gosta de fazer algo que já fez outrora e que já sabe o resultado? Eu não. E acredito que o banco de dados Oracle sempre esteve incomodado em repetir uma ação que ele já sabe o retorno. Até por isso, na versão 11 release 1 foi disponibilizado o RESULT_CACHE que permite – no popular – o reaproveitamento do resultado de uma execução anterior. É claro que no Oracle já existem – há muito tempo – outros mecanismos de cache, mas aqui temos algumas particularidades.

A keyword RESULT_CACHE informa aos dados “se guarde, que vou lhe usar”. Os resultados permanecem em cache e podem ser utilizados por outras sessões, não somente na corrente. Desta maneira, ao ser executado uma FUNCTION, com os mesmos argumentos e parâmetros, o resultado estará em cache e esta informação é que será retornada sem a necessidade de execuções extras.

Ainda, imagine que sua FUNCTION busca informações de uma tabela e esta tabela sofra alguma modificação nos dados. Neste caso não se preocupe, pois o cache será invalidado e as novas informações serão captadas novamente e colocada em cache. Isto acontece de forma transparente apenas para o Oracle 11g a partir do Release 2. Entretanto, no Release 1 você deverá, para estas situações, se antever e adicionar RELIES_ON(tabela) após RESULT_CACHE, caso o retorno de sua FUNCTION dependa de uma tabela.

Vamos aos exemplos:

Antes de tudo deverá ser verificado se o RESULT_CACHE está ativo:

SELECT dbms_result_cache.status() FROM dual;
Result Cach em Functions

Caso seja retornado DISABLE, deverá ser habilitado, conforme abaixo. Para este caso deverá ter privilégio de DBA:

ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 3M SCOPE = BOTH;

ALTER SYSTEM SET RESULT_CACHE_MAX_RESULT = 5 SCOPE = BOTH;

Promover o Shutdown e Startup do banco, e verificar se as alterações surtiram efeitos:

SELECT dbms_result_cache.status() FROM dual;
Spg6Abylcsw4tQHyd1xJq612KZ2gwEtpJ3v8wSY1uhmdJ1zQkQqCR OzvKvkNpBFMiwPXmBY 5TJgfHn8TAuPeGmGmcgZWwYJ8uWYK46z2XhVbRrRBX7s J9sFFlgu8Tn0qcJepyeysugMsIzg

Vamos criar a tabela base que utilizaremos na FUNCTION exemplo.

CREATE TABLE temp_teste AS
  (SELECT ROWNUM                                     my_id,
          Substr(dbms_random.String('U', 10), 1, 10) my_name,
          Round(dbms_random.Value(1, 7000))          salary
   FROM   dual
   CONNECT BY LEVEL <= 100 -- quantidade de registro a serem criados
  )

Para criar a FUNCTION exemplo, utilize o script abaixo:

CREATE OR replace FUNCTION F_buscaf(v_myid IN temp_teste.my_id%TYPE)
RETURN temp_teste%ROWTYPE
IS
  v_return temp_teste%ROWTYPE;

BEGIN
    SELECT my_id,
           my_name,
           salary
    INTO   v_return.my_id, v_return.my_name, v_return.salary
    FROM   temp_teste
    WHERE  my_id = 1;
    dbms_output.Put_line('RODOU');
    RETURN v_return;

END f_buscaf;

Observe que foi colocado um PUT_LINE com a palavra “RODOU” dentro da FUNCTION F_BUSCAF. O propósito é verificar quanto vezes esta mesma FUNCTION é executada quando se utilizando do RESULT_CACHE e quando não.

Agora, vamos comparar as execuções:

A FUNCTION F_BUSCAF será chamada sem a utilização do RESULT_CACHE, através do bloco anônimo abaixo:

SET serveroutput ON

BEGIN
    dbms_output.Put_line(F_buscaf(1).my_id
                         ||' - '
                         ||F_buscaf(1).my_name
                         ||' - '
                         ||F_buscaf(1).salary);

END;
H1SZXeYUGIxbe9Dx 05gheAo 1LKofCbNw1F09t6uBLi eOCH7Sq2 DU dL65Fd amnCN6aGxPKTgdS BKezJV8LbgT24MLaHsP3wq EVfHyHU1rxnQb6iLnj bQ0inm5r Pveez1XR Y2gUCA

Atente-se que “RODOU” foi impresso três vezes, exatamente a mesma quantidade que a FUNCTION F_BUSCAF foi chamada no bloco anônimo. Uma para buscar o MY_ID, outra para buscar MY_NAME e a terceira para buscar SALARY.

Agora, a FUNCTION F_BUSCAF se utilizará do RESULT_CACHE. Vamos recompilar a função, conforme script abaixo adicionando o keyword RESULT_CACHE antes do IS:

CREATE OR replace FUNCTION F_buscaf(v_myid IN temp_teste.my_id%TYPE)
RETURN temp_teste%ROWTYPE RESULT_CACHE
IS
  v_return temp_teste%ROWTYPE;

BEGIN
    SELECT my_id,
           my_name,
           salary
    INTO   v_return.my_id, v_return.my_name, v_return.salary
    FROM   temp_teste
    WHERE  my_id = 1;
    dbms_output.Put_line('RODOU');
    RETURN v_return;

END f_buscaf;

Executaremos o bloco anônimo:

SET serveroutput ON

BEGIN
    dbms_output.Put_line(F_buscaf(1).my_id
                         ||' - '
                         ||F_buscaf(1).my_name
                         ||' - '
                         ||F_buscaf(1).salary);

END;

Agora “RODOU” é impresso apenas uma vez e todas as informações solicitadas foram retornadas nesta única execução. Ou seja, as informações foram recuperadas do cache, haja vista que necessitava, de fato, de uma só passagem para se conhecer o conteúdo de MY_ID, MY_NAME e SALARY.

Ainda, caso, novamente seja solicitada a FUNCTION F_BUSCAF esta não precisaria ser executada, já que seu retorno está em cache e “RODOU” não seria impresso nenhuma vez. Se a tabela TEMP_TESTE sofrer alteração em seu conteúdo o cache é refeito considerando estas alterações.

Existe a possibilidade de utilizar o hint /*+ RESULT_CACHE */ para query’s, bem como “forçar” o banco de dados a se utilizar do Result Cache mesmo que este não esteja declarado no código/query alterando o parâmetro de sistema result_cache_mode para FORCE. Contudo, o tema é extenso e discutirei em outro artigo apresentando os planos de execução das execuções.

*Todos os conteúdos dos parâmetros de sistema, aqui exposto, possuem valores ilustrativos e em seu ambiente deverá ser avaliado o preenchimento correto para melhor aderência e performance.

Referências

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 29

Sem votos ! Seja o primeiro a classificar !

Deixe um comentário

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

plugins premium WordPress