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

      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
  - Settings that Impact Parallel Execution in Oracle 11gR2
     http://www.centroid.com/knowledgebase/blog/settings-that-impact-parallel-execution-in-oracle-11gr2
  - Monitoring Parallel Execution Performance
   http://docs.oracle.com/cd/E18283_01/server.112/e16541/parallel006.htm