改進資料庫效能-SQL查詢優化

  首先、這個部分主要是針對oracle11g（管理藝術）相關章節做的筆記，方便自己有一個系統全面的理論指導。

 

調優的方法論：

1. 正確設計應用程式
2. 調優應用程式的SQL程式碼
3. 優化記憶體
4. 優化IO
5. 優化爭用和其他的問題

 

oracle11g增強了主動調優proactive tuning,被動的效能優化reactive performance tuning

 

優化SQL的查詢處理：

首先需要了解SQL處理的過程和優化要點

SQL語句經歷了語句分析parsing、優化optimizing、和執行executing階段，如果是查詢語句，資料要能夠被檢索，那麼在SQL處理之前還有一個額外的獲取（fetch）階段

 

語法分析：

主要有語法和語義，分解成關係代數查詢，檢查過程中參照資料字典（快取和基表）

 

優化：

使用優化程式CBO來選擇最好的訪問方法來查詢所涉及的表和索引的檢索資料，分為查詢重寫階段和物理執行計劃生成

執行計劃生成階段：

• 查詢執行的操作
• 操作執行的次序
• 執行每個操作的演算法
• 從磁碟或者記憶體檢索資料的最好方式
• 查詢期間從一個操作向另一個操作傳送資料的最好方式

生成幾個有效的物理查詢計劃，然後對他們進行成本估算，選擇估算值最低的執行計劃。

 

執行階段：select-直接返回查詢結果：insert update delete語句記錄被修改

 

最重要的就是查詢優化處理階段---它決定了快速檢索資料所涉及的所有重要問題。

 

統計資料：

oracle11g一般每天晚上10-早上6點以及週末的全天開啟。gather_stats_job作業，可以禁用

gather_stats_job為所有沒有優化程式統計資料，對一個物件，如果修改了10%的資料-就統計，可以通過DBA_TAB_MODIFICATIONS檢視相關更改資訊

DBA_tables—last_analyzed 提示的上次統計的時間，sample_size取樣（表大小和取樣百分比沒有關係），沒有變化的資料就不會做統計分析

 

必要的統計資料：

• 錶行數
• 每個資料塊的行數
• 行的平均長度
• 表的總塊數
• 每個索引的級別數
• 每個索引葉塊的數目
• 表中每一個列的不同資料值的數目
• 資料分佈柱狀圖
• 索引建的數目
• 基數
• 每個列的最大和最小值

OPTIMIZER_MODE引數

all_rows，這個是預設的，以實現最大吞吐量

first_row_n：實現響應時間

first_row：使用探測法（經驗規則）無論是否有統計資料，響應時間最小。

 

優化程式做什麼：

1、SQL轉換

常用的轉換：

• 將IN轉換or語句
• 將or轉換為union或者union all
• 將非直接相關的巢狀的選擇語句轉換為更有效的連線（jion）
• 將外聯結轉換為更有效的內連結（inner jion)
• 將複雜的子查詢轉換為聯結、半聯結或者反聯結（antijoin）
• 將基於星型模式的資料倉儲進行星型轉換
• 將between轉換為大於等於或者小於等於語句

2、選擇訪問路徑

• 全表掃描
• 通過rowid對錶進行訪問
• 索引掃描

3、選擇聯結方法

• 巢狀迴圈聯結 nested-loop join（驅動表每行資料都要讀取內部表的所有行）
• 雜湊聯結 hash join
• 分類合併聯結 sort-merge join

4、選擇聯結次序

聯結方式消除最大數目的行資料

 

CBO缺點：

• 版本不穩定帶來計劃不穩定，使用stored outline
• 有時候不是最好的，可以瞭解應用的人強制hint
• 依賴統計資料

收集統計資料：

DBMS_stats

必須手工收集統計資料：

• 外部表
• 需要收集系統統計資料
• 大型作業之後

可以收集資料庫、表、索引、使用者模式

gather_database_statistics

gather_schema_statistics

gather_table_statistics

gather_index_statistics

 

estimate_percent--統計的百分比，如果是null就是全部

method_opt執行是否收集直方圖

granularity ：all 所有表的子分割槽、分割槽

cascade=yes就是所有的索引統計資料

option

  gather為所有的物件搜尋統計資訊

  gather_auto只對那些ORACLE人為必要的物件收集

  gather_empty為沒有收集過的收集統計資訊

gather_stale只對那些過期的收集

 

收集系統統計資料

DBMS_STATS.GATHER_SYSTEM_STATS

包括IO 和CPU（無負荷模式和負荷模式）

 

收集資料字典統計資料

DBMS_STATS.gather_database_stats  中gather_sys=yes

DBMS_STATS.gather_dictionary_stats

 

動態取樣optimizer_dynamic_sampling在表中不存在統計資料或者資訊過舊的時候，讓ORACLE即時估算優化程式統計資料。

 

 

高效SQL

• 使用函式索引
• 使用右聯結
• 使用case
• 執行高效的子查詢
• 使用where代替having
• 最小化表查詢

聯結方法：

• 避免笛卡爾聯結
• 巢狀迴圈
• 雜湊聯結
• 合併聯結

索引：

對什麼進行索引

• 對高選擇行進行索引
• 對重要的外來鍵
• 對所有的謂詞
• 表聯結的列

B樹索引

點陣圖索引

IOT索引組織表

反向索引

函式索引

組合索引

 

使用繫結變數

避免全表掃描

 

DBA：

分割槽表

壓縮技術

物化檢視

使用儲存大綱保證穩定性

 

SQL計劃管理SPM

SQL baseline

並行執行

 

效能分析工具

explain plan

utlxplan.sql--建立分析表

utlxpls.sql格式化

 

autotrace

1. utlxplan.sql--建立分析表
2. plustrce.sql授權

 

SQLTRACE+tkprof

第三方工具

 

端對端跟蹤

DBMS_MONITOR

需要三個屬性：

客戶端標示符

服務名稱

模組名稱 服務名稱以及活動名稱的組合

利用觸發器鋪貨客戶端連線到DB的sessionID

 

 

結果快取記憶體

客戶端快取記憶體

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