create table as select * from xx是否会生成redo

最新推荐文章于 2022-04-10 18:53:25 发布

cnje5551

最新推荐文章于 2022-04-10 18:53:25 发布

阅读量310

点赞数

文章标签：数据库运维

redo

重做日志文件（redo log file）对Oracle数据库来说至关重要，它们是数据库的事务日志。Oracle维护着两类重做日志文件：

在线（online）重做日志文件和归档（archived）重做日志文件。这两类重做日志文件都用于恢复；其主要目的是，

万一实例失败或介质失败，它们就能派上用场。

如果数据库所在主机掉电，导致实例失败，Oracle会使用在线重做日志将系统恰好恢复到掉电之前的那个时间点。

如果磁盘驱动器出现故障（这是一个介质失败），Oracle会使用归档重做日志以及在线重做日志将该驱动器上的

数据备份恢复到适当的时间点。

归档重做日志文件实际上就是已填满的"旧"在线重做日志文件的副本。系统将日志文件填满时，

ARCH进程会在另一个位置建立在线重做日志文件的一个副本，也可以在本地和远程位置上建立多个另外的副本.

如果由于磁盘驱动器损坏或者其他物理故障而导致失败,就会用这些归档重做日志文件来执行介质恢复.

Oracle拿到这些归档重做日志文件，并把它们应用于数据文件的备份，使这些数据文件能与数据库的其余部分保持一至.

归档重做日志文件是数据库的事务历史。

测量redo

redo管理是数据库中的一个串行点.任何Oracle实例都有一个LGWR，最终所有事务都会归于LGWR，要求这个进程管理它们的redo,

并COMMIT其事务,LGWR工作越忙,系统就会越慢.通过查看一个操作会生成多少redo,并对一个问题的多种解决方法进行测试,

可以从中找出最佳的方法。

与redo有关的视图

V$MYSTAT，其中有会话的提交信息

V$STATNAME，这个视图能告诉我们V$MYSTAT中的每一行表示什么（查看的统计名）。

查询redo大小的语句

SELECT a.NAME,

b.VALUE cur_size_byte,

round(b.VALUE / 1024, 3) || 'KB' cur_size_kb

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'

数据库归档模式

数据库归档用来保存redo的日志文件副本,一般安装时默认未开启数据库的归档模式。

在NOARCHIVELOG模式的数据库中,除了数据字典的修改外,CREATE TABLE不会记录日志.

如果你想在NOARCHIVELOG模式的数据库上看到差别,可以把对表T的DROP TABLE和CREATE TABLE换成DROP INDEX和CREATE INDEX。

默认情况下,不论数据库以何种模式运行，这些操作都会生成日志。

因为不同的模式可能导致不同的行为。你的生产系统可能采用ARCHIVELOG模式运行.

倘若你执行的大量操作在ARCHIVELOG模式下会生成redo,而在NOARCHIVELOG模式下不会生成redo,

你肯定想在测试时就发现这一点，而不要等到系统交付给用户时才暴露出来！

查看是否归档

查看数据库是否开启归档

select name,log_mode from v$database;

启用归档

startup mount

alter database archivelog;

alter database open;

禁止归档

shutdown immediate

startup mount

alter database noarchivelog

alter database open

force logging(强制日志)模式：

如果数据库强制日志模式开启后,则Oracle无论什么操作都进行redo的写入。

查看强制日志模式

通过

select force_logging from v$database

可以看到当前数据库是否开启了强制日志模式状态

开启强制日志模式

如果未开启数据库强制日志模式（默认未开启），则可以通过

alter database force logging开启,之后Oracle无论什么操作都进行redo的写入,不依赖于数据库的归档模式等其他因素.

关闭强制日志模式

如果已经开启了数据库强制日志模式，则可以通过

alter database no force logging关闭强制日志模式。

使数据库恢复先前的设置,数据库是否写入redo由数据库的归档模式等其他因素决定

disable_logging

那么在Oracle内部还存在一个内部参数：_disable_logging 默认是false

通过更改为true可以让Oracle在修改表中的记录的时候完全不记录redo,这个参数要甚用,平时,我们只作为性能测试用。

查看：show parameter disa /disable/_disable_logging

开启：alter system set "_disable_logging"=true scope=both;

禁用：alter system set "_disable_logging"=false

表的归档模式

查看表的logging模式

查看表是否是logging状态用如下SQL:

select table_name,logging from dba_tables where table_name='tablename';

修改表的logging模式

修改表的logging状态sql:

alter table table_name nologging/logging

减少redo写入

