您的位置: 首页  >  IT文章  >  普普通通表与临时表DML操作会产生REDO/UNDO对比与分析

普普通通表与临时表DML操作会产生REDO/UNDO对比与分析

分类: IT文章 • 2023-11-08 15:26:49
普通表与临时表DML操作会产生REDO/UNDO对比与分析

ORACLE临时表介绍：

ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

Oracle临时表分为 会话级临时表 和 事务级临时表。

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。
事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。
临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，临时表不需要DML锁。

 当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.可以索引临时表和在临时表基础上建立视图.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.   临时表可以拥有触发器.


全文的REDO/UNOD大小的单位均为BYTES。

一、环境及用户

BYS@bys1>select * from v$version;

BANNER
--------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

BYS@bys1>select force_logging from v$database;

FOR
---
NO
BYS@bys1>select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
BYS                            DBA                            NO  YES NO
BYS@bys1>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE

SYS_TEMP_FBT                   TABLE

创建一个表，600W条数据--源数据为dba_objects，通过多次查询插入。

BYS@bys1>create table test9 as select * from dba_objects;
Table created.

BYS@bys1>insert into test9 select * from test9;   ---多次使用此语句插入数据

BYS@bys1>commit;
Commit complete.
BYS@bys1>select count(*) from test9;   将近700W条。
  COUNT(*)
----------
   6957120


#########################################

二、创建一个普通表，并统计建表及插入数据等操作所产生的REDO及UNDO大小

注：其中每一步后的查看REDO及UNDO大小我都查询了好几遍，节约篇幅未列出；并且测试系统上只有此客户端在数据库环境中进行操作。

建表前后的REDO/UNDO大小变化

BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                         BYTES
---------------------------- ----------
redo size                                                     1824
undo change vector size                                188

BYS@bys1>create table test1 as select * from test9 where 1=0;
Table created.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';

NAME                                                         BYTES
---------------------------- ----------
redo size                                                   238604
undo change vector size                                       6924

插入数据前后的REDO/UNDO大小变化

BYS@bys1>insert into test1 select * from test9;    ---需要时间较长，我这里用了8分半。
6957120 rows created.

Elapsed: 00:08:26.37
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                         813924652
undo change vector size                                            30676180

提交前后的REDO/UNDO大小变化

BYS@bys1>commit;
Commit complete.

Elapsed: 00:00:00.05
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                         813924888
undo change vector size                                            30676180


查询前后的REDO/UNDO大小变化：

第一次查询产生REDO是因为延迟块清除：

BYS@bys1>set autotrace on
BYS@bys1>select count(*) from test1;
  COUNT(*)
----------
   6957120
Elapsed: 00:01:38.73
Execution Plan
----------------------
Plan hash value: 3896847026

--------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------
|   0 | SELECT STATEMENT   |       |     1 | 26827   (1)| 00:05:22 |
|   1 |  SORT AGGREGATE    |       |     1 |           |   |
|   2 |   TABLE ACCESS FULL| TEST1 |  7495K| 26827   (1)| 00:05:22 |
--------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------
         29  recursive calls
          1  db block gets
     198000  consistent gets
      99253  physical reads
       5000  redo size
        425  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
BYS@bys1>set autotrace off
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                         BYTES
---------------------------- ----------
redo size                                                813932848
undo change vector size                                   30678540

正常查询并没有产生REDO和UNDO

BYS@bys1>select count(*) from test1;

  COUNT(*)
----------
   6957120

Elapsed: 00:00:26.95
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                         BYTES
---------------------------- ----------
redo size                                                813932848
undo change vector size                                   30678540



统计情况如下：

create table test1 as select * from dba_objects where 1=0;语句：产生REDO/UNDO分别为： 236780       6736

insert into test1 select * from dba_objects;语句：产生REDO/UNDO分别为： 813686048      30669256
COMMIT语句：产生REDO/UNDO分别为：236和0

三、创建一个ON COMMIT DELETE ROWS  临时表，并统计建表及插入数据等操作所产生的REDO及UNDO大小

PRESERVE ROWS临时表中的测试和ON COMMIT DELETE ROWS结果类似，不再重复贴了。

在上一步做完后退出SQLPLUS再登陆进行操作。

建表前后的REDO/UNDO大小变化

BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                         BYTES
---------------------------- ----------
redo size                                                     1956
undo change vector size                                164
BYS@bys1>create global temporary table temp1 on commit delete rows  as select * from test9 where 1=0;
Table created.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                         BYTES
---------------------------- ----------
redo size                                                    26404
undo change vector size                                       6692

插入数据前后的REDO/UNDO大小变化

BYS@bys1>insert into temp1 select * from test9;
6957120 rows created.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                          43254212
undo change vector size                                            30540820
BYS@bys1>select count(*) from temp1;
  COUNT(*)
----------
   6957120
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                          43254212
undo change vector size                                            30540820

提交前后的REDO/UNDO大小变化

BYS@bys1>commit;
Commit complete.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                          43254448
undo change vector size                                            30540820


查询前后的REDO/UNDO大小变化：--无变化

BYS@bys1>select count(*) from temp1;
  COUNT(*)
----------
         0
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------- ----------
redo size                                                          43254448
undo change vector size                                            30540820

统计情况如下：

create global temporary table temp1语句： 产生REDO和UNDO分别为： 24448       6528

insert into temp1 select * from dba_objects;语句：产生REDO和UNDO分别为：43227808      30534128
COMMIT语句：产生REDO/UNDO分别为： 1346  和0



四：两次操作产生的REDO/UNDO大小对比

普通表统计情况如下：

create table test1 as select * from dba_objects where 1=0;语句：产生REDO/UNDO分别为： 236780       6736

insert into test1 select * from dba_objects;语句：产生REDO/UNDO分别为： 813686048 约775.99M  30669256
COMMIT语句：产生REDO/UNDO分别为：236和0

ON COMMIT DELETE ROWS  临时表统计情况如下：

create global temporary table temp1语句： 产生REDO和UNDO分别为： 24448       6528

insert into temp1 select * from dba_objects;语句：产生REDO和UNDO分别为：43227808 约41M     30534128 
COMMIT语句：产生REDO/UNDO分别为： 1346  和0

总结：临时表的建立和插入数据也产生REDO和UNDO。

建立临时表时因为修改了数据字典所以产生了少量REDO与UNDO；

提交时是在REDO中插入一条提交的标签，所以只产生少量REDO。

那么在插入数据时，临时表还是会产生REDO和UNDO，但是REDO量比普通表插入相同数据量时产生的REDO少很多，UNDO大小相近，这个是怎么解呢？

大致是因为：临时表产生了undo，而undo的变化又产生了REDO LOG， 所以临时表的DML操作也产生了REDO。
但是临时表产生的REDO的大小却比普通表DML操作的小，是因为临时表中不记录表中数据变化所产生的REDO，只记录了UNDO数据变化所产生的REDO。

相关推荐