Páginas

8 de mar. de 2013

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

Olá pessoal,

    Dando continuidade ao artigo Paralelismo automático no Oracle Database 11G - Parte 1, irei demonstrar abaixo como configurar Automatic DOP (ver Imagem 01) no modo AUTO e também aproveitarei para comentar sobre outros parâmetros de configuração deste poderoso recurso.  


     É importante lembrar (ver artigo anterior) que o modo AUTO é a única configuração que utiliza todos os recursos do Automatic DOP no 11G, e por este motivo, essa é a configuração que eu recomendo (quando for possível utilizar) se você quer extrair máxima performance deste recurso, em Bancos de Dados Oracle Database 11G, que possuam as características mencionadas na 1ª parte deste artigo.

Imagem 01 - Como funciona Automatic DOP

     Segue abaixo um roteiro passo-a-passo de como configurar e testar Automatic DOP no modo AUTO. A tabela utilizada nas consultas do roteiro abaixo é uma tabela de um sistema da empresa em que trabalho, que contém  1.144.253.700 linhas, tem o tamanho de 21,40 GB e que está com um nome fictício:


Configurando Automatic DOP no modo AUTO
-------------------------------------------------------------------------
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.
--------------------------------------------------------------------------
  
Passo 1 - Habilitando modo MANUAL:
     Execute o comando abaixo para desabilitar Automatic DOP. Esta é configuração padrão de uma instância de BD Oracle, porém iremos executar o comando só para ter certeza de que a instrução SQL que iremos executar no próximo passo será executada em modo serial:
             alter system set parallel_degree_policy = MANUAL;

Passo 2 - Verificando o tempo de execução de uma query em modo serial:
     Analise o plano de execução da query abaixo e verifique que o tempo estimado de execução dela em modo serial é de 46 segundos.

explain plan for
  select * from schema.tabela;
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 3175653815
------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows   | Bytes | Cost (%CPU) | Time     |  
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |  1144M|    25G|   319K  (2)   | 00:00:46|
|   1 |  TABLE ACCESS FULL  | TABELA         |  1144M|    25G|   319K  (2)     00:00:46 |
------------------------------------------------------------------------------------------------------------

Passo 3 - Habilitando modo AUTO:
     Execute o comando abaixo para habilitar Automatic DOP, e desse modo, permitir que a instrução SQL seja executada com paralelismo:
             alter system set parallel_degree_policy = AUTO;


Passo 4 - Verificando o tempo de execução de uma query em modo paralelo:
     Analise o plano de execução da mesma query do passo 2 e veja que o tempo estimado de execução dela com paralelismo caiu para 6 segundos, com DOP igual a 10:


 PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 987973301
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time      |    TQ   |IN-OUT| PQ Distrib    |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                          |  1144M|    25G| 35494   (2)       | 00:00:06 |             |             |                      |
|   1 |  PX COORDINATOR           |                          |               |          |                          |                |             |             |                        |
|   2 |   PX SEND QC (RANDOM)| :TQ10000         |  1144M|    25G| 35494   (2)       | 00:00:06 |  Q1,00 | P->S    | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |                           |  1144M|    25G| 35494   (2)      | 00:00:06 |  Q1,00 | PCWC |                        |
|   4 |     TABLE ACCESS FULL   | TABELA         |  1144M|    25G| 35494   (2)     | 00:00:06  |  Q1,00 | PCWP |                        |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 10
   

   Observe através dos planos de execução dos passos 2 e 4, que no passo 4, tivemos um ganho de performance de 766% com o uso de paralelismo automático, sem ter que alterar nenhum objeto do BD e nem incluir hints na instrução SQL. Automatic DOP no modo AUTO é muito simples de habilitar e usar, e por isso é necessário tomar cuidados para que ele não sobrecarregue os recursos de CPU e I/O do servidor de BD e se torne um problema. Após habilitar Automatic DOP, monitore a sua utilização através da consulta abaixo. A quantidade de queries que foram executadas (desde o startup do BD ou momento em que o recurso foi habilitado) irá aparecer na primeira linha do resultado e muitas outras informações referentes à execução de paralelismo na instância podem ser analisadas nessa consulta.
                    
          SELECT NAME, VALUE FROM GV$SYSSTAT          
          WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'          
          OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';


Resultado:
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                    301
DML statements parallelized                                        0
DDL statements parallelized                                         0 
DFO trees parallelized                                               301 
Parallel operations not downgraded                          301 
Parallel operations downgraded to serial                      0 
Parallel operations downgraded 75 to 99 pct                0 
Parallel operations downgraded 50 to 75 pct                0 
Parallel operations downgraded 25 to 50 pct                0 
Parallel operations downgraded 1 to 25 pct                  0 
PX local messages sent                                            7679 
PX local messages recv'd                                         7679 
PX remote messages sent                                              0 
PX remote messages recv'd                                           0 
      
     Ao habilitar Automatic DOP em um BD OLAP, por exemplo, considere ajustar cuidadosamente os parâmetros PARALLEL_MIN_TIME_THRESHOLD (comentado no artigo anterior), PARALLEL_SERVERS_TARGET, PARALLEL_DEGREE_LIMIT e outros que podem ser consultados com mais detalhes no artigo Settings that Impact Parallel Execution in Oracle 11gR2. Em breve sairá um artigo meu na SQL Magazine com muito mais detalhes e informações sobre o assunto!
 
  
[]s


Referências
   - Automatic Degree of Parallelism in Oracle 11gR2
  - How Parallel Execution Works 

4 comentários:

  1. Respostas
    1. Helder, eu é quem agradeço o feedback. É pelo feedback (neste caso, os comentários) que vc e outros leitores do meu blog dão que eu consigo saber se estou indo no caminho certo e se os artigos estão fáceis de entender!

      []s

      Excluir
  2. Olá Fábio, Ótimo artigo.
    Tenho apenas uma dúvida referente ao DOP, fiz testes com o parâmetro parallel_degree_policy = 'AUTO' e 'LIMITED', quando utilizo 'LIMITED' e defino parallel (degree default) para a tabela a mesma não executa em paralelismo apenas se setar parallel (degree 8) exemplo. Quando utilizo 'AUTO' também não executa em paralelismo, apenas se definir manualmente o paralelismo para a tabela parallel (degree 8). A versão que utilizo é Enterprise Edition 11g R2 Release 11.2.0.4 e o parametro parallel_min_time_threshold = '7'. Executei o pacote dbms_resource_manager.calibrate_io e nada, principalmente DML que são executadas pela aplicação não executam a nível de paralelismo.
    Já passou por esse tipo de situação?
    As dúvidas serão muitas para o curso de Database Performance Tuning rsrs. Abraços

    ResponderExcluir
    Respostas
    1. Wender, para os DMLs serem executados com paralelismo vc precisa habilitar parallel dml. Execute o comando ALTER SESSION ENABLE PARALLEL DML; e verifique se depois de sua execução tudo começa a funcionar.

      []s

      Excluir