-- 重建temp

    SQL>alter database temp tempfile '......' drop;
    SQL>alter tablespace temp add tempfile '......';

    可以说,以上的方法都是治标不治本的,因为temp增长过快显然是由于disk sort过多,造成disksort的原因也很多,比如sort area较小等原因,当然,sort area设置多大才合理？这个当然需要满足In-memory Sort大于99%以上哦。

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait %: 99.99
    Buffer Hit %: 99.36 In-memory Sort %: 100.00
    Library Hit %: 99.87 Soft Parse %: 99.84
    Execute to Parse %: 1.17 Latch Hit %: 99.96
    Parse CPU to Parse Elapsd %: 92.00 % Non-Parse CPU: 94.59

    排序区域的分配
    - 专用服务器分配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进行分析,尽而优化之。

    [oracle@www1 sql]$ more show_sql.sh
    #!/bin/bash
    sqlplus -s aaa/bbbcol 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
    !

    总结,如何从根本上降低temp表空间的膨胀呢？方法有2个：

    1 设置合理的pga或sort_area_size

    2 优化引起disk sort的sql