Oracle 學習之效能最佳化（一）SQL語句處理

標籤：sql 語句 處理流程

  當向Oracle提交一個sql命令時，Oracle到底做了哪些事情？對這個問題有很好的理解，能協助你更好的分析sql語句的最佳化。

  執行一條sql語句從開始到結束，需要經曆4個步驟：

• 分析--對提交的語句進行文法分析、語義分析和共用池檢查。

• 最佳化--產生一個可在資料庫中用來執行語句的最佳計劃

• 行資源產生--為會話取得最佳計劃並建立執行計畫

• 語句執行--完成實際執行查詢的行資源產生步驟的輸出。對應DDL來說，這一步就是語句的結   束。對應SELECT來說，這一步是取資料的開始。

 以上步驟，有的是可以省略的，例如最佳化、行資源產生器階段。這樣可以節省大量的時間。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/71/7D/wKiom1XRyoCCObRBAADlo46nyKY605.jpg" title="01.PNG" alt="wKiom1XRyoCCObRBAADlo46nyKY605.jpg" />

一、分析：

  文法分析，sql是否符合文法標準。

SQL> select * form tab;select * form tab         *ERROR at line 1:ORA-00923: FROM keyword not found where expected

  語義分析，假設sql是合法的，但是它有意義嗎？你要訪問的對象，你有存取權限嗎？查詢的列存在嗎？是否存在歧義等待。

SQL> conn scott/tigerConnected.SQL> select x from dual;select x from dual       *ERROR at line 1:ORA-00904: "X": invalid identifierSQL> select * from dba_objects;select * from dba_objects              *ERROR at line 1:ORA-00942: table or view does not exist

  對於DML語句，還有第三步。

  共用池檢查，此語句是否被其他使用者使用過？可以重用已經執行過的工作嗎？如果是，就是軟解析soft parse，如果否，那就是硬解析。

  DDL總是硬解析，語句從不重用。

  Shared pool是SGA中的一部分，用來緩衝以前執行過的sql語句、PLSQL、資料字典內容的緩衝（以行的形式緩衝內容，而buffer cache是以block的方式緩衝內容）以及其他許多資訊，以供會話重用。

 從技術上來說，Oracle的語句解析分為兩種：

• 硬解析--語句通過語句執行的每一個步驟從分析到最佳化，到行資源產生，到語句執行。

• 軟解析--語句通過語句執行的某些步驟，特別是跳過最佳化步驟(最昂貴的步驟)。為了執行軟解析，必須通過兩個步驟。首先Oracle必須進行語義匹配，查看提交給Oracle的語句是否已經被執行過。然後，進行環境匹配。比如一個會話的初始化參數optimizer_mode=ALL_ROWS,一個會話的初始化參數optimizer_mode=FIRST_ROWS,這兩個會話的環境就不一樣。

 

  為了開始這個處理，Oracle必須在Shared pool中尋找語句。為了高效的完成此操作，oracle將每個提交的sql語句，進行hash演算法，產生一個hash_values。oracle使用hash_values尋找Shared pool中是否有相同的語句。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/71/7D/wKiom1XRz4_hzdwHAAEpSG0kcxo710.jpg" title="shared pool check.PNG" alt="wKiom1XRz4_hzdwHAAEpSG0kcxo710.jpg" />

  一旦找到，Oracle將進行語義和環境檢查，sql語句都相同，難道還有語義不同的嗎？我們看下面的例子。

1. 建立兩個使用者 

SQL> create user a identified by a;User created.SQL> create user b identified by b;User created.

2. 賦予使用者權限

SQL> grant connect ,resource to a;Grant succeeded.SQL> grant connect,resource to b;Grant succeeded.

3.啟用一個會話

SQL> conn a/aConnected.SQL> create table emp (id int);Table created.SQL> select * from emp;no rows selected

4. 啟用另一個會話

SQL> conn b/bConnected.SQL> create table emp (id int);Table created.SQL> select * from emp;no rows selectedSQL> select * from emp;no rows selected

5.啟用另一會話，使用sys使用者串連，進行如下查詢。

SQL> SET LINESIZE 200SQL> COL SQL_TEXT FOR A50SQL> SELECT address, executions, sql_text      FROM v$sql     WHERE UPPER (sql_text) LIKE ‘SELECT * FROM EMP‘;ADDRESS  EXECUTIONS SQL_TEXT---------------- ---------- --------------------------------------------------00000000893DF470  2 select * from emp00000000893DF470  1 select * from empSQL>

可見，雖然發出的語句是一樣的，但是語義不同，所以v$sql中會有兩條記錄。b使用者下，相同的語句執行了兩次，因為語義相同，所以是一條記錄，但是executions是2 。

  我們再看看，語義相同，但是環境不同，會是什麼結果。

以上的串連全部退出，建立一個串連進行如下查詢。

SQL> conn / as sysdbaConnected.SQL> alter session set optimizer_mode=ALL_ROWS;Session altered.SQL> SELECT * FROM A.EMP;no rows selectedSQL> alter session set optimizer_mode=FIRST_ROWS;Session altered.SQL> SELECT * FROM A.EMP;no rows selected

查看sql解析情況

SQL> SET LINESIZE 200SQL> COL SQL_TEXT FOR A50SQL> select address,executions,sql_text        from v$sql       where upper(sql_text) like ‘SELECT * FROM A.EMP‘;ADDRESS  EXECUTIONS SQL_TEXT---------------- ---------- --------------------------------------------------0000000091CD7810  1 SELECT * FROM A.EMP0000000091CD7810  1 SELECT * FROM A.EMP

可見，雖然語義相同，但是環境不同，Oracle也會當成2條語句來解析。

  分析總結：

   分析階段做了如下操作，語法檢查、計算散列值、語義檢查、環境檢查、計運算元遊標的散列值等。 此外Oracle還可能做了如下步驟：

  在Shared pool中尋找匹配的hash_value，如果能找到，確認訪問對象的許可權、檢查環境。產生子遊標hash_value。如果子遊標hash_value也能匹配。那麼將跳過最佳化和行資源產生。（此部門我們將在遊標中詳細討論）

二、最佳化和行資源產生

  當所有的DML語句第一次提交給Oracle時，在它的生命週期中至少被最佳化一次。最佳化發生在硬解析中。語義和文法完全相同，並且執行環境也相同的語句的執行可以利用以前的硬解析工作。這種情況下，對他們將進行軟解析。

 最佳化是一個費勁的、CPU密集型的處理，可能花在最佳化上的時間比實際執行還要長。最佳化不單耗cpu，還是導致Shared pool 高栓鎖率。最佳化的規則有兩種

  1. 基於規則的最佳化法則（RBO）

  2. 基於成本的最佳化法則（CBO）

 最佳化就是根據最佳化法則，產生各種各樣的執行計畫，並且選擇一個最好的執行計畫。

 行資源產生器是一個軟體，就是將執行計畫轉化成其他部分可以利用的資料結構。

三、執行

  利用行資源產生器輸出的執行計畫結構，執行具體的步驟。  

 

本文出自 “叮咚” 部落格，請務必保留此出處http://lqding.blog.51cto.com/9123978/1685341

Oracle 學習之效能最佳化（一）SQL語句處理