Oracle中临时表产生过量Redo的说明

最新推荐文章于 2021-04-08 11:37:51 发布
最新推荐文章于 2021-04-08 11:37:51 发布
阅读量3.2k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Oracle技术 文章标签： oracle sql statistics insert table disk
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/eygle/article/details/33499
博客围绕Oracle9i中的临时表展开，提出是否使用过临时表以及其是否带来性能提高的疑问，聚焦于信息技术领域数据库相关内容。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

最近,在Oracle9i中你用过临时表吗?

它是否给你带来了性能提高?你注意过么?

好了言归正传.

我们知道临时表在DML操作中可以减少redo的生成,从而在保存中间结果集时可以带来较大的性能提高.

可是,如果你注意到了,在Oracle9i里,临时表可能比常规表还要产生更多的redo:

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Sat Jul 3 16:37:01 2004

Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production ----请注意版本
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> create table ccc1? (c1 number);

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc1 select rownum from dba_objects;
10468 rows created.


Statistics
----------------------------------------------------------
???????? 73? recursive calls
??????? 175? db block gets
????? 25623? consistent gets
???????? 36? physical reads
???? 157336? redo size --------------------常规表产生的redo,大约154K
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed


SQL> create global temporary table ccc2 (c1 number) ;

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc2 select rownum from dba_objects;

10468 rows created.


Statistics
----------------------------------------------------------
??????? 135? recursive calls
????? 10897? db block gets
????? 25653? consistent gets
??????? 115? physical reads
??? 1476012? redo size ---------------------这是临时表,产生了大约1.4M的redo
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed

这使得在Oracle9i的这些版本中,使用临时表的意义不大了.

?

今天down了个Oracle9205的patch,测试一下在Oracle9205中的情况:

?

E:/Oracle/ora92/bin>sqlplus eygle/eygle 
SQL*Plus: Release 9.2.0.5.0 - Production on 星期六 7月 3 17:37:22 2004 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
连接到: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production ---注意版本 
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production 
?
SQL> create table ccc1 (c1 number) tablespace eygle; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc1 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
50 recursive calls 
157 db block gets 
3416 consistent gets 
0 physical reads 
92764 redo size --------------------------常规表大约产生90K的redo 
611 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 
0 sorts (disk) 
6188 rows processed 
SQL> drop table ccc1; 
表已丢弃。 
SQL> create global temporary table ccc2 (c1 number) ; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc2 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
9 recursive calls 
87 db block gets 
3402 consistent gets 
0 physical reads 
16844 redo size -------------------------临时表这时只产生了16K的redo 
614 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 0 sorts (disk) 
6188 rows processed 
SQL>

在9205中Oracle修正了这个bug.

如果你从来没有遇到这个bug，那么你是幸运的:)

?

在buglist中，找到了以下说明:

2874489: Excessive REDO generated for INSERT as SELECT into GLOBAL TEMPORARY TABLES.

Fixed: 9205

?

临时表终于回来了!

?

2004年7月3日 星期六 17:53

If you have any question,please mail to eygle@itpub.net .

?

