【Oracle】排序与sort_area_size

生产环境的一个查询语句在随着时间推移时查询速度越来越慢，终于到了我忍无可忍的地步，决定一探究竟。

• Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -64bit Production
• 数据量：382947
• workarea_size_policy:AUTO
• sort_area_size:65536

查看执行计划

查看Oracle的执行计划，发现sort排序操作耗费了很多时间和临时空间。去掉排序后执行时间在0.1S，加上排序操作后立马飙升至3.6s。由于业务需求的必要性不可以去掉这个排序操作，只能尝试优化排序，缩短查询时间。但是目前并没有纯sql方案从语句上来优化查询。只能通过参数设置来进行优化。
执行计划

Id	Operation	Name	Rows	Bytes	TempSpc	Cost (%CPU)	Time
0	SELECT STATEMENT		100	280K		32723 (1)	0.00454861111111111
1	COUNT STOPKEY
2	FILTER
3	VIEW		125K	344M		32723 (1)	0.00454861111111111
4	SORT ORDER BY		125K	73M	75M	32723 (1)	0.00454861111111111
5	HASH JOIN RIGHT OUTER		125K	73M		16452 (1)	0.00229166666666667
6	TABLE ACCESS FULL	xxxxxx	60	1860		3 (0)	1.15740740740741E-05
7	HASH JOIN RIGHT OUTER		125K	69M		16448 (1)	0.00229166666666667
8	TABLE ACCESS FULL	xxxxxx	488	40504		5 (0)	1.15740740740741E-05
9	HASH JOIN RIGHT OUTER		125K	59M		16442 (1)	0.00229166666666667
10	TABLE ACCESS FULL	xxxxxx	471	47571		68 (0)	1.15740740740741E-05
11	HASH JOIN RIGHT OUTER		125K	47M	5168K	16372 (1)	0.00228009259259259
12	TABLE ACCESS FULL	xxxxxx	29399	4823K		308 (1)	4.62962962962963E-05
13	TABLE ACCESS FULL	xxxxxx	125K	27M		14376 (1)	0.00200231481481481

Oracle参数修改

查看当前设置

show parameter sort_area_size;--65536
show parameter workarea_size_policy;--AUTO

修改设置

alter system set workarea_size_policy=MANUAL deferred ;
alter system set sort_area_size=500000000 deferred;--500M

deferred表示这次修改对当前会话不发生作用, 在以后打开的会话中起作用, 故它有”推迟”影响的效果.。

验证设置

再次执行排序语句，发现时间不仅没有变短，反而变得很长很长，长到我失去等待的耐心，按道理说sort_area_size变大，排序操作应该都是在内存中完成，反复测试发现当前的排序操作已经是在内存中完成，扩大sort_area_size并没有起到优化，反而变得更慢。可能是因为关闭了PGA内存自动管理功能。
参看当前排序的方式安排，如果sorts(disk)/sorts(memory)>5%,你就迫切需要进行排序优化了。

 Select * from v$sysstat where name like '%sort%';

查询结果

所以我们并不需要调整sort_area_size。因此只能通过重新索引，碎片整理来进行优化。