提升Oracle所需的基礎技能____Oracle

用了幾年的Oracle，對一些日常的開發和最佳化技術已經所有耳聞，簡單羅列一些

1：單表的查詢效能

要看資料的量，查詢條件，是否命中主鍵、索引；如果資料量在幾百萬，幾乎不需要考慮；

如果幾千萬到億的資料量，如果不走索引，查詢的效能可想而知；

 

當然查詢記錄的資料量跟整表的資料量對比，如果查詢的結果跟總量對比比較大時，避免全部掃描

如果幾乎相當，全部掃描未必是效率低下。

 

2：多表的查詢效能

需要考慮更多因素

全部掃描訪問方式【全表掃描，或者快速全索引掃描】

索引掃描訪問方式

關註：oracle常用的索引類型

B樹索引：適合資料重複率低的列

位元影像索引：適合資料重複率高的列

 

索引掃描機制：

索引範圍掃描，索引唯一掃描，索引全掃描，索引跳躍掃描，索引快速全掃描。

當然索引的掃描離不開索引統計資訊：聚簇因子(Clustering factor)的統計資訊用來協助最佳化器產生使用索引的成本資訊。

需要識別索引掃描的情境：

索引唯一掃描：當謂詞中包含使用UNIQUE或PRIMARY KEY索引作為條件的時候，就會選用索引唯一掃描。

索引範圍掃描：當謂詞中返回一定範圍資料的條件時，就會選用索引範圍掃描---比如<,>,LIKE,BETWEEN

索引全掃描：當沒有謂詞但是所需擷取列的列表可以通過其中一列的索引來獲得，謂詞中包含一個位於索引中非引導列上的條件。

索引跳躍掃描：當謂詞中包含位於索引中非引導列上的條件，並且引導列的值唯一的時候，會選擇索引跳躍掃描。

 

多個表之間的聯結方法，最佳化器確定如何將多個表串連起來的最佳方法以及最恰當的順序。

多個表之間的查詢，如果沒有指定關聯關係，會隱含式地將多個表之間的資料一一聯結，稱為笛卡兒連接。

 

多表串連的方式包括：

嵌套迴圈連接：使用一次訪問運算所得到的結構集中的每一行來於另一個表進行對碰；如果結構集的大小是有限的，並且在用來連接的列上建有索引的話，連接的效率比較高。

排序-合并連接：獨立地讀取需要串連的兩張表，對每張表中的資料行（where字句中的資料行）按照連接鍵進行排序，然後對排序後的資料行進行合并。

排序開支比較大，但是合并的過程較快。

嵌套連接：

笛卡爾連接：將兩個表的結果一一相乘。

外連接：返回一張表的所有行以及另一張連接表中滿足連接條件的行資料，通過(+)

 

3：理解SQL的本質

關於集合的，並非類似程式設計語言是面向過程的

比如：

Union:為將兩個集合的結構合計起來，但是會去掉重複的行

Union All:則返回所有行，包括重複的行記錄資料

當然Oracle 10G，可以通過HASH Unique運算子來去重重複行

Minus：通常替代NOT Exists,反連接

Intersect:通常替代EXISTS(半連接）,擷取兩個集合中都存在的資料行集。

 

4：關注SQL的執行計畫

從前面的索引和連接方式，以及SQL的集合的本質，如果想深入最佳化資料查詢的效能，必須關注執行計畫

PL/SQL Developer可以清楚的分析執行計畫，或者通過SQLPlus來擷取執行計畫，當然運行前的執行計畫，跟運行後的實際執行情況，會有略微區別。

需要關註：

解釋計劃：通過Explain plan用來顯示最佳化器為SQL語句所選擇的執行計畫，一個預期執行計畫

                  包括SQL所引用的表、

                  訪問表的方法

                  對每一對需要連接的資料來源所用的連接方法

                 按次序列出的所有需要完成的運算

                計劃中各步驟的謂詞資訊列表

                對於每個運算，估計出該步驟所要操作的資料行數和位元組數

               對於每個運算，計算出成本值

              如果適用，所訪問的分區資訊

             如果適用，並存執行的相關資訊

可以通過explain plan for (sql).

對應的結果儲存在PLAN_TABLE中

dbms_xplan.display函數

一個問題不可忽視：解釋計劃基於適用環境，跟最終運行環境可能存在差異；

不考慮綁定變數的資料類型

不“窺視”綁定變數的值

所以可能導致，解釋計劃跟最終的執行計畫有不同。

執行計畫

學會閱讀的技巧

以及擷取最種的實際的執行計畫

通過V$SQL_PLAN來查看計劃運算，跟PLAN_TABLE類似，但是包括一些如何在庫快取中定位並找出當前所執行語句的列

ADDRESS

HASH_VALUE

SQL_ID

PLAN_HASH_VALUE

CHILD_ADDRESS

CHILD_NUMBER

DBMS_XPLAN

執行個體：select /*recentsql */ sql_id,child_number,hash_value,address,executions,sql_text from v$sql where parsing_user_id=...

查看相關執行計畫

select /*+ gather_plan_statistics*/ empno,ename from scott.emp where ename='Test';

如果有興趣，可以深入分析DBMS_XPLAN

 

 更多精彩會進一步補充......