Otimização de Store Procedures em PL/SQL

Problemas com desempenho e sobrecarga de servidores de banco de dados são comuns, principalmente quando lidamos com procedimentos armazenados complexos para acesso aos dados. Estes procedimentos, conhecidos também como Stored Procedures, podem durar horas e atrapalhar o processamento de outras rotinas gerando “locks” nas tabelas. Isto pode causar travamento e atraso de transações cruciais para o bom funcionamento de sistemas dependentes destes dados.

A Oracle oferece uma série de instruções em sua linguagem procedural capaz de minimizar o tempo de execução desses procedimentos. O uso auxiliar de tipos definidos, coleções e tabelas temporárias são exemplos eficazes e relativamente fáceis de usar. Apresentaremos agora os conceitos e a aplicabilidade de coleções ou tabelas por índice, TABLE OF, BULK COLLECT, FORALL e TEMPORARY TABLE.

O comando TABLE OF e a utilização de tabelas por índice em PL/SQL

O comando TABLE OF permite ao desenvolvedor declarar tipos de dados de coleção (tais como arrays). Neste artigo usaremos a declaração de tabelas por índice. Essa declaração funciona como um vetor e é mantida em memória (tendo cada um de seus elementos indexado por um valor inteiro). Neste artigo, o uso e a declaração de coleções o ajudarão durante a utilização dos recursos de Bulk Bindings. São as coleções que nos permitem manipular dados dinamicamente, juntamente com a execução dos comandos BULK COLLECT e FORALL. Apresentaremos agora algumas características do comando TABLE OF.

Nota: É importante explicitar algumas diferenças entre tabela por índice e arrays. Uma tabela por índice:

1.      Permite que seus elementos possam ser inseridos esparsamente;

2.      Não exige a definição de tamanho máximo para a tabela;

3.      Respeita o limite de 256mb de memória por usuário/sessão (Oracle 8.1.7 em 32bits).

Na declaração de uma tabela por índice devemos especificar um tipo e em seguida uma ou mais variáveis do tipo. Ao declarar o tipo, é especificado o data_type para a coleção e para o índice da tabela. O data_type pode ser um tipo escalar (NUMBER, VARCHAR2, entre outros), ou um tipo de dado composto, como um registro. A sintaxe desta declaração é:

TYPE type_name IS TABLE OF data_type INDEX BY BINARY_INTEGER;

Os parâmetros utilizados são:

·   type_name: é o nome dado ao tipo. Ele é utilizado para declarar as variáveis da tabela por índice.

·   data_type: é o tipo de dados da coleção.

Como foi dito, o parâmetro data_type pode ser escalar ou registro. Enquanto o primeiro só pode ter um único valor, o segundo pode conter vários valores relacionados. Os exemplos citados a seguir mostram estas diferenças.

TYPE val_custo_rh_func IS TABLE OF number INDEX BY BINARY_INTEGER;

TYPE nome_func_prod IS TABLE OF tab_func_prod%rowtype INDEX BY

BINARY_INTEGER;

Tendo os tipos definidos, é possível logo em seguida declarar variáveis:

v_CUSTO_RH_FUNC val_custo_rh_func;

v_NOME_FUNC nome_func_prod;

Por fim, para percorrer a tabela por índice é necessário declarar uma variável do tipo BINARY_INTEGER para ser o índice da tabela.

v_INDICE BINARY_INTEGER;

O exemplo da Listagem 1 ilustra algumas declarações de tabelas por índice, bem como as declarações de variáveis dos tipos definidos:

Nota: Toda variável do tipo Table Type possui os seguintes métodos:

COUNT => Devolve o número de elementos dentro da variável Table Type;

FIRST => Devolve o índice do primeiro elemento dentro da variável;

LAST => Devolve o índice do último elemento dentro da variável;

DELETE => Exclui todos os elementos da variável;

EXISTS(N) => Devolve TRUE caso exista o elemento de índice (n), caso contrário retorna falso;

Extend => Adiciona um elemento nulo a variável;

Listagem 1 - Tipos de tabelas PL/SQL e declarações de variáveis.

