【Oracle篇】SQL性能优化实战案例(从15秒优化到0.08秒)（第七篇，总共七篇）

🕗 发布于 2024-11-22 09:03 oracle sql 性能优化 数据库 运维

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

哈喽哈喽，各位小伙伴！👋博主今天终于将统计信息、优化器、执行计划、Hint、STA系列的最后一篇给肝出来啦！🎉🎉这个系列耗时3个月，此系列是目前博主耗时最长的一个系列😭

为了让这个系列的每一篇博文都能闪闪发亮，博主可是倾尽了毕生所学（但确实花了不少心思，查阅了多篇官方文档），投入了大量的时间和精力。💪💪 部分博文前前后后改了十遍以上，只为为各位小伙伴提供一份高质量的学习资料！📚📚

所以嘛，看在博主我这么拼，这么精益求精，无私奉献的份上，小伙伴们是不是该给我来个一键三连呢？🙏🙏

关于理论部分博主已经在前六篇文章详细做了介绍和阐述，所以今天这篇博客只讲实战案例，讲通过执行计划对慢SQL进行优化，情也煽够了，那么开始今天的慢SQL优化。还是老规矩为了让大家更容易消化和逐个理解，我将分成七篇文章来进行介绍，以便大家劳逸结合而不至于感觉到阅读枯燥，七篇的内容分别如下：

第一篇：统计信息和动态采样的深度剖析
第二篇：全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)
第三篇：SQL执行计划之访问路径(含表级别、B树索引、位图索引、簇表四大类访问路径)
第四篇：SQL执行计划之多表连接(含内连接、外连接、半连接、反连接、笛卡尔连接五种连接方式和嵌套、哈希、排序合并三种连接算法)
第五篇：精细化查询优化：如何有效使用Hint对优化器的执行计划进行干预
第六篇：掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南
第七篇：SQL性能优化实战案例(从15秒优化到0.08秒)（当前篇）

案例开始

生产test用户的查询SQL非常慢，用户没有dba权限，通过autotrace查看执行计划，并进行优化

1）执行查询语句

SYS@orcl1> create user test identified by 123456;

SYS@orcl1> grant connect to test;
SYS@orcl1> @?/rdbms/admin/utlxplan.sql 
SYS@orcl1> @?/sqlplus/admin/plustrce.sql   ---生成 plustrace角色
SYS@orcl1> grant plustrace to test;           ---普通用户（非dba用户）需要有plustrace角色才能在sqlplus下使用autotrace。

SYS@orcl1> conn test/123456
test@orcl1> 
SELECT a.id,
       a.name,
       a.sex,
       a.region,
       b.logtime,
       b.cardid,
       b.score,
       c.tel,
       c.joindate
FROM test_member a, test_sales b, test_m10 c
WHERE a.cardid = b.cardid
    AND b.cardid=c.cardid
    AND a.name in ('test1175189','test3077390','test7132935')
    AND b.cardid like '10%'
    AND c.joindate > to_date('2010-12-15', 'yyyy-mm-dd')
ORDER BY c.joindate;

多表联合查询test_member、test_sales、test_m10表，结果返回3行消耗了接近16秒，查询时间太长，分析SQL执行计划

2）显示这条语句的执行计划和统计信息，分析原因

test@orcl1> set line 400;
test@orcl1> set autotrace on;
test@orcl1> 
SELECT a.id,
       a.name,
       a.sex,
       a.region,
       b.logtime,
       b.cardid,
       b.score,
       c.tel,
       c.joindate
FROM test_member a, test_sales b, test_m10 c
WHERE a.cardid = b.cardid
    AND b.cardid=c.cardid
    AND a.name in ('test1175189','test3077390','test7132935')
    AND b.cardid like '10%'
    AND c.joindate > to_date('2010-12-15', 'yyyy-mm-dd')
ORDER BY c.joindate;
SQL语句的执行计划和统计信息分析

