数据库的默认并行度
本例的测试环境:
os:Windows 10 专业版
plsql:Version 14.0.1.1965
Oracle:Version 11.2.0.4.0
单实例数据库的并行度 = parallel_threads_per_cpu x cpu_count
RAC的并行度 = parallel_threads_per_cpu x cpu_count x instance_count
查看数据库的默认并行度(plsql命令窗口):
#查看并行度参数
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 160
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
#每个逻辑核心的并行度为2
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
#查看CPU参数
SQL> show parameter cpu;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
#数据量服务器的逻辑核心数为4
cpu_count integer 4
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 4
#查看实例参数
SQL> show parameter instance;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string master
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
测试数据库的默认并行度=8。
#查看表的单实例并行度参数
SQL> select table_name,degree from user_tables;
TABLE_NAME DEGREE
------------------------------ --------------------
DIM_COMM_DATE 1
DIM_COMM_REGION 1
DIM_COMM_TIME 1
INF_RUNLOG 1
MAP_REGION_FJ2COMM 1
TMP_SHOP 1
parallel DQL(query)
简要说明
实现方式
可以通过三种方式实现数据的并行查询,分别是hist方式,session方式和object方式。
hist方式允许用户在每次查询时决定是否使用并行查询和如何使用并行度。
session方式允许用户在单个session决定是否使用并行查询和设置默认并行度。
object方式允许用户决定在某个表是否使用并行查询和设置默认并行度。
优先级
hist方式 > session方式 > object方式
注意事项
1,并行查询是以消耗服务器资源为代价获取较快的查询效率,所以请在系统的CPU较多 , IO负载不高,内存够大的时候考虑使用。
2,并行查询主要争对对大批量数据的查询,在olap模式下使用比较多,在oltp模式下请谨慎使用。
3,使用前请查看数据库的默认并行参数,确保设置合适的并行度(经测试,当设置的并行度超过数据库的默认并行度是,无法起到提升查询效率的作用)。
Hist方式
语法:
Select /+parallel(tablename/nickname, n)/ from tablename;
注意:如果使用别名且查询条件中使用别名时,请务必在hist语句中使用表别名。
示例:
#对表ods_fjyw_etl_salelist 启动 并行度4,对表dim_fjyw_etl_shop 启动并行度4进行关联查询
#并行查询
select /*+parallel(m,4)(s,4)*/
sum(qty), sum(salevalue), sum(discvalue)
from ods_fjyw_etl_salelist m, dim_fjyw_etl_shop s
where m.shopid = s.id;
#并行语句代价
Plan Hash Value : 3423504441
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13588 | 00:02:44 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 25 | | |
| 4 | SORT AGGREGATE | | 1 | 25 | | |
| * 5 | HASH JOIN | | 24487727 | 612193175 | 13588 | 00:02:44 |
| 6 | BUFFER SORT | | | | | |
| 7 | PX RECEIVE | | 6289 | 44023 | 6 | 00:00:01 |
| 8 | PX SEND BROADCAST | :TQ10000 | 6289 | 44023 | 6 | 00:00:01 |
| 9 | INDEX FAST FULL SCAN | PK_FJYW_SHOP_ID | 6289 | 44023 | 6 | 00:00:01 |
| 10 | PX BLOCK ITERATOR | | 24487727 | 440779086 | 13565 | 00:02:43 |
| 11 | TABLE ACCESS FULL | ODS_FJYW_ETL_SALELIST | 24487727 | 440779086 | 13565 | 00:02:43 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("M"."SHOPID"="S"."ID")
#并行查询耗时
#耗时有波动
# 7000万+数据,耗时 1.784s
#不开启并行查询
select sum(qty), sum(salevalue), sum(discvalue)
from ods_fjyw_etl_salelist m, dim_fjyw_etl_shop s
where m.shopid = s.id;
# 语句代价
Plan Hash Value : 3511703330
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 48941 | 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
| * 2 | HASH JOIN | | 24487727 | 612193175 | 48941 | 00:09:48 |
| 3 | INDEX FAST FULL SCAN | PK_FJYW_SHOP_ID | 6289 | 44023 | 6 | 00:00:01 |
| 4 | TABLE ACCESS FULL | ODS_FJYW_ETL_SALELIST | 24487727 | 440779086 | 48869 | 00:09:47 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("M"."SHOPID"="S"."ID")
#查询耗时
#耗时有波动
# 7000万+数据,耗时 5.216s
Session方式
语法:
alter session force parallel query parallel n;
注意:这里的n是线程当前查询的并行度的上限。
示例:
#当前线程启动并行度8,对表ods_fjyw_etl_salelis和dim_fjyw_etl_shop进行关联查询
alter session force parallel query parallel 8;
select sum(qty), sum(salevalue), sum(discvalue)
from ods_fjyw_etl_salelist m, dim_fjyw_etl_shop s
where m.shopid = s.id;
#查询耗时
#耗时有波动
# 7000万+数据,耗时 2.198s
Object方式
语法:
alter table tablename parallel n;
注意:这里的n是表的默认并行度。
示例:
#对表ods_fjyw_etl_salelis和dim_fjyw_etl_shop进行关联查询
#查询表的默认并行度并修改并行度为4
select table_name,degree from user_tables;
alter table ods_fjyw_etl_salelist parallel 4;
alter table dim_fjyw_etl_shop parallel 4;
select table_name,degree from user_tables;
alter session force parallel query parallel 8;
#查询数据
select sum(qty), sum(salevalue), sum(discvalue)
from ods_fjyw_etl_salelist m, dim_fjyw_etl_shop s
where m.shopid = s.id;
#查询耗时
#耗时有波动
# 7000万+数据,耗时 2.158s
parallel DML(insert,update,delete,merge)
简要说明
实现方式
可以通过三种方式实现数据的并行操作,分别是hist方式,session方式和object方式。
hist方式允许用户在每次操作时决定是否使用并行和如何使用并行度。
session方式允许用户在单个session决定是否使用并行操作和设置默认并行度。
object方式允许用户决定在某个表是否使用并行操作和设置默认并行度。
优先级
hist方式 > session方式 > object方式
Hist方式
语法:
update:
update /*+ PARALLEL(tablename,4) / tablename set column1 = column1 + 1;
insert:
insert /+ PARALLEL(tablename1,4) /
into tablename1
select /+ PARALLEL(tablename2,4) /
*
from tablename2;
delete:
delete /+ PARALLEL (tablename, 4) */
from tablename;
merge:
merge into tablename1 t1
using (select * from tablename2) t2
on (t1.column1 = t2.column1)
when matched then
update set t1.column2 = t2.column2
when not matched then
insert (t1.column1, t1.column2) values (t2.column1, t2.column2);
session方式
语法:
alter session force parallel DML parallel n;
object方式
同 DQL object方式相同,如果已经设置无须重复设置。
语法:
alter table tablename parallel n;
parallel DDL
简要说明
实现方式
可以通过两种方式实现对象的并行操作,分别是hist方式,session方式。
hist方式允许用户在每次操作时决定是否使用并行和如何使用并行度。
session方式允许用户在单个session决定是否使用并行操作和设置默认并行度。
根据是否是分区表有不同操作:
非分区表有如下操作:
create index;
create table;
alter index … rebuild;
分区表
create index;
create table;
alter index … rebuild;
alter table… [move|split|coalesce] partition;
alter index … [rebuild |split] partition;
优先级
hist方式 > session方式
Hist方式
语法(以非分区表为例):
create index indexname on tablename(column1) parallel 8;
create table tabalename parallel 8 as select * from tablename1;
alter index indexname rebuild parallel 8;
session方式
语法:
alter session force parallel DDL parallel n;