nologging测试(传统路径和直接路径插入以及logging和nologging状态下测量redo)

最新推荐文章于 2024-06-03 17:04:01 发布

blockheading

最新推荐文章于 2024-06-03 17:04:01 发布

阅读量416

点赞数

分类专栏： Oracle Basic Principle 文章标签： logging nologging archive insert redo

本文链接：https://blog.csdn.net/u013169075/article/details/52887692

版权

Oracle Basic Principle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

--归档与不归档下插入redo比较
EODA@PROD1> create table t as select * from big_table where 1=0;

Table created.

EODA@PROD1> insert into t select * from big_table;

10000000 rows created.


Statistics
----------------------------------------------------------
       6653  recursive calls
    1313492  db block gets
     428133  consistent gets
     150761  physical reads
 1235348064  redo size  --大约1253MBredo
	914  bytes sent via SQL*Net to client
       1005  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	107  sorts (memory)
	  0  sorts (disk)
   10000000  rows processed

EODA@PROD1> truncate table t;

Table truncated.

EODA@PROD1> set timing on               
EODA@PROD1> insert /*+ APPEND */ into t select * from big_table;

10000000 rows created.

Elapsed: 00:06:20.60

Statistics
----------------------------------------------------------
       2019  recursive calls
     157340  db block gets
     151888  consistent gets
     150559  physical reads
 1257956296  redo size
	906  bytes sent via SQL*Net to client
       1019  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
   10000000  rows processed

EODA@PROD1> conn / as sysdba 
Connected.
SYS@PROD1> startup force mount
ORACLE instance started.

Total System Global Area  732352512 bytes
Fixed Size		    1347456 bytes
Variable Size		  322961536 bytes
Database Buffers	  406847488 bytes
Redo Buffers		    1196032 bytes
Database mounted.

Elapsed: 00:00:00.21
SYS@PROD1> alter database noarchivelog;  --更改为非归档

Database altered.

Elapsed: 00:00:00.21
SYS@PROD1> alter database open;

Database altered.

Elapsed: 00:00:22.29
SYS@PROD1> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       /u01/app/oracle/fast_recovery_area/PROD1/archivelog/
Oldest online log sequence     200
Current log sequence	       202
SYS@PROD1> conn eoda/foo
Connected.
EODA@PROD1> truncate table t;

Table truncated.

Elapsed: 00:00:01.41
EODA@PROD1> insert /*+ APPEND */ into t select * from big_table;

10000000 rows created.

Elapsed: 00:00:44.32

Statistics
----------------------------------------------------------
       2013  recursive calls
     157337  db block gets
     151886  consistent gets
     136319  physical reads
     695664  redo size   --大约69MBredo
	901  bytes sent via SQL*Net to client
       1019  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
   10000000  rows processed

--创建表logging和nologging状态下比较redo
SYS@PROD1> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /u01/app/oracle/fast_recovery_area/PROD1/archivelog/
Oldest online log sequence     201
Next log sequence to archive   203
Current log sequence	       203
SYS@PROD1> conn eoda/foo
Connected.

EODA@PROD1> drop table t purge;

Table dropped.

EODA@PROD1> variable redo number;
EODA@PROD1> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

EODA@PROD1> set timing on
EODA@PROD1> create table t as select * from all_objects;

Table created.

Elapsed: 00:00:04.22
EODA@PROD1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated...' );
8652596 bytes of redo generated...  --大约8MB的redo信息

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
EODA@PROD1> drop table t purge;

Table dropped.

Elapsed: 00:00:00.09
EODA@PROD1> variable redo number
EODA@PROD1> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
EODA@PROD1> create table t NOLOGGING as select * from all_objects;

Table created.

Elapsed: 00:00:01.52  --创建速度明显较快
EODA@PROD1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated...' );
100664 bytes of redo generated...  --大约KB的redo信息

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

--在索引上设置nologging

EODA@PROD1> select log_mode from v$Database;

LOG_MODE
------------
ARCHIVELOG

Elapsed: 00:00:00.02
EODA@PROD1> create index t_idx on t(object_name);  --创建logging索引

Index created.

Elapsed: 00:00:01.43
EODA@PROD1> variable redo number
EODA@PROD1> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
EODA@PROD1> alter index t_idx rebuild;

Index altered.

Elapsed: 00:00:00.75
EODA@PROD1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated...');
3085436 bytes of redo generated...  --大约3MBredo

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
EODA@PROD1> alter index t_idx nologging;  --设置为nologging索引

Index altered.

Elapsed: 00:00:00.01
EODA@PROD1> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
EODA@PROD1> alter index t_idx rebuild;

Index altered.

Elapsed: 00:00:00.34
EODA@PROD1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated...');
55512 bytes of redo generated...  --大约55KBredo

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

nologging小结：
可以用来创建和重建索引。
CTAS建表。
alter table move/split
如果使用得到可以加快许多操作的速度，因为它能显著的减少重做日志的产生。
不过一旦出现介质失败可能会丢失数据。

附：get_stat_val函数代码

create or replace function get_stat_val( p_name in varchar2 ) return number
  as
       l_val number;
  begin
      select b.value
        into l_val
        from v$statname a, v$mystat b
       where a.statistic# = b.statistic#
         and a.name = p_name;

      return l_val;
  end;
/