Concurrent degree __oracle of Oracle parallelism

Oracle Parallelism (degree of parallelism)

The number of slave processes that are used in parallel in the operation is the degree of parallelism dop,index&table both have DOP as default operation parallelism 1 means no parallel processing is used

sql> CREATE TABLE T1 (a int) parallel 6;

Table created.

Sql> Select degree from user_tables where Table_name= ' t1′;

DEGREE
———-
6

sql> ALTER TABLE T1 parallel 3;

Table altered.

Sql> Select degree from user_tables where Table_name= ' t1′;

DEGREE
———-
3
* Disable ALTER TABLE (index) Parallel 1 (noprallel)
Using parallel at #create time will not only make table&index, subsequent operations DDL,DML also be used (if you only want to use the table, you can modify it after you build it)

Sql> CREATE TABLE t2 (a int) parallel (no degree of parallelism specified)

Table created.

Sql> Select degree from user_tables where Table_name= ' t2′;

DEGREE
———-
Default ~ ~ ~ So use default degree of parallelism = (CPU_COUNT*PARALLEL_THREADS_PER_CPU)

Sql> Show Parameter Cpu_count

NAME TYPE VALUE
———————————— ———– ——————————
Cpu_count Integer 2
Sql> Show Parameter Parallel_thread

NAME TYPE VALUE
———————————— ———– ——————————
PARALLEL_THREADS_PER_CPU Integer 2
Sql>

sql> INSERT INTO T2 values (1);

1 row created.

Sql> commit;

Commit complete.

sql> Set Autotrace trace exp
Sql> select * from T2;

Execution Plan
———————————————————-
Plan Hash value:1216610266

——————————————————————————–
——————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time
| TQ | In-out| PQ Distrib |

——————————————————————————–
——————————

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0) | 00:00:01
| | | |

| 1 | PX Coordinator | | | |
| | | |

| 2 | PX SEND QC (RANDOM) | : TQ10000 | 1 | 13 | 2 (0) | 00:00:01
| q1,00 | P->s | QC (RAND) |

| 3 | PX Block Iterator | | 1 | 13 | 2 (0) | 00:00:01
| q1,00 | PCWC | |

| 4 | TABLE ACCESS full| T2 | 1 | 13 | 2 (0) | 00:00:01
| q1,00 | PCWP | |

——————————————————————————–
——————————

Note
—–
–dynamic sampling used for this statement

Sql>
Sql> set Autotrace off
Sql> select process from V$pq_tqstat;

No rows selected

You can see Using set Autotrace to check V$pq_tqstat norows, which is caused by 2 reasons.
The principle of 1.set autotrace
Open Autotrace time a process corresponds to 2 sessions
Typically, a session corresponds to a server processs, but the server porcesss can correspond to multiple sessions

Sql> Conn xh/a831115
is connected.
Sql> select distinct SID from V$mystat;

Sid
———-
144

Sql> Select username, sid, serial#, Server, paddr, status from V$session where S
id=144;

USERNAME SID serial# SERVER paddr STATUS
—————————— ———- ———- ——— ——– ——–

XH 144 dedicated 20E4CC3C INACTIVE

Sql> Select program, addr from v$process where addr= (select Paddr from V$session
where sid=144);

Program ADDR
—————————————————————- ——–
ORACLE. EXE (Shad) 20e4cc3c

Sql> select Sid from V$session where paddr= ' 20e4cc3c ';

Sid
———-
144

Sql> set Autotrace on
Sql> select Sid from V$session where paddr= ' 20e4cc3c ';

Sid
———-
144
154

2.v$pq_tqstat only provides information on the last parallel execution of SQL statements for the current session
Synthesis 2 points can see that the last session of Set Autotrace is executed, so v$pq_tqstat is no rows

So direct execution
Sql> select * from T2;

A
———-
1

Sql> SELECT Dfo_number, tq_id, Server_type, process, Num_rows, bytes
2 from V$pq_tqstat
3 ORDER by Dfo_number, tq_id, Server_type DESC, process;

Dfo_number tq_id Server_typ PROCESS num_rows BYTES
———- ———- ———- ———- ———- ———-
1 0 Producer P000 1 24
1 0 Producer P001 0 20
1 0 Producer P002 0 20
1 0 Producer P003 0 20
1 0 Consumer QC 1 84

You can see that 4 slave process has been started, these 4 process=cpu_count*parallel_threads_per_cpu (2*2)

Related hint:
Parallel (10g,11g), No_parallel (10g,11g), Parallel_index (10g,11g,9i), No_parallel_index (10g,11g)
Noparallel (9i), Noparallel_index (9i)
# #需要知道并行hint只是告诉优化器可以使用并行, but not forced to use parallel everything or from cost (emphasis)
Declare
Begin
For I in 1..1000 loop
INSERT into T2 values (i);
End Loop;
Commit
End

Sql> explain plan set statement_id= ' t2_pp ' to select/*+parallel (T2 2) */* from T2 where a>900;

explained.

Sql> CREATE index t2_id on T2 (a);

Index created.

Sql> explain plan set statement_id= ' t2_id ' to select/*+parallel (T2 2) */* from T2 where a>900;

explained.

Sql> Set Linesize 1000
Sql> select * FROM table (Dbms_xplan.display (null, ' t2_pp '));

Plan_table_output
————————————————————————————————————————————————————————————————————————————————————————————————————
Plan Hash value:1216610266

————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | TQ | In-out| PQ Distrib |
————————————————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 1300 | 2 (0) | 00:00:01 | | | |
| 1 | PX Coordinator | | | |
| 2 | PX SEND QC (RANDOM) | : TQ10000 | 100 | 1300 | 2 (0) | 00:00:01 | q1,00 | P->s | QC (RAND) |
| 3 | PX Block Iterator | | 100 | 1300 | 2 (0) | 00:00:01 | q1,00 | PCWC | |
|* 4 | TABLE ACCESS full| T2 | 100 | 1300 | 2 (0) | 00:00:01 | q1,00 | PCWP | |
————————————————————————————————————–

Plan_table_output
————————————————————————————————————————————————————————————————————————————————————————————————————

predicate information (identified by Operation ID):
—————————————————

4–filter ("A" >900)

Note
—–
–dynamic sampling used for this statement

Rows selected.

Sql> select * FROM table (Dbms_xplan.display (null, ' t2_id '));

Plan_table_output
————————————————————————————————————————————————————————————————————————————————————————————————————
Plan Hash value:523330294

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 1300 | 2 (0) | 00:00:01 |
|* 1 | INDEX RANGE scan| t2_id | 100 | 1300 | 2 (0) | 00:00:01 |
————————————————————————–

predicate information (identified by Operation ID):
—————————————————

Plan_table_output
————————————————————————————————————————————————————————————————————————————————————————————————————

1–access ("A" >900)

Note
—–
–dynamic sampling used for this statement

Rows selected.

You can see that even if you use the cost at the same time as the index (cost surface), use the trace alter session set event ' 10053 Trace name context forever '; See more detailed, parallel CPU cost is higher, so chose to go index, so hint parallel,parallel_index just tell query optimizer
You can consider that parallelism is not mandatory)

Article Author: Xuhui
This article address: http://xuguohao.gotoip55.com/?p=182
Copyright © reproduced must be linked to the form of the author and the original source.