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

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来固定执行计划的。

本文来自于无忧网客联盟

推荐阅读

数据库
MyBatis入门指南：环境搭建与基础配置详解

本文详细介绍了MyBatis的基础配置流程，包括在Maven项目中添加MyBatis依赖、IDEA中配置数据库连接、导入SQL脚本以及编写mybatis-config.xml配置文件等关键步骤。 ... [详细]

蜡笔小新 2024-11-24 18:58:57
sql
SQLite ORDER BY 语句详解与应用

本文基于最新版SQLite 3.33.0（发布于2020年8月20日），详细介绍如何使用ORDER BY语句进行数据排序，包括单列和多列排序的方法。 ... [详细]

蜡笔小新 2024-11-24 18:49:26
database
41款高效LISP工具助力数据处理全流程

本文由公众号【数智物语】(ID: decision_engine)发布，关注获取更多干货。文章探讨了从数据收集到清洗、建模及可视化的全过程，介绍了41款实用工具，旨在帮助数据科学家和分析师提升工作效率。 ... [详细]

蜡笔小新 2024-11-24 00:11:50
数据库
MySQL 高级特性深度解析

本文深入探讨了MySQL中的高级特性，包括索引机制、锁的使用及管理、以及如何利用慢查询日志优化性能。适合有一定MySQL基础的读者进一步提升技能。 ... [详细]

蜡笔小新 2024-11-23 22:01:49
database
将XML数据迁移至Oracle Autonomous Data Warehouse (ADW)

随着Oracle ADW的推出，数据迁移至ADW成为业界关注的焦点。特别是XML和JSON这类结构化数据的迁移需求日益增长。本文将通过一个实际案例，探讨如何高效地将XML数据迁移至ADW。 ... [详细]

蜡笔小新 2024-11-23 20:27:56
insert
必须为元素类型 mapper 声明属性 namespace解决

在使用mybatis进行mapper.xml测试的时候发生必须为元素类型“mapper”声明属性“namespace”的错误项目目录结构UserMapper和UserMappe ... [详细]

蜡笔小新 2024-11-23 20:06:58
database
Windows环境下Oracle数据库迁移实践

本文详细记录了一次在Windows操作系统下将Oracle数据库的控制文件、数据文件及在线日志文件迁移至外部存储的过程，旨在为后续的集群环境部署做好准备。 ... [详细]

蜡笔小新 2024-11-23 19:57:51
数据库
2021年度精选：新手必知的数据分析工具指南

面对众多的数据分析工具，如何选择最适合自己的那一个？对于初学者而言，了解并掌握几种核心工具是快速入门的关键。本文将从数据处理的不同阶段出发，推荐三种广泛使用的数据分析工具。 ... [详细]

蜡笔小新 2024-11-23 19:47:22
数据库
Java连接MySQL数据库的方法及测试示例

本文详细介绍了如何安装MySQL数据库，并通过Java编程语言实现与MySQL数据库的连接，包括环境搭建、数据库创建以及简单的查询操作。 ... [详细]

蜡笔小新 2024-11-23 18:58:43
oracle
MySQL与Oracle数据库连接命令详解

本文详细介绍了如何使用SQL*Plus连接Oracle数据库以及使用MySQL客户端连接MySQL数据库的方法，包括基本命令和具体操作步骤。 ... [详细]

蜡笔小新 2024-11-23 17:26:52
insert
利用Scrapy构建的数据采集与分析可视化系统

本文探讨了如何使用Scrapy框架构建高效的数据采集系统，以及如何通过异步处理技术提升数据存储的效率。同时，文章还介绍了针对不同网站采用的不同采集策略。 ... [详细]

蜡笔小新 2024-11-23 16:56:38
sql
如何在SQL Report Service中应用TOP N功能

本文探讨了如何在SQL Server Reporting Services (SSRS)中利用TOP N功能来筛选和展示数据集中的前N条记录。通过正确的配置图表属性中的筛选器设置，可以轻松实现这一目标。 ... [详细]

蜡笔小新 2024-11-23 16:41:53
sql
PHP中Smarty模板引擎自定义函数详解

本文详细介绍了如何在PHP的Smarty模板引擎中自定义函数，并通过具体示例演示了这些函数的使用方法和应用场景。适合PHP后端开发者学习。 ... [详细]

蜡笔小新 2024-11-23 15:39:25
insert
MySQL InnoDB事务日志详解：Redo Log与Undo Log

本文详细介绍了MySQL InnoDB存储引擎中的Redo Log和Undo Log，探讨了它们的工作原理、存储方式及其在事务处理中的关键作用。 ... [详细]

蜡笔小新 2024-11-23 15:34:23
sql
MySQL性能监控与优化策略

本文探讨了MySQL中的死锁现象及其监控方法，并介绍了如何通过配置和SQL语句调整来优化数据库性能。同时，还讲解了慢查询日志的配置与分析技巧。 ... [详细]

蜡笔小新 2024-11-23 14:34:35

白小蛮

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

Tags | 热门标签

RankList | 热门文章