当前位置: 开发笔记 > 数据库 > 正文

OracleSQL执行计划变更的问题

作者：白小蛮 | 来源：互联网 | 2014-07-13 17:52

oracle的执行计划经常会遭遇绑定变量窥视问题，导致执行计划变更，引起数据库负载飙升，带来性能问题。昨天有段时间，数据库负载报警，cpu使用率近100%。今天通过awr发现，等待事件第一位的是readbyothers...SyntaxHighlighter.all();

oracle的执行计划经常会遭遇绑定变量窥视问题，导致执行计划变更，引起数据库负载飙升，带来性能问题。

昨天有段时间，数据库负载报警，cpu使用率近100%。

今天通过awr发现，等待事件第一位的是read by other session；SQL Statistics 中几乎都是同一条sql排在第一：56s18gn1k19yp 该sql一个小时内执行了3700多次。

从这种迹象来看，应该是执行计划发生了变更。 www.2cto.com

oracle 10G中可以通过下面的三个视图查询到语句的历史执行信息：

DBA_HIST_SQL_PLAN

DBA_HIST_SQLSTAT

DBA_HIST_SNAPSHOT

查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。

通过下面的sql查询执行计划是否发生变化：

select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time

from dba_hist_sqlstat a, dba_hist_snapshot b

where sql_id =&＃39;56s18gn1k19yp&＃39;

and a.snap_id = b.snap_id

order by instance_number, snap_id;

SQL> select a.snap_id, a.sql_id, a.plan_hash_value,to_char(b.begin_interval_time,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)

2 from dba_hist_sqlstat a, dba_hist_snapshot b

3 where sql_id =&＃39;56s18gn1k19yp&＃39;

4 and a.snap_id = b.snap_id

5 order by snap_id desc; www.2cto.com

