昨天接到客户报在生产库有一个查询报错：

SQL> select * from gupr_agt_code a, agt_code b where a.agt_code=b.agt_code;

select * from gupr_agt_code a, agt_code b where a.agt_code=b.agt_code

ORA-03232: unable to allocate an extent of blocks from tablespace

ORA-02063: preceding line from GUPRLINK

据说这个错误是从上周才开始出现的，以前都没有报过这种错误，查询metalink有这样一篇文章（见最后），意思是在Oracle 8.1.x环境，当HASH_MULTIBLOCK_IO_COUNT参数为0（默认）时，Oracle会为每一个查询动态计算这个参数的值，这个参数决定了必须为这个查询分配多少个连续的block；如果一个表空间的next_extent小于这个值，就会报ora-03232的错误，特别是对一些hash join的查询，需要较大的temp空间来进行排序，就可能会导致这种错误。

我检查了一下temp表空间的参数

SQL> select * from dba_tablespaces

2 where tablespace_name='TEMP';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN

------------------------------ -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- ----------------- --------------- ----------

TEMP 65536 65536 1 0 65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO

Next_extent只有64K，查了一下其他同构的数据库，这个值是500K，显然是太小了，于是调整了这个参数

SQL> alter tablespace temp default storage(next 500k);

Tablespace altered

重新执行这个查询，没有报错，问题解决。

现在的问题是为什么以前的查询都很正常，从上周才开始报错，联想到上周更改过sort_area_size参数，怀疑是这个参数的修改导致了执行计划发生改变，查了一下，上周把这个参数从202144增加到了1048576，这是当前值环境下的执行计划：

已选择15644行。

已用时间: 00: 00: 09.09

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=978 Card=5092892 Bytes=5775339528)

1 0 HASH JOIN (Cost=978 Card=5092892 Bytes=5775339528)

2 1 REMOTE* (Cost=28 Card=15643 Bytes=2487237) GUPRLINK

.WORLD

3 1 TABLE ACCESS (FULL) OF 'AGT_CODE' (Cost=31 Card=32557 Bytes=31743075)

执行计划果然是选用了全表扫描和hash join，这也解释了为什么需要较大的temp空间来进行排序；

修改当前会话的sort_area_size到原值202144

alter session set sort_area_size=262144;

会话已更改。

再看当前的执行计划

已选择15644行。

已用时间: 00: 00: 10.05

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1297 Card=5092892 Bytes=5775339528)

1 0 MERGE JOIN (Cost=1297 Card=5092892 Bytes=5775339528)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'AGT_CODE' (Cost=826 Card=32557 Bytes=31743075)

3 2 INDEX (FULL SCAN) OF 'PK_AGT_CODE_AGT_CODE' (UNIQUE) (Cost=26 Card=32557)

4 1 SORT (JOIN) (Cost=443 Card=15643 Bytes=2487237)

5 4 REMOTE* (Cost=28 Card=15643 Bytes=2487237) GUPRLINK

.WORLD

果然，执行计划发生了很大变化，选用了merge join和索引扫描，相对来说需要较小的temp空间来进行排序，这也解释了为什么原来temp表空间较小的next_extent值也可以满足要求。但是，我们并不能说原来的执行计划就更好，从我测试的效果调整前后的执行时间是差不多的，具体的执行效率需要具体问题具体分析。

总结：sort_area_size参赛的调整会导致一些查询的执行计划发生很大的变化，通常来说增大这个参数会使排序的速度更快，但是也会使查询对temp表空间的要求更高，是否还有其他影响现在还不知道，因此在调整这个参数的同时也需要对temp表空间参数进行相应的调整。