ITPub博客

首页 > 数据库 > Oracle > alter table nologging /*+APPEND PARALLEL(n)*/

alter table nologging /*+APPEND PARALLEL(n)*/

原创 Oracle 作者:哎呀我的天呐 时间:2018-05-31 19:57:53 0 删除 编辑

最近系统中关键任务做了如下操作,
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/MySQL工作多年,Oracle OCM、MySQL OCP,擅长Oracle/MySQL SQL Tuning & DB Performance

注册时间:2014-10-30

  • 博文量
    293
  • 访问量
    2002213