SQL大致流程
1、客戶端程式，透過監聽器將SQL語句傳送到服務端資料庫伺服器
2、服務端資料庫伺服器會觸發一個Server process生成，來對該客戶程式服務
3、Server process得到SQL語句之後把該SQL放入PGA
4、進行語法檢查，如果語法正常則到下一步，否則將錯誤結果返回客戶端
5、把該SQL轉換為ASCII字元，也就是一串數字，對該數字串運用hash函式，得到hash value，假設該hash value是3。
6、進入library cache，在3號bucket上查詢該SQL是否存在。10g和之前版本透過library cache latch來保護。11g開始透過mutex保護。
7、假設找到了，進入軟解析：把object裡指向的plan拿出來執行。
8、假設沒找到，那麼進入硬解析：
8.1、到shared pool裡分割一段連續的記憶體空間，叫一個chunk。使用了shared pool latch來保護。
8.2、把該SQL文字以及對應的控制結構放入該chunk。
8.3、把該chunk掛載到3號bucket上。10g和之前版本透過library cache latch來保護。11g開始透過mutex保護。
8.4、進行語義檢查，檢查t1表是否存在，c1和c2列是否存在
8.5、進行許可權檢查
8.6、生成執行計劃(根據資料字典中的物件的統計資訊，來計算多個執行計劃的cost，從而得到一個最優的執行計劃,這一步涉及到大量的資料運算，從而會消耗大量的CPU資源)
8.7、把執行計劃放入heap裡。
8.8、在對應的object裡放入一個指標，指向plan所在的heap。10g和之前版本透過library cache latch來保護。11g開始透過mutex保護。
8.9、執行該plan
9、當以上第8步的場景中有Baseline(即SQL在Baseline中有執行計劃，不管OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false還是什麼)，則又會有如下小過程
9.1、SQL語句硬解析過程中，CBO(最佳化器)會產生很多個的執行計劃，CBO從中選擇一個成本最低執行計劃。
9.2、基於SQL語句的文字形成一個雜湊值（signature)，透過這個雜湊值來檢查資料字典中是否存在這個SQL對應的SQL Plan Baseline(SQL Plan Baseline儲存在資料字典中)。
9.3、如果基線存在，最佳化器會對剛剛生成的執行計劃和儲存在基線中的執行計劃進行比較。
9.3.1、如果基線中有與剛剛生成的執行計劃一樣的執行計劃，並且被標記為可接受（'accepted'），則直接呼叫基線中的這個執行計劃。
9.3.2、如果基線中沒有與剛剛生成的執行計劃一樣的執行計劃，則使用基線中被標記為'accepted'的多個執行計劃中成本最低的那個執行計劃。
9.3.3、如果基線中沒有與剛剛生成的執行計劃一樣的執行計劃，並且剛剛生成的執行計劃比基線中的執行計劃COST都低，這個新生成的執行計劃被標記為'not-accepted'並儲存在基線中。直到這個執行計劃被演化且驗證後才會被考慮使用，即標記為accepted。
9.4、如果基線不存在，說明該SQL語句還沒有重複執行過，如果OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true則又有如下過程。
9.4.1、如果該SQL是第一次執行，則使用剛剛生成的執行計劃，該執行計劃不會放入基線
9.4.2、如果該SQL是第二次執行和第一次執行計劃一樣，則使用第一次的執行計劃，並把第一次的執行計劃放入基線並且記為可接受（'accepted'），並直接呼叫基線中的這個執行計劃
9.4.3、如果該SQL是第二次執行和第一次執行計劃不一樣(說明第二次執行遇到了硬解析，比如第一次執行的執行計劃為全表掃描，第二次執行之前對條件欄位建立了索引即DDL操作，第二次執行會硬解析重新生成執行計劃，生成了走索引的執行計劃)，則使用第二次的執行計劃，並把第二次的執行計劃放入基線並且記為可接受（'accepted'），並直接呼叫基線中的這個執行計劃

select
1、所有塊都在db buffer，從這些塊中找到對應的行，則是邏輯讀
2、db buffer中找不到，則需要先從磁碟讀取到db buffer，就是物理讀，再從db buffer中讀取對應的行，就是邏輯讀，所以一次物理讀就一定產生一次邏輯讀
3、如果select過程中發現塊被修改了（資料塊上的SCN 大於等於select程式本身的SCN），就去undo中找，就是一致性讀

insert
寫入的塊原來是半空還全空，如果是半空，要讀出來，如果是全空，則預設不用讀出來(因為空塊怎麼讀呢，物理讀這個塊怎麼讀不知道啊，但是有文件說insert時要讀segment header，因為insert不可能直接寫磁碟，要先把空塊讀到記憶體裡面來，在記憶體裡面改，然後再寫回磁碟資料檔案去)

update、delete
塊寫之前，必須先把該塊給讀出來,因為要寫一份到UNDO啊

