当前位置： 首页 > article >正文

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

article 2024/11/22 20:52:17

💫《博主介绍》：✨又是一天没白过，我是奈斯，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相关内容最全的学习资料~


http://www.kler.cn/a/404806.html

相关文章：

  • 开源许可协议
  • Compose Navigation快速入门
  • 【Maven】IDEA创建Maven项目 Maven配置
  • 【每日 C/C++ 问题】
  • 大数据-227 离线数仓 - Flume 自定义拦截器(续接上节) 采集启动日志和事件日志
  • 【Linux】＜共享内存应用＞——模拟实现不同进程把hello字符对＜共享内存文件对象＞的放入和取出操作
  • 前端反向代理的配置和實現
  • 深入解析MySQL中的事务处理
  • 从0开始linux（28）——使用vscode远程链接linux云服务器
  • 【Redis】服务器异常重启，导致redis启动失败
  • Redis 6.2 源码导读
  • Java 实现：根据字符串生成正则表达式的方法详解
  • Rust 力扣 - 70. 爬楼梯
  • 网络编程 day4~day5.1——多点通信，域套接字
  • 基于LSTM的新闻中文文本分类——基于textCNN与textRNN
  • CSRF保护--laravel进阶篇
  • Linux四剑客及正则表达式
  • 【微软：多模态基础模型】（4）统一视觉模型
  • 【jvm】方法区常用参数有哪些
  • 设计模式之 单例设计模式
  • SparkContext讲解
  • 多线程并发造成的数据重复问题解决方案参考(笔记记录)
  • 小鹏汽车智慧材料数据库系统项目总成数据同步
  • Go 常量为什么只支持基本数据类型？
  • （C语言）文件操作
  • 如何在 Ubuntu 上安装 Anaconda 开发环境