Paralelismo automático no Oracle Database 11G - Parte 1

Olá pessoal,

     No artigo de hoje vou comentar sobre um recurso que evolui bastante e que na minha opinião ficou muito bom no Oracle Database 11G: o uso de paralelismo automático no nível de instância ou sessão.

     Com paralelismo automático habilitado, podemos otimizar a performance de muitas aplicações, utilizando recursos (processamento e I/O) subutilizados da máquina servidora de um Banco de Dados. Este recurso é muito útil para aquelas operações que demoram muito tempo para serem executadas, como por exemplo, uma consulta que demora mais que 30 segundos ou um index rebuild que pode demorar muitos minutos ou até mesmo horas. Este recurso pode ser habilitado tanto em ambientes OLAP, quanto  em processamento batch, em ambientes OLTP. Em BDs OLAP pode-se configurar este recurso no nível da instância, mas em BDs OLTP, recomenda-se configurá-lo somente no nível de sessão.

     No 11G o que mudou em relação ao paralelismo automático é que a Oracle introduziu o recurso "Automatic Degree of Parallelism", que permite ao SGBD calcular automaticamente a quantidade de processos paralelos (conhecida como DOP) ideal para cada operação em que eles serão aplicados. Antes do 11G essa tarefa ficava para os DBAs ou Desenvolvedores e era muito difícil fazer um bom uso e balanceamento adequado dos processos paralelos ao tamanho dos objetos e recursos de hardware disponíveis. Dentre as principais características do Automatic Degree of Parallelism (Automatic DOP), podemos citar:

       - O cálculo do DOP baseia-se no tamanho dos segmentos a ser acessado, em conjunto com os parâmetros de configuração do DOP e carga geral do BD;

       - Efetua enfileiramento de instruções paralelas, que assegura a não saturação do sistema pelo uso de paralelismo;

       - Utiliza um novo mecanismo chamado "in memory parallel execution" que permite reutilizar blocos da Buffer Cache (inclusive entre nós de um ambiente RAC) para executar as operações paralelas. Normalmente, as operações paralelas fazem acesso direto em disco (direct path I/O), no espaço de trabalho privado de cada processo de servidor paralelo.

     A configuração de Automatic DOP pode envolver vários parâmetros, mas podemos habilitá-lo configurando apenas 1 deles, e para melhorar o uso do recurso, configuraremos neste artigo mais outro.

     O primeiro e principal parâmetro é o PARALLEL_DEGREE_POLICY, que aceita 3 valores:

          - MANUAL:
            Este é o valor padrão do parâmetro na instância e indica que Automatic DOP está desabilitado.
  
          - LIMITED:

            Este valor permite usar paralelismo somente em operações que envolvem objetos com DOP habilitado diretamente na definição do objeto, com o valor DEGREE DEFAULT. Com este valor Automatic DOP não faz uso dos recursos de enfileiramento de instruções e in memory parallel execution.

    
         - AUTO:

           Permite aplicar paralelismo em qualquer operação em que processos paralelos possam ser utilizados. Este valor é o único que utiliza todos os novos recursos do Automatic DOP no 11G.

   
     O segundo parâmetro que iremos configurar é o:

          - PARALLEL_MIN_TIME_THRESHOLD:
          Permite configurar a quantidade mínima de tempo, em segundos, que uma instrução deverá ser considerada para ser executada com paralelismo. O valor padrão é 30 segundos, portanto, se uma instrução em modo serial (1 processador) pode ser executada, por exemplo, em 10 segundos, Automatic DOP não será utilizado. Automatic DOP só será utilizado naquelas instruções que em modo serial demorariam 30 segundos ou mais;
     
     Antes de configurar Automatic DOP devemos ter ciência de que jamais devemos habilitar este recurso nas seguintes ocasiões:

         
          - No nível da instância (normalmente ambiente OLTP), se a maior parte das queries ou transações são curtas (duram poucos segundos);

          - Em ambientes onde os recursos de CPU, memória ou I/O já estão saturados ou próximos do limite de uso.

Obs.: Habilitar Automatic DOP nas ocasiões acima pode piorar o tempo de execução das operações que utilizarão paralelismo e comprometer a performance geral do BD.

     Segue abaixo um roteiro passo-a-passo de como configurar e testar Automatic DOP no modo LIMITED:

Configurando Automatic DOP no modo LIMITED

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

Para iniciar o passo-a-passo abaixo, é necessário:

