Implicit Fetch Call Bulk Mode

Implicit Fetch Call Bulk Mode

Há um tempo atrás passei por uma situação que me fez escrever este artigo. Estava, eu, incumbido de alterar uma procedure para ganho de performance de um processo batch. Como uma primeira ação, iniciei substituindo os FOR LOOP, os quais aparentavam estarem sendo processados row by row, por BULK COLLECT.

Em uma primeira execução após as alterações supracitadas notei que o ganho de performance havia sido alcançado, com tudo bem ínfimo ao que eu esperava. O ganho tinha sido bem pequeno mesmo. Decidi então efetuar o trace das duas versões da procedure e eis que vejo que o FOR LOOP estava fazendo o fetch das linhas em bulk collect, em uma proporção de fetch size de 100.

Assim me lembrei do conceito de Implicit Fetch Call Bulk Mode, descrito no livro Expert PL/SQL Practices: For Oracle Developers and DBAs de 2011 da editora Apress. A partir da versão 10 do Oracle foi feito uma melhoria para automatizar o bulk collect em algumas situações. Como em tese um cursor dentro do FOR LOOP recupera todas as linhas de um só vez, o engine do Oracle se utiliza da otimização do bulk collect para retornar as linhas da forma mais eficiente possível. Contudo, para isto existe alguns pontos a serem levados em consideração:

• Dentro do loop não deverá ter comandos de linhas únicas ou controle, como por exemplo CONTINUE e EXIT;
• O fetch size é feito com 100 linhas (ao menos nos meus testes e em alguns artigos da internet que afirmam isto);
• O parâmetro PLSQL_OPTIMIZE_LEVEL deverá está 2 ou mais;

Com intuito de simular tal ocorrência, vamos primeiramente criar uma tabela com 1 milhão de registros:

--- criando tabela com 1 milhão de registros

CREATE TABLE temp_teste AS
SELECT ROWNUM ID,
  CAST (dbms_random.STRING('U',10) AS VARCHAR2(10)) NOME,
  MOD(ROWNUM,5) CLASSE
FROM DUAL
  CONNECT BY LEVEL <= 1000000;

Agora, irei, para a sessão, garantir que o PLSQL_OPTIMIZE_LEVEL está 2 e ativar o trace:

alter session set plsql_optimize_level = 2;

alter session set tracefile_identifier = "MEU_TRACE";

alter session set events '10046 trace name context forever, level 12';

Em um primeiro teste executarei o cursor explicitamente sendo row by row:

DECLARE
  CURSOR c_cursor
  is
    SELECT nome nome1 FROM temp_teste;
  v_nome temp_teste.nome%TYPE;

BEGIN
  OPEN c_cursor;
  LOOP
    FETCH c_cursor INTO v_nome;
    EXIT
  WHEN c_cursor%notfound;
  END LOOP;
  CLOSE c_cursor;
END;

Ao analisar o trace observamos que para recuperar 1 milhão de registros foi feito o fetch row by row, tendo o count para esta operação sido 1000001.

Agora vamos ver como se comporta ao realizar um bulk collect explícito de todas as linhas.

DECLARE
  CURSOR c_cursor
  IS
    SELECT nome nome2 FROM temp_teste;

TYPE trow_type
IS
  TABLE OF c_cursor%rowtype INDEX BY pls_integer;
  trow trow_type;

BEGIN
  OPEN c_cursor;
  FETCH C_CURSOR BULK COLLECT INTO TROW;
  CLOSE c_cursor;
END;

O trace mostra que necessitou de apenas 1 Fetch para as mesmas 1 milhão de linhas. Até aqui tudo normal.

Vamos, agora, nos valer da tentativa de simular o Implicit Fetch Call Bulk Mode utilizando o FOR LOOP:

BEGIN
  for c_cur in
  (SELECT nome nome4 FROM temp_teste
  )
  LOOP
    NULL;
  END LOOP;
END;

Podemos ver que para recuperar 1 milhão de linhas, o For Loop realizou Fetch de 10001, ou seja, o fetch size tem tamanho 100 linhas. Isto, nos mostra que automaticamente a engine do Oracle entendeu que poderia fazer um bulk collect automático, não necessitando explicitar a realização desta atividade. Mas porque fez 10001 e não 10000? O 1 no final é dado, pois o Oracle necessita de uma última passada só para garantir que não existe quaisquer outras linhas a serem recuperadas.

Por fim, apenas com efeito de curiosidade e comparação, explicitamente será feito Bulk Collect, contudo com a cláusula LIMIT 100 para comportar-se conforme o Implicit Fetch Call Bulk Mode do exemplo anterior:

DECLARE
  CURSOR c_cursor
  is
    SELECT nome nome5 FROM temp_teste;

TYPE trow_type
IS
  TABLE OF c_cursor%rowtype INDEX BY pls_integer;
  trow trow_type;

BEGIN
  OPEN C_CURSOR;
  loop
    FETCH C_CURSOR BULK COLLECT INTO TROW LIMIT 100;
    EXIT
  WHEN TROW.COUNT = 0;
  END LOOP;
  close c_cursor;
END;

Para este último exemplo, vê-se que com o Limit 100 o fetch necessário para trazer as 1 milhão de linhas foram os mesmos do o Implicit Fetch Call Bulk Mode, ou seja, 10001.

Notas

Foi utilizado o tkprof para análise do arquivo de trace;

Para desabilitar o trace foi utilizado: ALTER SESSION SET EVENTS ‘10046 trace name context off’;

Referências

• Livro: Expert PL/SQL Practices: For Oracle Developers and DBAs
• http://www.dba-oracle.com/t_oracle_bulk_collect.htm
• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:588234700346069527
• https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4627185800346328273