[FAQ] 什么时候，如何使用no logging？ [复制链接]

中级会员

精华贴数: 0
技术积分: 1026
社区积分: 0
注册时间: 2002-8-27

论坛徽章:: 0

发表于 2004-7-6 11:51:30 |显示全部楼层

我在执行一句insert语句或者update语句的时候，因为数据量比较大，我能使用with no logging吗？

lucky_lau

版主

精华贴数: 2
技术积分: 16177
社区积分: 6277
注册时间: 2002-1-5

论坛徽章:: 26

发表于 2004-7-6 11:53:11 |显示全部楼层

不能

coolbid

老会员

精华贴数: 0
技术积分: 940
社区积分: 56
注册时间: 2002-3-28

论坛徽章:: 1

发表于 2004-7-6 11:55:20 |显示全部楼层

update不可以
insert into ... values也不可以
insert into select可以

斑竹

精华贴数: 0
技术积分: 450
社区积分: 295
注册时间: 2004-7-1

论坛徽章:: 3

发表于 2004-7-6 12:01:42 |显示全部楼层

insert ...select...
可以

先alter table nologging

然后用insert/*+ append */ into select ......

但是，你的table上如果有index，redo还是会很多的。
[php]
SQL> create table test as select * from all_objects where 1=2;

Table created

SQL> insert into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
   1805  recursive calls
   3490  db block gets
   167200  consistent gets
      0  physical reads
3547480  redo size
      850  bytes sent via SQL*Net to client
      569  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
   31011  rows processed

SQL> insert /*+ append */ into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
   1792  recursive calls
   1108  db block gets
   166790  consistent gets
      0  physical reads
   118536  redo size
      841  bytes sent via SQL*Net to client
      583  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   31011  rows processed

SQL> alter table test nologging;

表已更改。

SQL> insert /*+ append */ into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
      213  recursive calls
      87  db block gets
   166074  consistent gets
      0  physical reads
      784  redo size
      843  bytes sent via SQL*Net to client
      583  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
   31011  rows processed
'
[/php]

斑竹

精华贴数: 0
技术积分: 450
社区积分: 295
注册时间: 2004-7-1

论坛徽章:: 3

发表于 2004-7-6 12:11:54 |显示全部楼层

再来看看有index的状况：
[php]
SQL> create index ind_test on test (object_id);

索引已创建。

SQL> insert /*+ append */ into test  select * from all_objects;

已创建31012行。

Statistics
----------------------------------------------------------
      263  recursive calls
   1631  db block gets
   166246  consistent gets
      0  physical reads
2063232  redo size
      843  bytes sent via SQL*Net to client
      583  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
   31012  rows processed
'
[/php]

maying

中级会员

精华贴数: 0
技术积分: 1026
社区积分: 0
注册时间: 2002-8-27

论坛徽章:: 0

发表于 2004-7-6 14:54:49 |显示全部楼层

最初由 coolbid 发布
[B]update不可以
insert into ... values也不可以
insert into select可以 [/B]

非常感谢。能解释一下为什么加了/* + append */后，redo size就明显减少了吗？

eygle

超级版主

天下有雪

精华贴数: 66
技术积分: 208411
社区积分: 6479
注册时间: 2001-10-8

论坛徽章:: 100

发表于 2004-7-12 19:38:29 |显示全部楼层

最初由糖醋兔子发布
[B]insert ...select...
可以
[php]
SQL> create table test as select * from all_objects where 1=2;

Table created

SQL> insert into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
   1805  recursive calls
   3490  db block gets
   167200  consistent gets
      0  physical reads
3547480  redo size
      850  bytes sent via SQL*Net to client
      569  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
   31011  rows processed

SQL> insert /*+ append */ into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
   1792  recursive calls
   1108  db block gets
   166790  consistent gets
      0  physical reads
   118536  redo size
      841  bytes sent via SQL*Net to client
      583  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   31011  rows processed

