【Oracle】排序與sort_area_size

風靈使發表於2018-12-30
Oracle排序

生產環境的一個查詢語句在隨著時間推移時查詢速度越來越慢，終於到了我忍無可忍的地步，決定一探究竟。

  • 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。因此只能通過重新索引，碎片整理來進行優化。

相關文章