SQL優化筆記 [final]

 

1. 基礎資料做少量冗餘，減少表關聯

2. 對歷史資料做遷移，保留最近一段時間資料

3. 80%的效率問題因為低效的SQL導致

4. AWR中主要檢視專案
   SQL ordered by Elapsed Time查每個執行消耗的時間
   SQL ordered by CPU Time查每個執行消耗的時間
   SQL ordered by Gets        buffer gets 邏輯讀  
   SQL ordered by Reads     物理讀

5. 邏輯讀物理讀高的SQL及執行時間長的SQL需要優化

6. 通過執行計劃優化SQL語句:explain plan, sql trace等
SQL> explain plan for select * from dual;
SQL> select * from table(dbms_xplan.display);

7. OLTP中優化SQL原則：儘量減少資料的讀取(只讀取必要的資料)

8. 表的關聯部分：不管多少表連線，都是先兩個表得出結果集，
   然後結果集與表或結果集的操作。

9. 幾個要素：預估記錄數(操作後的結果集)，開銷(cost)及可選擇性

10. 關聯條件：where a.col1 =b.col1，
    過濾條件：where a.col1<=103（常量）

11. 使用hint來改變存在問題的執行計劃

12. 查詢統計資訊是否是最新的(表，列，索引，柱狀圖)
    推薦使用 DBMS_STATS.GATHER_XXX_STATS(); 

13. 複雜SQL採用拆分的方式進行優化

14. 考慮新建索引以及實施分割槽

15. 檢視訪問可以考慮使用物化檢視取代，減少多表連線查詢

16. 表連線選擇：
    巢狀迴圈連線：有高選擇性索引或進行限制性搜尋時效率比較高，適用於返回較少結果集。
    排序合併連線：當缺乏索引或者索引條件模糊時，排序合併連線比巢狀迴圈有效。
                  適合對大資料量需要不全等於（>, >=,    雜湊連線    ：當缺乏索引或者索引條件模糊時，雜湊連線連線比巢狀迴圈有效。
                  通常比排序合併連線快。在資料倉儲環境下，如果表的紀錄數多，
                  效率高。用於等價連線下大資料關聯。

17. 引數 OPTIMIZER_INDEX_COST_ADJ， OPTIMIZER_INDEX_CACHING

18. CBO儘可能從過濾性最強的條件入手

19. 子查詢上約束條件強，適用IN, 主語句上約束條件強，使用EXISTS 

20. 調優相關的幾個檢視
v$session
V$sqltext
V$sql_plan
V$sql_plan_statistics
V$sql_plan_statistics_all
V$ses_optimizer_env
User_table_histograms
User_tab_statistics
User_tab_col_statistics
User_ind_statistics

21. 使用10046事件，可以跟蹤某個SQL語句完整的執行過程，
獲取其解析，執行，CPU使用時間，等待事件，每個操作的
具體耗時等資訊。這對獲取語句詳細的執行計劃，分析定位
其效率問題，從而有針對性地優化該語句，非常有用。

Alter session set events '10046 trace name conetxt,  level  n';
執行SQL。
Alter session set events '10046 trace name conetxt,  off';

22. 使用outline固定執行計劃

23. 使用DBMS_SQLTUNE包及DBMS_ADVISOR包獲取語句最簡執行計劃建議
 
24. 給CBO採集系統的統計資訊，執行計劃將更優。
Exec dbms_stats.gather_system_stats(‘INTERVAL’, 180);
Select * from sys.aux_stats$;

25. DBMS_PROFILE包用來調優儲存過程，通過跟蹤及記錄儲存過程
    中每條SQL的執行時間，來確定哪些SQL語句最耗時。

25. 11g中ACS自適應共享遊標克服繫結窺視的弊端

26. 11g中對聯合列收集統計資訊

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-708087/，如需轉載，請註明出處，否則將追究法律責任。