oracle里面常见的执行计划以及执行计划的调整

一.查看执行计划：

先从开头一直连续往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，考上的先执行。

总结两个字：  右  上 

二.oracle里面常见的执行计划

2.1.与表访问相关的执行计划

全表扫描：(TABLE ACCESS BY ROWID)

rowid扫描：(TABLE ACCESS BY USER ROWID或者TABLE ACCESS BY INDEX ROWID)

2.2.与B树索引相关的执行计划

索引唯一扫描：index unique scan

索引范围扫描：index range scan

索引全扫描：index full scan

索引快速扫描：index fast scan

索引跳跃式扫描：index skip scan

2.3.与位图索引相关的执行计划

2.3.1.位图所以主要用于数据仓库或者DSS系统。

2.3.2.结构：被索引的键值，对应的rowid的下限，对应rowid的上限，位图段。

位图索引的结构就表明了位图索引中锁的粒度是位图段，没有行锁这个概念，但是多个数据行可能对应同一个索引行的位图段，这个锁的粒度就决定了位图索引不适合于高并发且频繁修改的OLTP系统，如果在高并发且频繁修改的OLTP系统中使用了位图索引，很可能导致严重的并发问题，甚至产生死锁。

2.3.3.与btree索引相比，位图索引的优势：

因为位图索引的位图段是压缩后存储的，所以如果被索引列的distinct值较少，会节省很多空间。

位图索引能够快速处理一下包含各种and或者or查询条件的sql，因为位图索引能够实现快捷的按位运算。

2.3.4.相关执行计划： 位图索引单键值扫描，位图索引范围扫描，位图索引全扫描，位图索引快速全扫描，位图按位与，位图按位或，位图按位减等。

2.4.与表连接相关的执行计划

排序合并连接--sort join和 merge join。 

嵌套循环连接--nested loops

哈希连接--hash join

反连接--ant1

半连接---sem1

三.oracle里执行计划的稳定

oracle11g推出了SPM(SQL Plan Management)，SPM是一种主动的稳定执行计划的手段，能够保证只有被验证过得执行计划才会被启用。当启用了SPM之后，每一个sql都会存在对应的sql plan baseline，这个sql plan baseline里面存储的就是该sql的执行计划，可以从DBA_SQL_PLAN_BASELINES查看目标sql的所有sql plan baseline。

在oracle11g机及其以上的版本中，有两种方法可以产生目标sql的sql plan baseline

自动不活。

手工生成/批量导入。(批量导入适用于oracle数据库大版本的升级)

参数 OPTIMIZER_CAPTURE_SQL_BASELINES用于控制是否开启自动捕获sql plan baseline，默认值为false。

这个值可以再session和系统级别动态修改，当我们将其改为TRUE后，oracle会对参数影响范围内所有重复执行的sql自动捕获其sql plan baseline。

参数OPTIMIZER_USER_SQL_PLAN_BASELINES用于控制是否启用SQL plan Baseline，其默认值为true，表示在默认情况下，oracle在生成执行计划时就会启用SPM，使用已有的sql plan baseline，这个参数也可以在session和系统级别动态修改。

就是说oracle默认不会捕获sql base baseline，会默认固定执行计划。

sql plan baseline有两个值ENABLED和ACCEPTED,当新产生的sql plan baseline的这两个值都为yes的时候，sql的执行计划才会采纳这个新的sql plan baseline.

实用一.我们想启用新的执行计划

11gr2：

前提是我们已经启用自动捕获：optimizer_capture_sql_plan_baselines，并且已经生成了新的sql base baseline。然后我们想使用新的执行计划。

就是先将我们新的sql base baseline的accepted改为yesyes，然后把原来sql base baseline的ENABLED改为NO。

var temp number

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将目标sql新的执行计划将accepted改为yes。

exec :temp := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => '',plan_name=>'',verify=>'NO',commit=>'YES');

DBMS_SPM.ALTER_SQL_PLAN_BASELINE将原来的执行计划所对应的SQL PLAN BASELINE的enabled的值设为no。

exec :temp :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'',plan_name=>'',attribute_name='ENABLED',attribute_values => 'NO');

实用二.当执行计划突然改变，如果重新绑定为之前的执行计划

(前提是之前的执行计划还在内存中)

-- 1、查看plan_hash_value是否在内存中存在：
select plan_hash_value from v$sql where sql_id = '&sql_id' group by plan_hash_value;

