1.Oracle服务器维护一个可用于并行操作的并行子进程池。数据库配置参数parallel_max_servers integer 和 parallel_min_servers integer 决定这个池的初始数和最大数，如果当前没有足够的子进程是活跃的，但池还没有达到最大值，则oracle将创建更多的子进程，经过一个设定的不活动周期，子进程将被关闭SQL> show parameter parallel;
NAME TYPE VALUE
------------------------------------ ----------- fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 120
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
2.查询目前用了多少个并行进程，还有多少个并行进程可用：SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT;
STATISTIC VALUE
---------------------------------------------------------------------Servers In Use 0
Servers Available 8
Servers Started 8
Servers Shutdown 0
Servers Highwater 3
Servers Cleaned Up 0
Server Sessions 3
Memory Chunks Allocated 4
Memory Chunks Freed 0
Memory Chunks Current 4
Memory Chunks HWM 4
Buffers Allocated 23
Buffers Freed 15
Buffers Current 8
Buffers HWM 20
15 rows selected.
3.确定并行度，最优的并行度对于活得好的并行度性能至关重要，oracle根据下面的规则来确定并行度，如果指定或请求并行执行，但没有指定并行度，默认的并行度被设置为系统CPU核数的两倍，对RAC系统，并行度设置为整个集群的cpu核数的两倍SQL> show parameter cpu;
NAME TYPE VALUE
------------------------------------ ----------- --------------------cpu_count integer 4
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 4
4.V$PQ_TQSTAT视图：
即使有explan_PLAN和sql_traces输出，还是很难准确地解释并行查询的如何执行的，例如，实际的并行度是什么，每个并行服务进程都做了哪些工作？v$pq_tqstat视图包含了有关并行查询服务器的每个集合间传输的信息，包括发送和接收的行数。不过这个视图仅对发送并行查询的会话和最近执行过的查询是可见的。这些限制了它在产品中的使用，但在调优并行查询事，它有作用的。SELECT /*+ parallel */
prod_id, SUM(amount_sold)
FROM sales
GROUP BY prod_id
5 ORDER BY 2 DESC;
72 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3876052276
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72 | 648 | 317 (10)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,01 | P->P | RANGE |
| 6 | HASH GROUP BY | | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 72 | 648 | 317 (10)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 918K| 8075K| 292 (2)| 00:00:01 | 1 | 28 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| SALES | 918K| 8075K| 292 (2)| 00:00:01 | 1 | 28 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
2056 recursive calls
0 db block gets
4940 consistent gets
1724 physical reads
0 redo size
2224 bytes sent via SQL*Net to client
567 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
131 sorts (memory)
0 sorts (disk)
72 rows processedSELECT /*+ parallel(8) */
prod_id, SUM(amount_sold)
FROM sales
GROUP BY prod_id
5 ORDER BY 2 DESC;
72 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3876052276
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72 | 648 | 80 (10)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,01 | P->P | RANGE |
| 6 | HASH GROUP BY | | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 72 | 648 | 80 (10)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 918K| 8075K| 73 (2)| 00:00:01 | 1 | 28 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| SALES | 918K| 8075K| 73 (2)| 00:00:01 | 1 | 28 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 8 because of hint
Statistics
----------------------------------------------------------
73 recursive calls
0 db block gets
1667 consistent gets
1619 physical reads
0 redo size
2224 bytes sent via SQL*Net to client
567 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
72 rows processedSELECT a.dfo_number,
a.tq_id,
a.server_type,
MIN(a.num_rows),
MAX(a.num_rows),
COUNT(*)
FROM v$pq_tqstat a
GROUP BY a.dfo_number, a.tq_id, a.server_type
9 ORDER BY a.dfo_number, a.tq_id, a.server_type
10 ;
DFO_NUMBER TQ_ID SERVER_TYPE MIN(A.NUM_ROWS) MAX(A.NUM_ROWS) COUNT(*)
---------- ---------- ---------------------------------------- --------------- --------------- ----------
1 0 Consumer 40 96 8
1 0 Producer 69 72 8
1 1 Consumer 8 10 8
1 1 Producer 5 12 8
1 1 Ranger 72 72 1
1 2 Consumer 72 72 1
1 2 Producer 8 10 8
7 rows selected.
5.通过检查v$px_session视图，我们可以实时得到系统上正在发生的并执行的视图，这个视图显示当前哪些并行子进程正在执行SQL，将v$px_session，v$session和v$sql3个视图关联，使我们能识别出当前正在使用并行处理的会话和SQL，以及看到想要的和真实的并行度。WITH px_session AS
(SELECT qcsid,
qcserial#,
MAX(degree) degree,
MAX(req_degree) req_degree,
COUNT(*) no_of_processes
FROM v$px_session p
GROUP BY qcsid, qcserial#)
SELECT s.sid, s.username, degree, req_degree, no_of_processes, sql_text
FROM v$session s
JOIN px_session p
ON (s.sid = p.qcsid AND s.serial# = p.qcserial#)
JOIN v$sql SQL
14 ON (sql.sql_id = s.sql_id);
SID USERNAME DEGREE REQ_DEGREE NO_OF_PROCESSES
---------- ------------------------------ ---------- ---------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15 SH 0 36 36
SELECT /*+ parallel(36) */ prod_id, SUM(amount_sold) FROM sh.sales GROUP BY prod_id ORDER BY 2 DESC
6.V$SYSSTAT 视图包含一些与并行查询降级有关的统计数据，它可以帮助我们确定与请求并行度相比，并性查询被降级的频度
7.确保执行计划的所有部分都做了并行化，在一个复杂的并行sql中，确保查询执行中的所有重要步骤都实现了并行是很重要的，如果复杂查询中一个步骤以串行执行，其他的并行步骤可能必须等待串行步骤完成，从而将并行化的有点丢失，dbms_xplan中的S>P,
例如，在下面的实例中，CUSTOMERS表实现了并行，但SALE表没有，这个两个表联结和group by包括许多的并行化操作，但是sales表的全表扫描没有没有实现并行化，警告S->P标签显示SALES表的行以串行的方式传输到接下来的并行操作中。SQL> alter table customers parallel(degree 4);
Table altered.
SQL> alter table sales noparallel;
Table altered.
explain plan for SELECT /* ordered use_hash(c) */
cust_last_name, SUM(amount_sold)
FROM sales s
JOIN customers c
USING (cust_id)
6 GROUP BY cust_last_name;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3204180427
--------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH |
| 6 | HASH GROUP BY | | Q1,02 | PCWP | |
| 7 | HASH JOIN | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | Q1,01 | PCWP | |
| 12 | BUFFER SORT | | Q1,02 | PCWC | |
| 13 | PX RECEIVE | | Q1,02 | PCWP | |
| 14 | PX SEND HASH | :TQ10000 | | S->P | HASH |
| 15 | PARTITION RANGE ALL| | | | |
| 16 | TABLE ACCESS FULL | SALES | | | |
--------------------------------------------------------------------------------
23 rows selected.
一个部分并行的执行计划，例如前面提及的那个，可能提供了两种最坏的情况，因为串行的操作形成了对整体执行的瓶颈，耗时并没有得到提高，然而，sql占用了并行服务器进程，可能影响其他并行执行的sql的性能。如果我们设置了sales表的并行度，串行瓶颈将消失，sales表的全表扫描现在以并行方式执行，而且S->P的瓶颈替换成了完全并行化的P->P操作SQL> alter table sales parallel(degree 4);
Table altered.
explain plan for SELECT /* ordered use_hash(c) */
cust_last_name, SUM(amount_sold)
FROM sales s
JOIN customers c
USING (cust_id)
6 GROUP BY cust_last_name;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2890400653
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 6 | HASH GROUP BY | | Q1,01 | PCWP | |
| 7 | HASH JOIN | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| CUSTOMERS | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL | SALES | Q1,01 | PCWP | |
------------------------------------------------------------------------------
20 rows selected.
8.RAC的并行执行。在真正的应用集群(RAC)的数据库中，SQL可以在组成集群的多个实例上执行并行操作，除非采取了特定的步骤来阻止它，否则ORACLE在整个集群上无缝地执行并行操作。
SELECT a.dfo_number,
a.tq_id,
a.server_type,
MIN(a.num_rows) min_rows,
MAX(a.num_rows) max_rows,
COUNT(*) dop,
COUNT(DISTINCT instance) no_of_instances
FROM v$pq_tqstat a
GROUP BY a.dfo_number, a.tq_id, a.server_type
10 ORDER BY a.dfo_number, a.tq_id, a.server_type DESC;
DFO_NUMBER TQ_ID SERVER_TYPE MIN_ROWS MAX_ROWS DOP NO_OF_INSTANCES
---------- ---------- ---------------------------------------- ---------- ---------- ---------- ---------------
1 0 Producer 26 70 30 1
1 0 Consumer 0 125 30 1
1 1 Ranger 72 72 1 1
1 1 Producer 0 5 30 1
1 1 Consumer 2 13 30 1
1 2 Producer 2 13 30 1
1 2 Consumer 72 72 1 1
7 rows selected.
Elapsed: 00:00:00.66
8.基于索引的并行查找，基于索引的查询通常是不能并行的，但是，如果涉及的索引是分区表上的本地分区索引，使用该索引的查找可以被并行处理。每个分区可由一个单独的进程运行，且可以实现与分区数量一样的高的并行度。
9.并行DML，任意一个运行扫描操作的DML语句都可以被并行，至少运行表读取那部分语句是可以的。explain plan for update /*+ parallel(s) */
2 sales set unit_price=amount_sold/quantity_sold;
Explained.
Elapsed: 00:00:01.23
SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 83819421
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | SALES | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| SALES | Q1,00 | PCWP | |
-----------------------------------------------------------------------
12 rows selected.
Elapsed: 00:00:00.15
SALES的全表扫描可以被并行处理，但请注意，尽管被更新的行可以被并行进程识别，真实的更新是由查询协调进程串行方式进行的，为了执行真正的并行DML，首先应该用下面的语句开启并行DMLSQL> alter session enable parallel DML;
为了是DML语句完全并行执行，发送一个alter session enable parallel DML的语句，否则，这个语句将只会部分被并行(最好的情况下)
10.并行插入：INSERT /*+ parallel(s) */
INTO sales s
SELECT /*+ parallel(u) */
*
FROM sales_update u;SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3385836410
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | |
| 1 | LOAD TABLE CONVENTIONAL | SALES | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | SALES_UPDATE | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
12 rows selected.
所以并行插入的正确写法：1. alter session enable parallel dml;
2. insert /*+ append parallel(a 4) */ select /*+ parallel(b 4) */ * from b;
oracle 10g 并行HINT如下写法，11不用添加表的名字select /*+parallel(a,4),parallel(b,4),parallel(c,4),parallel(d,4),parallel(e,4)
*/ * from a,b,c,d,e
11.DDL并行，
create index 和CREATE TABLE AS SELECT 这样的DDL语句都能做并行处理，create table as select 使用与并行插入极相似的方式处理并行。explain plan for create index sales_idx
2 on sales(prod_id,time_id) parallel(degree 8);
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 967460003
---------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE| SALES_IDX | Q1,01 | PCWP | |
| 4 | SORT CREATE INDEX | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| SALES | Q1,00 | PCWP | |
---------------------------------------------------------------------------
15 rows selected.explain plan for
create table sales_copy parallel(degree 8)
3 as select * from sales;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1556691280
--------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | SALES_COPY | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | SALES | Q1,00 | PCWP | |
--------------------------------------------------------------------------
12 rows selected.
扩展：怎样避免数据仓库获取不到并行进程，而导致存储过程跑得慢：BEGIN
FOR i IN 1 .. 5 LOOP
SELECT a.value
INTO v_num
FROM v$px_process_sysstat a
WHERE a.statistic LIKE 'Servers Available%';
END LOOP;
IF v_num >= 8
THEN
dbms_output.put_line('足够进程');
--执行JOB
ELSE
dbms_lock.sleep(60);
END IF;
END;
66
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
