问答榜上引发的Oracle并行的探究（二）

书接上回

问答榜上引发的Oracle并行的探究（一）

4、并行加载数据

• SQLLoader 导入文本数据的操作：
  使用DIRECT方式，针对同一个表进行并行导入：
  sqlldr USERID=SCOTT/tiger CONTROL=load1.ctl DIRECT=TRUE PARALLET=true
  sqlldr USERID=SCOTT/tiger CONTROL=load1.ctl DIRECT=TRUE PARALLET=true
  sqlldr USERID=SCOTT/tiger CONTROL=load1.ctl DIRECT=TRUE PARALLET=true
  启用三个Session，它们同时用SQLLoader并行执行对同一个表的导入操作。参数：PARALLET=true才能实现同一个表以DIRECT方式并行导入。

5、并行备份与恢复

Oracleo数据库的备份与恢复也可以实现并行。

• 备份
  通过设置RMAN的参数PARALLELISM或手工分配多个cannel来达到并行备份的目的。

1. 设置RMAN的参数PARALLELISM实现并行：

configure device type disk parallelism 3;
-- 脚本：
run{
backup incremental level=0
format '/backup/dat_%t_%s_p.bak' database filesperset 4
plus archivelog
format '/backup/arch_%t_%s_p.bak'
delete al input;
}

2. 通过手工分配channel并行备份：

 run{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/autobackup/20221015/%F';
allocate channel c1 device type disk format='/backup/backupset/20221015/%U';
allocate channel c2 device type disk format='/backup/backupset/20221015/%U';
allocate channel c3 device type disk format='/backup/backupset/20221015/%U';
allocate channel c4 device type disk format='/backup/backupset/20221015/%U';
allocate channel c5 device type disk format='/backup/backupset/20221015/%U';
backup as compressed backupset full database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
crosscheck  backup;
}

• 并行恢复
  可以通过设置参数RECOVERY_PARALLELISM来控制Oracle在做实例恢复时是否启用并行实例恢复。另外，也可以通过参数：FAST_START_PARALLEL_ROLLBACK 来控制SMON在做事务回滚时是否启用并行恢复，从Oracle 9i开始，SMON的并行事务恢复在默认情况下就已开启。

6、并行收集统计信息

• DBMS_STATS包的并行执行是通过手工指定输入参数DEGREE来实现的：
  exec dbms_stats.gather_table_stats(ownname=>‘SYS’,tabname=>‘T1’,cascade=>true,estimate_percent=>100,degree=>4);

-- session 1:开启4个并行进行收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',cascade=>true,estimate_percent=>100,degree=>4);
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.01

-- session 2:观察并行收集统计信息的并行子进程的详情
-- 收集进行中：
SQL> select slave_name,status from v$pq_slave;
SLAV STAT
---- ----
P000 BUSY
P001 BUSY
P002 BUSY
P003 BUSY
P004 BUSY
P005 BUSY
P006 BUSY
P007 BUSY
8 rows selected.
--收集完成后：8个并行子进程状态由BUSY变为IDLE，它们并没有马上终止退出：
SQL> select slave_name,status from v$pq_slave;
SLAV STAT
---- ----
P000 IDLE
P001 IDLE
P002 IDLE
P003 IDLE
P004 IDLE
P005 IDLE
P006 IDLE
P007 IDLE
8 rows selected.

在上述并行收集统计信息的过程中，Oracle启用了8个并行子进程来并行收集统计信息，这是因为Oracle启动了两组Quer Slave set，每组Query Slave Set 里并行子进程的个数就是我们手工指定的并行度4。

7、跨库插入数据能不能使用并行？（回答问题榜问题）

• create table 表名 as select
  –可以使用并行，见如下示例

-- 远端数据库：T2表
SQL> select count(*) from t2;
  COUNT(*)
----------
     46576
-- 创建testdblink
SQL> create public database link testdblink connect to scott identified by tiger using 'TEST1';
Database link created.
Elapsed: 00:00:00.07


-- 开8个并行创建表：
SQL> create table t1 parallel 8 as select /*+ parallet(8) */ * from t2@testdblink;
Table created.
Elapsed: 00:00:00.41

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  46unkr2gzny1x, child number 0
-------------------------------------
create table t1 parallel 8 as select /*+ parallet(8) */ * from
t2@testdblink
Plan hash value: 2511483212

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT  |          |       |       |   109 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 15683 |  2909K|    35   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          | 15683 |  2909K|    35   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 15683 |  2909K|    35   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       REMOTE            | T2       | 15683 |  2909K|    35   (0)| 00:00:01 | TESTD~ | R->S |            |
-----------------------------------------------------------------------------------------------------------------
SQL> select count(*) from t1;
  COUNT(*)
----------
     46576

• insert into 表名 select
  –Hint并行失效，见如下示例

SQL> insert into  /*+ parallel(8) */ t1  select /*+ parallet(8) */ * from t2@testdblink;
46576 rows created.
Elapsed: 00:00:00.40
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID  1br3knnqkmyrr, child number 0
-------------------------------------
insert into  /*+ parallel(8) */ t1  select /*+ parallet(8) */ * fromt2@testdblink
Plan hash value: 1788691278
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    35 (100)|          |        |      |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |       |            |          |        |      |
|   2 |   REMOTE                 | T2   | 15683 |  2909K|    35   (0)| 00:00:01 | TESTD~ | R->S |
-------------------------------------------------------------------------------------------------

– alter session enable parallel dml; 使用并行

SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.00
SQL> insert into  /*+ parallel(8) */ t1  select /*+ parallet(8) */ * from t2@testdblink;
46576 rows created.
Elapsed: 00:00:00.65
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
SQL_ID  1br3knnqkmyrr, child number 1
-------------------------------------
insert into  /*+ parallel(8) */ t1  select /*+ parallet(8) */ * fromt2@testdblink
Plan hash value: 2511483212
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          |       |       |    35 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 15683 |  2909K|    35   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          | 15683 |  2909K|    35   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 15683 |  2909K|    35   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       REMOTE            | T2       | 15683 |  2909K|    35   (0)| 00:00:01 | TESTD~ | R->S |            |
-----------------------------------------------------------------------------------------------------------------

                   文章推荐