Execution Plan为执行计划部分：执行计划部分列出了相关SQL语句每步的具体情况和消耗，包括访问路径、扫描情况、扫描行数、使用字节数、使用临时空间字节数，以及每个步骤的成本和时间估计
Execution Plan
---------------------------------------------------------------------------------------------
Plan hash value: 3560787711
Plan hash value：这一行是这一条SQL语句的hash值，oracle对每条语句产生的执行计划放在share pool里面，第一次要经过硬解析，产生hash值。下次再执行该语句时候比较hash值，如果相同就不执行硬解析，执行软解析（检查此sql是否被当前用户使用过，如果是就是软解析soft parse，如果否那就是硬解析。DDL总是硬解析，语句从不重用）

这条执行计划的执行顺序为：4、5、3、6、2、1、0。如果有不懂执行计划的执行顺序的小伙伴可以参考我之前的博客哦，直通车👉【Oracle篇】全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)（第二篇，总共七篇）_oracle如何通过解释计划分析优化sql-CSDN博客👈
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  |       Operation      |       Name    |   Rows  |   Bytes  |  Cost (%CPU)  |   Time    |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     3  | 264  |  76499   (1)  |  00:15:18  |
|   1 |  SORT ORDER BY      |             |     3  |  264  |  76499   (1)  |  00:15:18  |
|*  2 |   HASH JOIN          |             |     3  | 264  |  76498   (1)  |  00:15:18  |
|*  3 |    HASH JOIN      |             |     3  | 180  |  75726   (1)  |  00:15:09  |
|*  4 |     TABLE ACCESS FULL  |  TEST_MEMBER     |     3  |  123  |  75296   (1)  |  00:15:04  |
|*  5 |     TABLE ACCESS FULL  |  TEST_SALES      |  10000  |185K  |   430   (1)  |  00:00:06  |
|*  6 |    TABLE ACCESS FULL   |  TEST_M10        |  86375  |  2361K  |   772   (1)  |  00:00:10  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Id：内部标识符，这里并不是表示的执行计划的执行顺序，只是用于表示执行计划中的节点顺序。特别注意：有*的标识符表示有谓词信息，也就是说这步操作有where过滤条件或者join连接条件，where过滤条件或者join连接条件对于查询性能最重要，通过谓词信息部分考虑是否可以通过添加或修改索引来提高过滤操作的效率。

Operation：描述了数据库执行的具体操作（访问路径），如“SELECT STATEMENT”，“HASH JOIN”等。在这条SQL中虽然没有通过join连接实现多表关联，但通过oracle的内部机制，将SQL进行了hash join连接。

Name： 通常表示正在执行操作的对象的名称，例如表名或索引名。如果是索引名那么表示使用了索引，如果是表名那么就是用到了全表扫描，也可以通过Operation是不是TABLE ACCESS FULL来确定是不是进行了全表扫描，如果是全备扫描，需要重点关注。

Rows/card：估计的行数。这通常是查询优化器对查询结果的估计。

Bytes：估计的字节数。这通常是查询优化器对查询结果的估计。

TempSpc：估计的临时空间需求（以字节为单位）。这通常用于排序或分组操作。虽然这个SQL使用到了ORDER BY排序，但是因为排序的操作很少就没有使用到临时空间，大排序或者分组操作会显示TempSpc这列

Cost (%CPU)：开销。估计的成本和CPU使用百分比。成本是查询优化器用来决定执行顺序的一个度量，它考虑了多种因素，如I/O、CPU使用等。

Time：估计的执行时间（HH:MM:SS），返回的结果仅供参考，计划执行不真实。特别注意：虽然估计的执行时间不真实，但是如果这里的时间明显长，那么就需要重点关注。

Predicate Information (identified by operation id)为谓词信息部分：谓词信息部分列出了执行计划部分where过滤条件或者join连接条件的信息，其实也就是where过滤条件或者join连接条件都会在谓词信息中，where过滤条件或者join连接条件对于查询性能最重要，通过谓词信息部分考虑是否可以通过添加或修改索引来提高过滤操作的效率。

