一、 Parallel query
默认情况下session 是ENABLE状态
1. 实现方式
1 Alter session force parallel query;
2 Alter table tab1 parallel n;
3 Hist方式
2. 并行度设置
Alter table tab1 parallel n;
Select /*+parallel(tab n)*/ from tab;
Alter session force parallel query parallel n;
如果没有设置将执行默认并行度
3. 默认并行度
单实例 = PARALLEL_THREADS_PER_CPU x CPU_COUNT
RAC = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
新创建表默认并行度是1
SQL> create table tab_3 as select * from dba_objects;
Table created.
SQL> select table_name,degree from user_tables;
TABLE_NAME DEGREE
------------------------------ ----------
TAB_3 1
4. 优先级(并行度覆盖)
官方说明
If you are sure you want to execute in parallel and want to avoid setting the DOP for a table or modifying the queries involved, you can force parallelism with the following statement:
ALTER SESSION FORCE PARALLEL QUERY;
All subsequent queries are executed in parallel provided no restrictions are violated. You can also force DML and DDL statements. This clause overrides any parallel clause specified in subsequent statements in the session, but is overridden by a parallel hint.
In typical OLTP environments, for example, the tables are not set parallel, but nightly batch scripts may want to collect data from these tables in parallel. By setting the DOP in the session, the user avoids altering each table in parallel and then altering it back to serial when finished.
Hint > session > object
二、 Parallel DML (INSERT, UPDATE, DELETE, and MERGE)
默认情况下session 是DISBALE状态
只有再使用(Alter session force parallel DML;
或者Alter session enable parallel DML)才可以使用parallel并行
1. 实现方式
Alter session force parallel DML;
Alter table tab1 parallel n;
Hist 方式
2. 并行度设置
Alter table tab1 parallel n;
Alter session force parallel DML parallel n;
UPDATE /*+ PARALLEL(tab1,4) */ tbl_2 SET c1=c1+1;
INSERT /*+ PARALLEL(tbl_ins,2) */ INTO tbl_ins
SELECT /*+ PARALLEL(tbl_sel,4) */ * FROM tbl_sel;
DELETE /*+ PARALLEL (t1, 2) */ FROM t1
如果没有设置将执行默认并行度
3. 优先级(并行度覆盖)
Hint > session > object
三、 Parallel DDL
支持的操作
非分区表
CREATE INDEX
CREATE TABLE ... AS SELECT
ALTER INDEX ... REBUILD
分区表
CREATE INDEX
CREATE TABLE ... AS SELECT
ALTER TABLE ... [MOVE|SPLIT|COALESCE] PARTITION
ALTER INDEX ... [REBUILD|SPLIT] PARTITION
默认情况下session 是ENABLE状态
1. 实现方式
ALTER SESSION FORCE PARALLEL DDL
PARALLEL clause
2. 优先级(并行度覆盖)
Hint > session
3. 并行度设置
ALTER SESSION FORCE PARALLEL DDL parallel 10;
CREATE INDEX ….parallel 10;
ALTER INDEX ... REBUILD parallel 10;
ALTER INDEX ... MOVE PARTITION parallel 10;
ALTER INDEX ...SPLIT PARTITION parallel 10;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15747463/viewspace-1064687/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15747463/viewspace-1064687/