STAT_ID STATISTIC# NAME
---------- ---------- ----------------------------------------------------------------
2091983730 640 sorts (memory)
2533123502 641 sorts (disk)
3757672740 642 sorts (rows)
-- 查看每个session可以使用的pga大小
select a.ksppinm "PARAMETER" , b.ksppstvl "VALUE" , a.ksppdesc "DESCRIPTION"
from x$ksppi a
inner join x$ksppcv b
on a.indx = b.indx
where a.ksppinm like '%_pga_max_size%';
-- 查看sort消耗大的sql，执行次数
col SQL_TEXT for a100
set lines 1000
set pages 100
select * from（
select sql_text, sorts ,executions
from v$sqlarea
order by sorts desc ,executions desc）
where rownum < 6;
SQL_TEXT SORTS EXECUTIONS
---------------------------------------------------------------------------------------------------- ---------- ----------
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) 428909 428909
or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (
this_date is null) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.d
b_is_logstdby = 1 and job >= 1000000000)) order by next_date, job
-- 查看会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么
select * from
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 gb
from v$sort_usage order by blocks desc)
where rownum<=200;
USERNAME SESSION_ADDR SQL_ID CONTENTS SEGTYPE GB
------------------------------ ---------------- ------------- --------- --------- ----------
RPT_HBTELE 00000028E1F03FA8 3nz260v5dwqnv TEMPORARY HASH .212402344
ALLDM 0000002882256EF8 9m7787camwh4m TEMPORARY LOB_DATA .001464844
RPT_HBTELE 0000002901F40CB8 9m7787camwh4m TEMPORARY DATA .000244141
ALLDM 00000029422906B0 3nz260v5dwqnv TEMPORARY LOB_DATA .000244141
ALLDM 00000029422906B0 3nz260v5dwqnv TEMPORARY INDEX .000244141
RPT_HBTELE 00000029022B3998 bydf32qgqdwdu TEMPORARY DATA .000244141
ALLDM 0000002882256EF8 9m7787camwh4m TEMPORARY LOB_INDEX .000244141
说明：SEGTYPE 类别
SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数(window function)、建索引等产生的排序。
DATA：临时表(Global Temporary Table)存储数据使有的段。
INDEX：临时表上建的索引使用的段。
HASH：hash算法，如hash连接所使用的临时段。
LOB_DATA和LOB_INDEX：临时LOB使用的临时段。
根据上述的段类型，大体可以分为三类占用：
1、SQL语句排序、HASH JOIN占用
2、临时表占用
3、临时LOB对象占用
## v$sort_usage中的SQL_ID不一定是正在运行的sql，如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。
查看打开的游标中是否有大数据量排序的SQL
select sql_id,sorts,rows_processed/executions from v$sql
where parsing_schema_name='ACCT' and executions>0 and sorts>0
and sql_id in (select sql_id from v$open_cursor where sid=4505)
order by 3;
优化 lob临时段：
Oracle中的LOB变量，类似于C语句中的指针，或者类似于JAVA代码中的数据库连接Connection，是需要释放的。上述有问题的代码，缺少了释放LOB的代码：dbms_log.freetemporary(v_lob)。好在对于这种情况，Oracle提供了一个补救措施，就是设置60025事件可以自动清理掉不活动的LOB，只需要在参数文件中加上event='60025 trace name context forever'。
在Oracle数据库中，xmltype类型内部也实际上是LOB类型，xmltype类型的数据操作可能会产生较多的LOB临时段。lob类型的字段上的更改操作，比如lob拼接等，同样会产生LOB临时段。如果在v$sort_usage中发现大量的LOB类型的临时段，那么通常是由于代码存在问题，没有释放LOB，或者是由于Oracle本身的BUG。在MOS上，如果以lob temporary关键字搜索，会发现相当多的关于lob临时段的泄露或临时段没有释放相关的文档。
最后，不管是什么情况导致的临时表空间被过多占用，通常重启应用能够释放掉临时段，因为会话退出后，相对应的临时段就会被释放。看来，“重启”大法在这种情况下就很有用。