Oracle 并行原理深入解析及案例精粹
Oracle 并行原理深入解析及案例精粹
引言:首先说明并行技术属于大数据范畴,适合OLAP系统,在任务分割、数据块分割、资源充裕的场合应用较广,本次分享主要概括并行原理、实际应用、性能对比、并行直接加载、索引属性、特点小结等六个小点去重点阐述。下面的测试是我的笔记,这些笔记也参考了《让Oracle跑得更快2》作者:谭怀远 一书的引导,在此向谭总表示感谢,向帮助过我们的人表示感谢 zhixiang yangqiaojie等好友,下面我们就开始快乐的旅途!
一、简单介绍OLTP和OLAP系统的特点小结
答:OLTP和OLAP是我们大家在日常生产库中最常用到的2种系统,简单的说OLTP是基于多事务短时间片的系统,内存的效率决定了数据库的效率。
OLAP是基于大数据集长时间片的系统,SQL执行效率决定了数据库的效率。因此说“并行parallel”技术属于OLAP系统范畴
二、并行技术实现机制和场合
答:并行是相对于串行而言的,一个大的数据块分割成n个小的数据块,同时启动n个进程分别处理n个数据块,最后由并行协调器coordinater整合结果返回给用户。实际上在一个并行执行的过程中还存在着并行进程之间的通信问题(并行间的交互操作)。上面也说过并行是属于大数据处理的技术适合OLAP,并不适合OLTP,因为OLTP系统中的sql执行效率通常都是非常高的。
三、测试并行技术在实际中的应用和规则
(1)在有索引的表leo_t上使用并行技术,但没有起作用的情况
创建一张表
LS@LEO> create table leo_t as select rownum id ,object_name,object_type from dba_objects;
在表id列上创建索引
LS@LEO> create index leo_t_idx on leo_t(id);
收集表leo_t统计信息
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_T',method_opt=>'for all indexed columns size
2',cascade=>TRUE);
为表启动4个并行度
LS@LEO> alter table leo_t parallel 4;
启动执行计划
LS@LEO> set autotrace trace explain stat
LS@LEO> select * from leo_t where id=100; 使用索引检索的数据,并没有启动并行
Execution Plan 执行计划
----------------------------------------------------------
Plan hash value: 2049660393
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO_T | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LEO_T_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Statistics 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets 4次一致性读,即处理4个数据块
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
说明:我们在这个表上启动了并行但没有起作用是因为CBO优化器使用了B-tree索引来检索的数据直接就定位到rowid(B-tree索引特点适合重复率比较低的字段),所以才发生了4个一致性读,发现使用索引效率非常高,资源代价比较小没有使用并行的必要了。
(2)读懂一个并行执行计划
LS@LEO> select object_type,count(*) from leo_t group by object_type; 对象类型分组统计
35 rows selected.
Execution Plan 并行执行计划
----------------------------------------------------------
Plan hash value: 852105030
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10337 | 111K| 6 (17)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10337 | 111K| 6 (17)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 10337 | 111K| 6 (17)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10337 | 111K| 6 (17)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 10337 | 111K| 6 (17)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 10337 | 111K| 6 (17)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 10337 | 111K| 5 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| LEO_T | 10337 | 111K| 5 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Statistics 统计信息
----------------------------------------------------------
44 recursive calls
0 db block gets
259 consistent gets 259次一致性读,即处理259个数据块
0 physical reads
0 redo size
1298 bytes sent via SQL*Net to client
403 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
35 rows processed
ps -ef | grep oracle 从后台进程上看也能发现起了4个并行进程和1个协调进程
oracle 25075 1 0 22:58 ? 00:00:00 ora_p000_LEO
oracle 25077 1 0 22:58 ? 00:00:00 ora_p001_LEO
oracle 25079 1 0 22:58 ? 00:00:00 ora_p002_LEO
oracle 25081 1 0 22:58 ? 00:00:00 ora_p003_LEO
oracle 25083 1 0 22:58 ? 00:00:00 ora_p004_LEO
说明:在进行分组整理的select中,会处理大量的数据集(发生了259次一致性读),这时使用并行来分割数据块处理可以提高效率,因此oracle使用了并行技术,解释一下并行执行计划步骤,并行执行计划应该从下往上读,当看见PX(parallel execution)关键字说明使用了并行技术
1.首先全表扫描
2.并行进程以迭代iterator的方式访问数据块,并将扫描结果提交给父进程做hash group
3.并行父进��对子进程传递过来的数据做hash group操作
4.并行子进程(PX SEND HASH)将处理完的数据发送出去,子和父是相对而言的,我们定义发送端为子进程,接收端为父进程
5.并行父进程(PX RECEIVE)将处理完的数据接收
6.按照随机顺序发送给并行协调进程QC(query coordinator)整合结果(对象类型分组统计)
7.完毕后QC将整合结果返回给用户
说明并行执行计划中特有的IN-OUT列的含义(指明了操作中数据流的方向)
Parallel to Serial(P->S): 表示一个并行操作向一个串行操作发送数据,通常是将并行结果发送给并行调度进程QC进行汇总
Parallel to Parallel(P->P):表示一个并行操作向另一个并行操作发送数据,一般是并行父进程与并行子进程之间的数据交流。
Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作,同时父操作也是并行的。
Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作,同时子操作也是并行的。
Serial to Parallel(S->P): 表示一个串行操作向一个并行操作发送数据,如果select部分是串行操作,就会出现这个情况
(3)介绍4个我们常用的并行初始化参数
parallel_min_percent 50% 表示指定SQL并行度最小阀值才能执行,如果没有达到这个阀值,oracle将会报ora-12827错误
parallel_adaptive_multi_user TRUE 表示按照系统资源情况动态调整SQL并行度,已取得最好的执行性能
parallel_instance_group 表示在几个实例间起并行
parallel_max_servers 100 表示整个数据库实例的并行进程数不能超过这个值
parallel_min_servers 0 表示数据库启动时初始分配的并行进程数,如果我们设置的并行度小于这个值,并行协调进程会按我们的并行度来分配并行进程数,如果我们设置的并行度大于这个值,并行协调进程会额外启动其他的并行进程来满足我们的需求
(4)使用hint方式测试DML并行查询性能
首先说一下什么时候可以使用并行技术
1.对象属性:在创建的时候,就指定了并行关键字,长期有效
2.sql强制执行:在sql中使用hint提示方法使用并行,临时有效,它是约束sql语句的执行方式,本次测试就是使用的hint方式
LS@LEO> select /*+ parallel(leo_t 4) */ count(*) from leo_t where object_name in (select /*+ parallel(leo_t1 4) */ object_name from
leo_t1);
Execution Plan 执行计划
----------------------------------------------------------
Plan hash value: 3814758652
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 16 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 94 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 94 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 94 | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN SEMI | | 10337 | 948K| 16 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 10337 | 282K| 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 10337 | 282K| 5 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 10337 | 282K| 5 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| LEO_T | 10337 | 282K| 5 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 10700 | 689K| 11 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 10700 | 689K| 11 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10700 | 689K| 11 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| LEO_T1 | 10700 | 689K| 11 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
并行先扫描子查询leo_t1表,然后对主查询leo_t表进行扫描,按照随机顺序发送到并行协调进程QC整合结果,最后将结果返回给用户
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement
Statistics 统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
466 consistent gets 466次一致性读,即处理了446个数据块
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
|
评论暂时关闭