Oracle 學習之性能優化（一）SQL語句處理

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

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

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

• 優化--生成一個可在數據庫中用來執行語句的最佳計劃

• 行資源生成--為會話取得最佳計劃并建立執行計劃

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

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

一、分析：

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

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

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

SQL> conn scott/tiger
Connected.
SQL> select x from dual;
select x from dual
       *
ERROR at line 1:
ORA-00904: "X": invalid identifier


SQL> 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中是否有相同的語句。

  一旦找到，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/a
Connected.
SQL> create table emp (id int);

Table created.
SQL> select * from emp;

no rows selected

4. 啟用另一個會話

SQL> conn b/b
Connected.
SQL> create table emp (id int);

Table created.
SQL> select * from emp;

no rows selected
SQL> select * from emp;

no rows selected

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

SQL> SET LINESIZE 200
SQL> COL SQL_TEXT FOR A50
SQL> SELECT address, executions, sql_text
      FROM v$sql
     WHERE UPPER (sql_text) LIKE 'SELECT * FROM EMP';

ADDRESS 	 EXECUTIONS SQL_TEXT
---------------- ---------- --------------------------------------------------
00000000893DF470	  2 select * from emp
00000000893DF470	  1 select * from emp

SQL>

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

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

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

SQL> conn / as sysdba
Connected.
SQL> alter session set optimizer_mode=ALL_ROWS;

Session altered.

SQL> SELECT * FROM A.EMP;

no rows selected

SQL> alter session set optimizer_mode=FIRST_ROWS;

Session altered.

SQL> SELECT * FROM A.EMP;

no rows selected

查看sql解析情況

SQL> SET LINESIZE 200
SQL> COL SQL_TEXT FOR A50
SQL> 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.EMP
0000000091CD7810	  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）

 優化就是根據優化法則，生成各種各樣的執行計劃，并且選擇一個最好的執行計劃。

 行資源生成器是一個軟件，就是將執行計劃轉化成其他部分可以利用的數據結構。

三、執行

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