軟解析
為什麼軟解析沒有表物件、列物件、許可權的檢查，因為SQL引用的物件發生了DDL和DCL，就要重新生成執行計劃，所以這些步驟一定是在硬解析的步驟。

軟軟解析
同一條SQL被同一個session執行N次以後，該SQL就會一直存在於PGA的opened cursor區域裡。下次該同一個session執行同一條SQL的時候，就不需要重新生成metadata。
軟軟解析兩種情況下發生
一、設定相關引數session_cached_cursors；
二、寫程式的時候使用靜態遊標，將解析移到迴圈之外，就是一次解析，多次執行。declare xx cursor is …begin loop ..end loop end;。

SQL Plan Management automatically control SQL plan evolution by maintaining what is called "SQL Plan Baseline"
SPM即SQL Plan Management，11的SPM使用SQL Plan Baseline取代了SQLprofile(10g)、Outline(9i)的功能，使用場景其實並不多，主要用於特定的SQL，一個SQL可能在SQL Plan Management中存在多個不一樣的執行計劃。
相關引數：optimizer_capture_sql_plan_baselines(預設false)、optimizer_use_sql_plan_baselines(預設true)
相關檢視：DBA_SQL_PLAN_BASELINES
相關包：DBMS_SPM

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.
alter system set optimizer_capture_sql_plan_baselines=true；將該引數設定為TRUE將開啟自動標識可重複SQL 語句，以及自動為此類語句建立計劃歷史記錄的功能。
OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.

DBA_SQL_PLAN_BASELINES displays information about the SQL plan baselines currently created for specific SQL statements.
DBA_SQL_PLAN_BASELINES顯示有關當前為特定SQL語句建立的SQL計劃基準的資訊。
欄位值說明
ENABLED：表示計劃已啟用，可供最佳化程式使用。如果未設定ENABLED，則系統將不考慮此計劃。
ACCEPTED：表示將計劃更改為ACCEPTED 時計劃已經過驗證為有效計劃（系統自動進行的或使用者手動進行的）。
計劃必須為ENABLED和ACCEPTED，最佳化程式才會考慮使用它。

DBMS_SPM程式包支援手動管理SQL計劃基線。使用此程式包，可以將SQL計劃從遊標快取記憶體或現有的SQL最佳化集(STS)直接載入到SQL計劃基線中。

A SQL plan baseline contains one or more accepted plans
The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time. Because only accepted plans are in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.
SQL計劃基準包含一個或多個接受的計劃
計劃歷史是最佳化器隨著時間的推移為SQL語句生成的一組計劃，被接受和不被接受。由於只有接受的計劃在SQL計劃基準中，基線中的計劃形成計劃歷史的一個susbset。例如，在最佳化器為SQL計劃基準生成第一個可接受的計劃之後，後續計劃是計劃歷史的一部分，但不是計劃基線的一部分。

DBA_SQL_PLAN_BASELINES可以查出accepted和not accepted，所以上面的概念和圖感覺有點糊塗了，所以個人直接把plan history和plan baseline統稱基線

如果使用的是自動計劃捕獲，則第一次將某個SQL語句標識為可重複時，其最佳成本計劃將被新增到對應的SQL計劃基線中並且記為可接受（'accepted'）。然後該計劃將用於執行相應的語句。
第一次將某個SQL語句標識為可重複時，說明這個SQL是第二次執行，第一次和第二次執行可能不一樣，把最佳的執行計劃放入基線並標記為accepted。
如果是自動計劃捕獲的執行計劃，第一次將某個SQL語句標識為可重複時，該SQL的執行計劃放入plan history也放入baseline

Baseline實驗場景和總結
1.執行兩次SQL，這樣baseline裡面就有了執行計劃走的全表掃描，再建立索引再第三次執行SQL，baseline裡面新增了一個執行計劃走索引，但是第三次執行SQL卻走baseline裡面的全表掃描的執行計劃
2.執行一次SQL，再建立索引再第二次執行SQL，baseline裡面就有了執行計劃走的索引掃描
2.1、set optimizer_capture_sql_plan_baselines =false後，如果索引還在，則以後的SQL還是會使用基線裡的走索引的執行計劃
2.2、set optimizer_capture_sql_plan_baselines =false後，如果drop 掉索引，則以後的SQL不會使用基線裡的走索引的執行計劃，直接全表掃描了
3.建立索引執行一次SQL，再刪除索引再第二次執行SQL，baseline裡面就有了執行計劃走的全表掃描，set optimizer_capture_sql_plan_baselines=false後，以後執行該SQL還是會使用基線裡的走全表的執行計劃，如果重新建立索引，以後執行該SQL仍是會使用基線裡的走全表的執行計劃，除非對執行計劃進行evolve

場景1
開一個session：session 1
connect hr/hr
create table emp as select * from employees;
alter session set optimizer_capture_sql_plan_baselines=true;
select * from EMP where employee_id=100;

