oracle 并行原理深入解析及案例精粹
一、简单介绍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列上创建索引
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> 2',cascade=>TRUE);
为表启动4个并行度
LS@LEO>> 启动执行计划
LS@LEO> set autotrace trace explain stat
LS@LEO> select * from leo_t where> Execution Plan执行计划
----------------------------------------------------------
Plan hash value: 2049660393
-----------------------------------------------------------------------------------------
|> -----------------------------------------------------------------------------------------
| 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> ---------------------------------------------------
2 - access("ID"=100)
Statistics 统计信息
----------------------------------------------------------
1recursive calls
0db block gets
4consistent gets 4次一致性读，即处理4个数据块
0physical reads
0redo> 544bytes sent via SQL*Net to client
381bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows 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
------------------------------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------------------------------
| 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 统计信息
----------------------------------------------------------
44recursive calls
0db block gets
259consistent gets259次一致性读，即处理259个数据块
0physical reads
0redo> 1298bytes sent via SQL*Net to client
403bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
35rows processed
ps -ef | grep oracle从后台进程上看也能发现起了4个并行进程和1个协调进程
oracle 25075 10 22:58 ? 00:00:00 ora_p000_LEO
oracle 25077 10 22:58 ? 00:00:00 ora_p001_LEO
oracle 25079 10 22:58 ? 00:00:00 ora_p002_LEO
oracle 25081 10 22:58 ? 00:00:00 ora_p003_LEO
oracle 25083 10 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_userTRUE 表示按照系统资源情况动态调整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
-------------------------------------------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------------------------------------------
| 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> ---------------------------------------------------
5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement
Statistics 统计信息
----------------------------------------------------------
28recursive calls
0db block gets
466consistent gets 466次一致性读，即处理了446个数据块
0physical reads
0redo> 413bytes sent via SQL*Net to client
381bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
1rows processed
（5）并行DDL测试
使用10046事件生成文法追踪文件，level 12：包括sql语句解析、执行、提取、提交和回滚与等待事件，这是最高级别，而且向下兼容
10046事件解释：10046 event是oracle用于系统性能分析的重要事件。当激活这个事件后，将通知oracle kernel追踪会话的相关即时信息，并写入到相应trace文件中。这些有用的信息主要包括sql是如何进行解析，绑定变量的使用情况，会话中发生的等待事件等10046event 可分成不同的级别（level），分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的trace信息包含低于此级的所有信息。
启动10046事件命令：alter session set events '10046 trace name context forever,level 12';
关闭10046事件命令：alter session set events '10046 trace name context off';
注：oracle提供了一个tkprof工具来对trace文件进行格式化翻译，过滤出有用的信息
LS@LEO>>
Session> 表对象属性，在创建的时候就直接指定好了并行度，后面我们会从trace文件中看出，已经列出了sql解析、执行、取操作的性能指标，后面又列出了等待事件，在等待事件中我们可以看到PX并行等待事件，说明使用了并行技术执行
S@LEO> create table leo_t2 parallel 4 as select * from dba_objects;
Table created.
格式化trace文件
$ pwd
/u01/app/oracle/admin/LEO/udump
$ tkprof leo_ora_20558.trc leo.txt sys=no
TKPROF:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
输出内容
create table leo_t2 parallel 4 as select * from dba_objects
call count cpu elapsed disk query current rows
------- -------------- ---------- ---------- ---------- --------------------
Parse 1 0.01 0.03 0 0 1 0
Execute 1 0.41 4.26 199 2985 1176 10336
Fetch 0 0.00 0.00 0 0 0 0
------- -------------- ---------- ---------- ---------- --------------------
total 2 0.42 4.29 199 2985 1177 10336
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user> Elapsed times include waiting on following events:
Event waited on等待时间列表 Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
os thread startup 7 0.21 0.43
PX Deq: Join ACK 连接应答 5 0.01 0.05
PX qref latch 闩 2 0.01 0.01
PX Deq: Parse Reply 解析回复 4 0.17 0.23
enq: PS - contention 1 0.00 0.00
PX Deq: Execute Reply 执行回复 12 1.01 2.24
rdbms ipc reply 3 0.13 0.33
db file scattered read 3 0.00 0.00
log file sync 日志文件同步 2 0.00 0.00
PX Deq: Signal ACK 信号应答 4 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
索引对象属性，在创建索引的时候使用并行可以大大提高执行的效率，前提是系统资源充裕，否则可能适得其反哦：）
机制：把全部索引分成4份给4个并行进程去处理，把处理完的数据随机顺序发给QC整合结果，最后QC把最终结果返回给用户，完成sql操作
创建B-tree索引
LS@LEO> create index leo2_t_index on leo_t2(object_id) parallel 4;
Index created.
重建索引
LS@LEO>>
Index> 输出内容
create index leo2_t_index on leo_t2(object_id) parallel 4
call count cpu elapsed disk query current rows
------- -------------- ---------- ---------- ---------- --------------------
Parse 2 0.02 0.06 0 3 0 0
Execute 2 0.11 4.72 80 632 471 0
Fetch 0 0.00 0.00 0 0 0 0
------- -------------- ---------- ---------- ---------- --------------------
total 4 0.14 4.79 80 635 471 0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user> Elapsed times include waiting on following events:
Event waited on Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
os thread startup 10 0.04 0.25
PX Deq: Join ACK 10 0.01 0.02
enq: PS - contention 4 0.00 0.00
PX qref latch 37 0.09 0.37
PX Deq: Parse Reply 7 0.01 0.06
PX Deq: Execute Reply 81 1.96 3.15
PX Deq: Table Q qref 3 0.24 0.24
log file sync 2 0.00 0.00
PX Deq: Signal ACK 6 0.00 0.01
latch: session allocation 1 0.01 0.01
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
alter index leo2_t_index rebuild parallel 4
call count cpu elapsed disk query current rows
------- -------------- ---------- ---------- ---------- --------------------
Parse 2 0.02 0.09 0 54 6 0
Execute 2 0.03 0.83 122 390 458 0
Fetch 0 0.00 0.00 0 0 0 0
------- -------------- ---------- ---------- ---------- --------------------
total 4 0.05 0.93 122 444 464 0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user> Elapsed times include waiting on following events:
Event waited on Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
enq: PS - contention 3 0.00 0.00
PX Deq: Parse Reply 3 0.00 0.00
PX Deq: Execute Reply 84 0.06 0.40
PX qref latch 3 0.08 0.09
PX Deq: Table Q qref 4 0.00 0.01
log file sync 5 0.00 0.00
PX Deq: Signal ACK 7 0.00 0.00
reliable message 2 0.00 0.00
enq: RO - fast object reuse 2 0.00 0.00
db file sequential read 2 0.00 0.00
rdbms ipc reply 4 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
（6）并行DML测试
前提：首先说明oracle对并行操作是有限制的，必须设置启用会话并行度，否则即使SQL指定了并行，oracle也不会执行DML并行操作
其次oracle只对partition table分区表做并行处理（有几个分区就开几个并行），普通表oracle不做并行处理，只限delete update merge操作
LS@LEO>>
Session> 我的表leo_t1是普通表，liusheng_hash分区表（包括10个分区）
LS@LEO> explain plan for delete /*+ parallel(leo_t1 2) */ from leo_t1;
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划，对于普通表即使设置了并行度，oracle也不做并行处理，看还是使用的全表扫描
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3964128955
---------------------------------------------------------------------
|> ---------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 10700 | 40 (0)| 00:00:01 |
| 1 |DELETE | LEO_T1 | | | |
| 2 | TABLE ACCESS FULL| LEO_T1 | 10700 | 40 (0)| 00:00:01 |
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
LS@LEO> explain plan for delete /*+ parallel(liusheng_hash 2) */ from liusheng_hash;
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划，oracle对于分区表是做并行处理的，从in-out字段上也可以看出并行全表扫描
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1526574995
----------------------------------------------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 10996 | 26 (0)| 00:00:01 | | | | | |
| 1 |PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10996 | 26 (0)| 00:00:01 | | |Q1,00 | P->S | QC (RAND)|
| 3 | DELETE | LIUSHENG_HASH | | | | | |Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 10996 | 26 (0)| 00:00:01 | 1 | 10 |Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| LIUSHENG_HASH | 10996 | 26 (0)| 00:00:01 | 1 | 10 |Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
LS@LEO> explain plan for update /*+ parallel(liusheng_hash 4) */ liusheng_hash set object_name=object_name||' ';
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划更新操作也是一样
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 225854777
------------------------------------------------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10996 | 708K| 13 (0)| 00:00:01 | | | | | |
| 1 |PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10996 | 708K| 13 (0)| 00:00:01 | | |Q1,00 | P->S | QC (RAND)|
| 3 | UPDATE | LIUSHENG_HASH | | | | | | |Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 10996 | 708K| 13 (0)| 00:00:01 | 1 | 10 |Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| LIUSHENG_HASH | 10996 | 708K| 13 (0)| 00:00:01 | 1 | 10 |Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
接下来做insert并行测试，在insert测试中只有insert into ...... select ......做并行才有意义，insert into ......values ......单条插入没有意义
LS@LEO> explain plan for insert /*+ parallel(leo_t1 4) */ into leo_t1 select /*+ parallel(leo_t2 4) */ * from leo_t2;
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划 insert和select操作别分使用了并行，它们是相互独立的互不干涉
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1922268564
-----------------------------------------------------------------------------------------------------------------
|> -----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10409 |1799K| 11 (0)| 00:00:01 | | | |
| 1 |PX COORDINATOR | | | | | | | | &nbs,p; |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10409 |1799K| 11 (0)| 00:00:01 |Q1,01 | P->S | QC (RAND)|
| 3 | LOAD AS SELECT | LEO_T1 | | | | |Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10409 |1799K| 11 (0)| 00:00:01 |Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN| :TQ10000 | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | P->P | RND-ROBIN|
| 6 | PX BLOCK ITERATOR | | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| LEO_T2 | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
下面的insert语句没有在select使用并行，那么我们看看select语句是否用的串行操作
LS@LEO> explain plan for insert /*+ parallel(leo_t1 4) */ into leo_t1 select * from leo_t2;
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划的in-out(进程间数据流)中可以看出S->P：Serial to Parallel一个串行操作（全表扫描）向一个并行操作发送数据，例如
select子句是串行操作，所以就会出现这种情况
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2695467291
------------------------------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10409 |1799K| 40 (0)| 00:00:01 | | | |
| 1 |PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10409 |1799K| 40 (0)| 00:00:01 |Q1,01 | P->S | QC (RAND)|
| 3 | LOAD AS SELECT | LEO_T1 | | | | |Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | |Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 10409 |1799K| 40 (0)| 00:00:01 |Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN| :TQ10000 | 10409 |1799K| 40 (0)| 00:00:01 | | S->P | RND-ROBIN|
| 7 | TABLE ACCESS FULL | LEO_T2 | 10409 |1799K| 40 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
下面的insert语句没有在insert使用并行，让我们看看效果怎么样
LS@LEO> explain plan for insert into leo_t1 select /*+ parallel(leo_t2 4) */ * from leo_t2;
Explained.
LS@LEO> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 执行计划 TABLE ACCESS FULL - PCWP 全表扫描用的是并行，PX SEND QC (RANDOM) - P->S 表示一个并行操作向一个串行操作发送数
据，这就表示了我们先用并行select后面insert用的是串行了
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 985193522
--------------------------------------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10409 |1799K| 11 (0)| 00:00:01 | | | |
| 1 |PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | P->S | QC (RAND)|
| 3 | PX BLOCK ITERATOR | | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| LEO_T2 | 10409 |1799K| 11 (0)| 00:00:01 |Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
（7）使用并行的3种方法
1.hint 方式 临时有效
LS@LEO> set autotrace trace exp
LS@LEO> select /*+ parallel(leo_t1 4) */ * from leo_t1;
LS@LEO> select /*+ parallel(leo_t1 4) */ count(*) from leo_t1;
Execution Plan 执行计划 hint方式
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 | | | |
| 1 |SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | |Q1,00 | P->S | QC (RAND)|
| 4 | SORT AGGREGATE | | 1 | | |Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| LEO_T1 | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
2.alter table 定义方式 长期有效
LS@LEO>>
Table> LS@LEO> select count(*) from leo_t1;
Execution Plan 执行计划 定义方式
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 | | | |
| 1 |SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | |Q1,00 | P->S | QC (RAND)|
| 4 | SORT AGGREGATE | | 1 | | |Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| LEO_T1 | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
3.alter session force parallel 强制定义并行度
LS@LEO>>
Table>
LS@LEO>>
Session> LS@LEO> select count(*) from leo_t1;
Execution Plan 执行计划强制使用并行度4执行SQL
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 | | | |
| 1 |SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | |Q1,00 | P->S | QC (RAND)|
| 4 | SORT AGGREGATE | | 1 | | |Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| LEO_T1 | 10700 | 11 (0)| 00:00:01 |Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
（8）/*+ append */直接加载
直接加载：指数据不经过db_buffer_cache内存区，直接写入到数据文件中，实际上是直接追加到数据段的最后，不在段中寻找空闲空间而插入
LS@LEO> create table leo_t3 as select * from dba_objects; 创建表leo_t3
Table created.
LS@LEO> insert /*+ append*/ into leo_t3 select * from dba_objects; 直接加载数据
10337 rows created.
LS@LEO> create table leo_t4 as select * from leo_t3 where rownum select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';表leo_t4占用了16个区
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4 0 65536
LEO_T4 1 65536
LEO_T4 2 65536
LEO_T4 3 65536
LEO_T4 4 65536
LEO_T4 5 65536
LEO_T4 6 65536
LEO_T4 7 65536
LEO_T4 8 65536
LEO_T4 9 65536
LEO_T4 10 65536
LEO_T4 11 65536
LEO_T4 12 65536
LEO_T4 13 65536
LEO_T4 14 65536
LEO_T4 15 65536
LEO_T4 16 1048576
LS@LEO> delete from leo_t4; 删除所有记录
9999 rows deleted.
LS@LEO> commit; 提交
Commit complete.
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4'; 删除之后为什么还占用16个区呢，我来解释一下，oracle在delete操作后数据并没有真实的删除了。只是打上一个“标记”说明这些数据不可用了，也说明了为什么删除之后磁盘空间没有回收的问题。
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4 0 65536
LEO_T4 1 65536
LEO_T4 2 65536
LEO_T4 3 65536
LEO_T4 4 65536
LEO_T4 5 65536
LEO_T4 6 65536
LEO_T4 7 65536
LEO_T4 8 65536
LEO_T4 9 65536
LEO_T4 10 65536
LEO_T4 11 65536
LEO_T4 12 65536
LEO_T4 13 65536
LEO_T4 14 65536
LEO_T4 15 65536
LEO_T4 16 1048576
LS@LEO> insert into leo_t4 select * from leo_t3 where rownum select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4 0 65536
LEO_T4 1 65536
LEO_T4 2 65536
LEO_T4 3 65536
LEO_T4 4 65536
LEO_T4 5 65536
LEO_T4 6 65536
LEO_T4 7 65536
LEO_T4 8 65536
LEO_T4 9 65536
LEO_T4 10 65536
LEO_T4 11 65536
LEO_T4 12 65536
LEO_T4 13 65536
LEO_T4 14 65536
LEO_T4 &nbs,p; 15 65536
LEO_T4 16 1048576
LS@LEO> delete from leo_t4; 删除所有记录
9999 rows deleted.
LS@LEO> commit;
Commit complete.
LS@LEO> select count(*) from leo_t4; 记录数为0
COUNT(*)
----------
0
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4'; 这个表还是占用16个区，数据块有数据但是可以覆
盖，我们认为是空闲的块
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4 0 65536
LEO_T4 1 65536
LEO_T4 2 65536
LEO_T4 3 65536
LEO_T4 4 65536
LEO_T4 5 65536
LEO_T4 6 65536
LEO_T4 7 65536
LEO_T4 8 65536
LEO_T4 9 65536
LEO_T4 10 65536
LEO_T4 11 65536
LEO_T4 12 65536
LEO_T4 13 65536
LEO_T4 14 65536
LEO_T4 15 65536
LEO_T4 16 1048576
LS@LEO> insert /*+ append */ into leo_t4 select * from leo_t3 where rownum commit; 必须commit之后，oracle才讲HWM高水位线移动到新数据块之上，如果没有commit，oracle不会移动HWM高水位线，因此看不到数据字典里面的变化（也就是不显示后面的20个区），如果此时回滚的话，HWM高水位线不用动，就想什么都没有发生一样
Commit complete.
LS@LEO>select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';
SEGMENT_NAME EXTENT_ID BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4 0 65536
LEO_T4 1 65536
LEO_T4 2 65536
LEO_T4 3 65536
LEO_T4 4 65536
LEO_T4 5 65536
LEO_T4 6 65536
LEO_T4 7 65536
LEO_T4 8 65536
LEO_T4 9 65536
LEO_T4 10 65536
LEO_T4 11 65536
LEO_T4 12 65536
LEO_T4 13 65536
LEO_T4 14 65536
LEO_T4 15 65536
LEO_T4 16 65536
LEO_T4 17 65536
LEO_T4 18 65536
LEO_T4 19 65536
LEO_T4 20 65536
LEO_T4 21 65536
LEO_T4 22 65536
LEO_T4 23 65536
LEO_T4 24 65536
LEO_T4 25 65536
LEO_T4 26 65536
LEO_T4 27 65536
LEO_T4 28 65536
LEO_T4 29 65536
LEO_T4 30 65536
LEO_T4 31 65536
LEO_T4 32 65536
LEO_T4 33 65536
LEO_T4 34 65536
LEO_T4 35 65536
LEO_T4 36 65536
37 rows selected.
（9）/*+ append */直接加载和redo
LS@LEO> create table leo_t5 as select object_id,object_name from dba_objects;创建表leo_t5
Table created.
LS@LEO> create table leo_t6 as select object_id,object_name from dba_objects;创建表leo_t6
Table created.
LS@LEO>>
Table> LS@LEO> truncate table leo_t5;截断表
Table truncated.
LS@LEO> set autotrace trace stat;启动统计信息
insert into leo_t5 select * from leo_t6;传统加载
LS@LEO>
10340 rows created.
未完待续。。。。。。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]