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'），并直接调用基线中的这个执行计划

ITPub博客

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

最新文章