CREATE OR REPLACE PROCEDURE PR_EXEMPLO_02 is

/*Declarando a variável de índice para a tabela */

v_INDICE BINARY_INTEGER;

/*Declarando os tipos de dados para cada coluna da tabela TAB_ATIVIDADE_FUNC_PROD*/

TYPE T_CODIGO_FUNC IS TABLE OF TAB_ATIVIDADE_FUNC_PROD.CODIGO_FUNC%TYPE INDEX BY BINARY_INTEGER;

TYPE T_DATA_INICIO_ATIVIDADE IS TABLE OF TAB_ATIVIDADE_FUNC_PROD.DATA_INICIO_ATIVIDADE%TYPE INDEX BY BINARY_INTEGER;

TYPE T_DATA_TERMINO_ATIVIDADE IS TABLE OF TAB_ATIVIDADE_FUNC_PROD.DATA_TERMINO_ATIVIDADE%TYPE INDEX BY BINARY_INTEGER;

TYPE T_CODIGO_TIPO_ATIVIDADE IS TABLE OF TAB_ATIVIDADE_FUNC_PROD.CODIGO_TIPO_ATIVIDADE%TYPE INDEX BY BINARY_INTEGER;

TYPE T_DURACAO_ATIVIDADE IS TABLE OF TAB_ATIVIDADE_FUNC_PROD.DURACAO_ATIVIDADE%TYPE INDEX BY BINARY_INTEGER;

/*Declarando as variáveis dos tipos definidos nesta rotina */

v_CODIGO_FUNC T_CODIGO_FUNC;

v_DATA_INICIO_ATIVIDADE T_DATA_INICIO_ATIVIDADE;

v_DATA_TERMINO_ATIVIDADE T_DATA_TERMINO_ATIVIDADE;

v_CODIGO_TIPO_ATIVIDADE T_CODIGO_TIPO_ATIVIDADE;

v_DURACAO_ATIVIDADE T_DURACAO_ATIVIDADE;

-- Para a leitura de qualquer elemento dos tipos declarados, basta utilizar como índice a variável declarada como BINARY_INTEGER.

-- v_CODIGO_FUNC(v_INDICE);

begin

v_CODIGO_func.DELETE;

v_DATA_INICIO_ATIVIDADE.DELETE;

v_DATA_TERMINO_ATIVIDADE.DELETE;

v_CODIGO_TIPO_ATIVIDADE.DELETE;

v_DURACAO_ATIVIDADE.DELETE;

end;

Melhorando o desempenho usando o Bulk Binding

Bulk Binds são recursos da linguagem PL/SQL que permitem manipular e recuperar registros de forma performática, fornecendo agilidade e reduzindo o acesso à base dados. Isto por que o número de interrupções para execução dos códigos PL/SQL e SQL é reduzido pelo fato de coleções inteiras – não apenas elementos individuais – serem enviadas para o servidor para serem processadas.

Os comandos que apóiam o binding são o BULK COLLECT e o FORALL, ambos sados através de instruções SQL.

Vejamos agora os benefícios da utilização de Bulk Binds em detrimento de comandos SQL. A diferença pode ser notada facilmente quando são utilizados vários comandos SQL em blocos de PL/SQL, como no exemplo da Listagem 3.

Listagem 3 - Utilização de Loops para manipulação dos dadosTipos de tabelas PL/SQL e declarações de variáveis.

CREATE OR REPLACE PROCEDURE PR_EXEMPLO_04 IS

TYPE SEQC_FUNC IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

v_INDICE SEQC_FUNC;

BEGIN

FOR I IN 1..1500 LOOP

v_INDICE(I) := I;

END LOOP;

FOR I IN 1..1500 LOOP

DELETE FROM TAB_FUNC_PROD WHERE TO_NUMBER(CODIGO_FUNC,’0000000’)

= v_INDICE(I);

END LOOP;

END;

