被埋没的SQL优化利器——Oracle SQL monitor

简介:

转载声明:本文为DBA+社群原创文章,转载必须连同本订阅号二维码全文转载,并注明作者名字及来源:DBA+社群(dbaplus)。

 

 

据说,在Oracle企业版数据库中有一个免费的工具,乃SQL优化之利器,那就是Oracle SQL monitor。下面,由DBA+社群原创专家周俊,给大家科普一下这一被埋没的神器。

 

专家简介

 
 

周俊

DBA+社群原创专家

 

具有14年以上Oracle数据库技术支持经验,在IBM的7年间担任华东区非IBM logo产品技术支持团队team leader,同时是IBM中国区Oracle 软件支持服务的技术负责人。目前任职于Oracle公司,专注于Oracle数据集成方案设计和实施。获得Oracle 8i~11g OCP、Oracle 10g OCM、DB2 administrator、PMP等证书。

 

 

前言
 

 

说实话,我以前也不太爱用花哨的图形界面工具进行SQL优化,最近参加了Oracle RWP培训,我发现Oracle 11g 引入的SQL monitor确实蛮好用的,是个被埋没的SQL优化利器。最重要的是Oracle SQL monitor在Oracle企业版数据库中是免费供大家使用的。下面我和大家分享如何利用SQL monitor简化我们的SQL优化工作。

 

如何打开SQL monitor report
 

 

方法一

 

Step1:打开Oracle EM console主页,切换到性能页面,点击右下角的SQL监控。

 

 

Step2:选择 时间范围,可以按照持续时间或者数据库时间对SQL语句进行排序。

 

 

在Oracle 11g中,当SQL满足以下条件之一就会被sql monitor捕获到,监控数据被记录在v$sql_monitor视图中。

 

  1. 当SQL并行执行时,会立即被实时监控到

  2. 当SQL单进程运行时,如果消耗超过5秒的CPU或I/O时间,它也会被监控到

  3. 使用/*+ monitor */提示的sql语句

 

Step3:选择您想要进行SQL优化语句前,点击第一列状态栏中勾号,Oracle就会将该SQL语句的执行情况华丽丽的展现在您面前。

 

 

方法二

 

在性能页面左下角的顶级会话中,点击您想要查看的SQL语句ID。

 

 

在SQL监控页面点击第一列状态栏中的图标。

 

 

方法三

 

如果您没有配置Oracle EM,但是知道待优化SQL语句对应的SQLID,可以通过以下脚本利用SQL monitor查看SQL语句在数据库中真实的执行计划。

 

在SecureCRT中启用log跟踪,选择保存的日志文件(后缀html)

在SQLPLUS 中执行

set trimspool on

set arraysize 512

set trim on

set pagesize 0

set linesize 1000

set long 1000000

set longchunksize 1000000

spool sqlmon.html

select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'input your SQL ID', report_level=>'ALL', type=>'ACTIVE') from dual;

spool off

cat sqlmon.html

在SecureCRT中关闭log跟踪,打开保存的文件就可以看到SQL执行计划了。

 

如何利用SQL monitor进行SQL优化
 

 

使用SQL monitor打开SQL执行计划后,

 

  1. 我们通常会根据最右边CPU和wait的activity,找到SQL执行计划中资源消耗较高的步骤。

  2. 然后查看一下Oracle估算的返回行数和实际的返回行数是否相差很大,如果估算的行数和实际的行数相差不大,至少表明目前对应数据库对象上的统计信息是准确的。

  3. 本例中Oracle估算的返回行数和实际返回行数相差不大,Oracle优化器采用了布隆过滤和HASH 右连的执行计划,接下去我们通常会检查SQL的筛选条件,判断是否使用了正确的索引等优化手段,这里我就不一一展开了。

 

 

下面是我最近遇到的一个利用SQL monitor进行快速SQL优化的案例分享。

 

SQL Text:SQL语句比较长 ,我截选了其中部分有代表性的SQL。

 

 

利用SQL monitor我们可以在执行计划中快速的定位需要重点关注的步骤。

 

 

我们看到该SQL语句已经运行了5.4小时,Oracle估算的返回行数和实际行数相差非常大,表明相关表上的统计信息不准确。

 

我们对DMS_CONTAINERS和DMS_CONTAINER_JN表进行了统计信息收集,统计信息重新收集后Oracle马上使用了DMS_CONTAINER_JN表上正确的IYC_CNTRID字段的索引,但是DMS_CONTAINERS表上仍旧使用了选择度不高TYPE字段索引。

 

进一步查看SQL语句,我们发现该SQL是通过视图YMS_GUI_LOAD_CONTAINERS_VW访问DMS_CONTAINERS表,该视图的定义如下:

 

 

由于在视图where条件中有IYC_TYPE字段,Oracle优先选用IYC_TYPE字段上的索引对DMS_CONTAINERS表进行访问,在CBO下,Oracle不会再去自动选择其他字段上单独的索引进行访问(除非手工设定AND-EQUAL提示),因此没有选择筛选度更高的YC_LSTUPDDT字段上的索引。 如果需要Oracle使用到其他字段上的索引,最简单的方法就是在IYC_TYPE和YC_LSTUPDDT字段上创建联合索引,Oracle在分析索引列的时候自动会分析两个列的组合情况,从而选择该复合索引。

 

 

总结
 

 

通过前面的介绍相信大家对Oracle SQL monitor华丽、直观的界面留下了深刻的印象,下面我再总结一下使用Oracle SQL monitor进行SQL优化的步骤:

 

  1. 通过SQL monitor监控我们可以快速地发现异常运行的SQL语句,如果您知道SQL对应的SQL ID也可以通过脚本利用SQL monitor查看SQL语句在数据库中真实的执行计划。

  2. 查看SQL执行计划,通过CPU和WAIT的活动比重快速找到SQL执行计划中的关键步骤。

  3. 通过比较Oracle估算的行数和实际返回行数能够快速判断是否需要重新收集统计信息,帮助我们分析Oracle优化器选择的SQL执行计划有无问题。

  4. 具体的SQL优化方法大家可以参考之前丁俊大师在DBA+社群分享过的Oracle SQL优化专题

  5. ,我在这就不做进一步展开啦。


本文来自云栖社区合作伙伴"DBAplus",原文发布时间:2015-11-26
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
目录
相关文章
|
29天前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构,并且通过合理使用可以显著改善查询速度及整体性能。需要注意,在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
96 8
|
4月前
|
存储 Oracle 关系型数据库
Oracle存储过程插入临时表优化与慢查询解决方法
优化是一个循序渐进的过程,就像雕刻一座雕像,需要不断地打磨和细化。所以,耐心一点,一步步试验这些方法,最终你将看到那个让你的临时表插入操作如同行云流水、快如闪电的美丽时刻。
203 14
|
5月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
7月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
6月前
|
SQL Oracle 关系型数据库
解决大小写、保留字与特殊字符问题!Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中,双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景,包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议,帮助开发者规避常见错误,提高代码可维护性和效率。
219 6
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
|
8月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
279 9
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
本文来自YashanDB官网,介绍如何处理Oracle客户端sql*plus中使用@@调用同级目录SQL脚本的场景。崖山数据库23.2.x.100已支持@@用法,但旧版本可通过Python脚本批量重写SQL文件,将@@替换为绝对路径。文章通过Oracle示例展示了具体用法,并提供Python脚本实现自动化处理,最后调整批处理脚本以适配YashanDB运行环境。
|
8月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
272 9

热门文章

最新文章

推荐镜像

更多