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

ops/2024/11/22 22:04:38/

💫《博主介绍》:✨又是一天没白过,我是奈斯,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)执行查询语句

sql">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.cardidAND b.cardid=c.cardidAND 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)显示这条语句的执行计划和统计信息,分析原因 

sql">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.cardidAND b.cardid=c.cardidAND 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语句每步的具体情况和消耗,包括访问路径、扫描情况、扫描行数、使用字节数、使用临时空间字节数,以及每个步骤的成本和时间估计

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博客👈

sql">----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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表示没有使用索引作为过滤条件

sql">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-ddhh24:mi:ss'))                                               ---filter表示没有使用索引作为过滤条件,这个是内部标识符6的谓词信息,根据执行计划部分,虽然是全表扫描,但是Time并没有耗时多少

  

Statistics为执行计划统计信息部分:

sql">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=2G0  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列加上索引

                      

创建索引:

sql">TEST@orcl1> create index idx_test_member_name on test_member(name);  

          

4)再次执行SQL语句,并查看执行计划

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.cardidAND b.cardid=c.cardidAND 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.ppmy.cn/ops/135895.html

相关文章

计算机网络 第三章:数据链路层(关于争用期的超详细内容)

数据链路层要干的是:解决在一组网络上(一段链路上)传输的问题。 (在第一章我们讨论过数据是如何经过五层传输的,但在本章我们只考虑数据链路层之间传输数据的问题,所以就先想象成是两个链路层之间在直接传输…

【K8S系列】Kubernetes 中如何调试imagePullSecrets配置详细步骤介绍

调试 imagePullSecrets 配置是确保 Kubernetes 能够成功拉取私有镜像所需的关键步骤。以下是详细的调试步骤和建议。 1. 确认 imagePullSecrets 配置 首先,确保在 Pod 的 YAML 配置中正确引用了 imagePullSecrets。其基本结构如下: apiVersion: v1 kin…

【如何用更少的数据作出更好的决策】-gpt生成

如何用更少的数据作出更好的决策 用更少的数据作出更好的决策是一种能力的体现,需要结合有效的方法、严谨的逻辑以及对问题的深刻理解。以下是一些可以帮助你实现这一目标的策略: 明确目标 在收集和分析数据之前,先明确你的决策目标是什么…

JMeter 性能测试计划深度解析:构建与配置的树形结构指南

Apache JMeter 的 TestPlan .jmx 文件是采用树形结构进行组织的,这种结构使得测试计划的构建和配置更加直观和易于管理。以下是对 JMeter GUI 配置内容的详细描述: 一、一级目录:jmeterTestPlan jmeterTestPlan:这是整个测试计…

Zustand 让 React 状态变得太简单

为什么选择 Zustand? Zustand 是一个为 React 打造的现代化状态管理库,它以其简洁的 API 和强大的功能正在改变前端开发的方式。相比 Redux 繁琐的样板代码(action types、dispatch、Provider等),Zustand 提供了更加优雅且直观的解决方案。 核心特性 1. 基于 Hook 的简洁API i…

MySQL最后练习,转转好物交易平台项目

第一步,做一个项目要先做数据库,创建表 这边已经帮你们创好了, CREATE TABLE UserInformation_普通用户信息表 ( id INT(4) NOT NULL COMMENT 编号 AUTO_INCREMENT, username VARCHAR(10) NOT NULL COMMENT 用户名, password VARCHAR(20) N…

HarmonyOS NEXT应用元服务开发Intents Kit(意图框架服务)习惯推荐方案概述

一、习惯推荐是HarmonyOS学习用户的行为习惯后做出的主动预测推荐。 1.开发者将用户在应用/元服务内的使用行为向HarmonyOS共享,使得HarmonyOS可以基于共享的数据学习用户的行为习惯。 2.在HarmonyOS学习到用户的行为习惯后,会给用户推荐相应功能&#x…

前端:HTML (学习笔记)【1】

一,网络编程的三大基石 1,URL (1)url —— 统一资源定位符: 网址——整个互联网中可以唯一且准确的确定一个资源的位置。 【项目外】 网址——https://www.baidu.com/ …