꿈꾸는 개발자, DBA 커뮤니티 구루비

#리두 조사하기

데이타베이스에 LGWR 은 하나만 존재하며, 과도한 리두의 생성은 전체 시스템에 영향을 준다
h3. (on) 리두 측정하기

Demo#1 (conventional path INSERT)
{code:sql	borderStyle=solid} SQL> set autotrace traceonly statistics; SQL> alter table t nologging; SQL> truncate table t;

Table truncated.

SQL> insert into t select * from big_table;

1000000 rows created.

Statistics

--
897 recursive calls
133607 db block gets
45584 consistent gets
7890 physical reads
122053428 redo size
1146 bytes sent via SQL*Net to client
1281 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000000 rows processed

|
||Demo#2 (direct path INSERT)||
|{code:sql|borderStyle=solid}
SQL> set autotrace traceonly statistics;
SQL> alter table t nologging;
SQL> truncate table t;

Table truncated.

SQL> insert /*+ append */ into t select * from big_table;

1000000 rows created.


Statistics
----------------------------------------------------------
        944  recursive calls
      17637  db block gets
      15690  consistent gets
      14805  physical reads
     200232  redo size
       1131  bytes sent via SQL*Net to client
       1295  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

|

(on) 리두 로그를 생성하지 못하도록 막을 수 없다

Demo#3 (create table)
{code:sql	borderStyle=solid} SQL> variable redo number SQL> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

SQL> create table t as select * from all_objects;

Table created.

SQL> exec dbms_output.put_line( ( get_stat_val ( 'redo size' ) - :redo ) || ' bytes of redo generated... ' );
7352828 bytes of redo generated...

PL/SQL procedure successfully completed.

|
||Demo#4 (create table / nologging)||
|{code:sql|borderStyle=solid}
SQL> variable redo number
SQL> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

SQL> create table t nologging as select * from all_objects;

Table created.

SQL> exec dbms_output.put_line( ( get_stat_val ( 'redo size' ) - :redo ) || ' bytes of redo generated... ' );
95988 bytes of redo generated...

PL/SQL procedure successfully completed.

|

NOLOGGING 작업

어느 정도 리두는 생성 됨 (데이터 딕셔너리)
이후 작업은 정상 적으로 리두 생성 됨 (direct-path LOAD/INSERT 예외)
작업 소요 시간을 극적으로 개선
ARCHIVE에 의한 복구 불가 하므로 별도 백업 필요

Demo#5 (index rebuild)
{code:sql	borderStyle=solid} – INDEX 에 NOLOGGING 설정 SQL> create index t_idx on t ( object_name );

Index created.

SQL> variable redo number
SQL> exec :redo := get_stat_val ( 'redo size' );

PL/SQL procedure successfully completed.

SQL> alter index t_idx rebuild;

Index altered.

SQL> exec dbms_output.put_line( ( get_stat_val ( 'redo size' ) - :redo ) || ' bytes of redo generated... ' );
2610036 bytes of redo generated...

PL/SQL procedure successfully completed.

|
||Demo#6 (index rebuild nologging)||
|{code:sql|borderStyle=solid}
SQL> alter index t_idx nologging;

Index altered.

SQL> variable redo number
SQL> exec :redo := get_stat_val ( 'redo size' );

PL/SQL procedure successfully completed.

SQL> alter index t_idx rebuild nologging;

Index altered.

SQL> exec dbms_output.put_line( ( get_stat_val ( 'redo size' ) - :redo ) || ' bytes of redo generated... ' );
55052 bytes of redo generated...

PL/SQL procedure successfully completed.

-- NOLOGGING 상태로 REBUILD 된 T_IDX 는 장애로 부터 보호받지 못함

|

NOLOGGING 가능 작업

CREATE/REBUILD INDEX
DIRECT-PATH LOAD/INSERT
LOB UPDATE
CTAS
ALTER TABLE (MOVE / SPLIT)

(on) 왜 새로운 로그를 할당할 수 없는가?

{code:sql

borderStyle=solid}
– Database Hang
Thread 1 cannot allocate new log, sequence 1466
Checkpointnot complete
Current log# 3 seq# 1465 mem# 0: /data/redo/redo03.log
{code}

원인

DBWR 리두 로그에 의해 보호되는 데이터에 대한 체크포인트를 완료 못함
ARCH 리두 로그 파일을 아카이브 저장소에 복제를 완료 못함

해결방법

DBWR : 속도를 좀더 빠르게 (DBWR I/O 슬레이브 OR DBWR 프로세스 추가)
DBWR/ARCH : 비동기(ASYNC) I/O 활성화, I/O 분산
리두 로그 파일 추가 : DBWR(체크포인트수행), ARCH(리두로그복사)에 여유 시간 확보
리두 로그 파일 크기 확장 : 리두 로그 파일 별 사용 기간 연장 → 로그 스위치 감소 → 체크포인트 감소
잦은 체크포인트 유도 : 버퍼캐시축소, FAST_START_MTTR_TARGET, LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT 활용 → ???

(on) 블록 클린아웃

변경을 완료한 데이터베이스에서 '락킹' 관련 정보를 제거
* 지연된 버퍼 클린아웃 : 다음 번 액세스에 클린아웃
* 커밋 클린아웃 : 커밋 시점에 클린아웃

Demo#7 (블록클린아웃)
{code:sql	borderStyle=solid} – SGA 자동 메모리 관리 비활성화, DB_CACHE_SIZE = 16MB (2048블록) -- 테이블은 1블록 당 1ROW 구성, 10000개 블록 변경 발생 SQL> create table t 2 ( id number primary key, 3 x char(2000), 4 y char(2000), 5 z char(2000) 6 ) 7 /