本节所讲的都是当数据库未开启强制日志模式时的操作。

对象的操作在执行时会产生重做日志,采用某种方式,生成的redo会比平常(即不使用NOLOGGING子句时)少得多.

注意,这里说"redo"少得多,而不是"完全没有redo".所有操作都会生成一些redo,不论数据库的日志模式是什么,

所有数据字典操作都会计入日志。

如何减少redo

create table时减少redo的方法

创建表时crate table as加入nolongging选项减少redo，格式如下

create table [table_name] nologging as [select表达式]。

insert into减少redo的方法

insert 大批量数据时加入/*+append */选项减少redo写入,格式如下

insert /*+append */ into [table_name] [select表达式]

数据库归档模式下生成redo规则

create table时nologging效果

归档模式下创建的表，默认为logging模式。

创建表时crate table as加入nolongging选项减少redo写入明显

验证

下面比较以下两种create table as时产生的redo size量。

SELECT a.NAME,

b.VALUE cur_size_byte,

round(b.VALUE / 1024, 3) || 'KB' cur_size_kb

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'

查询当前的重做日志大小记录下来

create table test_1 as select * from test;

SELECT a.NAME,

b.VALUE cur_size_byte,

round(b.VALUE / 1024, 3) || 'KB' cur_size_kb

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'

查询当前的重做日志大小减去前面记录下来的值计算刚才这个操作产生的redo大小标记为redo_1

SELECT a.NAME,

b.VALUE cur_size_byte,

round(b.VALUE / 1024, 3) || 'KB' cur_size_kb

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'

查询当前的重做日志大小记录下来

create table test_2 nologging as select * from test;

SELECT a.NAME,

b.VALUE cur_size_byte,

round(b.VALUE / 1024, 3) || 'KB' cur_size_kb

FROM v$statname a, v$mystat b

WHERE a.statistic# = b.statistic#

AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'

查询当前的重做日志大小减去前面记录下来的值计算刚才这个操作产生的redo大小标记为redo_2

比较redo_1和redo_2的大小就知道crate table as加入nolongging或不加nologging选项的区别了

insert into时加入append效果

表模式logging

当表模式为logging状态时,无论是append模式还是no append模式,redo都会生成,即加入append选项无法生效。

验证

下面比较以下两种insert时产生的redo size量,可以看出redo量是差不多的。

计算重做大小的方法与上面的一样就不说了

1、insert /*+append */ into test_1 select * from test;

commit;

2、insert into test_1 select * from test;

commit;

表模式nologging

当表模式为nologging状态时,只有加入append模式会明显减少生成redo。

验证

1、insert /*+append */ into test_1 select * from test;

commit;

2、insert into test_1 select * from test;

commit;

数据库非归档模式生成redo规则

create table 使用nologging对产生redo的影响

非归档模式下创建的表，默认为nologging模式。

在NOARCHIVELOG模式的数据库中，除了数据字典的修改外，CREATE TABLE不会记录日志。

因此创建表时（crate table as）加入nologging选项减少redo写入不明显,即nologging选项加不加都差不多

验证

下面比较以下两种create table as时产生的redo size量。

create table test_1 as select * from test;

create table test_2 nologging as select * from test;

insert into时append效果

表模式logging

当表模式为logging状态时,加入append模式明显减少生成redo,而no append模式下不会减少生成。

验证

insert /*+append */ into test_1 select * from test;

commit;

insert into test_1 select * from test;

commit;

表模式nologging

当表模式为nologging状态时，append的模式会减少生成redo,而no append模式不会减少生成。

验证

insert /*+append */ into test_1 select * from test;

commit;

insert into test_1 select * from test;

commit;

其实就是直截加载与传统加载的区别

直接加载优势

直接加载比传统加载效率要高

不扫描原来的空数据块

不需要sql解析，减少系统的负载

不经过SGA

不走DBWR进程，走自己的专属进程，所以速度快

直接加载限制

不能加载簇表

锁定整个表，在表上有活动事务的时候不能加载

直接加载特点

直接加载是在所有数据块后面加载新数据块，修改高水位线，不扫描原来的空数据块。

直接加载只产生一点点的管理redo，因为要修改数据字典（也可以讲不产生redo）。

回滚，如果加载失败把新分配数据块抹掉就行了。

无需SGA，无需SQL解析，无需DBWR进程

实验

现在我们已经定义了test;

SQL> select count(*) from test; 现在表里没有记录数

COUNT(*)

----------

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST'; 现在分配了1个区

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log 传统方式加载数据

