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';

现在就是走索引。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

上一篇:10、线程停止


下一篇:Python 炫技操作(九):调用函数的九种方法