Table created.

SQL> exec dbms_stats.set_table_stats( user, 'T', numrows=>10000, numblks=>10000 );

PL/SQL procedure successfully completed.

SQL> declare
2 l_rec t%rowtype;
3 begin
4 for i in 1 .. 10000
5 loop
6 select * into l_rec from t where id = i;
7 end loop;
8 end;
9 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

SQL> insert into t
2 select rownum, 'x', 'y', 'z'
3 from all_objects
4 where rownum <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

-- 테스트1 (지연된블록클린아웃 있음)
SQL> variable redo number
SQL> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

SQL> declare
2 l_rec t%rowtype;
3 begin
4 for i in 1 .. 10000
5 loop
6 select * into l_rec from t where id = i;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( (get_stat_val('redo size') - :redo) || ' bytes of redo generated...' );
531292 bytes of redo generated...

PL/SQL procedure successfully completed.

-- 테스트2 (지연된블록클린아웃 없음)
SQL> exec :redo := get_stat_val( 'redo size' );

PL/SQL procedure successfully completed.

SQL> declare
2 l_rec t%rowtype;
3 begin
4 for i in 1 .. 10000
5 loop
6 select * into l_rec from t where id = i;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( (get_stat_val('redo size') - :redo) || ' bytes of redo generated...' );
0 bytes of redo generated...

PL/SQL procedure successfully completed.

|

h6. 지연된블록클린아웃 해결책
 * 대량 블록 변경 후 통계수집 등을 통해서 블록을 직접 방문(다른 사람들이 지연된 블록클린아웃을 겪지 않도록)

h3. (on) 로그 경합
bq. 'log file sync', 'log file parallel write' 이벤트를 통해 확인 (리두 로그가 느리게 기록됨)
h6.  원인
 * AP의 잦은 COMMIT
 * 느린 디바이스에 리두 저장 (RAID-5 등)
 * 전용 디바이스가 아닌 곳에 리두 저장 (경합)
 * 일반적인 파일 시스템에 리두 저장 (중복된 버퍼링)

h6.  결론 (LGWR~ARCH 간 경합이 없도록 아래와 같이 설계 / p.429 그림 9-6)
 * 리두 로그 그룹 1 : (빠르고작은) 디스크1, 3
 * 리두 로그 그룹 2 : (빠르고작은) 디스크2, 4 [복제]
 * 아카이브 저장소 1 : (느리고큰) 디스크5
 * 아카이브 저장소 2 : (느리고큰) 디스크6 [복제]

h3. (on) 임시 테이블과 리두/언두
 * 리두 : 자신의 데이터 블록에 대해서는 리두를 만들지 않고 (복구 불가)
 * 언두 : 롤백을 고려하여 생성 한다. (언두의 리두는 생성)
||Demo#8 (임시테이블)||
|{code:sql|borderStyle=solid}
SQL> create table perm
  2  ( x char(2000),
  3    y char(2000),
  4    z char(2000) )
  5  /

Table created.

SQL> create global temporary table temp
( x char(2000),  2
  3    y char(2000),
  4    z char(2000) )
  5  on commit preserve rows
  6  /

Table created.

SQL> create or replace procedure do_sql ( p_sql in varchar2 )
  2  as
  3     l_start_redo number;
  4     l_redo       number;
  5  begin
  6     l_start_redo := get_stat_val( 'redo size' );
  7     execute immediate p_sql;
  8     commit;
  9     l_redo := get_stat_val( 'redo size' ) - l_start_redo;
 10     dbms_output.put_line
 11     ( to_char(l_redo, '99,999,999') || ' bytes of redo generated for "' ||
 12       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
 13  end;
 14  /

Procedure created.

SQL> set serveroutput on format wrapped

SQL> begin
  2     do_sql( 'insert into perm select 1, 1, 1 from all_objects where rownum <= 500' );
  3     do_sql( 'insert into temp select 1, 1, 1 from all_objects where rownum <= 500' );
  4     dbms_output.new_line;
  5
  6     do_sql( 'update perm set x = 2' );
  7     do_sql( 'update temp set x = 2' );
  8     dbms_output.new_line;
  9
 10     do_sql( 'delete from perm' );
 11     do_sql( 'delete from temp' );
 12  end;
 13  /
  3,286,308 bytes of redo generated for "insert into perm select 1"...
     68,448 bytes of redo generated for "insert into temp select 1"...
     ==> GTT INSERT는 REDO/UNDO 거의 안만든다 (UPDATE NULL 2 2000 BYTE 와 같다)

  2,644,224 bytes of redo generated for "update perm set x = 2"...
  1,800,128 bytes of redo generated for "update temp set x = 2"...
     ==> GTT UPDATE는 REDO가 반절

  3,220,148 bytes of redo generated for "delete from perm"...
  3,225,996 bytes of redo generated for "delete from temp"...
     ==> GTT DELETE는 REDO가 같다 (UPDATE 2000 BYTE 2 NULL 과 같다)

PL/SQL procedure successfully completed.

-- 임시 테이블은 INSERT / SELECT 위주로 사용하면 효율적

|

리두 조사하기 (by openlsw) [2012.10.20]

#리두 조사하기

(on) 리두 로그를 생성하지 못하도록 막을 수 없다

NOLOGGING 작업

NOLOGGING 가능 작업

(on) 왜 새로운 로그를 할당할 수 없는가?

원인

해결방법

(on) 블록 클린아웃