LS@LEO> select count(*) from test; 已经成功加载了100万条数据

COUNT(*)

----------

1000000

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST'; 100万条数据占用28个区

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

TEST 1 65536

TEST 2 65536

TEST 3 65536

TEST 4 65536

TEST 5 65536

TEST 6 65536

TEST 7 65536

TEST 8 65536

TEST 9 65536

TEST 10 65536

TEST 11 65536

TEST 12 65536

TEST 13 65536

TEST 14 65536

TEST 15 65536

TEST 16 1048576

TEST 17 1048576

TEST 18 1048576

TEST 19 1048576

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 20 1048576

TEST 21 1048576

TEST 22 1048576

TEST 23 1048576

TEST 24 1048576

TEST 25 1048576

TEST 26 1048576

TEST 27 1048576

28 rows selected

SQL> delete from test; 删除100万条数据

1000000 rows deleted.

SQL> commit; 提交

Commit complete.

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST';

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

TEST 1 65536

TEST 2 65536

TEST 3 65536

TEST 4 65536

TEST 5 65536

TEST 6 65536

TEST 7 65536

TEST 8 65536

TEST 9 65536

TEST 10 65536

TEST 11 65536

TEST 12 65536

TEST 13 65536

TEST 14 65536

TEST 15 65536

TEST 16 1048576

TEST 17 1048576

TEST 18 1048576

TEST 19 1048576

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 20 1048576

TEST 21 1048576

TEST 22 1048576

TEST 23 1048576

TEST 24 1048576

TEST 25 1048576

TEST 26 1048576

TEST 27 1048576

28 rows selected

把数据都删除了还占用空间,oracle的delete操作不回收空间,只是把自己的记录标记为删除,实际呢还占用的空间不释放

