【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) | 00:06:33 | ||
1 | COUNT STOPKEY | ||||||
2 | FILTER | ||||||
3 | VIEW | 125K | 344M | 32723 (1) | 00:06:33 | ||
4 | SORT ORDER BY | 125K | 73M | 75M | 32723 (1) | 00:06:33 | |
5 | HASH JOIN RIGHT OUTER | 125K | 73M | 16452 (1) | 00:03:18 | ||
6 | TABLE ACCESS FULL | xxxxxx | 60 | 1860 | 3 (0) | 00:00:01 | |
7 | HASH JOIN RIGHT OUTER | 125K | 69M | 16448 (1) | 00:03:18 | ||
8 | TABLE ACCESS FULL | xxxxxx | 488 | 40504 | 5 (0) | 00:00:01 | |
9 | HASH JOIN RIGHT OUTER | 125K | 59M | 16442 (1) | 00:03:18 | ||
10 | TABLE ACCESS FULL | xxxxxx | 471 | 47571 | 68 (0) | 00:00:01 | |
11 | HASH JOIN RIGHT OUTER | 125K | 47M | 5168K | 16372 (1) | 00:03:17 | |
12 | TABLE ACCESS FULL | xxxxxx | 29399 | 4823K | 308 (1) | 00:00:04 | |
13 | TABLE ACCESS FULL | xxxxxx | 125K | 27M | 14376 (1) | 00:02:53 |
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。因此只能通过重新索引,碎片整理来进行优化。