alter table nologging /*+APPEND PARALLEL(n)*/
最近系統中關鍵任務做了如下操作,
alter table table_name nologging;
alter session enable parallel dml;
insert into /*+append parallel(n)*/ table_name select xx left join xxx on xx.id = xxx.id;
commit;
alter table table_name logging;
注意:系統中允許table nologging,注意任務中表鎖的情況,需要及時提交。
下面是測試結果:
--------------------------------------------------------------------------------
create table test1 as select * from test where 1=2;
[1]
insert into test1
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created )
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id =
b.object_id order by a.object_id desc;
Elapsed: 00:01:42.44
3355 recursive calls
2009619 db block gets
1455125 consistent gets
393476 physical reads
2971133724 redo size
18754752 rows processed
[2]
alter table test1 nologging;
insert into test1
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created )
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id =
b.object_id order by a.object_id desc;
Elapsed: 00:01:52.06
3426 recursive calls
2010276 db block gets
1455523 consistent gets
393476 physical reads
2971157132
redo size
875 bytes sent via SQL*Net to client
1167 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
1 sorts (disk)
18754752 rows processed
[3]
drop table test1 purge;
create table test1 as select * from test where 1=2;
alter table test1 nologging;
insert /*+append*/into test1
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created )
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id =
b.object_id order by a.object_id desc;
Elapsed: 00:00:53.17
3588 recursive calls
368898 db block gets
727929 consistent gets
393476 physical reads
460940
redo size
861 bytes sent via SQL*Net to client
1186 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
18754752 rows processed
[4]
drop table test1 purge;
create table test1 as select * from test where 1=2;
alter table test1 nologging;
insert /*+append parallel(12)*/into test1
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created )
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id =
b.object_id order by a.object_id desc;
Elapsed: 00:00:23.98
3476 recursive calls
368906 db block gets
728849 consistent gets
1101962 physical reads
460896
redo size
3 SQL*Net roundtrips to/from client
6 sorts (memory)
4 sorts (disk)
18754752 rows processed
[5]
drop table test1 purge;
create table test1 as select * from test where 1=2;
insert /*+ parallel(12)*/into test1
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created )
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id =
b.object_id order by a.object_id desc;
【結論】並行開的多避免磁碟排序,加相同的並行不加append時間是1分08秒,都加了是23秒,只加append時間介於兩者之間。
所以先設定alter table test nologging; 然後sql寫hint /*+append parallel(n)*/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-2155434/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Oracle] Create table as 速度測試nologging+parallelOracleParallel
- Nologging and append testAPP
- nologging與append ztAPP
- oracle nologging和appendOracleAPP
- "Alter index rebuild online parallel n"會修改索引的degree屬性IndexRebuildParallel索引
- Alter table for ORACLEOracle
- nologging和insert /*+append*/APP
- append和nologging的案例APP
- alter table move 和 alter table shrink space的區別
- 轉eygle_append與nologgingAPP
- 聊聊Append、nologging和Redo LogAPP
- mysql的ALTER TABLE命令MySql
- oracle alter table詳解Oracle
- alter table using indexIndex
- v$lock之alter table drop column與alter table set unused column區別系列五
- [Oracle] Insert into速度測試nologging+parallelOracleParallel
- [Oracle] Create index速度測試nologging+parallelOracleIndexParallel
- alter table語法增補(一)
- ALTER TABLE MOVE | SHRINK SPACE區別
- 使用append+nologging引起恢復故障實驗APP
- ALTER TABLE MOVE和SHRINK SPACE區別
- Direct Path Insert與APPEND, PARALLEL的梳理與小結APPParallel
- Parallel DML和append將在表上產生exclusive lockParallelAPP
- alter session force parallel query與執行計劃變化SessionParallel
- alter table move 與shrink space的區別
- alter table move跟shrink space的區別
- alter table engine=memory ERROR 1114Error
- alter session enable parallel dml 使DML語句並行執行SessionParallel並行
- Oracle NoLogging Append 方式減少批量insert的redo_sizeOracleAPP
- 歸檔模式下的 nologging和/*+ append */引數 效能測試模式APP
- linux parallel rsync 拷貝N多檔案LinuxParallel
- Oracle 11g alter table move與shrink spaceOracle
- alter table move跟shrink space的區別(轉)
- ALTER TABLE causes auto_increment resulting key 'PRIMARY'REM
- 表、索引遷移表空間alter table move索引
- alter table列管理的一些區別
- oracle 中 ALTER TABLE ADD default 的明確Oracle
- oracle 10g__alter table shrink space compactOracle 10g