1.临时表和普通表日志量产生的对比
1.1临时表的创建
在第一个会话中创建session1
-创建session级别的临时表
SQL> create global temporary table tb_temp1 (id integer,name varchar2(50)) on commit preserve rows;
Table created.
--创建事务级别的临时表
SQL> create global temporary table tb_temp2 (id integer,name varchar2(50)) on commit delete rows;
Table created.
1.2创建普通表
在第二个会话中创建session2
SQL> create table tb_test(id integer,name varchar2(50)) tablespace tbs_xb;
Table created
1.3在session1中执行如下操作
--查看当前日志
SQL> select a.name,
2 b.value
3 from v$statname a
4 join v$mystat b
5 on a.statistic#=b.statistic#
6 where a.name='redo size';
NAME VALUE
-------------------- ----------
redo size 50104
SQL> begin
2 for i in 1..1000000 loop
3 insert into tb_temp1 values(i,'striverzhaolizheng'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from tb_temp1;
COUNT(*)
----------
1000000
SQL> select a.name,
2 b.value
3 from v$statname a
4 join v$mystat b
5 on a.statistic#=b.statistic#
6 where a.name='redo size';
NAME VALUE
-------------------- ----------
redo size 140639384
SQL> select (140639384-50104)/1024/1024 from dual;
(140639384-50104)/1024/1024
---------------------------
134.076385
产生了大约134MB的日志
1.4在session2中执行如下操作
SQL> select a.name,
2 b.value
3 from v$statname a
4 join v$mystat b ona.statistic#=b.statistic#
5 where a.name='redo size';
NAME VALUE
-------------------- ----------
redo size 15408
SQL> begin
2 for i in 1..1000000 loop
3 insert into tb_test values(i,'striverzhaolizheng'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from tb_test;
COUNT(*)
----------
1000000
SQL> select a.name,
2 b.value
3 from v$statname a
4 join v$mystat b on a.statistic#=b.statistic#
5 where a.name='redo size';
NAME VALUE
-------------------- ----------
redo size 274658276
SQL> select (274658276-15408)/1024/1024 from dual;
(274658276-15408)/1024/1024
---------------------------
261.919849
产生了大约262MB的日志
1.6验证redolog生成日志量是否真实
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
-------------------- ---------- ---------- ---------- ------ ---------- -------------------------
1 1 29 52428800 1 NO CURRENT 585726 22-MAY-13
2 1 27 52428800 1 NO INACTIVE 585566 22-MAY-13
3 1 28 52428800 1 NO INACTIVE 585647 22-MAY-13
SQL> selecta.name,
2 b.value
3 from v$statname a
4 join v$mystat b
5 on a.statistic#=b.statistic#
6 where a.name='redo size';
NAME VALUE
------------------------------------------------------------------------------------------------------------------------------------------
redo size 0
SQL> begin
2 fori in 1..1000000 loop
3 insert into tb_temp1 values(i,'striverzhaolizheng'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select a.name,
2 b.value
3 from v$statname a
4 join v$mystat b
5 on a.statistic#=b.statistic#
6 where a.name='redo size';
NAME VALUE
------------------------------------------------------------------------------------------------------------------------------------------
redo size 140565400
SQL> select 140565400/1024/1024 from dual;
140565400/1024/1024
-------------------
134.053612
共生成大约134MB的数据
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
-------------------- ---------- ---------- ---------- ------ ---------- -------------------------
1 1 29 52428800 1 NO INACTIVE 585726 22-MAY-13
2 1 30 52428800 1 NO ACTIVE 588973 22-MAY-13
3 1 31 52428800 1 NO CURRENT 589025 22-MAY-13
Sequence#从29变为了31,每一个日志文件的大小是50MB,大致可以推算出日志应该是134MB.