简单讨论在11G，10G中如何稳定SQL执行计划（一）

最新推荐文章于 2021-06-20 08:16:38 发布
最新推荐文章于 2021-06-20 08:16:38 发布
阅读量4.5k 收藏 1
点赞数
分类专栏： Oracle优化之SQL 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dbcrocodile/article/details/20605073
版权

首先，什么是SQL的执行计划，包括为什么一个SQL会有多个不同执行计划等类似问题，我这里就不做讨论了，各位可以网上百度

在这里，我主要讨论一下，当一个常用的SQL，执行计划忽然发生改变，我们如何最快速度的将其执行计划稳定为平日使用的合理执行计划。

11G现在比较多，我们先讨论11G

11G，有个新特性，或者说一个新的功能包（package) ,SPM(Sql Plan Management)

有两个初始化参数与其相关

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean	 FALSE
optimizer_use_sql_plan_baselines     boolean	 TRUE

我们用来稳定执行计划的，就是SPM中的SQL baseline。

第二个参数很好理解

第一个参数，则是是否开启捕获baseline ，什么意思呢

     当一个新SQL 执行，并产生执行计划时，如果 第一个参数为true时，oracle会自动为其产生一个baseline，该baseline对应这个执行计划（

     当该SQL因为统计信息，或什么其他东西 而导致执行计划发生改变时，那么会执行一次，并产生一个新的baseline，但是下一次再执行时，依旧会使用第一个baseline（起到稳定执行计划的作用），只有当DBA确认 第二个执行计划确实比第一个好时，DBA可以通过DBMS_SPM包进行调整，使用第二个执行计划产生的basaline，那么以后该sql的执行计划就会使用第二个执行计划。


默认，我们捕获baseline是关闭的，所以一个SQL在执行时，不会有baseline产生。

我们通过从library cache中load一个SQL合理的执行计划 为该SQL的baseline，从而保证其计划稳定

SQL> create table haha(a varchar2(30),b number);

Table created.

SQL> insert into haha  select object_id,object_id from dba_objects;

75407 rows created.

SQL> create index haha_idx on haha(a);

Index created.


我们创建了一个表，插入一些数据，并在列上创建一个索引 

SQL>exec dbms_stats.gather_table_stats('SYS','HAHA',NO_INVALIDATE =>FALSE);

PL/SQL procedure successfully completed.

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select sql_id,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%haha where%';


SQL_ID	      CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
auwza0aq10mk0          0	1624320650
select * from haha where a='10000'

我们收集了统计信息，并且执行语句select * from haha where a=10000;

通过查v$sql视图获取它的sql_id,child_number,我们现在查看执行计划

