DB - Degree Of Parallelism

English Version

Neste post escrevo sobre gestao manual do do DOP ( Degree of Parallelism). A gestao do paralelismo pode ser feita manualmente de 3 formas : 

1 - Paralelismo ao nivel da Sessao 

ALTER SESSION ENABLE | DISABLE | FORCE Parallel QUERY | DML | DDL 

2 - Paralelismo ao nivel do Objecto

create table my_tab(i number) parallel 5 ;
alter table  my_tab parallel 10 ;

select table_name, degree  from dba_tables where table_name = 'INDX_P_5';

-- tabelas particionadas  - -- aplica- se ano nivel da tabelas  nao das particoes 

--  Table IOT 
create table my_iot_tab( i parallel key ) organization index parallel;

-- index
create index indx_p_5 on Tabela (i) Parallel 5 ;
alter index indx_p_5 Parallel 10 ;

select index_name, degree  from dba_indexes where index_name = 'INDX_P_5';

3 - Paralelismo Hints

* tem precedencia sobre todos as outras opcoes (MANUAL ou AUTO)

3.1 - Nivel da sentenca

/*+ parallel (Manual) | NO_PARALLEL */
/*+ parallel (N) */ 

eg: select /*+ parallel (3) */ * from my_tab;

3.2 - Nivel do objecto 

/*+ parallel (my_tab,5) */ - DOP 5 
/*+ parallel (my_tab) */ - DOP - decoration 
/*+ parallel_index (my_tab, indx_p_5, 2 ) */
/*+ parallel_index (my_tab, indx_p_5) */

-- objecto na sentenca 
parallel (a,3)  - DOP 3 
parallel (b) - DOP - decoration 

select /*+ parallel (a,3) parallel (b) */ * 
from my_tab_1 a , my_tab_2 b
where a.id=b.id ;

Obrigado pela leitura.

Espero que este post o tenha ajudado.

Versao Portuguesa

This post is about manual management of DOP ( Degree of Parallelism), that could be done within 3 ways : 

1 - Session Level 

ALTER SESSION ENABLE | DISABLE | FORCE Parallel QUERY | DML | DDL 

2 - Object Level

create table my_tab(i number) parallel 5 ;
alter table  my_tab parallel 10 ;

select table_name, degree  from dba_tables where table_name = 'INDX_P_5';

-- tabelas particionadas  - -- aplica- se ano nivel da tabelas  nao das particoes 

--  Table IOT 
create table my_iot_tab( i parallel key ) organization index parallel;

-- index
create index indx_p_5 on Tabela (i) Parallel 5 ;
alter index indx_p_5 Parallel 10 ;

select index_name, degree  from dba_indexes where index_name = 'INDX_P_5';

3 - Hints

Note : Hints overwrite all other methods  including MANUAL or  AUTO

3.1 - Statement 

/*+ parallel (Manual) | NO_PARALLEL */
/*+ parallel (N) */ 

eg: select /*+ parallel (3) */ * from my_tab;

3.2 - Object in the Statement 

/*+ parallel (my_tab,5) */ - DOP 5 
/*+ parallel (my_tab) */ - DOP - decoration 
/*+ parallel_index (my_tab, indx_p_5, 2 ) */
/*+ parallel_index (my_tab, indx_p_5) */

SQL> select /*+ parallel (a,3) parallel (b) */ * 
from my_tab_1 a , my_tab_2 b
where a.id=b.id ;

Thank you for reading.

hope this post was helpful.