【Oracle】排序与sort_area_size

最新推荐文章于 2023-11-23 14:27:29 发布

转载最新推荐文章于 2023-11-23 14:27:29 发布 · 1.2k 阅读

Oracle数据库专栏收录该内容

162 篇文章

订阅专栏

面对Oracle数据库查询速度随时间逐渐下降的问题，本文详细分析了一个特定查询语句的执行计划，发现排序操作导致性能瓶颈。通过调整Oracle参数如workarea_size_policy和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

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