本文深入探讨了Oracle数据库的并行执行原理，通过hint、alter table和alter session三种方式展示了如何临时和长期启用并行查询。同时，文章详细解释了直接加载数据（/*+append*/）的方式，指出其不经过缓冲区直接写入数据文件，避免了在段中寻找空闲空间，从而提高效率。此外，还分析了直接加载与产生redo日志的关系，表明两者在redo量上的差异并不显著。
Oracle 并行原理深入解析及案例精粹
[日期：2012-08-12]
来源：Linux社区
作者：Leonarding
[字体：大 中 小]
(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
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 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> alter table leo_t1 parallel 4;
Table altered.
LS@LEO> select count(*) from leo_t1;
Execution Plan 执行计划 定义方式
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 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> alter table leo_t1 parallel 1; 首先我们已经修改并行度为1
Table altered.
LS@LEO> alter session force parallel query parallel 4; 再次强制定义并行度为4
Session altered.
LS@LEO> select count(*) from leo_t1;
Execution Plan 执行计划 强制使用并行度4执行SQL
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 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<10000; 创建表leo_t4
Table created.
LS@LEO> 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<10000; 传统加载 oracle会找段中的空闲空间插入数据，看还是利旧了原来的16个区
9999 rows created.
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 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<10000; 直接加载方式，oracle把新数据直接插入到新的20个区里了，并没有使用原来的16个区空闲块，也就应了不在段中寻找空闲块插入
9999 rows created.
LS@LEO> 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> alter table leo_t5 logging; 设置产生redo日志模式
Table altered.
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.
Statistics 统计信息
----------------------------------------------------------
197 recursive calls
185 db block gets
92 consistent gets
60 physical reads
37128 redo size 37128 redo量
664 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
10340 rows processed
LS@LEO> rollback; 回滚
Rollback complete.
LS@LEO> insert /*+ append */ into leo_t5 select * from leo_t6; 直接加载
10340 rows created.
Statistics
----------------------------------------------------------
111 recursive calls
180 db block gets
79 consistent gets
21 physical reads
36640 redo size 36640 redo量
664 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10340 rows processed
小结：我们看到传统加载和直接加载产生的redo量并没有太大的差异，因为只要底层数据块发生变化，就会生成redo信息，不管传统和直接都会修改数据块，用来恢复依据，所以并没有太大的差异。
扫一扫
3753
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
