




全文预览已结束
下载本文档
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
ORACLE 临时表空间使用率过高的原因及解决方案 在数据库的日常学习中,发现公司生产数据库的默认临时表空间 temp 使用情况达到了 30G,使用率达到了 100%; 待调整为 32G 后,使用率还是为 100%,导致磁盘空间使用紧张。根据临时表空间的主要是对临时数据进行排序和缓存临时数据等特性,待重启数据库后, temp 会自动释放。于是想通过重启数据库的方式来缓解这种情况,但是重启数据库之后,发现临时表空间 temp 的使用率还是 100%,一点没变。虽然运行中应用暂时没有报什么错误,但是这在一定程度上存在一定的隐患,有待解决该问题。由于临时表空间主要使用在以下几种情况:1、order by or group by (disc sort 占主要部分);2、索引的创建和重创建;3、distinct 操作;4、union NAMED:ORACLEORADATAORCLTEMP01.DBFSQL select username,temporary_tablespace from dba_users;USERNAME TEMPORARY_TABLESPACE- -MGMT_VIEW TEMPSYS TEMPSYSTEM TEMPDBSNMP TEMPSYSMAN TEMP1.创建中转临时表空间create temporary tablespace TEMP1 TEMPFILE D:ORACLEORADATAORCLtemp02.DBF SIZE 512M REUSE AUTOEXTEND ON NEXT 1MMAXSIZE UNLIMITED;2.改变缺省临时表空间为刚刚创建的新临时表空间 temp1alter database default temporary tablespace temp1;3.删除原来临时表空间drop tablespace temp including contents and datafiles;4.重新创建临时表空间create temporary tablespace TEMP TEMPFILE D:ORACLEORADATAORCLtemp01.DBF SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;5.重置缺省临时表空间为新建的 temp 表空间alter database default temporary tablespace temp;6.删除中转用临时表空间drop tablespace temp1 including contents and datafiles;以上的方法只是暂时释放了临时表空间的磁盘占用空间,是治标但不是治本的方法,真正的治本的方法是找出数据库中消耗资源比较大的 sql 语句,然后对其进行优化处理。下面是查询在 sort 排序区使用的执行耗时的 SQL:Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)as Space,tablespace,segtype,sql_textfrom v$sort_usage su,v$parameter p,v$session se,v$sql swhere =db_block_size and su.session_addr=se.saddrand s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;或是:Select su.username,su.Extents,tablespace,segtype,sql_textfrom v$sort_usage su,v$sql sWhere su.SQL_ID = s.SQL_ID;注:如果原临时表空间无用户使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是文件系统可以看看文件的时间戳。我们可以删除该表空间:如果原临时表空间还有用户在使用,你是删除不了这个表空间的!在一次生产环境的临时表空间切换中, 原临时表空间始终有用户在上面,即使我关闭了前台程序,也还是有用户,新的临时表空间已经没有用户在使用了。我估计用户进程已经死在原临时表空间了,后来 只有重新启动数据库才能把原来旧的临时表空间给删除。二、修改参数(这个方案紧适用于 8i 及 8i 以下的版本)修改一下 TEMP 表空间的 storage 参数,让 Smon 进程观注一下临时段,从而达到清理和 TEMP 表空间的目的。SQLalter tablespace temp increase 1;SQLalter tablespace temp increase 0;三、Kill session1、 使用如下语句 a 查看一下认谁在用临时段SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, gram, su.TABLESPACE,su.segtype, su.CONTENTS from v$session se, v$sort_usage su WHERE se.saddr = su.session_addr2、kill 正在使用临时段的进程SQLAlter system kill session sid,serial#;3、把 TEMP 表空间回缩一下SQLAlter tablespace TEMP coalesce;注:这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间(LMT:Local Managed Tablespace) ,不需要整理的。 9i 以后只能创建本地管理的表空间。CREATE TABLESPACE TEST DATAFILE D:TEST01.dbf SIZE 5M EXTENT MANAGEMENT DICTIONARYCREATE TABLESPACE TEST DATAFILE D:TEST01.dbf SIZE 5M EXTENT MANAGEMENT LOCAL;四、使用诊断事件,也是相对有效的一种方法1、查询事件代码SQLselect ts#, name from sys.ts$ ;TS# NAME- -0 SYSTEM1 UNDOTBS12 SYSAUX3 TEMP4 USERS5 UNDOTBS22、 执行清理操作SQLalter session set events immediate trace name DROP_SEGMENTS level 4;说明:temp 表空间的 TS# 为 3, So TS#+ 1= 4。oracle 临时表空间过大的原因Oracle 临时表空间主要是用来做查询和存放一些缓存的数据的,磁盘消耗的一个主要原因是需要对查询的结果进行排序,如果没有猜错的话,在磁盘空间的(内存)的分配 上,Oracle 使用的是贪心算法,如果上次磁盘空间消耗达到 1GB,那么临时表空间就是 1GB,如果还有增长,那么依此类推,临时表空间始终保持在一个 最大的上限。Oracle 临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的。1. 没有为临时表空间设置上限,而是允许无限增长。但是如果设置了一个上限,最后可能还是会面临因为空间不够而出错的问题,临时表空间设置太小会影响性能,临时表空间过大同样会影响性能,至于需要设置为多大需要仔细的测试。2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候,根据查询的字段和表的个数会生成一个迪斯卡尔积,这个迪斯卡尔积的大小就是一次 查询需要的临时空间的大小,如果查询的字段过多和数据过大,那么就会消耗非常大的临时表空间。3.对查询的某些字段没有建立索引。Oracle 中,如果表没有索引,那么会将所有的数据都复制到临时表空间,而如果有索引的话,一般只是将索引的数据复制到临时表空间中。针对以上的分析,对查询的语句和索引进行了优化,情况得到缓解,但是需要进一步测试。总结:1.SQL 语句是会影响到磁盘的消耗的,不当的语句会造成磁盘暴涨。2.对查询语句需要仔细的规划,不要想当然的去定义一个查询语句,特别是在可以提供用户自定义查询的软件中。3.仔细规划表索引。如果临时表空间是 temporary 的,空间不会释放,只是在 sort 结束后被标记为 free 的,如果是permanent 的,由 SMON 负责在 sort 结束后释放,都不用去手工释放的。查看有哪些用户和 SQL 导致 TEMP 增长的两个重要视图:v$sort_usage 和 v$sort_segment对于非 LMT 管理方式的 TEMP 表空间,最简单的方法是 Metalink 给出的一个方法:修改一下 TEMP 表空间的 storage 参数,让 Smon 进程观注一下临时段,从而达到清理和 TEMP 表空间的目的。SQLalter tablespace temp default storage(pctincrease 1);SQLalter tablespace temp default storage(pctincrease 0);而对于 LMT 管理方式的 TEMP 表空间,需要重新建立一个新的临时表空间,将所有用户的默认临时表空间指定到新的表空间上,然后 offline 旧的临时表空间,并 drop 掉。具体步骤如下:首先查询用户的缺省临时表空间:oraclejumper oracle$ sqlplus “/ as sysdba“SQL*Plus: Release .0 - Production on Wed Apr 12 11:11:43 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release .0 - ProductionWith the Partitioning optionJServer Release .0 - ProductionSQL select username,temporary_tablespace from dba_users;USERNAME TEMPORARY_TABLESPACE- -SYS TEMP2SYSTEM TEMP2OUTLN TEMP2EYGLE TEMP2CSMIG TEMP2TEST TEMP2REPADMIN TEMP213 rows selected.SQL select name from v$tempfile;NAME-/opt/oracle/oradata/conner/temp02.dbf/opt/oracle/oradata/conner/temp03.dbf重建新的临时表空间并进行切换:SQL create temporary tablespace temp tempfile /opt/oracle/oradata/conner/temp1.dbf size 10M;Tablespace created.SQL alter tablespace temp add tempfile /opt/oracle/oradata/conner/temp2.dbf size 20M;Tablespace altered.SQL alter database default temporary tablespace temp;Database altered.SQL select username,temporary_tablespace from dba_users;USERNAME TEMPORARY_TABLESPACE- -SYS TEMPSYSTEM TEMPOUTLN TEMPEYGLE TEMPCSMIG TEMPTEST TEMPREPADMIN TEMP.13 rows selected.如果原临时表空间无用户使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是文件系统可以看看文件的时间戳。我们可以删除该表空间:(如 果原临时表空间还有用户在用,你是删除不了这个表空间的!在一次生产环境的临时表空间切换中,原临时表空间始终有用户在上面,即使我关闭了前台程序,也还 是有用户,新的临时表空间已经没有用户在使用了。我估计用户进程已经死在原临时表空间了。后来只有重新启动数据库才能把原来旧的临时表空间给删除。 )SQL drop tablespace temp2;Tablespace dropped.SQLSQL select name from v$tempfile;NAME-/opt/oracle/oradata/conner/temp1.dbf/opt/oracle/oradata/conner/temp2.dbfSQL select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible2 from dba_temp_files3 /FILE_NAME TABLES
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 咸宁安全员b证考试题库及答案解析
- 中国水利协会安全员题库及答案解析
- 2025年国家开放大学(电大)《戏剧与影视美学》期末考试备考试题及答案解析
- 2025年血透室医院感染知识考试试题及答案
- 2025年矿业安全试题及答案
- 2025年国家开放大学(电大)《合同法基础》期末考试备考试题及答案解析
- 2025年湖北省技能高考(电气电子类)专业知识考试题库(含答案)
- 2025年国家开放大学(电大)《汉语言文字学》期末考试备考试题及答案解析
- 2025年国家开放大学《心理医学》期末考试备考试题及答案解析
- 安全装置培训考试题及答案解析
- 《中国高血压防治指南(2024年修订版)》解读课件
- 2025年辅警招聘考试题库(+答案解析)
- 社区护士培训课件
- DIEP乳房重建术后的护理指南
- 中信财务管理制度
- 2025至2030年中国硅烷气体行业发展现状调查及市场分析预测报告
- 2025贵阳银行笔试题库及答案
- GB/T 17643-2025土工合成材料聚乙烯土工膜
- 艺术漆涂料施工合同协议
- 陈皮种植转让合同协议
- 小学科学教科版六年级上册全册教案(共28课)2021年
评论
0/150
提交评论