nologging 模式
     當要批次載入資料時,資料庫還記錄redo,很影響載入速度.這是沒必要的,但預設情況下是記錄redo的.
     資料庫為保證批次載入資料時避免產生redo,以提高載入速度,提供了強制和不強制記錄日誌功能

建立實驗表
SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1 as select * from scott.emp;

Table created.

SQL> create table t2 as select * from scott.emp where 0=9;

Table created.

SQL> insert into t1 select * from t1;

14 rows created.

SQL>
......
SQL> /

1792 rows created.

SQL> commit;

Commit complete.

SQL>

SQL> select FORCE_LOGGING from v$database;

FOR
---
NO

SQL>
計算當前會話redo的大小指令碼
SQL> ! cat mystat.sql
set echo off
set verify off
column value new_val V
define S='redo size'

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/

SQL>
SQL> ! cat mystat2.sql
set echo off
set verify off
column value new_val V
define S='redo size'

select a.name, b.value V, to_char(b.value-&V,'999999999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

SQL>
SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1054292

SQL> insert into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1242168     187876

SQL>

SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1242168

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> commit;

Commit complete.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1247896         5728

SQL>

SQL> alter table t2 nologging;

Table altered.

SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1249948

SQL> insert /*+ append */ into t2 select * from t1;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1255464         5516

SQL> commit;

Commit complete.

SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1256004

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1256488          484

SQL>

雖然此時載入資料速度提高了但卻對閃庫有了負面影響

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1256488          484

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     927990

SQL>
SQL> commit;

Commit complete.

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL>

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size              1218992 bytes
Variable Size             79693392 bytes
Database Buffers     201326592 bytes
Redo Buffers              2973696 bytes

SQL> alter database mount;

Database altered.

SQL> flashback database to scn 927990;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 60721)
ORA-01110: data file 1: '/db254/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SQL>

將資料庫改為強制日誌模式
alter database force logging;
alter database no force nologging;

強制模式
     忽略表nologging引數
     忽略語句nologging引數
     忽略hint /*+ append */

(f)--閃回恢復區-- 並行載入對閃庫的影響

相關文章