生产环境的一个查询语句在随着时间推移时查询速度越来越慢，终于到了我忍无可忍的地步，决定一探究竟。
- Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -64bit Production
- 数据量：382947
workarea_size_policy:AUTOsort_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。因此只能通过重新索引，碎片整理来进行优化。