SQL> alter table test nologging;

表已更改。

SQL> insert /*+ append */ into test  select * from all_objects;

已创建31011行。

Statistics
----------------------------------------------------------
      213  recursive calls
      87  db block gets
   166074  consistent gets
      0  physical reads
      784  redo size
      843  bytes sent via SQL*Net to client
      583  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
   31011  rows processed
'
[/php] [/B]

你这个测试结果可疑的很,我不能再现你的过程
注意不要使用auto trace来查看redo size
这个值是有偏差的.

看看我的测试结果:

你可以建立这样一个视图给所有用户用:
[php]
create or replace view redo_size
as
select value
  from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
[/php]

测试:

[php]
SQL> create table test as select * from dba_objects
  2  where 1=0;

Table created.

SQL> select * from redo_size;

   VALUE
----------
2320828

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

   VALUE
----------
3408128

SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

   VALUE
----------
4491892

SQL> select (4491892 -3408128) redo2,(3408128 -2320828) redo1
  2  from dual;

   REDO2          REDO1
---------------          ----------------
1083764          1087300

.
SQL>
[/php]

我的版本:

[php]
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0    Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
.
[/php]

eygle

超级版主

天下有雪

精华贴数: 66
技术积分: 208411
社区积分: 6479
注册时间: 2001-10-8

论坛徽章:: 100

发表于 2004-7-12 19:47:12 |显示全部楼层

进一步的:

[php]
SQL> alter table test nologging;

Table altered.

SQL> select * from redo_size;

   VALUE
----------
4494224

SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

   VALUE
----------
4495800

SQL> select (4491892 -3408128) redo2,(3408128 -2320828) redo1,(4495800 -4494224) redo3 from dual;

   REDO2    REDO1    REDO3
---------- ---------- ----------
1083764 1087300    1576

.

[/php]

斑竹

精华贴数: 0
技术积分: 450
社区积分: 295
注册时间: 2004-7-1

论坛徽章:: 3

发表于 2004-7-12 20:25:47 |显示全部楼层

恩，我同意你说的autotrace的结果有偏差。
但是，我的结果还是这样啊

我的os是win2000的，oracel版本：
[php]
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0    Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
'
[/php]

看看这个测试：
[php]
SQL> drop table test;

表已丢弃。

SQL> create table test as select * from all_objects where 1=2;

表已创建。

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  11830268

SQL> insert into test  select * from all_objects;

已创建31014行。

SQL>  select value
  2 from v$mystat, v$statname
  3 where v$mystat.statistic# = v$statname.statistic#
  4 and v$statname.name = 'redo size';

   VALUE
----------
  15384092

SQL> commit;

提交完成。

SQL>  select value
  2 from v$mystat, v$statname
  3 where v$mystat.statistic# = v$statname.statistic#
  4 and v$statname.name = 'redo size';

   VALUE
----------
  15384176

SQL> insert /*+ append */ into test  select * from all_o

已创建31014行。

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  15503656

SQL> commit;

提交完成。

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  15504132
'
[/php]

数字就不用算了吧，一眼就看出来了

斑竹

精华贴数: 0
技术积分: 450
社区积分: 295
注册时间: 2004-7-1

论坛徽章:: 3

发表于 2004-7-12 20:35:58 |显示全部楼层

oracle10g上的情况：
[php]
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0    Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> drop table test;

表已丢弃。

SQL> create table test as select * from all_objects where 1=2;

表已创建。

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  17849016

SQL> insert into test select * from all_objects;

已创建47827行。

SQL> commit;

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  35428012

SQL> insert /*+ append */ into test select * from all_objects;

已创建47827行。

SQL> commit;

提交完成。

SQL> select value
  2  from v$mystat, v$statname
  3  where v$mystat.statistic# = v$statname.statistic#
  4  and v$statname.name = 'redo size';

   VALUE
----------
  35705816
'
[/php]

os也是win2000的。