Utilizando Processamento Paralelo com o PARALLEL ENABLE
Por Tércio Costa,
Postado em Junho 2016
Revisado por Marcelo Pivovar - Sulution Architect
Hoje em dia, o uso de processadores com mais de um core está completamente comum. Então, podemos tirar um pleno proveito disto, habilitando o processamento paralelos nas nossas querys, para isto iremos utilizar a cláusula PARALLEL ENABLE.
Quando essa cláusula é habilitada, a nossa query, como em uma função por exemplo, terá a capacidade de processar os dados nela em mais de uma seção diferente, quando o normal seria em apenas uma. Com certeza isso nos trará uma grande vantagem de performance, onde iremos ganhar em tempo de execução.
Mas claro, que para isto funcionar, a nossa função deverá satisfazer alguns requerimentos, que são:
- A mais óbvia, é que a função contenha a cláusula PARALLEL ENABLE.
- Ter também a cláusula PARTITION BY com exatamente um REF CURSOR
Com a cláusula PARTITION BY e o cursor, o Oracle saberá como dividir o trabalho para mais de uma sessão, sem isto seria impossível o Oracle saber como dividir o trabalho e assim trabalhar em paralelo. Apenas as cláusulas referente à PARALLEL ENABLE pode ser vista na Listagem 1.
Listagem 1. Cláusula PARALLEL ENABLE.
01 PARALLEL_ENABLE(PARTITION parameter-name BY [{HASH | RANGE} (culumn-list) | ANY ])
Podemos verificar que existe mais de uma opção de como particionar o nosso cursor. Utilizando as opções HASH ou RANGE podemos especificar com base nos valores de quais culunas será feito o particionamento. Já a opção ANY será particionado de modo aleatório pelo próprio Oracle. Uma observação a se fazer é referente ao tipo de cursor utilizado. Se o nosso cursor for do tipo weakly typed, ou seja, sem um retorno pré definido através da cláusula RETURN, só podemos utilizar o método de particionamento ANY. Mas se o nosso cursor for strong typed, podemos utilizar quaisquer uma das opções já mencionadas.
Para ver um exemplo de uma função assim, devemos criar o nosso ambiente, uma tabela já bem populada, com um vulume de dados considerável. Veja isto na Listagem 2 a seguir
Listagem 2. Criando o ambiente de Testes.
01 CREATE TABLE table_parallel (
02 idd NUMBER,
03 nome VARCHAR2(15)
04 );
05 /
06
07 BEGIN
08 FOR i IN 1..300000 LOOP
09 INSERT INTO table_parallel
10 VALUES(1, dbms_random.string('p', 15));
11 INSERT INTO table_parallel
12 VALUES(2, dbms_random.string('p', 15));
13 INSERT INTO table_parallel
14 VALUES(3, dbms_random.string('p', 15));
15 END LOOP;
16 END;
17 /
18
19 COMMIT;
20 /
Na listagem acima, foi criado uma tabela simples, com apenas duas culunas. Nas linhas 07 à17, no bloco anônimo, foi feito um loop onde foi adicionado exatamente 900 mil linhas com um VARCHAR2 aleatório e um outro valor já definido. Sendo assim, pode-se verificar que existe para cada 1/3 do conteúdo da tabela um valor em comum entre as linhas, formando 3 grupos com valores em comum. Isso será a base das nossas partições na função, dividindo o trabalho em mais de uma sessão.
Veja agora na Listagem 3, a criação de um package onde irá conter o nosso cursor entre outros tipos de dados e principalmente a nossa função com a cláusula PARALLEL ENABLE.
Listagem 3. Package e Função com PARALLLE ENABLE.
01 CREATE OR REPLACE PACKAGE parallel_package AS
02 --Record, registro de uma linha
03 TYPE p_parallel_record IS RECORD (
04 idd NUMBER,
05 nome VARCHAR2(15),
06 sidd NUMBER
07 );
08 --tabela de linhas
09 TYPE p_parallel_table IS TABLE OF p_parallel_record;
10 --Strong TYPED CURSOR
11 TYPE p_parallel_cursor IS REF CURSOR RETURN table_parallel%ROWTYPE;
12 --funcion assgnature
13 função parallel_função(f_cursor P_PARALLEL_CURSOR)
14 RETURN p_parallel_table PIPELINED
15 PARALLEL_ENABLE(PARTITION f_cursor BY HASH(idd));
16 END parallel_package;
17 /
18
19 CREATE OR REPLACE PACKAGE BODY parallel_package AS
20 função parallel_função(f_cursor P_PARALLEL_CURSOR)
21 RETURN p_parallel_table PIPELINED
22 PARALLEL_ENABLE(PARTITION f_cursor BY HASH(idd)) IS
23 linha p_parallel_record;
24 BEGIN
25 LOOP
26 FETCH f_cursor
27 INTO linha.idd, linha.nome;
28
29 EXIT WHEN f_cursor%NOTFOUND;
30
31 SELECT sid
32 INTO linha.sidd
33 FROM v$mystat
34 FETCH FIRST 1 ROW ONLY;
35
36 PIPE ROW(linha);
37 END LOOP;
38 RETURN;
39 END parallel_função;
40 END parallel_package;
41 /
Vamos analisar agora, o conteúdo do código acima, do package. Nas linhas 1 à 17, temos a especificação do nosso package. Nas linhas 3 à 7 é criado um objeto do tipo RECORD que condiz com a estrutura da tabela criada na Listagem 2. Mas nesse objeto foi adicionado mais uma culunaque não exista na tabela. Essa culuna irá armazenar o sid, que é o ID da seção que fez o processamento da partição correspondente da função. Esse valor é conseguido através da view v$mystat, na culuna sid. Na linha 9 é criado um tipo de dado, que é uma tabela do objeto RECORD que já foi discutido aqui. Logo após na linha 11 é criado o nosso CURSOR STRING TYPED. O retorno desse nosso cursor é exatamente um registro de linha da tabela que já foi mencionada aqui. Finalmente nas linhas 14 à 16, temos a assinatura da nossa função. Vamos analisar bem essa função. Na primeira linha, o argumento é justamente um CURSOR do tipo que criamos anteriormente nesse mesmo package. Na linha 22 foi utilizado esse mesmo argumento para fazer a partição, PARTITION BY, Foi utilizado o método HASH na culuna idd. O retorno da função é do tipo p_parallel_table que foi criado anteriormente. Vocês poderão testar as outras opções de partição depois.
A implementação dessa função esta no corpo do package nas linhas 19 à 41. Na linha 23 foi criada uma variável do tipo p_parallel_record, esse tipo foi criado nesse mesmo package. Essa variável irá receber cada um dos registros que estiver no nosso cursor. Para isto, é feito um LOOP nas linhas 23 à 37, onde é feito um fetch em todas as linhas do cursor, inserindo os registros nessa variável. Além dos dados contidos no cursor, é adicionado o sid da seção, isto é feito nas linhas 31 à 34. Observe muito bem a linha 34, pois ela só irá funcionar na versão 12c do banco de dados Oracle. Para versões anteriores poderão utilizar: where rownum = 1;. Na linha 36, o registro é adicionado ao PIPE, retornando assim a linha assim que possível e não esperando todo a consulta terminar para retornar tudo de uma vez, ganhando assim também em performance e melhorando o uso de memória, para isto funcionar, foi utilizado a cláusula PIPELINED na linha 21.
Vamos testar agora o uso dessa função, e ver quantas sid são utilizadas para processar as 900 mil linhas e quanto tempo irá levar. Veja a Listagem 4.
Listagem 4. Utilizando a Função.
01 SELECT sidd, count(*)
02 FROM TABLE(parallel_package.parallel_function(CURSOR(SELECT *
03 FROM table_parallel)))
04 GROUP BY sidd;
Nesse exemplo, foi feito um SELECT para exibir o sidd e a contagem total dos registros processados por cada sessão. Nas linhas 2 e 3 foi utilizada a função do package a função CURSOR para isto. Agrupamos tudo pelo sid para melhorar a exibição do nosso resultado e entendimento. Mas, para a nossa surpresa, veja o resultado abaixo.
Listagem 5. Resultado da Listagem 4.
SIDD COUNT(*)
01 42 900000
Foi comprovado então que nesse nosso teste foi utilizado somente uma única seção. Isso acontece por que o Oracle esculheu fazer isto. A cláusula PARALLEL ENABLE apenas habilita a função para processamento em paralelo, não quer dizer que ela sempre será executada assim. Para modificar esse comportamento, podemos utilizar um HINT, que nada mais é que uma sugestão ao Oracle de processar essa função em paralelo. Isto é feito na listagem a seguir.
Listagem 6. Utilizando a Função com um HINT.
01 SELECT sidd, count(*)
02 FROM TABLE(parallel_package.parallel_function(CURSOR(SELECT
03 /*+ parallel(table_parallel) */
04 * FROM table_parallel)))
05 GROUP BY sidd;
O HINT foi inserido entre a cláusula SELECT e a lista de culunas, que no caso foi apenas um *. Esse HINT pode ser visto na linha 3. Onde foi sugerido fazer o processamento em paralelo com mais de uma sessão na tabela especificada. O resto da query é o mesmo. Veja o resultado como mudou agora.
Listagem 7. Resultado da Listagem 6
SIDD COUNT(*)
01 13 300000
02 254 300000
03 30 300000
Perceba agora que o Oracle atendeu a sugestão e utilizou 3 sessões distintas para executar a mesma tarefa, cada sessão processou 1/3 das quantidades de linhas, justamente a quantidade de valores diferentes na culuna idd que foi utilizada para particionar o nosso cursor.
No quesito de performance, a Listagem 6 que fez o processamento em paralelo, utilizando 3 sessões diferentes, levou cerca de 32 segundos para finalizar a operação. Já a Listagem 4 que não utilizou o processamento em paralelo e usou assim apenas uma sessão, levando cerca de 73 segundos! Mais do dobro do tempo! Mostrando assim que o processamento em paralelo tem um grande ganho em performance. Mas vale lembrar que esse tipo de processamento esta disponível apenas na versão Enterprise do Oracle.
Tércio Costa Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvulvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN e articulista no portal http://www.profissionaloracle.com.br/gpo
Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.