SQLPLUS >set autot traceonly statistic
SQLPLUS >truncate table t ;
Table truncated.
SQLPLUS >insert into t
2 select * from big_table ;
565568 rows created.
Statistics
----------------------------------------------------------
654 recursive calls
35161 db block gets
14419 consistent gets
3 physical reads
59319436 redo size <-------------- 59MB 정도의 리두가 생성됨.
837 bytes sent via SQL*Net to client
790 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
565568 rows processed
SQLPLUS >truncate table t ;
Table truncated.
SQLPLUS >alter table t nologging ;
Table altered.
SQLPLUS >insert /*+ APPEND */ into t
2 select * from big_table ;
565568 rows created.
Statistics
----------------------------------------------------------
777 recursive calls
5788 db block gets
6116 consistent gets
2 physical reads
153404 redo size <-------------- 리두가 153KB 생성되었다.
825 bytes sent via SQL*Net to client
804 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
565568 rows processed
-- ====================================
-- 추가테스트
-- ====================================
SQLPLUS >truncate table t ;
Table truncated.
-- t 테이블은 logging 모드 테이블이다.
-- 데이터베이스는 ARCHIVELOG 모드이다.
SQLPLUS >insert /*+ APPEND */ into t
2 select * from big_table ;
565568 rows created.
Statistics
----------------------------------------------------------
736 recursive calls
5790 db block gets
6068 consistent gets
2 physical reads
61532396 redo size <----------------
823 bytes sent via SQL*Net to client
804 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
565568 rows processed
SQLPLUS >truncate table t ;
Table truncated.
SQLPLUS >insert /*+ APPEND NOLOGGING */ into t
2 select * from big_table ;
565568 rows created.
Statistics
----------------------------------------------------------
736 recursive calls
5791 db block gets
6068 consistent gets
2 physical reads
61532396 redo size <--------
824 bytes sent via SQL*Net to client
814 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
565568 rows processed
SQLPLUS >select log_mode from v$database ;
LOG_MODE
------------
ARCHIVELOG
SQLPLUS >drop table t purge ;
Table dropped.
SQLPLUS >variable redo number
SQLPLUS >exec :redo := get_stat_val('redo size');
PL/SQL procedure successfully completed.
SQLPLUS >create table t
2 as select * from all_objects ;
Table created.
SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
1996548 bytes of redo generated ...
PL/SQL procedure successfully completed.
-- 1.9 MB 정도의 리두 생성됨.
SQLPLUS >drop table t purge ;
Table dropped.
SQLPLUS >variable redo number
SQLPLUS >exec :redo := get_stat_val ('redo size');
PL/SQL procedure successfully completed.
SQLPLUS >create table t
2 NOLOGGING
3 as
4 select * from all_objects;
Table created.
SQLPLUS >set serveroutput on
SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo)|| ' bytes of redo generated..');
78644 bytes of redo generated ..
PL/SQL procedure successfully completed.
-- 78KB 정도의 리두 생성됨.
-- 1. LOGGING 으로 INDEX Rebuild
SQLPLUS >create index t_idx on t (object_name) ;
Index created.
SQLPLUS >variable redo number
SQLPLUS >exec :redo := get_stat_val('redo size');
PL/SQL procedure successfully completed.
SQLPLUS >alter index t_idx rebuild ;
Index altered.
SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
9733976 bytes of redo generated ...
PL/SQL procedure successfully completed.
-- 9MB 리두 생성됨
-- 2. NOLOGGING 으로 변경 후 INDEX Rebuidl
SQLPLUS >alter index t_idx nologging ;
Index altered.
SQLPLUS >exec :redo := get_stat_val('redo size');
PL/SQL procedure successfully completed.
SQLPLUS >alter index t_idx rebuild ;
Index altered.
SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
77800 bytes of redo generated ...
PL/SQL procedure successfully completed.
-- 77KB 리두 생성됨.
-- 이 상태에서 백업 없이 장애발생 시, t_idx 인덱스는 데이터를 잃게된다.
SQLPLUS >create table perm
2 ( x char(2000),
3 y char(2000),
4 z char(2000) )
5 /
Table created.
SQLPLUS >create global temporary table temp
2 ( x char(2000),
3 y char(2000),
4 z char(2000) )
5 on commit preserve rows
6 /
Table created.
create or replace procedure do_sql (p_sql in varchar2 )
as
l_start_redo number;
l_redo number;
begin
l_start_redo := get_stat_val('redo size');
execute immediate p_sql;
commit ;
l_redo := get_stat_val('redo size') - l_start_redo ;
dbms_output.put_line
( to_char(l_redo,'99,999,999') || ' bytes of redo generated for "' ||
substr(replace(p_sql, chr(10), ' ' ), 1, 25) || '" ... ') ;
end;
/
Procedure created.
SQLPLUS > set serveroutput on format wrapped
SQLPLUS > begin
do_sql ( 'insert into perm
select 1, 1, 1
from all_objects
where rownum <= 500' );
do_sql ( 'insert into temp
select 1, 1, 1
from all_objects
where rownum <= 500' );
dbms_output.new_line ;
do_sql('update perm set x = 2');
do_sql('update temp set x = 2');
dbms_output.new_line ;
do_sql('delete from perm');
do_sql('delete from temp');
dbms_output.new_line ;
end;
/
-- 결과
3,120,244 bytes of redo generated for "insert into perm " ...
41,332 bytes of redo generated for "insert into temp " ...
2,126,084 bytes of redo generated for "update perm set x = 2" ...
1,067,252 bytes of redo generated for "update temp set x = 2" ...
3,200,608 bytes of redo generated for "delete from perm" ...
3,156,716 bytes of redo generated for "delete from temp" ...
SQLPLUS >create index perm_idx on perm(x) ;
Index created.
SQLPLUS >create index temp_idx on temp(x);
Index created.
..
8,613,908 bytes of redo generated for "insert into perm " ...
3,029,920 bytes of redo generated for "insert into temp " ...
7,632,424 bytes of redo generated for "update perm set x = 2" ...
5,210,624 bytes of redo generated for "update temp set x = 2" ...
4,310,508 bytes of redo generated for "delete from perm" ...
4,252,840 bytes of redo generated for "delete from temp" ...
- 강좌 URL : http://www.gurubee.net/lecture/4028
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.