赞
踩
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。
临时表会产生UNDO,是因为临时表操作和普通表是一样的,也要支持rollback和commit,这样自然要记录到undo中。
————————————————
版权声明:本文为CSDN博主「还不算晕」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/haibusuanyun/article/details/11747133
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。