列出了执行计划部分where过滤条件或者join连接条件的信息，一共有两种：access表示使用索引作为过滤条件，那么filter表示没有使用索引作为过滤条件
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."CARDID"="C"."CARDID")                              ---access表示的是使用索引作为过滤条件。B表和C表的CARDID列作为两表的关联条件，但是两表的CARDID列并没有索引，多表关联时，oracle会自动选择合适的连接方式，可能连接方式也是一种优化，不用索引也很快，以后待学习
   3 - access("A"."CARDID"="B"."CARDID")                              ---access表示的是使用索引作为过滤条件。A表和B表的CARDID列作为两表的关联条件，但是两表的CARDID列并没有索引，多表关联时，oracle会自动选择合适的连接方式，可能连接方式也是一种优化，不用索引也很快，以后待学习
   4 - filter("A"."NAME"='test1175189' OR "A"."NAME"='test3077390' OR         
      "A"."NAME"='test7132935')                                   ---filter表示没有使用索引作为过滤条件，这个是内部标识符4的谓词信息，根据执行计划部分，是全表扫描，Time耗时了很久，所以需要对name加索引解决，或者通过STA（SQL Tuning Advisor）进行自动优化
   5 - filter(TO_CHAR("B"."CARDID") LIKE '10%')                       ---filter表示没有使用索引作为过滤条件，这个是内部标识符5的谓词信息，根据执行计划部分，虽然是全表扫描，但是Time并没有耗时多少
   6 - filter("C"."JOINDATE">TO_DATE(' 2010-12-15 00:00:00', 'syyyy-mm-dd
      hh24:mi:ss'))                                               ---filter表示没有使用索引作为过滤条件，这个是内部标识符6的谓词信息，根据执行计划部分，虽然是全表扫描，但是Time并没有耗时多少
Statistics为执行计划统计信息部分：
Statistics
----------------------------------------------------------
       0  recursive calls       ---递归调用。对其它SQL语句的调用的次数，越少越好
       0  db block gets         ---从buffer cache中读取的block的数据
  281344  consistent gets   ---计算sql占用的运行内存。从buffer cache中读取undo数据的block的数量，281344 * 8192/1024/1024/1024=2G。
  277030  physical reads  ---计算sql从磁盘读取的IO。从磁盘读取block的数量，如果是全盘扫描那么就是表的实际大小，277030 * 8192/1024/1024/ 1024=2G
   0  redo size                               ---生成的redo大小。Select不生成redo数据，所以为0  
    1324  bytes sent via SQL*Net to client        ---服务器到客户端的字节总数
 519  bytes received via SQL*Net from client  ---客户机接受的字节数
   2  SQL*Net roundtrips to/from client       ---客户机到服务器之间发生的sql*net 次数
   1  sorts (memory)                          ---在内存执行的排序量
   0  sorts (disk)                            ---在磁盘上执行的排序量
   3  rows processed                          ---影响数据的行数。就是结果返回的行数

3）对SQL语句进行分析，创建合适的索引

通过Execution Plan分析如下：

（1）执行计划中，耗时的部分都在对TEST_MEMBER进行全表扫描（TABLE ACCESS FULL），TEST_SALES和TEST_M10也进行了全表扫描（TABLE ACCESS FULL），但是耗时不长，所以TEST_SALES和TEST_M10可以不创建索引进行优化，TEST_MEMBER必须通过给出谓词进行进行创建索引优化

通过Predicate Information (identified by operation id)分析如下：

（1）filter表示没有使用索引作为过滤条件，那么结合Execution Plan部分进行分析，TEST_MEMBER进行的全表扫描（TABLE ACCESS FULL）对应的就是内部标识符4的谓词信息，所以对TEST_MEMBER的name列加上索引

创建索引：
TEST@orcl1> create index idx_test_member_name on test_member(name);  

4）再次执行SQL语句，并查看执行计划

test@orcl1> 
SELECT a.id,
       a.name,
       a.sex,
       a.region,
       b.logtime,
       b.cardid,
       b.score,
       c.tel,
       c.joindate
FROM test_member a, test_sales b, test_m10 c
WHERE a.cardid = b.cardid
    AND b.cardid=c.cardid
    AND a.name in ('test1175189','test3077390','test7132935')
    AND b.cardid like '10%'
    AND c.joindate > to_date('2010-12-15', 'yyyy-mm-dd')
ORDER BY c.joindate;
通过创建索引优化，查询时间降到了0.08秒

Execution Plan部分：test_member表不再进行全表扫描，而是使用到了IDX_TEST_MEMBER_NAME索引，进行了索引范围扫描(index range scan)

Predicate Information (identified by operation id)部分：access表示的是使用索引作为过滤条件。索引范围扫描(index range scan)对应的就是内部标识符4的谓词信息，test_member的name使用了索引

慢SQL优化圆满结束，在实际生产环境执行的SQL都在几百行，甚至上千行，要比这篇博客的SQL要复杂的多，但只要掌握了优化技巧和优化原理，即使再复杂的SQL通过多次实践也会手到擒来的，那么这篇文章就圆满结束，这个系列到这里也画上了圆满的句号。一个人可以走的很快，一群人才能走的更远，也希望各位集帅多提宝贵建议，让这个系列成为全网关于统计信息、优化器、执行计划、Hint、STA相关内容最全的学习资料~

原文地址：https://blog.csdn.net/naisiing/article/details/143835953

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：金融数据中心容灾“大咖说” | 美创科技赋能“灾备一体化”建设
下一篇：Spring WebFlux学习笔记（二）

《Vue零基础入门教程》第三课：起步案例
当状态值发生变化时, Vue会自动响应变化, 使用新的数据重新渲染视图。
阅读更多2024-11-27
AIGC-----AIGC在虚拟现实中的应用前景
AIGC是一种基于人工智能的内容生成方式，包括文本、图像、音频和视频的生成。而VR是一种通过计算机模拟创造出一种虚拟环境，给用户带来沉浸式体验的技术。AIGC与VR的结合可以使虚拟环境更具动态性和互动
阅读更多2024-11-27
C++设计模式之组合模式实践原则
通过考虑这些重要方面，你可以确保实现的组合模式符合软件设计原则，从而创建出灵活、可扩展、易于维护的系统。
阅读更多2024-11-27
【纪念365天】我的创作纪念日
博主的创作纪念日
阅读更多2024-11-27
第三届航空航天与控制工程国际 (ICoACE 2024)
北京航空航天大学航空科学与工程学院教授，博士生导师，澳大利亚国家优秀青年人才，北京航空航天大学青年拔尖人才。，北京航空航天大学自动化科学与电气工程学院教授，博士生导师，国家级青年人才。，上海大学自动化
阅读更多2024-11-27
uniapp-vue2引用了vue-inset-loader插件编译小程序报错
是node_modules目录的vue-template-compiler和vue依赖包的版本号不一样导致的报错。把vue依赖包改成和vue-template-compiler依赖包一样的版本号。在p
阅读更多2024-11-27
uniapp开发微信小程序笔记8-uniapp使用vant框架
前言：其实用uni-app开发微信小程序的首选不应该是vant，因为vant没有专门给uni-app设置专栏，可以看到目前Vant 官方提供了Vue 2 版本、Vue 3 版本和微信小程序版本，并由
阅读更多2024-11-27
uniapp+vue2+uview2.0导航栏组件二次封装
uniapp+vue2+uview2.0导航栏组件二次封装
阅读更多2024-11-27
【leetcode】动态规划
1. 这道题目没有规定一个数不能重复出现，所以判断前一个数是否存在，得到的下标有多个，要得到最大的子序列，的正整数数组形成序列 arr ，找到 arr 中最长的斐波那契式的子序列的长度。中删掉任意
阅读更多2024-11-27
【设计模式】【创建型模式（Creational Patterns）】之原型模式（Prototype Pattern）
原型模式是一种创建型设计模式，它通过复制现有对象来创建新对象，而不是通过构造函数。这种方式可以提高性能，特别是在对象初始化需要消耗大量资源或耗时较长的情况下。性能提升：通过复制现有对象来创建新对象，可
阅读更多2024-11-27

【Oracle篇】SQL性能优化实战案例(从15秒优化到0.08秒)（第七篇，总共七篇）

案例开始

相关文章