Neste código, o comando SQL DELETE é executado 1500 vezes, acessando cada valor da lista. A utilização de comandos para manipulação de registros dentro de loops ou interações torna o processo oneroso para o servidor. Cada linha de código lida dentro do laço provoca uma interrupção na linguagem PL/SQL para dar tempo de executar o comando SQL. Assim, o código visto na Listagem 3 apesar de ser bastante usado, pode ser impraticável quando o volume de dados manipulados é grande.

Apresentaremos agora os comandos do bulk bind focando no porquê deles serem mais eficientes.

BULK COLLECT

O comando BULK COLLECT permite codificar declarações SQL sem a necessidade de busca individual de informação utilizando código PL/SQL como ocorrido na Listagem 3. Ou seja, seu uso torna desnecessária a utilização de vários loops para popular as coleções evitando que o processamento da Stored Procedure seja demorado. A sintaxe genérica do comando Bulk Collect (ver exemplo na Listagem 4) é:

...BULK COLLECT INTO coleção, coleção1, coleção2...

Nota: No Oracle 8i não é permitido que o BULK COLLECT seja usado com coleções do tipo %ROWTYPE (na versão 9i é possível). A vantagem de trabalhar com %ROWTYPE é o fato de ter apenas uma linha de código declarando a coleção. Nossos exemplos usam o Oracle 8i, portanto, para cada coluna da tabela será declarada uma coleção.

Listagem 4 - Exemplo que mostra o uso do BULK COLLECT

CREATE OR REPLACE PROCEDURE PR_EXEMPLO_05 IS

CURSOR func_prod IS

SELECT CODIGO_func, NOME_func

FROM TAB_func_prod

ORDER BY NOME_func;

/*Define um tipo de tabela para cada coluna. */

TYPE CODIGO_func IS TABLE OF TAB_func_prod. CODIGO_func%TYPE INDEX BY BINARY_INTEGER;

TYPE NOME_func IS TABLE OF TAB_func_prod. NOME_func%TYPE INDEX BY BINARY_INTEGER;

/*Declara uma variável para cada coluna. */

v_CODIGO_func CODIGO_func;

v_NOME_func NOME_func;

/*Declara variável usada para índice. */

v_INDICE BINARY_INTEGER;

BEGIN

OPEN   func_prod;

FETCH func_prod BULK COLLECT INTO v_CODIGO_func, v_NOME_func;

CLOSE func_prod;

/*Exibe os resultados. */

FOR v_INDICE IN 1.. v_CODIGO_func.count LOOP

DBMS_OUTPUT.PUT_LINE (v_CODIGO_func(v_INDICE)||’-’||v_NOME_func(v_INDICE));

END LOOP;

END;

Na Listagem 4, o cursor func_prod retorna dois valores: o código e o nome do funcionário. Logo em seguida são definidos coleções para cada uma dessas colunas. O próximo passo é declarar variáveis que correspondam a cada coluna definida. A declaração FETCH usa a palavra-chave BULK COLLECT para ler todos os dados selecionados e copiá-los diretamente para os arrays. Trabalhar com o comando BULK COLLECT torna mais eficiente o acesso aos registros por que quando a de claração FETCH é acionada os registros são retornados de uma única vez.

FORALL

A palavra-chave FORALL permite que uma declaração Data Manipulation Language (DML) seja baseada no conteúdo de uma coleção, sendo executada com bastante eficiência. Quando FORALL é usada, a declaração DML é executada apenas uma vez, para toda a coleção.

O desempenho resultante é muito melhor do que se tivesse codificando comandos DELETE, UPDATE ou INSERT dentro de loops (interações), gerando interrupções da linguagem PL/SQL como foi visto no exemplo citado na Listagem 3.

Entretanto, ao utilizar o FORALL o desenvolvedor terá que ficar atento porque as transações são automaticamente gravadas. Só é possível executar o comando ROLLBACK para a última operação efetuada!

O exemplo da Listagem 5 mostra uma declaração do FORALL sendo utilizada na alteração do valor de custo de todos os funcionários da produção.

Listagem 5 - Utilização do FORALL

CREATE OR REPLACE PROCEDURE PR_EXEMPLO_06 IS

CURSOR todos_func_custo IS

