2、关于SORT和HASH
无独有偶，我今天又在另外一个数据库中遇到了大量SORT导致的临时表空间不足，通过实例来说明一下，我们的数据库临时表空间是30G，而早上来的时候检查日志发现在2012年10月18日下午4点出现了报错，如果是导致临时表空间耗尽的语句，那他的响应时间一定比较长，那么在DBA_HIST_ACTIVE_SESS_HISTORY一定会留下关于direct path write temp的等待时间，我通过语句
SQL> SELECT SAMPLE_TIME, SQL_ID, EVENT
2 FROM DBA_HIST_ACTIVE_SESS_HISTORY
3 WHERE SAMPLE_TIME > to_DATE('2012-10-18 16:00:00' , 'YYYY-MM-DD HH24:MI:SS')
4 AND SAMPLE_TIME
5 AND EVENT like '%direct%'
6 ORDER BY SAMPLE_TIME;
找到了大量的这样的语句，同时由于本库日常压力并不大，在shared pool中还缓存了其SQL文本，顺利的找到了SQL，语句很长就不给出了。
在空闲时间我又执行了一次，同时跟踪了临时表空间的使用
SQL> select BLOCKS*8/1024/1024 from v$sort_usage;
BLOCKS*8/1024/1024
------------------
31.9951171875
这里我的临时表空间满了，语句也就报错了。当然这里要通过SESSION ADDR来确认对应的那个记录，我这里就一条也就无所谓了。为了找到根源我得出了SQL执行计划。
执行计划如下：
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 366K| 0 |00:00:00.01 | 0 |
|* 2 | HASH JOIN | | 1 | 366K| 112M|00:03:45.58 | 70565 |
| 3 | TABLE ACCESS BY INDEX ROWID | WEB_BAS_DIC_DT | 1 | 16 | 16 |00:0
|* 4 | INDEX RANGE SCAN | IDX_WEB_BAS_DIC_DT_C_DICT_TYPE | 1 | 16 | 16 |00:0
|* 5 | HASH JOIN | | 1 | 343K| 112M|00:01:53.36 | 70561 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS FULL | WEB_CLM_VCH_RPT | 1 | 702K| 702K|00:00:00.01
| 7 | MERGE JOIN CARTESIAN | | 1 | 341K| 112M|00:00:00.63 | 44122 |
| 8 | TABLE ACCESS BY INDEX ROWID | FWK_BAS_DPT_CDE | 1 | 1 | 160 |00
| 9 | NESTED LOOPS | | 1 | 1 | 320 |00:00:03.74 | 6323 |
| 10 | NESTED LOOPS | | 1 | 1 | 160 |00:00:00.04 | 6161 |
| 11 | NESTED LOOPS | | 1 | 1 | 160 |00:00:00.04 | 5660 |
| 12 | NESTED LOOPS | | 1 | 1 | 160 |00:00:00.04 | 5338 |
|* 13 | TABLE ACCESS FULL | WEB_CLM_CNCL | 1 | 1 | 160 |00:00:00.04 |
| 14 | TABLE ACCESS BY INDEX ROWID| WEB_CLM_TEMP_DPT | 160 | 1 | 160 |00:00:00
|* 15 | INDEX UNIQUE SCAN | PK_DPT_DPT | 160 | 1 | 160 |00:00:00.01 |
| 16 | TABLE ACCESS BY INDEX ROWID | FWK_BAS_EMP_CDE | 160 | 1 | 160 |00:00:00.
|* 17 | INDEX UNIQUE SCAN | FWK_BAS_EMP_CDE_PK | 160 | 1 | 160 |00:00:00.01
| 18 | TABLE ACCESS BY INDEX ROWID | WEB_CLM_MAIN | 160 | 1 | 160 |00:00:00.01
|* 19 | INDEX UNIQUE SCAN | PK_WEB_CLM_MAIN | 160 | 1 | 160 |00:00:00.01 |
|* 20 | INDEX RANGE SCAN | IDX_FWK_BAS_DPT_CDE_ORG | 160 | 1 | 160 |00
| 21 | BUFFER SORT | | 160 | 693K| 112M|00:00:00.60 | 37660 |
| 22 | TABLE ACCESS FULL | WEB_CLM_RPT | 1 | 693K| 702K|00:00:00.01 |
简单的说一下这里最后需要排序的数据量为112M行1亿多行，为什么这么多行明显这里的笛卡尔积是可能不合理的地方，问了开发后确实他们写掉了2个大表之间的连接条件，修改后就好了。这里顺便说一下buffer sort，这个不是真正的排序，只是把数据缓存在BUFFER中，避免笛卡尔积多次扫描带来的影响，这里笛卡尔积驱动了这张表扫描160次。如果缓存了效率就会提高，不缓存就必须进行多次全表扫描。
https://blogs.oracle.com/toddbao/entry/buffer_sort%E6%98%AFbuffer%E5%8D%B4%E4%B8%8D%E6%98%AFsort
这篇文章进行了说明，是我考OCM的时候的监考老师包光磊的博客。
3 、关于临时LOB
可以参考文章
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [ID 802897.1]
还有一篇文章
http://blog.oracle.com.cn/html/87/t-81587.html
在10.2.0.4之前必须通过断开连接进行释放，但是在10.2.0.4之后可以通过alter session set events '60025 trace name context forever';
来释放本会话已经free但是没有释放的临时表空间，关于怎么看是否free
select * from v$temporary_lobs;
如果没有cache也没有nocache就是free了但是却没有释放的内存。Cache或者nocache是 dbms_lob.createtemporary中参数cache是来指定的。
