02

12

临时表空间历险[转]

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表空间,并重启数据库,确保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表空间的历险结束了,虽然最终没有达到我所想的手动释放表空间的效果,不过还是有收获的,发贴以供大家讨论,谢谢!


文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: Oracle
相关日志:
评论: 0 | 引用: 0 | 查看次数: -
发表评论
昵 称:
密 码: 游客发言不需要密码.
内 容:
验证码: 验证码
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.