记一次 Oracle 下的 SQL 优化过程

这篇具有很好参考价值的文章主要介绍了记一次 Oracle 下的 SQL 优化过程。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1. 介绍

事情是这样的,UAT 环境的测试小伙伴向我扔来一个小 bug,说是一个放大镜的查询很慢,转几分钟才出数据,我立马上开发环境试了一下,很快啊我说😏,放大镜的数据立马就出来了,然后我登录 UAT 环境一看,诶是有些慢😕 ,于是开始了我的排查之旅...

2. 过程

首先我立马拿到了执行的 SQL 在开发环境的数据库执行了下,很快,都在 1s 左右,感觉没啥问题啊,然后我就在页面上点点点,发现好像上面有一个相关联的下拉框,如果选中的有数据,再点击这个放大镜就会慢一点,然后我登录 UAT 环境一试,哦不是这个问题,于是只能开始排查 SQL 了。

百度了一圈 Oracle 性能调优,大多很空泛,没有一个通用的、具体的、可执行的步骤。但是找到了排查前必备的查看执行计划explain plan

以下是正儿八经的优化过程👇:

2.1 查看该条 SQL 的执行计划

2.1.1 生成执行计划

在要排查的SQL前面加上explain plan for,例如以下的例子:

explain plan for
SELECT
    * 
FROM
    SOURCE_LISTEX_202101 
WHERE
    pass_id = '012101200123001025061320201201002852';

2.1.2 查看执行计划

推荐使用该 SQL 去查询执行计划👍 (为什么?因为简短好记😂)

select * from table(dbms_xplan.display)

或者

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

查出来的应该是这个样子:

Plan hash value: 1335523602

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       | 44479 |    38M| 17411   (1)| 00:03:29 |
|   1 | TABLE ACCESS BY INDEX ROWID | SOURCE_LISTEX_202101  | 44479 |    38M| 17411   (1)| 00:03:29 |
|*  2 | INDEX RANGE SCAN            | LISTEX_202101_PASS_ID | 17792 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

2 - access("PASS_ID"='012101200123001025061320201201002852')

而且如果你的 SQL 长了之后会发现,Operation 列是会有缩进的,缩进代表层级关系,就很乱😥,这里我推荐 Datagrip👍的右键可视化Explain plan。
使用方法为:选中SQL,右键Explain Plan,就可以查看啦,大概长这个样子:

记一次 Oracle 下的 SQL 优化过程

记一次 Oracle 下的 SQL 优化过程

其中点击 Explain Plan (Raw),也是可以的,就是查看原生的执行计划样子,大概长这样:

记一次 Oracle 下的 SQL 优化过程

2.1.3 分析执行计划

在 1.2 节可视化那个图中,我们主要看表格中的 Total Cost, 它代表着该条操作的总消耗,我们根据层级关系逐个排查,找到最为耗时的操作,排查发现此处两个Full Scan 全表扫描的性能消耗占据了全部总消耗的98% ((384+384)/779)😖

记一次 Oracle 下的 SQL 优化过程

之后根据执行计划的层级关系我们去 SQL 中找到这两个全表扫描对应的部分:
记一次 Oracle 下的 SQL 优化过程

查一下 sett_ebankinstruct 这个表的数量 有近十万条,看了一下表的定义,一个索引都没加。。。

在此次 SQL 中,使用sett_ebankinstruct的字段只有 instructionidinstructtype

instructionid : 很有可能会作为关联条件去连接多个表,并且该字段不会频繁的update,故在该字段上加索引
如何加索引呢?

create index 索引名称
    on 表名 (字段);

在此处加索引的 SQL 为:

create index IDX_SETT_EBANKINSTRUCT_INSTRID
    on SETT_EBANKINSTRUCT (INSTRUCTIONID);

instructtype : 考虑到 instructtype 只是类型,并且使用的情况可能就是 = 或者in (具体的几个值),于是就不用加。

我们添加索引之后发现,第一个Full Scan 全表扫描已经消失😌,因为在 sp.id = se.instructionid 进行表连接的时候走了索引,但是第二个Full Scan全表扫描仍然存在,说明此处并未走索引😔 :
记一次 Oracle 下的 SQL 优化过程

原因是此处使用 not in ,括号里的instructionId并未走内部子查询的索引,那么怎么改成走索引呢?将其改写成 not exists 即可。
记一次 Oracle 下的 SQL 优化过程

再次查看下执行计划,发现两个全表扫描都消失了,都变成了索引扫描(Index Scan)😆😆😆:
记一次 Oracle 下的 SQL 优化过程

对比优化前后Total Cost
优化前:779
优化后:62
优化提升:92%

3. 结论

