问答榜上引发的oracle并行的探究（二） -m6米乐安卓版下载

书接上回

问答榜上引发的oracle并行的探究（一）：https://www.modb.pro/db/521260

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 |            |
-----------------------------------------------------------------------------------------------------------------

                   文章推荐