求知 文章 文库 Lib 视频 iProcess 课程 角色 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
分享到
普通表与临时表DML操作会产生REDO/UNDO对比与分析
 
作者 还不算晕,火龙果软件    发布于 2013-10-23
 

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大小变化

YS@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。

临时表会产生UNDO,是因为临时表操作和普通表是一样的,也要支持rollback和commit,这样自然要记录到undo中。

相关文章

我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
 
相关文档

数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
相关课程

高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优
 
分享到
 
 


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号