回滚表空间和临时表空间的问题

Yong Huang · 发表于 2007-12-11 02:59

原帖由 ziliu0305 于 2007-12-10 12:51 发表
是前者,我们用的GTT,但是我的确发现只要作这个查询,UNDO就会剧增

Your application only does select, no DML, no select for update?

When the app runs, is there another session doing DML? If so, this query only session needs to create CR blocks for consistent read. Maybe that's the undo you observed?

Using a GTT creates a temporary segment. That may explain ORA-1652. So let's focus on ORA-30036 only.

Yong Huang

netbanker · 发表于 2007-12-11 03:29

when u have UNDO increase, check which session/sql is using UNDO. then u will find out.

ziliu0305 · 发表于 2007-12-11 11:36

我这个查询的意思可能被你误解，处理是这样的：从table中查询记录，放到temp table中，然后对temp table处理，（如统计、再将统计结果插入临时表，然后再做一些统计操作....），最后select temp table返回cursor，这个过程是这样的，其间对临时表的使用只有这一个，其它的不使用。

ziliu0305 · 发表于 2007-12-11 11:38

原帖由 netbanker 于 2007-12-11 03:29 发表
when u have UNDO increase, check which session/sql is using UNDO. then u will find out.

这个通过什么命令/窗口来查看呢？

foxmile · 发表于 2007-12-13 19:59

临时表空间的历险

1、环境：
OS:WINDOWS XP
数据库：Oracle 9.2.0.1.0

2、起因
今天在看《oracle性能优化技术内幕》，中间有一个关于数据库缓冲区高速缓存的脚本：

Select o.owner,o.object_type,o.object_name,
Count(b.OBJD) From v$bh b,dba_objects o
Group By o.owner ,o.object_type,o.object_name
Having Count(b.OBJD)>(Select
to_number(Value*0.5) From v$parameter
Where Name = 'db_block_buffers')

用sys用户登陆数据库执行该脚本，然后一直处于正在执行的状态，然后报错，
“无法通过128（在表空间temp中）扩展 temp 段”，确定之后，检查临时表空间，是因为临时表空间满了，而且没有设置自动扩展，设置自动扩展之后，开始重新查询，我的temp表空间参数：大小200m，自动扩展，每次扩展100m。在执行该脚本的同时，用oem观察表空间的大小，一直到表空间增长至4g左右的时候，查询依然没有结果。此时表空间已经很大了，因此在考虑临时表空间到底是如何增长的呢？在什么情况下释放空间？是否可以收缩？

3、为此做如下测试：

1) 首先重启数据库，确保temp表空间没有被占用
C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 7 16:14:09 2007

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

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。
SQL>

2) 检查数据库当前大小和已使用情况
当前大小200M，未使用，

3) 执行下面的脚本，持续50秒
Select o.owner,o.object_type,o.object_name,
Count(b.OBJD) From v$bh b,dba_objects o
Group By o.owner ,o.object_type,o.object_name
Having Count(b.OBJD)>(Select
to_number(Value*0.5) From v$parameter
Where Name = 'db_block_buffers')

50秒之后该脚本依然没有执行出结果，中止该脚本的执行，当前数据库临时表空间大小900M，占用894M。

4、临时表空间的作用

Oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要对查询的结果进行排序
1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。

关于临时表空间的释放，在网上查了资料，确认重启数据库可释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。在网上看到有个帖子提供了几种释放的方法，但是有的不能应用于当前的版本。此外没有发现好的释放临时表空间的方法。

5、临时表空间的释放测试

使用语句进行表空间大小的修改：
SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2    200M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
*
ERROR 位于第 1 行:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

关闭并重启数据库，查看临时表空间的使用情况，当前大小900M，已使用0M

查资料显示oracle系统smon进程会自动清除的，个人并不明确smon什么时候清除临时段。

做如下测试：

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2    1500M;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2    900M;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
  2    200M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP.ORA' RESIZE
*
ERROR 位于第 1 行:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

证明temp表空间被限制在了900M，新建表空间：

SQL> CREATE
  2    TEMPORARY TABLESPACE "TEMP2" TEMPFILE
  3    'D:\ORACLE\ORADATA\ZERO\TEMP2.ora' SIZE 200M EXTENT
  4    MANAGEMENT LOCAL UNIFORM SIZE 1M;

表空间已创建。

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