SELECT CODIGO_func, CODIGO_ccusto, VALOR_custo_rh

FROM TAB_custo_func_prod

ORDER BY CODIGO_ccusto;

/*Define uma colecao para cada coluna.*/

TYPE t_CODIGO_func IS TABLE OF TAB_custo_func_prod.

CODIGO_func%TYPE INDEX BY BINARY_INTEGER;

TYPE t_CODIGO_ccusto IS TABLE OF TAB_custo_func_prod.

CODIGO_ccusto%TYPE INDEX BY BINARY_INTEGER;

TYPE t_VALOR_custo_rh IS TABLE OF TAB_custo_func_prod.

VALOR_custo_rh%TYPE INDEX BY BINARY_INTEGER;

/*Declara uma variável de tabela para cada coluna. */

v_CODIGO_FUNC t_CODIGO_func;

v_CODIGO_CCUSTO t_CODIGO_ccusto;

v_VALOR_CUSTO_RH t_VALOR_custo_rh;

/*Variável de índice para os tipos definidos*/

v_ÍNDICE BINARY_INTEGER;

BEGIN

OPEN todos_func_custo;

FETCH todos_func_custo BULK COLLECT

INTO v_CODIGO_FUNC, v_CODIGO_CCUSTO, v_VALOR_CUSTO_RH;

CLOSE todos_func_custo;

FORALL v_INDICE IN v_CODIGO_FUNC.first..v_CODIGO_FUNC.last

UPDATE TAB_custo_func_prod

SET VALOR_custo_rh = v_VALOR_custo_rh(v_INDICE) +

(v_VALOR_custo_rh(v_INDICE)*10/100)

WHERE CODIGO_func = v_CODIGO_FUNC(v_INDICE) and

CODIGO_ccusto = v_CODIGO_CCUSTO(v_INDICE);

END;

Veja, na listagem 6, o procedimento da listagem 3 modificado para usar FORALL.

Listagem 6

CREATE OR REPLACE PROCEDURE L7

PR_EXEMPLO_04_VersaoForall IS

TYPE SEQC_FUNC IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

v_INDICE SEQC_FUNC;

BEGIN

FOR I IN 1..1500 LOOP

v_INDICE(I) := I;

END LOOP;

FORALL I IN 1..1500

DELETE FROM TAB_FUNC_PROD WHERE

TO_NUMBER(CODIGO_FUNC,’0000000’)

= v_INDICE(I);

END;

Usando Tabelas Temporárias

Tabelas temporárias são usadas para armazenar conjuntos de dados intermediários, ou seja, aqueles que só estão acessíveis durante uma transação ou uma sessão.

Tabelas temporárias não permitem acessos de vários usuários. Esta é uma característica fundamental para o uso de tabelas temporárias, por que torna mais rápida a execução dos comandos SQL. O ganho de desempenho se dá pelo fato dos dados estarem na memória do servidor.

Por exemplo, quando a tabela temporária tem dados contidos em uma sessão, nenhuma outra sessão terá acesso àqueles dados, fazendo com que uma sessão nunca possa bloquear a outra. Isto faz com que o número de REDO das tabelas temporárias seja pequeno e os dados sejam acessados em memória, tornando mais ágil a execução de algum procedimento.

Nota: O REDO é conhecido também como R buffer de log ou redo logs. Contém um registro das alterações efetuadas no banco de dados, para ativar a recuperação dos dados se houver falhas.

As tabelas temporárias podem ser baseadas em sessão (os dados sobrevivem na tabela enquanto há conexão) ou transação (os dados desaparecem após o término transação com a execução do comando COMMIT). O exemplo da Listagem 7 mostra dois exemplos utilizando as declarações baseadas em sessão e transação:

Listagem 7 - Criação de Tabelas Temporárias

/*Comportamento por sessão */

CREATE GLOBAL TEMPORARY TABLE

TT_TAB_FUNC_PROD

ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TAB_FUNC_PROD;

/*Comportamento por transação */

CREATE GLOBAL TEMPORARY TABLE

TT_TAB_FUNC_PROD

ON COMMIT DELETE ROWS

