【优化思路集】临时表空间问题

  临时表空间管理有时也是很棘手的问题，做下笔记以备后事所需。

 查看当前临时表空间使用率：

点击(此处)折叠或打开

1. select * from (select a.tablespace_name,sum(a.bytes/1024/1024)allocated_mb 
2. from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname') 
3. group by a.tablespace_name) x,(selec sum(b.bytes_used/1024/1024) free_mb,sum(b.bytes_free/1024/1024) free_mb
4. from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname')
5. group by b.tablespace_name);

如果发现临时表空间快满了，这就要找出产生高临时表空间使用率的原因。

查询出那条sql语句将排序数据段中的空间用光了：

点击(此处)折叠或打开

1. select s.sid ||','|| s.serial# sid_serial,s.username,
2. o.blocks * t.block_size/1024/1024 mb_used,o.tablespace,
3. o.sqladdr address,h.hash_value,h.sql_text
4. from v$sort_usage o,v$session s,v$sqlarea h,dba_tablespaces t 
5. where o.session_addr = s.saddr and o.sqladdr = h.address (+) 
6. and o.tablespace = t.tablespace_name order by s.sid;

  oracle尽量在内存PGA中进行排序和散列运算，但是如果一个排序太大，内存中无法容纳，就会适应临时表空间进行。需要理解一点是，即使只有一个很大的排序运算，也有可能将整个临时表空间耗尽。因为所有数据会话都共享临时表空间，运行一个很大的排序运算会话就会有可能导致 其他所有会话产生空间不足的问题，一旦临时表空间被占满，所有试图使用临时表空间 的sql语句就汇报ORA_1652：UNABLE TO EXTEND TEMP SEGMENT（这个后文会讲到如何处理）。新的会话可能无法连接上。查询可能会被挂起，用户也可能不能在提交新的查询，这时如果试着去找出可能产生阻塞的锁，就会发现并没有任何阻塞锁存在。如果临时表空间被占满，事务就无法完成。
 使用order by 或者group by 子句的运算，频繁使用临时段来完成工作，当然，创建索引或者重建索引，也是会需要临时表空间来进行排序的。
Oracle使用PGA内存来进行排序和散列运算，因此，首先要做的事情就是查看当前为PGA_AGGREGATE_TARGET初始化参数所设置的值，并且看看将其提高是否有帮助。但是，即使设置了 PGA_AGGREGATE_TARGET参数设置了更大的值，也并不能保证Oracle会完全在内存中来进行大的排序运算。
 如果数据库中的临时表空间用完了，则必须增加一个临时文件来增加其大小。启用临时表空间空间的自动扩展也会防止“空间不足”问题，因为Oracle会在临时表空间中分配一块空间，来进行排序运算，也可以专门为进行大量排序的用户分配一个临时表空间，防止这些大的排序损害了数据库性能。


注意，不同于表数据段或者索引段对于每个对象可能有多个，但是临时表空间只有一个段，及排序段。所有会话共享这个排序段，一条sql语句可能会使用多个排序段和散列运算。此外，同一个会话可能有多个sql语句同时运行，每个语句都有可能使用多个排序和散列运算。一个排序运算完成后，数据库就会马上将该运算所使用的数据块标记为空闲，并将它们分配给另一个排序运算。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30430420/viewspace-1800205/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30430420/viewspace-1800205/