数据库已更改。

然后执行sql语句

SQL> Select * From test.sal_salary ss Order By ss.jsdjgz;

该语句结果较多，就不列出了，查看新临时表空间的使用情况：

大小200M，已用67M，执行下面的语句：

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE
  2    100M;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。
SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

进行如下操作：

SQL> shtudown immediate;
SP2-0734: 未知的命令开头 "shtudown i..." - 忽略了剩余的行。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 200m;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 70m;

数据库已更改。

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m;
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ZERO\TEMP2.ORA' RESIZE 67m
*
ERROR 位于第 1 行:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

SQL>

切换回原有的temp表空间，并重启数据库：

SQL> ALTER DATABASE DEFAULT
  2    TEMPORARY TABLESPACE "TEMP";

数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                453492 bytes
Variable Size          109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
数据库装载完毕。
数据库已经打开。
SQL>
SQL> drop  TABLESPACE temp2 including contents and datafiles;

表空间已丢弃。

目前从表象看temp表空间的已使用为0，但是执行如下脚本：

SQL> Select * From test.sal_salary ss Order By ss.jsdjgz;
执行完之后，temp表空间的已用变为894M。

结果证明在重启数据库之后其实oracle并没有释放temp表空间被占用的部分，虽然从表象看是已经释放了，但是一旦再次有问题排序出现，马上temp表空间的大小就增大至上次释放之前的大小。不知道这个到底是Oracle该版本的bug还是Oracle就是这么处理temp表空间的。

6、重建表空间：
C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 7 18:23:03 2007

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

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE
  2    TEMPORARY TABLESPACE "TEMP2" TEMPFILE
  3    'D:\ORACLE\ORADATA\ZERO\TEMP2.ora' SIZE 200M EXTENT
  4    MANAGEMENT LOCAL UNIFORM SIZE 1M;

表空间已创建。

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

数据库已更改。
SQL> drop tablespace temp including contents and datafiles;

表空间已丢弃。

SQL> CREATE
  2    TEMPORARY TABLESPACE "TEMP" TEMPFILE
  3    'D:\ORACLE\ORADATA\ZERO\TEMP1.ora' SIZE 200M REUSE AUTOEXTEND
  4    ON NEXT  100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
  5    UNIFORM SIZE 1024K;

表空间已创建。

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

数据库已更改。

SQL> drop tablespace temp2 including contents and datafiles;

表空间已丢弃。

SQL>
至此关于temp表空间的历险结束了，虽然最终没有达到我所想的手动释放表空间的效果，不过还是有收获的，发贴以供大家讨论，谢谢！

foxmile · 发表于 2007-12-13 20:02

undo表空间肯定是因为你发生了dml操作，否则不会增加。如果确实dml操作不可避免，那就没办法了，只好加大undo tablespace，然后定期删除重建。

Yong Huang · 发表于 2007-12-14 02:23

原帖由 foxmile 于 2007-12-13 05:59 发表
临时表空间的历险

Select o.owner,o.object_type,o.object_name,
Count(b.OBJD) From v$bh b,dba_objects o
Group By o.owner ,o.object_type,o.object_name
Having Count(b.OBJD)>(Select
to_number(Value*0.5) From v$parameter
Where Name = 'db_block_buffers')

...

结果证明在重启数据库之后其实oracle并没有释放temp表空间被占用的部分，虽然从表象看是已经释放了，但是一旦再次有问题排序出现，马上temp表空间的大小就增大至上次释放之前的大小。不知道这个到底是Oracle该版本的bug还是Oracle就是这么处理temp表空间的。

Did you intentionally drop the where clause in that query to test cartesian join?

Your understanding of the word 释放 needs to be qualified. When Oracle says temp space is released, it means the space previously used by another operation (sorting, hashnig, CTAS, GTT, etc.) is now ready to be used by a new operation, usually by another session. The file blocks previously used, i.e. written to, will NOT be cleaned; the temporary data in those blocks are still taking the space. This causes Oracle to not be able to shrink the tempfile to the pre-writing mark (I'm not sure what to call it), and for this reason dba_free_space is not suitable for checking free space in a tempfile. Instead, you can use v$temp_extent_pool, v$tempseg_usage (old name v$sort_usage) and v$sort_segment.

Yong Huang

回滚表空间和临时表空间的问题

浏览过的版块