1- Conectar-se previamente no Banco de Dados (BD) desejado, através do SQL Plus ou outra ferramenta compatível, 
com um usuário que tenha privilégios administrativos.
2- Ter instalado no BD o schema SH (Sales History), um schema de exemplos da Oracle normalmente utilizado para 
demonstrar conceitos de aplicações OLAP.

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

  

Passo 1 - Habilitando modo LIMITED:
     Execute o comando abaixo para habilitar Automatic DOP no modo LIMITED:
             alter system set parallel_degree_policy = LIMITED;
   

Passo 2 - Configurando DOP no nível de um objeto:

     Execute o comando abaixo para configurar o valor padrão de processos paralelos nas tabelas SH.SALES e SH.PRODUCTS, e permitir que Automatic DOP seja aplicado nas operações que envolverem estes objetos:               

             ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);
             ALTER TABLE SH.PRODUCTS PARALLEL (DEGREE DEFAULT);
   

Passo 3 - Testando Automatic DOP:
     Analise o plano de execução da instrução SQL abaixo e verifique que ela não utilizará paralelismo, pois o otimizador do Oracle detectou que em modo serial ela seria executada em um tempo menor que o valor mínimo para execução paralela:
    EXPLAIN PLAN FOR
    SELECT      S.PROD_ID, S.CUST_ID, TO_CHAR(S.TIME_ID, 'MM/YYYY') AS TIME_ID,
                P.PROD_NAME,
                SUM(S.QUANTITY_SOLD) AS QUANTITY_SOLD,
                SUM(S.AMOUNT_SOLD) AS AMOUNT_SOLD
    FROM        SH.SALES S
    INNER JOIN  SH.PRODUCTS P
          ON    S.PROD_ID = P.PROD_ID
    where       S.PROD_ID NOT IN (20, 21)
    GROUP BY    S.PROD_ID, S.CUST_ID, TO_CHAR(S.TIME_ID, 'MM/YYYY'), P.PROD_NAME
    --HAVING      SUM(S.QUANTITY_SOLD) > 3    
    UNION
    SELECT      S.PROD_ID, S.CUST_ID, TO_CHAR(S.TIME_ID, 'MM/YYYY'),
                P.PROD_NAME,
                SUM(S.QUANTITY_SOLD),
                SUM(S.AMOUNT_SOLD)
    FROM        SH.SALES S
    INNER JOIN  SH.PRODUCTS P
          ON    S.PROD_ID = P.PROD_ID
    where       S.PROD_ID IN (129)
    GROUP BY    S.PROD_ID, S.CUST_ID, TO_CHAR(S.TIME_ID, 'MM/YYYY'), P.PROD_NAME
    HAVING      SUM(S.QUANTITY_SOLD) > 3
    ORDER BY    TIME_ID, PROD_ID;    
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  

Resultado: 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Obs.: A mensagem acima foi extraída do final do relatório do plano de execução e indica que a instrução SQL não foi executada com paralelismo porque o tempo de execução dela em modo serial é menor que o valor do parâmetro PARALLEL_MIN_TIME_THRESHOLD.

Passo 4 - Alterando o parâmetro PARALLEL_MIN_TIME_THRESHOLD:

     Execute o comando abaixo para alterar o valor do parâmetro PARALLEL_MIN_TIME_THRESHOLD, configurando-o para permitir que seja realizado Automatic DOP em qualquer operação que demore 1 segundo ou mais para executar:  
             alter system set PARALLEL_MIN_TIME_THRESHOLD = 1;
  
Passo 5 - Testando novamente Automatic DOP:
     Execute novamente a instrução SQL do passo 3 e analise novamente o plano de execução dela. Verifique pela mensagem abaixo, extraída do final do relatório do plano de execução, que agora Automatic DOP foi aplicado, porém como o BD era apenas de teste, não tinha sobrecarga e o tamanho do segmento da tabela era pequeno, somente 1 processo paralelo foi criado, ou seja, Automatic DOP foi aplicado como se fosse um processo serial. Se os segmentos das tabelas fossem maior, com certeza, seriam utilizados N processos paralelos.

Resultado: 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1

Bom pessoal, por hoje é só!

A 2ª parte deste artigo pode ser consultada no link a seguir: Paralelismo automático no Oracle Database 11G - Parte 2


[]s


Referências
   - Automatic Degree of Parallelism in Oracle 11gR2
      http://www.centroid.com/knowledgebase/blog/automatic-degree-of-parallelism-in-oracle-11gr2
  - How Parallel Execution Works 

     http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm