書接上回

問答榜上引發的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 |            |
-----------------------------------------------------------------------------------------------------------------

???????????????????文章推薦