再開一個session：session 2，監控SPM：
select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP where employee_id=100';
沒有結果。

然後回到session 1，再次執行一次：
select * from EMP where employee_id=100;

再次回到session 2，檢查SPM，這時就會發現SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP where employee_id=100';
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.1571E+19 SYS_SQL_a0939ff977b28802
select * from EMP where employee_id=100
SQL_PLAN_a14wzz5vv5202d8a279cc AUTO-CAPTURE YES YES NO YES

回到session 1，執行：
create index idx_emp on emp(employee_id);
select * from EMP where employee_id=100;

回到session 2，檢查SPM，這時就會發現2個SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP where employee_id=100';
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.1571E+19 SYS_SQL_a0939ff977b28802
select * from EMP where employee_id=100
SQL_PLAN_a14wzz5vv5202b8c51770 AUTO-CAPTURE YES NO NO YES
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.1571E+19 SYS_SQL_a0939ff977b28802
select * from EMP where employee_id=100
SQL_PLAN_a14wzz5vv5202d8a279cc AUTO-CAPTURE YES YES NO YES

再次回到session 1，檢查執行計劃：
set autotrace traceonly exp;
select * from EMP where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 108 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 108 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_a14wzz5vv5202d8a279cc" used for this statement
很明顯，使用的是accepted為Yes的執行計劃，而且並沒有使用索引。

回到session 2，對執行計劃進行evolve：
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_a0939ff977b28802');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP where employee_id=100';
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.1571E+19 SYS_SQL_a0939ff977b28802
select * from EMP where employee_id=100
SQL_PLAN_a14wzz5vv5202b8c51770 AUTO-CAPTURE YES YES NO YES
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.1571E+19 SYS_SQL_a0939ff977b28802
select * from EMP where employee_id=100
SQL_PLAN_a14wzz5vv5202d8a279cc AUTO-CAPTURE YES YES NO YES
注意原來那個accepted為NO的現在變成YES了。

再次回到session 1，執行相同的SQL並檢視執行計劃：
set autotrace traceonly exp;
select * from EMP where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 306890541
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 133 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_a14wzz5vv5202b8c51770" used for this statement
可見走了索引

場景2
開一個session：session 1
connect hr/hr
create table emp2 as select * from employees;
alter session set optimizer_capture_sql_plan_baselines=true;
set autotrace traceonly exp;
select * from EMP2 where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 434478478
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP2 | 1 | 133 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
set autotrace off
select * from EMP2 where employee_id=100;

再開一個session：session 2，監控SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP2 where employee_id=100';
沒有結果。

然後回到session 1，再次執行一次：
create index idx_emp2 on emp2(employee_id);
select * from EMP2 where employee_id=100;

再次回到session 2，檢查SPM，這時就會發現SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP2 where employee_id=100';
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
3.1102E+18 SQL_2b29a9b0e788af7b
select * from EMP where employee_id=100
SQL_PLAN_2qad9q3msjbvv3f95bf73 AUTO-CAPTURE YES YES NO YES

回到session 1，執行：
set autotrace traceonly exp;
select * from EMP2 where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 4159171873
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 133 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_2qad9q3msjbvv3f95bf73" used for this statement
如果SQL第二次執行和第一次執行計劃不一樣時，則使用第二次的執行計劃，並把第二次的執行計劃放入基線並且記為可接受（'accepted'），並直接呼叫基線中的這個執行計劃

場景3
開一個session：session 1
connect hr/hr
create table emp3 as select * from employees;
create index idx_emp3 on emp3(employee_id);
alter session set optimizer_capture_sql_plan_baselines=true;
set autotrace traceonly exp;
select * from EMP3 where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 488428923
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP3 | 1 | 133 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP3 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
set autotrace off
select * from EMP3 where employee_id=100;

再開一個session：session 2，監控SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP3 where employee_id=100';
沒有結果。

然後回到session 1，再次執行一次：
drop index idx_emp3;
set autotrace traceonly exp;
select * from EMP3 where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2425169977
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP3 | 1 | 133 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
set autotrace off
select * from EMP3 where employee_id=100;

再次回到session 2，檢查SPM，這時就會發現SPM：
SQL> select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines where sql_text like 'select * from EMP2 where employee_id=100';

SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
3.1102E+18 SQL_e6520c5526ecf2c9
select * from EMP where employee_id=100
SQL_PLAN_fcnhcanmftwq94cf51402 AUTO-CAPTURE YES YES NO YES

回到session 1，執行：
set autotrace traceonly exp;
select * from EMP3 where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2425169977
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP3 | 1 | 133 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_fcnhcanmftwq94cf51402" used for this statement
如果SQL第二次執行和第一次執行計劃不一樣時，則使用第二次的執行計劃，並把第二次的執行計劃放入基線並且記為可接受（'accepted'），並直接呼叫基線中的這個執行計劃

SQL大致流程、SPM、軟軟、軟、硬解析

相關文章