Oracle: diferença entre determinístico e result_cache

Do tradutor: Decidi começar minha jornada para Habr não com uma tentativa de escrever algum texto exclusivo do zero, mas com a tradução de um artigo relativamente novo (de 17/08/2020) do clássico desenvolvimento PL / SQL Stephen Feuerstein , no qual ele discute a diferença em detalhes suficientes entre as duas variantes principais do cache de resultados de função PL / SQL. Espero que esta tradução seja útil para muitos desenvolvedores que estão começando com as tecnologias Oracle.

Introdução

Cedo ou tarde, qualquer desenvolvedor Oracle experiente recebe uma pergunta como:

Eu não entendo qual é exatamente a diferença entre determinístico e result_cache. Eles têm casos de uso diferentes? Eu uso determinística em muitas funções que obtêm dados de tabelas de pesquisa. Preciso usar a palavra-chave result_cache em vez de determinística?

Achei que valeria a pena escrever sobre as diferenças entre essas duas possibilidades. Primeiro, vamos ter certeza de que todos nós temos o mesmo entendimento de quando uma função é determinística.

A Wikipedia fornece a seguinte definição de algoritmo determinístico:

Um algoritmo determinístico é um algoritmo que retorna o mesmo conjunto de saídas para o mesmo conjunto de entradas, enquanto executa a mesma sequência de ações.

Em outras palavras, uma sub-rotina determinística (procedimento ou função) não tem efeitos colaterais. Ao passar um conjunto específico de valores como parâmetros de entrada, você sempre obterá o mesmo resultado na saída, independentemente de quando, onde ou com que freqüência você chama esta sub-rotina.

Uma pergunta razoável é: qual é o efeito colateral de uma função PL / SQL? No mínimo (a lista não é exaustiva):

• qualquer (ou seja, qualquer) operador DML

  
  
  
  
  

• Usando uma variável declarada fora desta função (ou seja, global, fora do escopo, também conhecido como "global")

  
  
  
  
  

• chame qualquer sub-rotina não determinística

  
  
  
  
  

, deterministic result_cache , . . ( result_cache), , , .

FUNCTION betwnstr (
   string_in   IN   VARCHAR2
 , start_in    IN   INTEGER
 , end_in      IN   INTEGER
)
   RETURN VARCHAR2 DETERMINISTIC 
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- substr



, , . , , .

, Oracle Database , DETERMINISTIC



( ).

?

• ,

  
  
  
  
  

• ( )

  
  
  
  
  

, :

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   DBMS_OUTPUT.put_line ('pass_number executed');
   RETURN 0;
END;
/

DECLARE
   n   NUMBER := 0;
BEGIN
   FOR rec IN (SELECT pass_number (1)
                 FROM all_objects
                WHERE ROWNUM < 6)
   LOOP
      n := n + 1;
   END LOOP;

   DBMS_OUTPUT.put_line (n + 1);
END;
/

pass_number executed
6
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , 5 , . Oracle Database , ( PL/SQL SQL-, ).

.

, result_cache

betwnstr



, result_cache:

FUNCTION betwnstr (
   string_in   IN   VARCHAR2
 , start_in    IN   INTEGER
 , end_in      IN   INTEGER
)
   RETURN VARCHAR2 
   RESULT_CACHE
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- RESULT_CACHE



. , DETERMINISTIC



, . , result_cache.

result_cache? :

• Oracle Database, SGA (Shared Global Area)

  
  
  
  
  

• , ,

  
  
  
  
  

• ( "" )

  
  
  
  
  

• ( Oracle - , references) , commit

  
  
  
  
  

, . RESULT_CACHE



- "" DETERMINISTIC



( , ) . RESULT_CACHE



, . RESULT_CACHE



Oracle Live SQL.

, , RESULT_CACHE



:

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
   RETURN NUMBER
   RESULT_CACHE
IS
BEGIN
   DBMS_OUTPUT.put_line ('pass_number executed for ' || i);
   RETURN 0;
END;
/

DECLARE
   n   NUMBER := 0;
BEGIN
   FOR rec IN (SELECT pass_number (100)
                 FROM all_objects
                WHERE ROWNUM < 6)
   LOOP
      n := n + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1));
END;
/

pass_number executed for 100
All done 6


BEGIN
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
END;
/

Returned 0
pass_number executed for 200
Returned 0
pass_number executed for 300
Returned 0
Returned 0
Returned 0
Returned 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

100 ( ), , , .

200 300 - , .

! ( ) PL/SQL , :

All done 6
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, RESULT_CACHE



, , . , - - .

: , result_cache, , .

deterministic result_cache?

DETERMINISTIC



RESULT_CACHE



.

?

, , DETERMINISTIC



, ( ) ( SQL-, ). , , .

, , , RESULT_CACHE



, (instance) , ( , ) . , .

?

: DETERMINISTIC



, .

PL/SQL SQL, , (, ).

, . , , .

DETERMINISTIC



, . Oracle , , .

, result_cache?

. RESULT_CACHE



. DBA, , . , SGA , (latch contention).

, result_cache. :

• ?

  
  
  
  
  

• , ? ,

  
  
  
  
  

• , , , ? , ,

  
  
  
  
  

• - , NLS? , , , , TO_CHAR
  
  
  
    .

  
  
  
  
  

: RESULT_CACHE



, , , .