Oracle临时表空间满的处理步骤

Oracle

临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是,需要对查询的中间结果进行排序。重启数据库可以释放临时表空间,如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。临时表空间不足

会产生如下报错

 网上有人猜测在磁盘空间的分配上，

oracle使用的是贪心算法，如果上次磁盘空间消耗达

到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。 临时表空间的主要作用：索引create或rebuild Order by 或group by Distinct 

操作 Union 或 intersect 或 minus Sort-merge joins analyze 清除临时表空间的方法治标不治本从根本上降低temp表空间的膨胀的方法有

2个： 

1 设置合理的pga或sort_area_size 

2 优化引起disk sort的sql 清除并重建临时表空间的步骤: 

 

0.shutdown immediate; 

 

1.startup --启动数据库

 

2.create temporary tablespace TEMP2 TEMPFILE 'E:/oracle/oradata/battery/temp02.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间

 

3.alter database default temporary tablespace temp2;--改变缺省临时表空间为刚刚创建的新临时表空间temp2 

4.drop tablespace temp including contents and datafiles;--删除原来临时表空间

 

5. create temporary tablespace TEMP TEMPFILE 'E:/oracle/oradata/battery/temp.dbf' 

SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时空间

 

6.alter database default temporary tablespace temp;--重置缺省临时表空间为新建的temp表空间

 

7.drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间

 

8. alter user backwardsys temporary tablespace temp; --重新指定用户表空间(用户名)为重建的临时表空间

 

9.可通过语句

select username,default_tablespace,temporary_tablespace from  dba_users  来查询数据库用户的临时表空间

sort_area_size是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。 
当出现大量排序时的磁盘I/O操作时，可以考虑增加sort_area_size的值。sort_area_size是Oracle用于一次排序所需的最大内存数，在排序结束但是结果列返回之前，Oracle会释放sort_area_size大小的内存，但是会保留sort_area_retained_size大小的内存，知道最后一行结果列返回以后，才释放所有的内存。 
会导致排序的语句有 Select DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作；而不会导致排序的语句有 Update , 带BETWEEN子句的Select 等等。

监控内存和硬盘的排序比率，最好使它小于 .10，增sort_area_size

Select name, value FROM v$sysstat Where name IN ('sorts (memory)', 'sorts (disk)');

1 sorts (memory) 23851793
2 sorts (disk) 59

 

 

Order by提速，与索引关系不大。 
因为order by 是对查询结果的排序，添加索引只能优化产生检索结果这个步骤，后面Order by的速度并不能够提高。 
如果速度难以忍受，应该考虑对排序区扩容。 
扩大sort_area_size的大小。

 

ORACLE內存管理 之一 ORACLE PGA SGA

轉自:http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!509.entry

 

MEM主要由兩部分組成

SGA, System Global Area---可以被所以PROCESS訪問。 
PGA, Process Global Area—單個PROCESS（thread）私有。 
UGA會包括在其中之一里 
UGA, User Global Area—SESSION私有，shared server的時候在SGA，dedicated server的時候在PGA

HASH_AREA_SIZE server process用來在內存裡存hash table的大小. 
            大致來看,如果workarea_size_policy=manual那麼pga_aggregate_target將會不被使用,而是使用響應的 sort_area_size,hash_area_size等參數.;如果workarea_size_policy=auto的話那麼就會使用 pga_aggregate_target而不使用其他的sort_area_size,hash_area_size等參數. 
if ( 數據庫版本 >= 10gR1 ) 
then 
   if (workarea_size_policy=auto) 
   then 
       sort area size 無效 
   else 
       sort area size 有效 
   end if 
else -- 數據庫版本 < 10gr1 
   if ( shared server 連接方式 ) 
   then 
       sort area size 有效 
   else -- dedicated server連接方式 
       if (workarea_size_policy=auto) 
       then 
           sort area size 無效 
       else 
           sort area size有效 
       end if 
    end if 
end if 
用sort_area_size 512K,1M,1G來看手工與自動管理時的異同，以下是自動管理
SQL> create table wwm as select * from all_objects; 
  
SQL> alter session set sort_area_size=524288; 
           session xxx memory表示現在這個時刻用了多少，session xxx memory max   表示最高峰時用了多少 
SQL> set autotrace traceonly statistics; 
SQL> select * from wwm order by 1,2,3,4 
--------------------------------------------------------- 
          0  recursive calls 
          0  db block gets 
        420  consistent gets 
          0  physical reads 
          0  redo size 
    2178018  bytes sent via SQL*Net to client 
      22768  bytes received via SQL*Net from client 
       2038  SQL*Net roundtrips to/from client 
          1  sorts (memory) 
          0  sorts (disk) 
      30547  rows processed 
  
SQL> alter session set sort_area_size=1048576;           同上 
SQL> alter session set sort_area_size=1048576000;    同上 
自動管理時sort_area_size不起作用. 
  
改成手工管理後
SQL> alter session set workarea_size_policy=manual; 
SQL> alter session set sort_area_size = 65536;                   --64K 
  
SQL>  set autotrace traceonly statistics; 
SQL> select * from wwm order by 1,2,3,4 
---------------------------------------------------------- 
          0  recursive calls 
         22  db block gets 
        420  consistent gets 
        513  physical reads 
          0  redo size 
    2178018  bytes sent via SQL*Net to client 
      22768  bytes received via SQL*Net from client 
       2038  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          1  sorts (disk) 
      30547  rows processed 
  
============退出，重進 
SQL> alter session set sort_area_size=1048576; 
SQL> set autotrace traceonly statistics; 
SQL> select * from wwm order by 1,2,3,4; 
---------------------------------------------------------- 
          0  recursive calls 
          4  db block gets 
        420  consistent gets 
        424  physical reads 
          0  redo size 
    2178018  bytes sent via SQL*Net to client 
      22768  bytes received via SQL*Net from client 
       2038  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          1  sorts (disk) 
      30547  rows processed 
           還有排序操作在DISK上 
SQL> set autotrace off 
==============退出，重進 
SQL> alter session set sort_area_size=1048576000; 
SQL> set autotrace traceonly statistics; 
SQL> select * from wwm order by 1,2,3,4; 
---------------------------------------------------------- 
          0  recursive calls 
          0  db block gets 
        420  consistent gets 
          0  physical reads 
          0  redo size 
    2178018  bytes sent via SQL*Net to client 
      22768  bytes received via SQL*Net from client 
       2038  SQL*Net roundtrips to/from client 
          1  sorts (memory) 
          0  sorts (disk) 
      30547  rows processed 
  
           以上分別用64K,1M,1G來測試,分配1G並不代表就要切實分配1G的MEM出去。而是說你有權利用到1G,相反,設置64K並不代表你的session只能用64k,一個SQL可能有多個SORT,指的是每個SORT 的限額