- 数据库高效优化：架构、规范与SQL技巧
- 马立和 高振娇 韩锋
- 1868字
- 2020-08-27 05:47:11
3.1 概述
3.1.1 什么是执行计划
数据库执行SQL语句是按照一定顺序、分步骤完成的。至于采用怎样的顺序、用什么方法访问数据，是由优化器来决定的。一旦优化器确定好了一个它认为最高效的执行方法，这一系列的顺序、步骤就被称为执行计划。简言之，Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。
下面通过一个示例说明SQL语句如何通过多个步骤处理得到需要的结果集。
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=7900;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"=7900)
5 - access("E"."DEPTNO"="D"."DEPTNO")
1.访问步骤
①Id＝0：操作为SELECT STATEMENT。这一行实际表示语句的类型是一条SELECT语句，而非一个真正的操作。因此在一些执行计划显示当中，没有显示ID＝0的操作。
②Id＝1：操作为NESTED LOOPS，表明以嵌套循环的方式进行表间关联。
③Id＝2：操作为TABLE ACCESS BY INDEX ROWID，而索引上的ROWID则是通过子步骤(ID＝3)来获取的。
④Id＝3：操作为INDEX UNIQUE SCAN，表明对索引进行唯一键值的访问以获取父操作所需要的ROWID。前面带有*号，说明这个操作有相关的谓词条件（访问条件或过滤条件），后面会有详细说明。
⑤Id＝4：操作为TABLE ACCESS BY INDEX ROWID，而索引上的ROWID则是通过子步骤（ID＝5）来获取的。
⑥Id＝5：操作为INDEX UNIQUE SCAN，表明对索引进行唯一键值的访问以获取父操作所需要的ROWID。前面也带有*号。
2.谓词条件
1）3-access("E"."EMPNO"=7900)：这是操作ID＝3的谓词条件，其中access是访问条件，表示通过指定条件定位到了数据。
2）5-access("E"."DEPTNO"="D"."DEPTNO")：这是操作ID＝5的谓词条件，access同样是访问条件，与前面不同的是，这里的条件值不是直接指定的，而是由嵌套循环传入的。
3.执行过程
上面语句的执行是由多个步骤组成的，具体的步骤这里就不详细介绍了，下面仅描述一下整体执行过程。
①首先根据指定的EMPNO=7900的条件，通过索引PK_EMP进行读取。
②根据读取到的索引数据中的ROWID信息，返回查询EMP表，获得其他需要的字段。
③按照上面的方式循环读取整个EMP表，对于获得的每条记录进入内层循环。
④内层循环中，根据传入的DEPTNO的条件，通过索引PK_DEPT进行读取。
⑤根据读取到的索引数据的ROWID信息，返回查询DEPT表，获得需要的字段，然后返回上层循环。
⑥整体循环结束后，返回结果集。
3.1.2 库执行计划存储方式
数据库生成执行计划，是一个开销很大的工作。因此，一般数据库都会采取缓存策略。将生成好的执行计划保存起来，下次可以重用，避免了再次生成产生开销。在Oracle数据库中有一块内存区域称为库高速缓存（它是共享池的一部分）。用户执行的SQL语句或者PL/SQL块，其执行计划会被缓存在这个区域中。当相同的SQL语句或者PL/SQL块再次执行时，就可以直接利用缓存在该区域中的执行计划，而不用再进行昂贵的解析操作。
在Oracle数据库中，每条SQL语句都有一个称为SQL_ID的唯一标识。在对一条SQL语句的解析中，Oracle会查询在库高速缓存中是否存在SQL_ID。如果不存在，则会申请一块内存区域用来保存解析后的结果。在逻辑上，这块内存区域保存的数据结构称为游标。在内存区域中，一部分是与SQL语句相关的，被称为父游标；另一部分是与语句的执行计划相关的，被称为子游标。从名字就可以看出，二者是有主从关系的。对于同一条SQL语句，可能会存在多个子游标，我们称之为不同版本的子游标。不同的子游标的执行计划可能相同，也可能不同，但它们都属于同一个父游标。每个子游标都会被赋予一个序列号，即CHILD_NUMBER。一条语句生成的第一个游标的CHILD_NUMBER为0，相应的Oracle会为每个执行计划生成一个哈希值以作区分。
下面通过一个实例，说明一下。
<user scott>
conn scott/tiger
select empno,ename from emp;
/*
当一条SQL第一次被执行的时候，Oracle会同时产生一个父游标(Parent Cursor)和一个子游标(Child Cursor)
*/
select sql_text,sql_id,version_count
from v$sqlarea
where sql_text like 'select empno,ename%';
SQL_TEXT SQL_ID VERSION_COUNT
-------------------------------------------------- ------------- -------------
select empno,ename from emp 78bd3uh4a08av 1
/*
目标SQL在V$SQLAREA中只有一条匹配记录，且这条记录的VERSION_COUNT的值为1（VERSION_COUNT表示某个Parent Cursor拥有的所有Child Cursor的数量），这说明了Oracle在执行这条SQL时确实只产生了一个Parent Cursor和一个Child Cursor
*/
select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
3956160932 0
/*
从V$SQL中查看所有Child Cursor的信息。根据SQL_ID查询V$SQL，发现只有一条匹配记录，而且这条记录的CHILD_NUMBER的值为0(CHILD_NUMBER表示某个Child Cursor所对应的子游标号)，说明Oracle在执行原目标SQL时确实只产生了一个编号为0的Child Cursor
*/
<user hf>
conn hf/hf
create table emp as select * from scott.emp;
select empno,ename from emp;
select sql_text,sql_id,version_count
from v$sqlarea
where sql_text like 'select empno,ename%';
SQL_TEXT SQL_ID VERSION_COUNT
-------------------------------------------------- ------------- -------------
select empno,ename from emp 78bd3uh4a08av 2
/*
在V$SQLAREA中发现匹配记录的VERSION_COUNTW为2，说明这个SQL语句有一个Parent Cursor和两个Child Cursor
*/
select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
3956160932 0
3956160932 1
/*
查看V$SQL，可以看到CHILD_NUMBER的值分别为0和1，表示有两个Child Cursor。这里产生两个Child Cursor的原因是，虽然上面的SQL语句(“select empno,ename from emp”)看起来是一样的，但是是由两个不同用户执行的，其实是两个完全不同的SQL，所以要生成两个游标，当然其对应的执行计划也就不能共享了，对应着也就有了两个不同的执行计划
*/