Oracle数据库中调整redo log（重做日志）的大小
zxrhhm的博客
06-14 3216
Oracle数据库中调整redo log重做日志的大小详细步骤及操作记录
ORACLE 临时表的REDO UNDO
朝着梦想 渐行前进
10-25 2032
1.对永久表的insert生成了大量的redo,而对临时表几乎没有生成任何redo。这是有道理的，对临时表的insert只会生成很少的undo数据，而且对于临时表只会为undo数据建立日志。 2.永久表的update生成的redo大约是临时表更新所生成的redo的两倍。同样，这也是合理的。必须保存update的大约一半（即 前映像）。对于临时表来说，不必保存“后映像”（redo）。 
临时表 DML 产生redo 问题说明
David Dai -- Focus on Oracle
05-30 5462
<br /> <br />关于Oracle 临时表，参考我的Blog：<br />            Oracle 临时表<br />            http://blog.youkuaiyun.com/tianlesoftware/archive/2009/10/20/4705283.aspx<br /> <br />对Oracle 临时表的操作，会产生redo 和undo。<br /> <br />先看一个示例：<br /> <br />SYS@anqing1(rac1)> CREATE GLOBAL
Oracle 临时表之临时表的应用问题
12-14
　1、先将大表中满足条件的记录抽出来生成一张临时表 　2、再将这较小的临时表与另一张较小的表进行关联查询 　先不论思路是否值得商榷，这把临时表当成中转站的做法还是很值得肯定。 　临时表本质上是一种...
oracle redo查询,Oracle产生redo日志量大小统计
weixin_42609225的博客
04-02 2043
在Oracle中，对于数据库的修改操作都会记录redo，那么不同的操作会产生多少redo呢？可以通过以下一些方式来查询来统计产生的redo日志量。(1)SQL*Plus中使用AUTOTRACE的使用。当在SQL*Plus中启用autotrace跟踪后，在执行了特定的DML语句时，Oracle会显示该语句的统计信息，其中，redo Size一栏表示的就是该操作产生的redo的数量，其单位为Bytes...
Oracle数据库自带表空间的详细说明
12-16
UNDO表空间中的数据同样会产生redo日志，以确保崩溃恢复。由于撤销信息的存在，Oracle的SELECT语句可以在不加锁的情况下实现一致性读取，提升了并发性能。 5. **USERS表空间** USERS表空间通常用于新创建的用户，...
临时表真的不生成redo日志吗？
zhuxiaoliao的专栏
11-07 1568
澄清大家一个常见的错误：临时表是否产生redo 临时表本身的操作并不产生redo，但是在临时表中处理数据时，是要产生undo的，而undo是要产生redo的，但相比较普通表要少很多。 [oracle@mydb02 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 7 14:21:52
普通表与临时表DML操作会产生REDO/UNDO对比与分析
还不算晕的专栏
09-17 3189
ORACLE临时表介绍： ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。 Oracle临时表分为 会话级临时表 和 事务级临时
Oracle DML产生 REDO SIZE测试
woi123的专栏
08-03 183
--DML redo测试 /* *普通表的redo size 为insert 5M,delete 18m, update 24M *临时表redo size 为insert 276096字节， Delete 为15M ,update 为 8M, *全局临时表与基于事务的临时表 产生的redo size差不多。 * */ /* *另外的备注，若普通表DML每一步，不comm...
oracle让数据库不产生redo,Oracle数据库级drop redolog并不危险
weixin_34429137的博客
04-05 632
有些DBA害怕在生产库上做REDO LOG的删除操作，因为担心把状态为ACTIVE和ACITVE的redolog group给删除造成数据库事故。实际上，如果从数据库级删除状态为CURRENT和ACTIVE的redo log，是删除不掉的，所以从数据库级删除redo log时大可以放心。但是要注意，如果在Unix/Linux平台操作系统层直接删除(rm)，Oracle是无法做检查的，如果在操作系统...
oracle删除唯一索引sql语句_Oracle数据库redo日志暴增，怎么查出是哪些sql语句影响导致？...
weixin_39676972的博客
12-06 499
概述Oracle数据库中如果redo日志暴增会间接导致归档日志数量增加，严重的会导致撑爆归档日志的存储空间，导致无法连接数据库。前面已经介绍了怎么去查询最近几天，每小时归档日志产生数量的脚本，这里就不多做介绍了。下面介绍一下怎么去查找引起redo日志暴增的相关sql语句。1、查看最近2小时"块改变"最多的segment redo大量产生必然是由于大量产生"块改变"。从awr视图中找到"块改变"最多...
oracle建立临时表备份,Oracle中临时表产生过量Redo的说明
weixin_33127399的博客
04-07 423
Oracle中临时表产生过量Redo的说明最近,在Oracle9i中你用过临时表吗?它是否给你带来了性能提高?你注意过么?好了言归正传.我们知道临时表在DML操作中可以减少redo的生成,从而在保存中间结果集时可以带来较大的性能提高.可是,如果你注意到了,在Oracle9i里,临时表可能比常规表还要产生更多的redo:[oracle@jumper oracle]$ sqlplus eygle/ey...
oracle 全局临时表空间,使用全局临时表也会有Redo
weixin_42099815的博客
04-03 391
全局临时表在ORACLE 8i中被引进，当创建一张全局临时表，该表的字典定义即被创建，但是只有在第一次往表中填充数据时，临时段才会被分配，就是说只有用户对全局临时表做第一次插入操作或者CTAS操作时，才会为全局临时表分配存储空间。全局临时表被创建在用户的默认临时表中。不同用户使用相同的全局临时表时，他们使用的是不同的全局临时段，不同用户可见的数据不同，他们只看到自己的表定义和表数据，其他什么也看不...
oracle临时表不归档,oracle系统默认临时表空间以及redo日志文件问题处理
weixin_29300805的博客
04-08 672
问题：本人现在要把oracle的数据同步到mysql，运用的ETL工具，由于数据量很大，而且有子查询要用到临时表空间，导致原来的该临时表空间，空间不足，根据报错直接想到了给该临时表空间添加临时文件。查看了它原有的临时文件的路径，也没有多想直接在这个路径下添加了一个文件，谁知道该路径空间不足了，还没有把新加的临时文件用完，数据库就down了，原因是redo日志文件也在这个挂载点下，我们知道任何操作都...
ORACLE 数据库REDO日志暴涨的处理思路
xiazhangch的专栏
12-08 856
数据库中可能我们经常会遇到因业务逻辑出现死循环或异常DML导致的日志暴涨问题，这时候如何快速排错，如何定位问题就是一件很棘手的事。下面是我日常工作中的处理套路，来供大家参考。 1、先确定日志增长的时间范围 SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00", ...
oracle sql 临时表
最新发布
07-24
<think>我们被要求回答关于Oracle SQL临时表的使用方法。根据引用内容，Oracle中有全局临时表（GLOBAL TEMPORARY TABLE）和私有临时表（PRIVATE TEMPORARY TABLE，12c引入）两种。此外，引用[2]还提到了临时表空间的使用情况查询，引用[3]展示了全局临时表的一个例子和undo使用情况。 在Oracle中，临时表用于存储会话或事务中的临时数据，数据在会话结束或事务结束时自动清除。全局临时表的结构是持久的，但数据是临时的。私有临时表（12c及以上）的表结构和数据都是临时的，并在会话结束时自动删除。 下面分别介绍两种临时表的使用方法： 1. 全局临时表（GLOBAL TEMPORARY TABLE）： - 创建表时使用`GLOBAL TEMPORARY`关键字。 - 可以指定数据是事务级（ON COMMIT DELETE ROWS）还是会话级（ON COMMIT PRESERVE ROWS）的。 - 事务级：每次提交事务后，数据自动清空。 - 会话级：数据在整个会话期间都存在，直到会话结束或手动删除。 示例： ```sql -- 创建事务级的全局临时表 CREATE GLOBAL TEMPORARY TABLE gtt_transaction ( id NUMBER, name VARCHAR2(50) ) ON COMMIT DELETE ROWS; -- 创建会话级的全局临时表 CREATE GLOBAL TEMPORARY TABLE gtt_session ( id NUMBER, name VARCHAR2(50) ) ON COMMIT PRESERVE ROWS; ``` 2. 私有临时表（PRIVATE TEMPORARY TABLE，12c及以上版本）： - 表名必须以`ORA$PTT_`为前缀（可以修改，但通常使用默认前缀）。 - 使用`ON COMMIT`定义数据保留策略，同时表是会话私有的，在会话结束时自动删除（包括结构和数据）。 - 创建语法中使用`PRIVATE TEMPORARY`关键字。 示例： ```sql -- 创建事务级的私有临时表（提交后数据删除，会话结束表自动删除） CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp ( id NUMBER, name VARCHAR2(50) ) ON COMMIT DROP DEFINITION; -- 注意：ON COMMIT DROP DEFINITION 表示提交时删除表（包括结构和数据），但Oracle也支持ON COMMIT PRESERVE DEFINITION（提交时保留表结构，只删除数据） -- 或者使用ON COMMIT PRESERVE DEFINITION（提交后保留表定义，只删除数据，会话结束表自动删除） CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_session ( id NUMBER, name VARCHAR2(50) ) ON COMMIT PRESERVE DEFINITION; ``` 注意：私有临时表在12.2及以上版本中引入，并且表名必须以前缀开头（默认是ORA$PTT_，但可以通过参数修改）。 使用临时表时，需要注意： - 临时表不产生redo日志，但是会产生undo日志（用于回滚），如引用[3]所示。 - 临时表的数据不会备份，也不会被恢复。 - 临时表可以创建索引、约束等，但同样也是临时的。 另外，引用[2]提供了查询临时表空间使用情况的SQL，这对于监控临时表空间的使用很有帮助。 下面给出一个使用全局临时表的完整示例： ```sql -- 创建全局临时表（会话级） CREATE GLOBAL TEMPORARY TABLE my_gtt ( id NUMBER, data VARCHAR2(100) ) ON COMMIT PRESERVE ROWS; -- 插入数据 INSERT INTO my_gtt VALUES (1, 'Session data'); -- 提交后数据仍然存在（因为是会话级） COMMIT; -- 查询数据 SELECT * FROM my_gtt; -- 结束会话后，数据自动清除。 -- 删除全局临时表（如果需要） DROP TABLE my_gtt; ``` 私有临时表的使用示例： ```sql -- 创建私有临时表（事务级：提交时删除表） CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp ( id NUMBER, data VARCHAR2(100) ) ON COMMIT DROP DEFINITION; -- 插入数据 INSERT INTO ora$ptt_my_temp VALUES (1, 'Temporary data'); -- 查询 SELECT * FROM ora$ptt_my_temp; -- 提交后，表自动被删除，无法再查询。 COMMIT; -- 注意：在提交之前，可以像普通表一样使用。 ``` 总结： - 全局临时表：表结构持久，数据临时（根据定义在事务结束或会话结束时清除）。 - 私有临时表：表结构和数据都是临时的，在会话结束或事务提交时（根据定义）自动删除。 根据需求选择合适的临时表类型。 相关问题： 1. 全局临时表和私有临时表的主要区别是什么？ 2. 如何监控Oracle临时表空间的使用情况？ 3. 临时表在什么情况下会产生重做日志和撤销日志？ 4. 在Oracle中，临时表是否支持并行操作？</think>### Oracle SQL 临时表使用方法详解 在Oracle中，临时表主要用于存储会话或事务中的临时数据，分为两种类型：**全局临时表（GLOBAL TEMPORARY TABLE）** 和 **私有临时表（PRIVATE TEMPORARY TABLE）**（12c及以上版本支持）。以下是具体使用方法： --- #### 一、全局临时表（GLOBAL TEMPORARY TABLE） **特点**：表结构持久化存储，数据根据配置在事务或会话结束时自动清除。 1. **创建语法** ```sql CREATE GLOBAL TEMPORARY TABLE 表名 ( 列1 数据类型, 列2 数据类型 ) ON COMMIT [DELETE ROWS | PRESERVE ROWS]; ``` - **`ON COMMIT DELETE ROWS`**：事务级临时表（提交后数据自动清除）[^1][^3] ```sql CREATE GLOBAL TEMPORARY TABLE gtt_transaction ( id INT, name VARCHAR2(50) ) ON COMMIT DELETE ROWS; ``` - **`ON COMMIT PRESERVE ROWS`**：会话级临时表（会话结束前数据保留） ```sql CREATE GLOBAL TEMPORARY TABLE gtt_session ( id INT, value VARCHAR2(10) ) ON COMMIT PRESERVE ROWS; ``` 2. **数据操作** 插入/查询数据与普通表相同，但数据仅对当前会话可见： ```sql INSERT INTO gtt_transaction VALUES (1, '测试'); SELECT * FROM gtt_transaction; -- 仅当前会话可见 ``` 3. **注意事项** - 表结构需提前创建，数据自动清理无需手动干预。 - 不产生重做日志（Redo），但会生成撤销日志（Undo）用于事务回滚[^3]。 --- #### 二、私有临时表（PRIVATE TEMPORARY TABLE, 12c+） **特点**：表结构和数据均为临时，会话结束时自动删除。 1. **创建语法** ```sql CREATE PRIVATE TEMPORARY TABLE 表名 ( 列1 数据类型, 列2 数据类型 ) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION]; ``` - **`ON COMMIT DROP DEFINITION`**：事务级（提交后表结构和数据均删除） ```sql CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp ( id INT, data VARCHAR2(100) ) ON COMMIT DROP DEFINITION; ``` - **`ON COMMIT PRESERVE DEFINITION`**：会话级（会话结束前保留表结构） ```sql CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_session ( id INT, value VARCHAR2(20) ) ON COMMIT PRESERVE DEFINITION; ``` 2. **命名规则** 表名必须以 `ORA$PTT_` 开头（默认前缀，可通过参数修改）。 --- #### 三、临时表空间监控 使用以下SQL监控临时表空间使用情况[^2]： ```sql SELECT f.tablespace_name, ROUND(f.sum_space, 2) "总空间(MB)", ROUND(u.used_space, 2) "已用空间(MB)", ROUND(f.sum_space - u.used_space, 2) "空闲空间(MB)", ROUND(u.used_space / f.sum_space * 100, 2) "使用率(%)" FROM ( SELECT tablespace_name, SUM(bytes)/1024/1024 AS sum_space FROM sys.dba_temp_files GROUP BY tablespace_name ) f JOIN ( SELECT tablespace, SUM(blocks * 8)/1024 AS used_space FROM sys.v_$sort_usage GROUP BY tablespace ) u ON f.tablespace_name = u.tablespace; ``` --- #### 四、典型应用场景 1. 中间结果存储（如复杂查询的分步计算）。 2. 批量数据处理时暂存中间状态。 3. 避免事务锁竞争（临时表数据对其他会话不可见）。 --- **注意事项**： - 全局临时表需显式创建（DDL操作），私有临时表自动销毁。 - 临时表不支持分区。 - 索引可在临时表上创建，但同样为临时对象。 --- ###
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值