一块来分析temp表空间不能被释放的问题！

白鹭

173 sorts (memory)
64 105190

174 sorts (disk)
64 1751

175 sorts (rows)
64 9016895
说明你超过1%的排序在磁盘进行，另外你的排序平均每次才约900行，说明大都是小排序，这样采用更大的sort_area_size意味着更多的排序在内存中进行，我还是推荐你用比128k更大的sort_area_size,
对于非mts配置，sort_area_size在PGA中，你如果担心占内存,可以去观测pga大小,
select sum(t.value)/1048576 "pga size in MB" from
v$sesstat t,v$statname n
where t.statistic#=n.statistic# and n.name='session pga memory';

我给你的语法uniform size 10m应调为uniform size 1028k。

dolphinli

本人曾经碰到过类似的问题，主要是应用程序的问题，多表联合查询时条件没有设好，产生了笛卡儿集，硬盘一下就被吃光了（一个小时吃掉了10G），如果你的程序没有经过测试的话，最好还是从你的应用程序入手！

chao_ping

Very sound suggestion, 白鹭! But you seem to be talking to someone that doesn't
listen; szbestway keeps complaining about running out of temp space.

Addition to what you say:

1. If he's afraid of running out of memory (2GB), he can monitor it from OS.
vmstat, top, sar, swap -l (or -s) etc. I'm only familiar with Solaris.
2. 256k*400 users is an overestimate. Sort memory is gradually allocated up to
sort_area_size, not allocated at one shot to that setting. It's absolutely
impossible for all users using sort_area_size to its limit at the same moment.
(Ref. Steve Adams' Nov 2000 Newsletter)
3. You can't change a dictionary-controlled TS to locally managed by ALTER
TABLESPACE. The only way is through
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL and it doesn't support uniform
size, the preferred way to use LMT. So the only way left is drop and recreate
the TS.
4. Never use 4k as db_block_size. If you're not sure which one is the best, use
8k.

Yong Huang

szbestway

改变sort_area_size=256K 后，仍然没有解决问题！
以下是今天的高峰时期的查询：

                                                              

10:15:55 SQL> select count(*) from sys.v_$sort_usage;

  COUNT(*)                                                                     
----------                                                                     
         0                                                                     

10:16:16 SQL> select count(*) from dba_free_space where tablespace_name='TEMP';

  COUNT(*)                                                                     
----------                                                                     
        66                                                                     

10:16:29 SQL> select * from sys.v_$sort_segment;

TABLESPACE_NAME                 SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE         
------------------------------- ------------ ------------- -----------         
CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS  
------------- ------------- ------------ ------------ ----------- ------------  
FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS   MAX_SIZE   
----------- ------------- ----------- ------------- ------------- ----------   
MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS         
---------- ------------- --------------- ------------- ---------------         
RELATIVE_FNO                                                                    
------------                                                                    
TEMP                                      36         97296        1024         
         1732          1732      1775304         1732     1775304            0  
          0          1732        9187             0             0       1732                                                                       
   1775304          1732         1775304             7            7173         
          36                                                                    
                                                                                

10:16:40 SQL> select * from dba_segments where tablespace_name='TEMP';

OWNER                                                                           
------------------------------                                                  
SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME               
------------------------------ ------------------ ------------------------------
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT        
----------- ------------ ---------- ---------- ---------- --------------        
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS     
----------- ----------- ----------- ------------ ---------- ---------------     
RELATIVE_FNO BUFFER_                                                            
------------ -------                                                            
SYS                                                                                                                                       
36.97296                       TEMPORARY          TEMP                                                                                   
         36        97296 7271628800    1775300       1732        4198400                                 
    4198400           1  2147483645            0          1               1                                                               
          36 DEFAULT                                                            
10:17:00 SQL> SELECT * FROM sys.V_$SYSSTAT
10:17:10   2  WHERE NAME LIKE '%sort%';

STATISTIC# NAME                                                                 
---------- ----------------------------------------------------------------     
     CLASS      VALUE                                                           
---------- ----------                                                           
       173 sorts (memory)                                                      
        64     332101                                                           
                                                                                
       174 sorts (disk)                                                         
        64       7625                                                           
                                                                                
       175 sorts (rows)                                                         
        64   36192873                                                           
                                                                                

10:17:13 SQL> r
  1  SELECT * FROM sys.V_$SYSSTAT
  2* WHERE NAME LIKE '%sort%'

STATISTIC# NAME                                                                 
---------- ----------------------------------------------------------------     
     CLASS      VALUE                                                           
---------- ----------                                                           
       173 sorts (memory)                                                      
        64     335400                                                           
                                                                                
       174 sorts (disk)                                                         
        64       7625                                                           
                                                                                
       175 sorts (rows)                                                         
        64   36422356                                                           
                                                                                

10:24:14 SQL> select sum(t.value)/1048576 "pga size in MB" from
10:31:24   2  v$sesstat t,v$statname n
10:31:24   3  where t.statistic#=n.statistic# and n.name='session pga memory';
v$sesstat t,v$statname n
*
ERROR at line 2:
ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP

11:11:41 SQL> select sum(value)/1048576 from sys.v_$sesstat where STATISTIC#=20;

SUM(VALUE)/1048576                                                              
------------------                                                              
        155.760555                                                              

11:13:30 SQL>

白鹭

看来这次并发用户得到提高1732,我觉得你不用什么都听Oracle的，具体情况应根据经验具体分析，你的sort_area_size 还可以调大，从你最近的一次查询显示约2%的查询在磁盘进行，Yong huang说得没错，我前面400*256K有误，我后来没有指出，一般只有Parallel Query时，Oracle会分配多个Sort area size的排序区域，从你的查询看来你还没有改为local manager,
在不用restart db的情况下，我建议你先用
alter system set sort_area_size=4m deferred;
来直接让新设置生效，你前面提到了空间不是问题，那就在增加几个数据文件,然后再查询。

szbestway

将sort_area_size＝2M后
－－查询
SELECT * FROM sys.V_$SYSSTAT
                     WHERE NAME LIKE '%sort%';
STATISTIC# NAME
---------- -------------------------
     CLASS      VALUE
---------- ----------
       173 sorts (memory)
        64     224637

       174 sorts (disk)
        64       6733

       175 sorts (rows)
        64   31675928

在硬盘上的排序反增大，有3％的排序在硬盘上进行！

至于设置temp 表空间为locally managed,可能要到晚上重建！
对了，白鹭 给的temp表空间脚本：

eate temporary tablespace temp
tempfile '/datadb1/oradata/temp01.dbf' size 1024M,
'/datadb1/oradata/temp02.dbf' size 1024M,
'/datadb2/oradata/temp03.dbf' size 1024M,
'/datadb2/oradata/temp04.dbf' size 1024M,
'/datadb3/oradata/temp05.dbf' size 1024M,
'/datadb3/oradata/temp06.dbf' size 1024M
extent management local
uniform size 1028k;

中tempdfile 与我们常用的datafile 有何区别？

白鹭

你看到的统计信息是自上次启动后的累计信息，并不能反映排序到磁盘百分比增加,另外，从建表空间时，你一定要注意sort_area_size和uniform size 的配合。
tempfile的不同:
1.8i中temporary tablespace 和local manage同时用
只能用tempfile
2.8i中tempfile可以从数据库中方便地删除

szbestway

由于客户担心，temp 表空间的重建的推迟到周末进行了！
今天的状况与往常差不多！
但通过top 关查，数据库服务器的iowait 很繁忙，有是居然有54.1%  iowait

但平时只有很少的值，不知是和原有！
请指教！

白鹭

io繁忙是有可能的，你想，光1000多个并发用户请求写临时表空间就够忙了，更不用说其他io了。

szbestway

但我将odbc 的连接数降低，今天的并发用户数并没有那么多，只有几百个（360）。
而且，昨天的iowait 并没有这么大，（并发1600）！ 是否有其他的可能！