临时表和普通表日志量产生的对比

来源：互联网发布：java 剩余天数编辑：程序博客网时间：2024/05/16 01:34

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.