AS

SELECT * FROM TAB_FUNC_PROD;

A cláusula ON COMMIT PRESERVE ROWS, permite que os registros permaneçam na tabela até que a sessão se desconecte ou que algum procedimento (da sessão) remova-os. A cláusula ON COMMIT DELETE ROWS elimina os dados após a execução do comando COMMIT.

As tabelas temporárias têm muitas características iguais às tabelas permanentes do Oracle. Podem ter triggers, restrições, índices e assim por diante.

Para finalizar, temos um exemplo completo dos recursos demonstrados na listagem 8. A stored procedure calcula ocusto total de funcionários por centro de custo, em função das horas trabalhadas. A tabela temporária funciona como apoio na consulta e gravação dos dados. No final do processo, os valores calculados na tabela temporária são armazenados fisicamente.

Listagem 8 - Utilização do FORALL

CREATE OR REPLACE PROCEDURE PR_GRAVA_CUSTO_FUNC IS

--------------------------------------------------

-- DECLARAÇÃO DAS VARIAVEIS

--------------------------------------------------

TYPE CODIGO_FUNC IS TABLE OF

TAB_ATIVIDADE_FUNC_PROD.CODIGO_FUNC%TYPE

INDEX BY BINARY_INTEGER;

TYPE DATA_INICIO_ATIVIDADE IS TABLE OF

TAB_ATIVIDADE_FUNC_PROD.DATA_INICIO_ATIVIDADE%TYPE

INDEX BY BINARY_INTEGER;

TYPE DURACAO_ATIVIDADE IS TABLE OF

TAB_ATIVIDADE_FUNC_PROD. DURACAO_ATIVIDADE%TYPE

INDEX BY BINARY_INTEGER;

TYPE CODIGO_CCUSTO IS TABLE OF

TAB_CUSTO_FUNC_PROD.CODIGO_CCUSTO%TYPE

INDEX BY BINARY_INTEGER;

TYPE VALOR_CUSTO_RH IS TABLE OF

TAB_CUSTO_FUNC_PROD.VALOR_CUSTO_RH%TYPE

INDEX BY BINARY_INTEGER;

vCODIGO_FUNC                     CODIGO_FUNC;

vDATA_INICIO_ATIVIDADE    DATA_INICIO_ATIVIDADE;

vDURACAO_ATIVIDADE         DURACAO_ATIVIDADE;

vCODIGO_CCUSTO                CODIGO_CCUSTO;

vVALOR_CUSTO_RH              VALOR_CUSTO_RH;

QTDE_VETOR BINARY_INTEGER;

QTDE_REG_TEMP NUMBER(5) := 0;

VALOR_CUSTO NUMBER(11,2) := 0;

QUANT_HORA_TRABALHADA NUMBER(11,2) := 0;

---------------------------------------------------

-- FIM DA DECLARACAO DAS VARIAVEIS

---------------------------------------------------

---------------------------------------------------

-- CURSOR PARA ACESSO DOS DADOS DE ATIVIDADES

-- REALIZADAS DOS FUNCIONÁRIOS NA PRODUÇÃO

---------------------------------------------------

CURSOR CURSOR_FUNC_PROD IS

SELECT ATIVIDADE.CODIGO_FUNC,

ATIVIDADE.DATA_INICIO_ATIVIDADE,

ATIVIDADE.DURACAO_ATIVIDADE,

CENTRO_CUSTO.CODIGO_CCUSTO,

CENTRO_CUSTO.VALOR_CUSTO_RH

FROM TAB_ATIVIDADE_FUNC_PROD ATIVIDADE,

TAB_CUSTO_FUNC_PROD CENTRO_CUSTO

WHERE ATIVIDADE.CODIGO_FUNC =

CENTRO_CUSTO.CODIGO_FUNC;

---------------------------------------------------

PROCEDURE PROCESSA_CUSTO_FUNC IS

BEGIN

QUANT_HORA_TRABALHADA :=

ROUND(vDURACAO_ATIVIDADE(QTDE_VETOR) / 60,2);