-- 2、plan_hash_value存在则固定
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'69ux4s03mvr53',PLAN_HASH_VALUE=>3236094840,FIXED=>'YES',ENABLED=>'YES');
END;
/

select sql_id,plan_hash_value,SQL_PLAN_BASELINE,child_number from v$sql where sql_id='&sql_id';

实用三.手工生成SQL Plan Baseline

还是调用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

3.3.1.针对目标sql使用DBMS_SPM_LOAD_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始化执行计划所对应的sql plan baseline。

dbms_spm.load_plans_cursor_cache

(

sql_id => '原目标sql的sql文本'

plan_hash_value => 原目标sql的plan hash value

)

3.3.2.改写目标sql的sql文本，在其中加入合适的hint，直到加入hint后所改写的sql能走出我们想要的执行计划，然后对改写的sql使用包手工生成新的执行计划所对应的sql plan baseline。

dbms_spm.load_plans_from_cursor_cache

(

sql_id => '加入合适的hint后改写的sql id',

plan_hash_value => '加入合适hint后改写sql的plan hash value',

sql_handle => '原目标sql在步骤1中所产生的sql plan baseline的sql_handle'

)

3.3.3.使用DBMS_SPM.DROP_SQL_PLAN_BASELINE删除在步骤1中手工生成的原目标sql的初始执行计划所对应的SQL plan baseline。

dbms_spm.drop_sql_plan_baseline

(

sql_handle => '原目标sql在3.3.1中所产生的sql plan baseline的sql_handle'

plan_hash_value => ''加入合适hint后改写的sql的plan hash value

sql_handle => '原目标sql在步骤1中所产生的sql plan baseline的sql_handle'

)

下面自己做一个手动生成sql plan baseline的示例：

第一步.先手工生成初始执行计划

SQL> select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10';

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5ph4uwu6m55aw, child number 0
-------------------------------------
select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10'

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |

================================================

SQL_ID 5ph4uwu6m55aw

Plan hash value: 3383998547

SQL> var temp number
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '5ph4uwu6m55aw',plan_hash_value => 3383998547);

PL/SQL procedure successfully completed.

select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(dept PK_DEPT) */ * from dept%';

SQL_eb3a5ffc38442091           SQL_PLAN_fqfkzzhw4884j0e23be79 MANUAL-LOAD    YES YES select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10'

我们看到，目标sql的初始执行计划所对应的sql plan baseline已经成功生成，其对应的sql_handle为SQL_eb3a5ffc38442091，plan_name为SQL_PLAN_fqfkzzhw4884j0e23be79

第二步.改写目标sql

SQL> select /*+ index(dept PK_DEPT) */ * from dept where deptno='10';

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0mpwgns2uh7uh, child number 0
-------------------------------------
select /*+ index(dept PK_DEPT) */ * from dept where deptno='10'

Plan hash value: 2852011669

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

===========================================================

SQL_ID 0mpwgns2uh7uh

Plan hash value: 2852011669

初始执行计划对饮的sql handel：SQL_eb3a5ffc38442091

SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '0mpwgns2uh7uh',plan_hash_value => 2852011669,sql_handle => 'SQL_eb3a5ffc38442091');

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(dept PK_DEPT) */ * from dept%';

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_eb3a5ffc38442091 SQL_PLAN_fqfkzzhw4884j0348d329 MANUAL-LOAD YES YES select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10'
SQL_eb3a5ffc38442091 SQL_PLAN_fqfkzzhw4884j0e23be79 MANUAL-LOAD YES YES select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10'

我们现在看到，改写过的sql的新执行计划所对应的的sql plan baseline已经成功生成。

第三部：DROP掉原执行计划

SQL> var temp number
SQL> exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_eb3a5ffc38442091',plan_name => 'SQL_PLAN_fqfkzzhw4884j0e23be79');

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(dept PK_DEPT) */ * from dept%';

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_eb3a5ffc38442091 SQL_PLAN_fqfkzzhw4884j0e23be79 MANUAL-LOAD YES YES select /*+ no_index(dept PK_DEPT) */ * from dept where deptno='10'

可以看到，现在只剩下改写过的sql对应的执行计划对应的sql plan baseline。

重新执行sql

select /*+ index(dept PK_DEPT) */ * from dept where deptno='10';

现在就是走索引。