如果排查确实是 SQL 问题,就直接看 执行计划 ,重点关注占用Total Cost的部分,然后查看对应的 SQL 。文章来源地址https://www.toymoban.com/news/detail-450867.html

  1. 如果是表频繁连接的字段,就要考虑加索引了。
  2. not innot exists,(业界流传 not existsnot in 快)
    其实非也,如果主查询和子查询表大小相当,那么用 inexists 差别不大。
    如果子查询表大,用 exists 快,如果子查询表小,用 in 快。
  3. Where :数据量多的情况下,排除越多记录的条件应该是先执行。
    Oracle 下能排除掉多的条件放后面,因为 Oraclewhere是从右往左执行的,格式化 SQL 后也就是从下往上执行,这样写那么会先排除大量的数据,因而加快后续操作的速度。
    MySQL 正好和 Oracle 相反,MySQL 下的 Where 是从左往右执行的,格式化 SQL 之后也就是从上往下执行,
    因而 MySQLWhere 的条件应该是排除多的条件放前面。

到了这里,关于记一次 Oracle 下的 SQL 优化过程的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 记一次centos 磁盘挂载过程

    最近买了云服务器磁盘,需要挂载,一下就由大猿来记录这次过程。 查看磁盘挂载情况 查看物理硬盘 标记分区 格式化分区 xfs ext4 xfs 和 ext4 区别 在大多数情况下,ext4和xfs都具有较高的性能,可以满足一般的存储需求。 对于大文件读写和吞吐量要求较高的场景,xfs表现更为

    2024年02月14日
    浏览(39)
  • 记一次线上BUG排查过程

    1. 线上遇到一个非常奇怪的bug,为一个用户分配业务线类型后,该用户登录时,提示502,但其它的用户登录完全是正常的 2. 问题现象 3. 排查思路 先去看线上日志,看是否有error,但日志里边这个接口200正常返回 本地debug,也复现一样问题,在分配角色类型超过22个总数时就报

    2024年02月09日
    浏览(51)
  • 记一次linux复制病毒处理过程

    某天我的阿里云突然发信息告诉我服务器有自变异木马,我用远程工具连接服务器异常卡顿甚至掉线,reboot也不好使.用阿里云的网页控制台会好些,但还是卡,我又用阿里云控制台重启服务器,重启之后发现服务器完全连不上了,ping也ping不通了,我问了客服说可以用救援连接试试,果

    2024年01月24日
    浏览(62)
  • 记一次后台开发面试拷打过程

    开头简单的自我介绍,面试官和我聊了聊天缓解个人紧张状况,然后就让开屏幕共享开视频做题目,做完以后,问了一些问题,就让等通知了,估计是凉了,不过这里且把当时做的笔试题目复盘一下吧!题目是ai做的题解,唉,AI都比我强,比我面试的时候解释的强多了,未来

    2024年02月08日
    浏览(43)
  • 记一次 stackoverflowerror 线上排查过程

         xxx 日,突然收到线上日志频繁告警 classCastException .从字面上的报警来看,仅仅是类型转换异常,查看细则发现其实是 stackOverFlowError .很多同学面试的时候总会被问到有没有遇到过线上 stackOverFlowError ?有么有遇到栈溢出?具体栈溢出怎么来解决?今天他来了,他带着问题走

    2024年01月23日
    浏览(43)
  • 记一次Kafka重复消费解决过程

            起因:车联网项目开发,车辆发生故障需要给三个系统推送消息,故障上报较为频繁,所以为了不阻塞主流程,采用了使用kafka。消费方负责推送并保存推送记录,但在一次压测中发现,实际只发生了10次故障,但是推送记录却有30多条。         问题排查,发现

    2024年02月13日
    浏览(58)
  • 记一次批量更新mysql数据过程

    一、前言 需求背景:mysql数据库中有一个表的数据(600多万)有一个字段的内容需要解密再通过另外一种加密方式进行加密再回存。通过java程序计算完成更新。 二、方案一 一条条计算更新。这里是将手机号解密,在通过另外一种方式回存。 算法步骤: 1、查询需要解密的数

    2024年02月10日
    浏览(35)
  • 记一次docker服务启动失败解决过程

    环境:centos 7.6 报错:start request repeated too quickly for docker.service 由于服务器修复了内核漏洞,需要重启,没想到重启后,docker启动失败了 查看状态 如下图 里面有一行提示: 提示要 journalctl -x 这个命令查看详细问题,其实用这个命令无法定位到具体问题的,于是使用了另外一

    2024年01月18日
    浏览(76)
  • 记一次Native memory leak排查过程

    路由计算服务是路由系统的核心服务,负责运单路由计划的计算以及实操与计划的匹配。在运维过程中,发现在长期不重启的情况下,有TP99缓慢爬坡的现象。此外,在每周例行调度的试算过程中,能明显看到内存的上涨。以下截图为这两个异常情况的监控。 TP99爬坡 内存爬坡

    2024年02月11日
    浏览(45)
  • 记一次SVN信息泄露挖掘过程(附工具)

    SVN是源代码管理软工具。使用SVN管理本地代码过程中,将生成名为.svn的隐藏文件夹,包含非常重要的源码信息。当网站管理员在发布代码时,没有使用导出功能,直接进行复制粘贴,导致出现SVN信息泄露漏洞 常见漏洞变现为 :域名/.svn/entries。 使用的工具为:AWVS,SvnExploi

    2024年02月06日
    浏览(35)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包