[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log 第二次传统方式加载数据

SQL> select count(*) from test; 已经成功加载了100万条数据

COUNT(*)

----------

1000000

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST';

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

TEST 1 65536

TEST 2 65536

TEST 3 65536

TEST 4 65536

TEST 5 65536

TEST 6 65536

TEST 7 65536

TEST 8 65536

TEST 9 65536

TEST 10 65536

TEST 11 65536

TEST 12 65536

TEST 13 65536

TEST 14 65536

TEST 15 65536

TEST 16 1048576

TEST 17 1048576

TEST 18 1048576

TEST 19 1048576

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 20 1048576

TEST 21 1048576

TEST 22 1048576

TEST 23 1048576

TEST 24 1048576

TEST 25 1048576

TEST 26 1048576

TEST 27 1048576

28 rows selected

使用传统方式加载数据，会扫描原来的空数据块，会把新加载的数据插入到空数据块内，看我们还是使用原来的28个区

SQL> delete from test; 这是第二次删除100万条数据

1000000 rows deleted.

SQL> commit; 提交

Commit complete.

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST';

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

TEST 1 65536

TEST 2 65536

TEST 3 65536

TEST 4 65536

TEST 5 65536

TEST 6 65536

TEST 7 65536

TEST 8 65536

TEST 9 65536

TEST 10 65536

TEST 11 65536

TEST 12 65536

TEST 13 65536

TEST 14 65536

TEST 15 65536

TEST 16 1048576

TEST 17 1048576

TEST 18 1048576

TEST 19 1048576

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 20 1048576

TEST 21 1048576

TEST 22 1048576

TEST 23 1048576

TEST 24 1048576

TEST 25 1048576

TEST 26 1048576

TEST 27 1048576

28 rows selected

delete还是不回收空间，我们依然占用着28个区

[oracle@secdb1 ~]$ sqlldr userid=ls/ls control=leo_test.ctl data=leo_test.data log=leo_test.log direct=true 直接方式加载数据

SQL> select segment_name,extent_id,bytes from user_extents where segment_name='TEST';

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 0 65536

TEST 1 65536

TEST 2 65536

TEST 3 65536

TEST 4 65536

TEST 5 65536

TEST 6 65536

TEST 7 65536

TEST 8 65536

TEST 9 65536

TEST 10 65536

TEST 11 65536

TEST 12 65536

TEST 13 65536

TEST 14 65536

TEST 15 65536

TEST 16 1048576

TEST 17 1048576

TEST 18 1048576

TEST 19 1048576

SEGMENT_NAME EXTENT_ID BYTES

-------------------------------------------------------------------------------- ---------- ----------

TEST 20 1048576

TEST 21 1048576

TEST 22 1048576

TEST 23 1048576

TEST 24 1048576

TEST 25 1048576

TEST 26 1048576

TEST 27 1048576

TEST 28 1048576

TEST 29 1048576

TEST 30 1048576

TEST 31 1048576

TEST 32 1048576

TEST 33 1048576

TEST 34 1048576

TEST 35 1048576

TEST 36 1048576

TEST 37 1048576

TEST 38 1048576

TEST 39 1048576

TEST 40 1048576

TEST 41 1048576

TEST 42 1048576

TEST 43 1048576

TEST 44 1048576

TEST 45 1048576

TEST 46 1048576

TEST 47 1048576

48 rows selected

发现同样的100万条记录,竟然占用了48个区,传统加载只用了28个,而我们使用直接加载到多了20个数据块,

对了直接加载不扫描原来的空数据块,会在所有数据块之后加载新的数据块插入数据修改高水位线HWM,

当提交事务之后,把高水位线移到新数据之后,其他的用户就可以看见了。

比较直接加载使用conventional 和direct方式产生的redo大小（可以通过/*+ append */模拟直接加载）。

明确：直接加载与logging配合下并不能显著的减少redo日志量

直接加载与nologging配合下可以大幅度的减少redo日志量

SQL> create table leo_t1 as select * from test where 1=2; 创建leo_t1表

Table created.

SQL> alter table leo_t1 logging; 设置leo_t1表logging模式

Table altered.

SQL> set autotrace traceonly;

SQL> insert into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 采用传统方式加载2万条记录

20000 rows created.

Statistics 统计信息

----------------------------------------------------------

1071 recursive calls

2668 db block gets

1860 consistent gets

386 physical reads

1680404 redo size 这是产生的日志量1680404

680 bytes sent via SQL*Net to client

603 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

20000 rows processed

SQL> rollback; 回滚操作，使用undo表空间

Rollback complete.

SQL> insert /*+ append */ into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 使用直接加载方式插入2万条记录

20000 rows created.

Statistics

----------------------------------------------------------

94 recursive calls

268 db block gets

1294 consistent gets

202 physical reads

1627260 redo size 当leo_t1为logging属性时，直接加载和传统加载产生redo日志差不多

664 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

20000 rows processed

小结：这是因为在logging模式下，所有的数据块的改变都会产生redo日志，为以后恢复做准备，这时候直接加载没有多大的优势。

直接加载与nologging配合下可以大幅度的减少redo日志量

重大前提

如果你的数据库开启了force_logging=yes模式，那么不管你是传统加载还是直接加载都不会减少redo产生量

所以要想大幅度减少redo日志就必须满足3个条件

（1）关闭force_logging选项 alter database no force logging; 启动 alter database force logging;

（2）数据对象级别nologging模式 alter table leo_t1 nologging;

（3）直接加载 insert /*+ append */ into

数据库归档与redo日志量关系

数据库处于归档模式

当表模式为logging状态时,无论是否使用append模式,都会生成redo.当表模式为nologging状态时,只有append模式,不会生成redo。

数据库处于非归档模式

无论是在logging还是nologing的模式下，append的模式都不会生成redo,而no append模式下都会生成redo。

SQL> alter database no force logging;

SQL> select force_logging from v$database; 已经关闭force_logging选项

FOR

---

SQL> alter table leo_t1 nologging; 设置leo_t1表nologging模式

Table altered.

SQL> select logging from user_tables where table_name='LEO_T1';

LOG

---

SQL> select count(*) from leo_t1; 0条记录

COUNT(*)

----------

SQL> select index_name from user_indexes where table_name='LEO_T1'; 表上没有索引

no rows selected

SQL> insert /*+ append */ into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 直接加载

20000 rows created.

Statistics

----------------------------------------------------------

1443 recursive calls

649 db block gets

1702 consistent gets

1519 physical reads

44900 redo size 直接加载产生的redo日志非常少

658 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

20000 rows processed

SQL> rollback;

Rollback complete.

LS@LEO> insert into leo_t1 select * from leo_test_sqlload where rownum <= 20000; 传统加载

20000 rows created.

Statistics

----------------------------------------------------------

4 recursive calls

2207 db block gets

1534 consistent gets

441 physical reads

1634064 redo size 传统加载产生的redo日志非常非常的多

673 bytes sent via SQL*Net to client

603 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

20000 rows processed

小结：直接加载与nologging配合下可以大幅度的减少redo日志量，因为插入的数据不产生redo日志，

所以在插入后要做备份操作，一旦数据损坏，就要使用备份来恢复，不能使用redo来恢复。注意要关闭force_logging选项

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-743199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-743199/

cnje5551

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