QL> select * from table(dbms_xplan.display_cursor('auwza0aq10mk0',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auwza0aq10mk0, child number 0
-------------------------------------
select * from haha where a='10000'


Plan hash value: 1624320650


----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       |       |       |     2 (100)|	       |
|   1 |  TABLE ACCESS BY INDEX ROWID| HAHA     |     1 |    11 |     2	 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | HAHA_IDX |     1 |       |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("A"='10000')


19 rows selected.

这个执行计划使用了索引，执行计划是良好的。

现在我们通过修改统计信息，优化器模式，改变执行计划（产生新的执行计划并使用它，这个执行计划是不良好的，也即是错误的执行计划）。

SQL> alter session set optimizer_mode=first_rows_1;

PL/SQL procedure successfully completed.

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> exec dbms_stats.set_index_stats(ownname=>user,indname=>'HAHA_IDX',NUMROWS=>1,numdist=>1,no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select sql_id,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%haha where%';

SQL_ID	      CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
auwza0aq10mk0		 0	1624320650
select * from haha where a='10000'

auwza0aq10mk0		 1	3694936490
select * from haha where a='10000'


我们可以看到，这里已经产生了两个不同的执行计划，注意看PLAN_HASH_VALUE列。

我们来查询一下新产生的PLAN

SQL> select * from table(dbms_xplan.display_cursor('auwza0aq10mk0',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 1
-------------------------------------
select * from haha where a='10000'

Plan hash value: 3694936490

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"='10000')


18 rows selected.

这个新的PLAN，为全表扫描，是不正确的执行计划

现在我在当前环境下执行该sql，会一直采用这个错误的plan，在业务系统中是很致命的。

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 1
-------------------------------------
select * from haha where a='10000'

Plan hash value: 3694936490

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"='10000')


18 rows selected.


那么我想将正确的执行计划稳定上，该如何做？

通过使用11G的新包，DBMS_SPM就可以实现

SQL BASELINE有两个状态，一个是ENABLED，一个是ACCEPTED，两个都为true，oracle才会使用，如果有同一个SQL的多个SQL_BASELINE存在，且状态都为true，那么会根据成本大小进行判断。（注：当自动捕获开启时，一个SQL，每产生一个不同的执行计划，都会产生一个baseline，enabled都为true，但只有第一个获取的baseline，accepted才为true，其他都为false）

SQL>variable cnt number; 
SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'auwza0aq10mk0',plan_hash_value=>1624320650);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, sql_text, plan_name, enabled,accepted FROM dba_sql_plan_baselines;

SQL_HANDLE		       SQL_TEXT 									PLAN_NAME		       ENA ACC
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- ---
SQL_32507237f9e5cfe6	       select * from haha where a='10000'						SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YES

现在，我们再从当前环境下执行该SQL 

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 3
-------------------------------------
select * from haha where a='10000'

Plan hash value: 1624320650

----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       |       |       | 14685 (100)|	       |
|   1 |  TABLE ACCESS BY INDEX ROWID| HAHA     |     1 |    11 | 14685	 (1)| 00:02:57 |
|*  2 |   INDEX RANGE SCAN	    | HAHA_IDX |     1 |       |   178	 (0)| 00:00:03 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"='10000')

Note
-----
   - SQL plan baseline SQL_PLAN_34n3k6zwybmz6dd4bd292 used for this statement



执行计划已经变为了正常的索引扫描，注意Note，说明有一个SQL_BASELINE在使用。


如果我们不想使用该SQL_BASELINE了，该如何呢？

可以有两个方法实现

第一、将该BASELINE的属性，Enabled，Accepted 改为false（Accepted在11r2里面改不了，只能改enabled）

第二、将该BASELINE删除(SQL_SPM.DROP_SQL_PLAN_BASELINE）

我们来一一演示

第一、修改属性

SQL>  select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE		       SQL_TEXT 			   PLAN_NAME			  ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SQL_32507237f9e5cfe6	       select * from haha where a='10000'  SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YES

SQL>variable cnt number; 
SQL>EXECUTE :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle  =>'SQL_32507237f9e5cfe6' ,plan_name   =>'SQL_PLAN_34n3k6zwybmz6dd4bd292',attribute_name=>'enabled',attribute_value =>'NO');

PL/SQL procedure successfully completed.

SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE		       SQL_TEXT 			   PLAN_NAME			  ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SQL_32507237f9e5cfe6	       select * from haha where a='10000'  SQL_PLAN_34n3k6zwybmz6dd4bd292 NO  YES

看，我们执行完DBMS_SPM.ALTER_SQL_PLAN_BASELINE之后，通过查询dba_sql_plan_baselines可以看到 Enabled属性已经变成了NO

现在我们执行select * from haha where a='10000';

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 0
-------------------------------------
select * from haha where a='10000'

Plan hash value: 3694936490

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"='10000')


18 rows selected.

看，这里的执行计划走成了全表扫描，而且note也没有了。（baseline不起作用了）



第二、删除sql plan baseline

我们除了修改属性以外，还有一种方法，那就是删除sql plan baseline

现在我们将属性改回（改回不是必须的，不过为了模拟场景而已），并删除该baseline

SQL>variable cnt number; 
SQL>EXECUTE :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle  =>'SQL_32507237f9e5cfe6' ,plan_name   =>'SQL_PLAN_34n3k6zwybmz6dd4bd292',attribute_name=>'enabled',attribute_value =>'YES');

好，现在我们查看，并删除（SQL_SPM.DROP_SQL_PLAN_BASELINE 该函数很简单，只有两个元素（输入值）)

SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE		       SQL_TEXT 			   PLAN_NAME			  ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SQL_32507237f9e5cfe6	       select * from haha where a='10000'  SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YES

SQL>variable cnt number; 
SQL>EXECUTE :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle  =>'SQL_32507237f9e5cfe6' ,plan_name   =>'SQL_PLAN_34n3k6zwybmz6dd4bd292');

PL/SQL procedure successfully completed.


SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;

no rows selected

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 0
-------------------------------------
select * from haha where a='10000'

Plan hash value: 3694936490

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"='10000')


18 rows selected.


我们看到执行计划中note也消失了，且走为全表扫描。



我们上面讨论了11G中 该如何稳定执行计划，

我们在  简单讨论在11G，10G中如何稳定SQL执行计划（二） 中来讨论 如何在10G中稳定执行计划























Croco_Dai
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
稳定ORACLE的执行计划
木乃伊、L
04-19 555
 很多时候可能我们都希望CBO能够帮我们生成正确、高效的执行计划，但是很多时候事实并非如此，可能因为各种各样的原因(如，统计信息不正确或者CBO天生的缺陷等)都会导致生成的执行计划特别的低效。之前的一家公司有一台专门用于批量做数据校验清洗的数据库，每次校验清洗完成数据就会清理掉，统计信息经常会发生较大的变更，之前跑得好好的SQL，可能有时候跑5-6个小时都跑不完了，这时候查看执行计划，发现不正确的...
详解PL/SQL Developer连接本地Oracle 11g 64位数据库
09-09
本文主要讨论如何使用PL/SQL Developer连接到本地的64位Oracle 11g数据库。以下是一步步的详细说明： 1. **登录PL/SQL Developer**： 在开始连接之前，确保已经正确安装了Oracle 11g数据库和PL/SQL Developer。...
oracle 如何稳定执行计划
weixin_34367845的博客
04-17 144
2.5.1automaticsqlprofile调整执行计划 适合sql无法改写或验证改写是否成功的情况 验证： Oracel账号SYS Conn/assysdba; 1>createtablet1(nnumber); 2>declare begin forii...
ORALCE的执行计划稳定性
biti_rainy的专栏
06-29 4341
      什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，
如何保持执行计划的稳定性
lyjiau的专栏
12-17 1260
1. 规划好优化统计信息的收集策略。对于Oracle 10g来说，默认的策略能够满足大部分需求，但是默认的收集策略会过多地收集列上的直方图。由于绑定变量与直方图固有的矛盾，为保持性能稳定，对使用绑定变量的列，不收集列上的直方图；对的确需要收集直方图的列，在SQL中该列上的条件就不要用绑定变量。统计信息收集策略，可以考虑对大部分表，使用系统默认的收集策略，而对于有问题的，可以用DBMS_STATS
[20120104]稳定一条sql语句的执行计划.txt
01-05 60
[20120104]稳定一条sql语句的执行计划.txthttp://www.itpub.net/thread-1495845-1-1.htmlhttp://space.itpub.net/267265/viewspace-72...
sql_ref.rar_SQL REF_ora10g sql r
最新发布
09-20
本资源"sql_ref.rar"包含了一个关于SQL的参考指南，特别关注Oracle 10g版本的SQL语法，这在数据库管理领域是非常重要的知识。 "sql_ref.chm"很可能是一个CHM（Compiled HTML Help）文件，这是一种由Microsoft开发的...
Ora 10G SQL Reference.chm 中文帮助文档
05-27
在Oracle 10g中，SQL（Structured Query Language）被广泛用于与关系型数据库交互，执行数据查询、更新、插入和删除等操作。以下是文档可能涵盖的一些关键知识点： 1. **SQL基础**：包括SELECT语句，用于从数据库中...
Pyora_ExternalCheck_11G_oracle11g_zabbix_
10-04
"Pyora_ExternalCheck_11G_oracle11g_zabbix_"这个标题表明，我们正在讨论的是一个用于Zabbix监控Oracle 11g数据库的特定工具或配置。 描述中提到，我们需要将附件中的"Pyora_ExternalCheck_11G.xml"模板导入到...
Oracle 10g 中文API Oracle Concepts (10g R2) 中文版_V11
12-26
在Oracle 10g中，API的改进和扩展包括对XML的支持增强，提高了性能和可伸缩性，并引入了新的数据类型和编程特性。 《Oracle Concepts (10g R2) 中文版_V11》则是Oracle数据库10g Release 2的官方概念指南，详细阐述...
oracle11g执行计划管理-(讲解如何固定sql的执行计划)
06-21
摘要：本文描述了11g的新特性之一：执行计划管理，介绍了引入该新特性的原因，以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何使用该新特性。 分析了如何固定优化过的执行计划，避免被sql优化器CBO覆盖
【Oracle】看懂执行计划之稳定执行计划
As before 如初
06-20 250
Oracle Hint   Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。 Hint的作用 调整目标SQL使用的优化器类型 调整优化器的优化目标 调整表的访问路径 调整表的连接类型 调整表的连接顺序 提高语句的并行程度 Hint的弊端 Hint是比较"暴力"的一种解决方式，不是很优雅。需要开发人员手工修改代码。 Hint不会去适应新的变化，只是在现有的基础上产生更优的执行计划。 Hint随着数据库
稳定（固定）执行计划
weixin_30587927的博客
11-09 426
sql执行计划为什么会变？ 为什么我们的SQL语句执行计划会改变？如何才能稳定SQL语句的执行计划？要想回答上面的2个问题，我们就要首先知道SQL语句的执行计划是如何产生的，有那些因素影响执行计划的生成，只有了解了这些因素我们才能对症下药，稳定我们的SQL语句执行计划。 我们知道，一条SQL语句他的执行计划可能不止一个，数据库是 如何确定采用那条执行计划的呢？这是由数据库的优化器所决定，它的作...
简单讨论在11G，10G中如何稳定SQL执行计划（二）
老代——Oracle不仅是工作，更是兴趣
03-10 1918
在简单讨论在 11G，10G中如何稳定SQL执行计划（一） 中我们讨论了如何通过SPM中的sql plan baseline 来稳定11G中的执行计划 SPM是11G才有的新特性，那么在11G之前，我们是如何稳定执行计划的？ 通过使用SQL PROFILE，关于SQL profile 的原理之类的，如上一篇一样，自己去百度。这里只讨论实际操作 首先，还是模拟出一个SQL对应出两个不同执行
关于执行计划稳定性（一）
congzhan1377的博客
04-13 230
从oracle8i开始，提供了稳定执行计划的功能。也就是预先将调试好的sql的执行计划保存下来，以后即使sql执行的环境发生了某些变化，比如插入大量数据后重新分析了表，重新生成了数据分布柱状图等，还是可以使用预先保存的执行计划，...
Oracle手动固定SQL执行计划
wrh_csdn的博客
09-06 5226
在运维过程中，我们经常会遇到Oracle由于数据、统计信息等的变动，导致Oracle CBO选择不是我们期望的执行计划的情况，下面记录一下手动固定执行计划的过程。 1、查询每个执行计划消耗的资源情况 SQL> set lines 150 pages 999; SQL> col p_user for 99999 ; SQL> col execs  for 9999999 ; SQL> se
JQ之小白计划二（选择器）
蓝莓味的鱼
10-27 220
参考《锋利的jQuery》 1：基本选择器 <script type="text/javascript"> $(document).ready(function(){ //选择 id为 one 的元素 $('#btn1').click(function(){ $('#one').css("background","#bfa"); ...
oracle固定执行计划
wgz7747147820的博客
05-04 1202
http://blog.itpub.net/26736162/viewspace-2107604/ 不错 参考一下
自我管理数据库-自动的sql调优
12-09
本文主要介绍了自我管理数据库的自动SQL调优功能，重点讨论了ORACLE11G自动SQL调优以及Sushil Kumar产品管理总管数据库可管理性甲骨文公司自我管理的数据库的相关议程和解决方案。文章首先介绍了手动调整所面临的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值