書接上回
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來達到并行備份的目的。
- 設置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;
}
- 通過手工分配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 | |
-----------------------------------------------------------------------------------------------------------------
???????????????????文章推薦
| Oracle: | URL |
|---|---|
| 《Oracle 自動收集統計信息機制》 | http://www.sunline.cc/db/403670 |
| 《Oracle_索引重建—優化索引碎片》 | http://www.sunline.cc/db/399543 |
| 《DBA_TAB_MODIFICATIONS表的刷新策略測試》 | http://www.sunline.cc/db/414692 |
| 《FY_Recover_Data.dbf》 | http://www.sunline.cc/doc/74682 |
| 《Oracle RAC 集群遷移文件操作.pdf》 | http://www.sunline.cc/doc/72985 |
| 《Oracle Date 字段索引使用測試.dbf》 | http://www.sunline.cc/doc/72521 |
| 《Oracle 診斷案例 :因應用死循環導致的CPU過高》 | http://www.sunline.cc/db/483047 |
| 《Oracle 慢SQL監控腳本》 | http://www.sunline.cc/db/479620 |
| 《Oracle 慢SQL監控測試及監控腳本.pdf》 | http://www.sunline.cc/doc/76068 |
| 《Oracle 腳本實現簡單的審計功能》 | http://www.sunline.cc/db/450052 |
| 《記錄一起索引rebuild與收集統計信息的事故》 | http://www.sunline.cc/db/408934 |
| 《RAC DG刪除備庫redo時報ORA-01623》 | http://www.sunline.cc/db/515939 |
| 《ASH報告發現:os thread startup 等待事件分析》 | http://www.sunline.cc/db/521146 |
| 《問答榜上引發的Oracle并行的探究(一)》 | http://www.sunline.cc/db/521260 |
| 《問答榜上引發的Oracle并行的探究(二)》 | http://www.sunline.cc/db/521304 |
最后修改時間:2024-12-03 11:14:05
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發送郵件至:contact@modb.pro進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