VALOR_CUSTO := vVALOR_CUSTO_RH(QTDE_VETOR) *

QUANT_HORA_TRABALHADA;

-----------------------------------------------

-- Verifica a existência de dados na tabela

-- temporária para o centro de custo.

-----------------------------------------------

SELECT COUNT(*) INTO QTDE_REG_TEMP

FROM TAB_TEMP_CUSTO_FUNC

WHERE CODIGO_CCUSTO = vCODIGO_CCUSTO(QTDE_VETOR);

-----------------------------------------------

--Se existir registro para o centro de custo.

-----------------------------------------------

IF QTDE_REG_TEMP <> 0 THEN

-----------------------------------------------

-- Atualiza o valor do custo,

-- somando com o custo existente.

-----------------------------------------------

UPDATE TAB_TEMP_CUSTO_FUNC

SET VALOR_TOTAL_CUSTO_FUNC =

  VALOR_TOTAL_CUSTO_FUNC + VALOR_CUSTO

WHERE CODIGO_CCUSTO = vCODIGO_CCUSTO(QTDE_VETOR);

ELSE

-----------------------------------------------

-- Senão, insere o centro de custo com o

-- valor do custo calculado na var. VALOR_CUSTO

-----------------------------------------------

INSERT INTO TAB_TEMP_CUSTO_FUNC

VALUES (

vCODIGO_CCUSTO (QTDE_VETOR),

VALOR_CUSTO

);

END IF;

COMMIT;

END;

----------------------------------------------

-- BLOCO PRINCIPAL

----------------------------------------------

BEGIN

------------------------------------------

--Inicializa as variáveis.

------------------------------------------

QTDE_VETOR:=1;

vCODIGO_FUNC.DELETE;

vDATA_INICIO_ATIVIDADE.DELETE;

vDURACAO_ATIVIDADE.DELETE;

vCODIGO_CCUSTO.DELETE;

vVALOR_CUSTO_RH.DELETE;

-------------------------------------------

--Insere dados nas tabelas por índice

-- através do comando Bulk Collect.

-------------------------------------------

OPEN CURSOR_FUNC_PROD;

FETCH CURSOR_FUNC_PROD BULK COLLECT INTO

vCODIGO_FUNC;

vDATA_INICIO_ATIVIDADE;

vDURACAO_ATIVIDADE;

vCODIGO_CCUSTO;

vVALOR_CUSTO_RH;

CLOSE CURSOR_FUNC_PROD;

------------------------------------------

-- Faz o loop para totalizar o

-- custo de horas trabalhadas.

------------------------------------------

WHILE QTDE_VETOR <= vCODIGO_FUNC.COUNT LOOP

PROCESSA_CUSTO_FUNC;

QTDE_VETOR:=QTDE_VETOR+1;

END LOOP;

-------------------------------------------

--Insere os dados totalizados na tabela

-- de uso corporativo.

------------------------------------------

INSERT INTO TAB_CUSTO_FUNC

(

SELECT

CODIGO_CCUSTO,

VALOR_TOTAL_CUSTO_FUNC,

SYSDATE;

FROM TAB_TEMP_CUSTO_FUNC

);

COMMIT;

DBMS_OUTPUT.PUT_LINE

(‘DADOS GERADOS COM SUCESSO!!!’);

END PR_GRAVA_CUSTO_FUNC;

O uso de tabelas temporárias como tabelas de apoio dentro de uma stored procedure torna o processamento muito mais ágil. O script completo das tabelas usadas neste exemplo está disponível para download.

Conclusão

Este artigo mostrou que é possível melhorar o desempenho do processamento de Stored Procedures sem a necessidade de muitas linhas de código. Os recursos apresentados têm a finalidade de minimizar o acesso contínuo e direto à base de dados, otimizando os processos nos servidores de banco de dados.

João Gilbert Sanábio Freesz (jgsf@terra.com.br), Analista de Sistemas, Pós-Graduado em Redes de Computadores, Desenvolvedor em Ambiente Visual Basic 6, Intranet, trabalhando com Banco de Dados (DB2/Oracle) há 8 anos.