oracle处理temp表空间爆长的问题(三)
时表空间为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;
3.删除原来临时表空间
drop tablespace temp including contents and datafiles;
4.重新创建临时表空间
create temporary tablespace TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp01.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;
7.如果有必要，那么重新指定用户表空间为重建的临时表空间
alter user arbor temporary tablespace temp;
当然，以上方法，是因为表空间撑爆了，我们才去采用解决办法，但最根本的原因还是在于disk的sort过多，而disk sort过多也可能是因为
sort area的设置有问题。不过，从memory和disk的空间上来考虑，如果因为disk sort而导致temp的空间增长太快，最应该注意的是到底是什么样的sql语句要使用这么多的temp空间？
排序区域的分配
-专用服务器分配sort area.
排序区域在PGA.
-共享服务器分配sort area.
排序区域在UGA. (UGA在shared pool中分配).
在9i以前的版本,由sort_area_size决定sort area的分配,在9i及以后的版本,
当workarea_size_policy等auto时,由pga_aggregate_target参数决定sort
area的大于,这时的sort area应该是pga总内存的5%.当workarea_size_policy
等manual时,sort area的大小还是于sort_area_size决定.
无论是那个版本,如果sort area开得过小,In-memory Sort率较低,那temp表空间
肯定会增长得很快,如果开得较高,在C/S结构中将会导致内存消耗严重(长连接较多).
由于smon进程每隔5分钟都要对不再使用的sort segment进行回收,如果你不想让
smon回收sort segment的话,可以使用以下两个event写入初始化参数文件,然后
重启实例,这样如果你的磁盘排序较多,很快就会涨暴磁盘......
event="10061 trace name context forever, level 10" //禁止加收
event="10269 trace name context forever, level 10" //禁止合并碎片
通过合理地设置pga或sort_area_size,可以消除大部分的dist sort,那其它的disk
sort该如何处理呢？从sort引起的原因来看,索引/分析/异常引起的disk sort应该是
很少的一部分,其它的应该是select中的distinct/union/group by/order by以及
merge sort join啦,那我们如何捕获这些操作呢？
通常如何有磁盘排序的SQL,它的逻辑读/物理读/排序/执行时间等都是比较大的,所以我
们可以对v$sqlarea或v$sql字典进行过滤,经过长期地监控数据库,相信可以把这些害
群之马找出来.即然找出这些引起disk sort的SQL后怎么办呢？当然是对SQL进行分析,
尽而优化之。
#!/bin/sh
ORACLE_SID=miat1cat;
export ORACLE_SID
sqlplus /nolog <
conn /as sysdba
col sql_text format a81
col disk_reads format 999999.99
col bgets_per format 99999999.99
col "ELAPSD_TIME(s)" format 9999.99
col "cpu_time(s)" format 9999.99
set long 99999999999
set pagesize 9999
select address,hash_value,disk_reads/executions disk_reads,elapsed_time/1000000/executions as "ELAPSD_TIME(s)",
buffer_gets/executions bgets_per,executions,first_load_time as first_time,sql_text
from v$sql
where executions > 0 and (disk_reads/executions > 500 or buffer_gets/executions > 20000) and command_type = 3
order by 3,4;
--select s.disk_reads,s.buffer_gets/s.executions bgets_per,first_load_time,st.sql_text
-- from v$sql s,v$sqltext_with_newlines st
--where s.address=st.address and s.hash_value=st.hash_value
-- and s.disk_reads > 1000 or (s.executions > 0 and s.buffer_gets/s.executions > 50000)
--order by st.piece;
exit
EOF
用以下语句查看表空间的使用情况：
也可以用如下的语句：
------查看所有表空间大小及使用情况
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",