SNAP_ID SQL_ID PLAN_HASH_VALUE TO_CHAR(B.BEGIN_INT

---------- ------------- --------------- -------------------

30569 56s18gn1k19yp 947531627 2012-03-02 14:00:30

30558 56s18gn1k19yp 947531627 2012-03-02 03:00:12

30552 56s18gn1k19yp 947531627 2012-03-01 21:00:36

30551 56s18gn1k19yp 2658265176 2012-03-01 20:00:27

30550 56s18gn1k19yp 947531627 2012-03-01 19:00:21

30535 56s18gn1k19yp 947531627 2012-03-01 04:00:31

30527 56s18gn1k19yp 947531627 2012-02-29 20:00:37

30524 56s18gn1k19yp 947531627 2012-02-29 17:00:24

30521 56s18gn1k19yp 947531627 2012-02-29 14:00:11

30519 56s18gn1k19yp 947531627 2012-02-29 12:00:04

30518 56s18gn1k19yp 947531627 2012-02-29 11:00:59

30511 56s18gn1k19yp 947531627 2012-02-29 04:00:11

30510 56s18gn1k19yp 947531627 2012-02-29 03:00:04

30502 56s18gn1k19yp 947531627 2012-02-28 19:00:27

30501 56s18gn1k19yp 947531627 2012-02-28 18:00:22

30500 56s18gn1k19yp 947531627 2012-02-28 17:00:18

30498 56s18gn1k19yp 947531627 2012-02-28 15:00:11

30497 56s18gn1k19yp 947531627 2012-02-28 14:00:07

30491 56s18gn1k19yp 947531627 2012-02-28 08:00:25

30475 56s18gn1k19yp 947531627 2012-02-27 16:00:03

30464 56s18gn1k19yp 947531627 2012-02-27 05:00:06

30463 56s18gn1k19yp 947531627 2012-02-27 04:00:00

30461 56s18gn1k19yp 947531627 2012-02-27 02:00:51

30456 56s18gn1k19yp 947531627 2012-02-26 21:00:31

30448 56s18gn1k19yp 2658265176 2012-02-26 13:00:55

30440 56s18gn1k19yp 947531627 2012-02-26 05:00:33

30439 56s18gn1k19yp 947531627 2012-02-26 04:00:25

30438 56s18gn1k19yp 947531627 2012-02-26 03:00:17

30437 56s18gn1k19yp 947531627 2012-02-26 02:00:09

30428 56s18gn1k19yp 947531627 2012-02-25 17:00:16

30423 56s18gn1k19yp 947531627 2012-02-25 12:00:51

30418 56s18gn1k19yp 947531627 2012-02-25 07:00:05

30416 56s18gn1k19yp 947531627 2012-02-25 05:00:55

30415 56s18gn1k19yp 947531627 2012-02-25 04:00:50

30406 56s18gn1k19yp 947531627 2012-02-24 19:00:52

30398 56s18gn1k19yp 947531627 2012-02-24 11:00:12

30396 56s18gn1k19yp 947531627 2012-02-24 09:00:56

30392 56s18gn1k19yp 947531627 2012-02-24 05:00:23

30391 56s18gn1k19yp 947531627 2012-02-24 04:00:11

30388 56s18gn1k19yp 947531627 2012-02-24 01:00:46

30383 56s18gn1k19yp 947531627 2012-02-23 20:00:15

30380 56s18gn1k19yp 947531627 2012-02-23 17:00:56

30377 56s18gn1k19yp 947531627 2012-02-23 14:00:37

30377 56s18gn1k19yp 2658265176 2012-02-23 14:00:37

我们注意到最近一次3月1号20点左右，执行计划发生了变化。

具体查看这两种执行计划有什么区别：

select sql_id,plan_hash_value,id,operation,options,object_owner,object_name,depth,cost,timestamp

from DBA_HIST_SQL_PLAN

where sql_id =&＃39;56s18gn1k19yp&＃39;

and plan_hash_value in (947531627,2658265176);

SQL> select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp

2 from DBA_HIST_SQL_PLAN www.2cto.com

3 where sql_id =&＃39;56s18gn1k19yp&＃39;

4 and plan_hash_value in (947531627,2658265176);

PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

--------------- --- -------------------- -------------------- ------------------------------- ----- ---------- -------------------

947531627 0 SELECT STATEMENT 0 84 2011-09-27 04:33:34

947531627 1 COUNT STOPKEY 1 2011-09-27 04:33:34

947531627 2 VIEW 2 84 2011-09-27 04:33:34

947531627 3 SORT ORDER BY STOPKEY 3 84 2011-09-27 04:33:34

947531627 4 TABLE ACCESS BY INDEX ROWID BLOG_USER 4 3 2011-09-27 04:33:34

947531627 5 NESTED LOOPS 5 83 2011-09-27 04:33:34

947531627 6 HASH JOIN OUTER 6 17 2011-09-27 04:33:34

947531627 7 TABLE ACCESS BY INDEX ROWID CIRCLE_PAPER_MAIN 7 13 2011-09-27 04:33:34

947531627 8 INDEX RANGE SCAN IDX_CIRCLE_PAPER_MAIN_CID 8 3 2011-09-27 04:33:34

947531627 9 TABLE ACCESS BY INDEX ROWID CIRCLE_DISCUSS_CLASS 7 3 2011-09-27 04:33:34 www.2cto.com

947531627 10 INDEX RANGE SCAN IDX_CIRCLE_DISCUSS_CLASS_CID 8 1 2011-09-27 04:33:34

947531627 11 INDEX RANGE SCAN IDX_BLOG_USER_BLOGID 6 2 2011-09-27 04:33:34

2658265176 0 SELECT STATEMENT 0 9516 2012-02-22 15:54:33

2658265176 1 COUNT STOPKEY 1 2012-02-22 15:54:33

2658265176 2 VIEW 2 9516 2012-02-22 15:54:33

2658265176 3 SORT ORDER BY STOPKEY 3 9516 2012-02-22 15:54:33

2658265176 4 HASH JOIN RIGHT OUTER 4 8181 2012-02-22 15:54:33

2658265176 5 TABLE ACCESS BY INDEX ROWID CIRCLE_DISCUSS_CLASS 5 3 2012-02-22 15:54:33

2658265176 6 INDEX RANGE SCAN IDX_CIRCLE_DISCUSS_CLASS_CID 6 1 2012-02-22 15:54:33

2658265176 7 HASH JOIN 5 8177 2012-02-22 15:54:33

2658265176 8 TABLE ACCESS FULL CIRCLE_PAPER_MAIN 6 953 2012-02-22 15:54:33

2658265176 9 TABLE ACCESS FULL BLOG_USER 6 4954 2012-02-22 15:54:33

我们从查询结果中可以看到不同：

plan_hash_value = 947531627 --执行计划走索引

plan_hash_value = 2658265176 --执行计划走全表扫描

使用coe_xfr_sql_profile.sql可以发现两种执行计划的效率(AVG_ET_SECS)：

SQL> @coe_xfr_sql_profile.sql

Parameter 1: www.2cto.com

SQL_ID (required)

Enter value for 1: 56s18gn1k19yp

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

947531627 .037

2658265176 24.646

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 947531627

如何固定执行计划：

10g推荐使用sql profile来固定执行计划，coe_xfr_sql_profile.sql的本质也是调用sql profile来固定执行计划的。

本文来自于无忧网客联盟

推荐阅读

insert
PostgreSQL中的模式管理

本文由瀚高PG实验室撰写，详细介绍了如何在PostgreSQL中创建、管理和删除模式。文章涵盖了创建模式的基本命令、public模式的特性、权限设置以及通过角色对象简化操作的方法。 ... [详细]

蜡笔小新 2024-12-26 11:37:26
insert
Python 爬虫基础教程及代码实例

根据最新发布的《互联网人才趋势报告》，尽管大量IT从业者已转向Python开发，但随着人工智能和大数据领域的迅猛发展，仍存在巨大的人才缺口。本文将详细介绍如何使用Python编写一个简单的爬虫程序，并提供完整的代码示例。 ... [详细]

蜡笔小新 2024-12-26 10:42:40
insert
Oracle SQL 动态执行与事务管理：动态SQL是否支持回滚？

本文探讨了在Oracle数据库中，动态SQL语句的执行及其对事务管理的影响，特别是关于回滚操作的有效性。重点讨论了一个具体场景：将预警短信从当前表迁移到历史表时遇到的字段长度不匹配问题及相应的异常处理。 ... [详细]

蜡笔小新 2024-12-25 16:52:29
数据库
掌握Java EE的全面指南

探讨如何真正掌握Java EE，包括所需技能、工具和实践经验。资深软件教学总监李刚分享了对毕业生简历中常见问题的看法，并提供了详尽的标准。 ... [详细]

蜡笔小新 2024-12-25 13:38:29
mongodb
Python入门指南：从零开始的全面学习路线

本文详细介绍了Python编程语言的学习路径，涵盖基础语法、常用组件、开发工具、数据库管理、Web服务开发、大数据分析、人工智能、爬虫开发及办公自动化等多个方向。通过系统化的学习计划，帮助初学者快速掌握Python的核心技能。 ... [详细]

蜡笔小新 2024-12-25 12:54:33
数据库
C++面试高频题

作者：守望者1028链接：https:www.nowcoder.comdiscuss55353来源：牛客网面试高频题：校招过程中参考过牛客诸位大佬的面经，但是具体哪一块是参考谁的我 ... [详细]

蜡笔小新 2024-12-25 12:32:36
sql
IMPDP分区表导入的关键注意事项

本文将深入探讨使用IMPDP工具进行分区表导入时需要注意的事项，涵盖最佳实践和常见问题。通过详细的分析与解释，帮助读者更好地理解和应用这些知识。 ... [详细]

蜡笔小新 2024-12-25 11:49:44
database
PostgreSQL 10 离线安装指南

本文详细介绍了如何在无法联网的服务器上进行 PostgreSQL 10 的离线安装，并涵盖了从下载安装包到配置远程访问的完整步骤。 ... [详细]

蜡笔小新 2024-12-25 11:46:55
sql
SQL 类似查询语句中正则表达式解析

探讨如何使用正则表达式从类 SQL 查询语句中提取字段及其对应的值。 ... [详细]

蜡笔小新 2024-12-25 11:01:15
数据库
TCP长连接设备管理平台：架构与功能概览

本文介绍了基于TCP长连接的设备管理平台的设计理念、技术选型及主要功能模块。最初，项目旨在实现简单的协议测试，但随着需求扩展，逐步演变为一个完整的前后端分离系统。 ... [详细]

蜡笔小新 2024-12-25 09:07:38
数据库
ASP.NET Core 3.1 中的Startup类

Startup 类配置服务和应用的请求管道。Startup类ASP.NETCore应用使用 Startup 类，按照约定命名为 Startup。 Startup 类：可选择性地包括 ... [详细]

蜡笔小新 2024-12-25 02:13:25
insert
使用Pandas高效读取SQL脚本中的数据

本文详细介绍了如何利用Pandas直接读取和解析SQL脚本，提供了一种高效的数据处理方法。该方法适用于各种数据库导出的SQL脚本，并且能够显著提升数据导入的速度和效率。 ... [详细]

蜡笔小新 2024-12-24 21:56:10
oracle
Oracle 中 UPDATE 和 SELECT 的联合使用

本文介绍了如何在 Oracle 数据库中结合使用 UPDATE 和 SELECT 语句，以实现复杂的数据更新操作。首先准备测试环境和数据表，然后通过嵌套查询的方式从其他表中获取需要更新的值，最后执行更新操作并验证结果。 ... [详细]

蜡笔小新 2024-12-24 20:49:04
sql
自己用过的一些比较有用的css3新属性【HTML】

web前端|html教程自己用过的一些比较用的css3新属性web前端-html教程css3刚推出不久，虽然大多数的css3属性在很多流行的浏览器中不支持，但我个人觉得还是要尽量开 ... [详细]

蜡笔小新 2024-12-24 19:26:54
odbc
MySQL DateTime 类型数据处理及.0 尾数去除方法

本文介绍如何在 MySQL 中处理 DateTime 类型的数据，并解决获取数据时出现的.0尾数问题。同时，探讨了不同场景下的解决方案，确保数据格式的一致性和准确性。 ... [详细]

蜡笔小新 2024-12-24 19:25:10

白小蛮

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章