【SQL优化】四月份培训的一个sql优化案例3

最新推荐文章于 2021-11-04 21:13:55 发布
最新推荐文章于 2021-11-04 21:13:55 发布
阅读量326 收藏
点赞数
分类专栏： Oracle数据库-SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/q3dxdx/article/details/48979783
版权
下面的语句同样是生产环境的慢sql，通过我对大家的培训，把部分慢sql给当场干掉，成功阻止了双节点cpu偶尔过高的问题：
直接上当时的材料：
原始sql语句：
select sum(aaa.acceptid)
  from (select count(c.accept_id) as acceptid
          from cct_log_eomsintf_201503 c,
               (select b.accept_id accept_id,
                       max(b.creation_time) creation_time
                  from cct_log_eomsintf_201503 b
                 where exists (select 1
                          from cct_log_eomsintf_201503 a
                         where a.exception_info like '%启动保障机制%'
                           and a.accept_id = b.accept_id)
                 group by b.accept_id) d,
               cct_wf_accept_201503 e
         where c.accept_id = d.accept_id
           and c.creation_time = d.creation_time
           and c.accept_id = e.accept_id
           and e.ring <> 128
           and e.ring <> 119
           and c.exception_info like '%启动保障机制%'
        union all
        select count(c.accept_id) as acceptid
          from cct_log_eomsintf_201504 c,
               (select b.accept_id accept_id,
                       max(b.creation_time) creation_time
                  from cct_log_eomsintf_201504 b
                 where exists (select 1
                          from cct_log_eomsintf_201504 a
                         where a.exception_info like '%启动保障机制%'
                           and a.accept_id = b.accept_id)
                 group by b.accept_id) d,
               cct_wf_accept_201504 e
         where c.accept_id = d.accept_id
           and c.creation_time = d.creation_time
           and c.accept_id = e.accept_id
           and e.ring <> 128
           and e.ring <> 119
           and c.exception_info like '%启动保障机制%') aaa
 
Execution Plan
----------------------------------------------------------
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |     1 |    13 | 73783   (1)|
|   1 |  SORT AGGREGATE                      |                          |     1 |    13 |            |
|   2 |   VIEW                               |                          |     2 |    26 | 73783   (1)|
|   3 |    UNION-ALL                         |                          |       |       |            |
|   4 |     SORT AGGREGATE                   |                          |     1 |    24 |            |
|   5 |      VIEW                            |                          |     1 |    24 | 44412   (1)|
|*  6 |       FILTER                         |                          |       |       |            |
|   7 |        HASH GROUP BY                 |                          |     1 |   134 | 44412   (1)|
|*  8 |         HASH JOIN RIGHT SEMI         |                          |   329K|    42M| 44399   (1)|
|*  9 |          TABLE ACCESS FULL           | CCT_LOG_EOMSINTF_201503  | 20242 |   612K| 16662   (1)|
|* 10 |          HASH JOIN                   |                          |   329K|    32M| 27735   (1)|
|* 11 |           TABLE ACCESS BY INDEX ROWID| CCT_LOG_EOMSINTF_201503  |     1 |    51 |     8   (0)|
|  12 |            NESTED LOOPS              |                          |  3757 |   297K| 11074   (1)|
|* 13 |             INDEX FAST FULL SCAN     | IDX_CCT_WF_ACCEPT_201503 |  3331 | 99930 |  1069   (1)|
|* 14 |             INDEX RANGE SCAN         | ACCEPT_ID_IDX_201503     |     6 |       |     2   (0)|
|  15 |           TABLE ACCESS FULL          | CCT_LOG_EOMSINTF_201503  |   404K|  8697K| 16658   (1)|
|  16 |     SORT AGGREGATE                   |                          |     1 |    24 |            |
|  17 |      VIEW                            |                          |     1 |    24 | 29371   (1)|
|* 18 |       FILTER                         |                          |       |       |            |
|  19 |        HASH GROUP BY                 |                          |     1 |   120 | 29371   (1)|
|* 20 |         HASH JOIN RIGHT SEMI         |                          | 14719 |  1724K| 29370   (1)|
|* 21 |          TABLE ACCESS FULL           | CCT_LOG_EOMSINTF_201504  | 11576 |   271K|  9536   (1)|
|* 22 |          HASH JOIN                   |                          | 61608 |  5775K| 19832   (1)|
|* 23 |           HASH JOIN                  |                          | 11575 |   836K| 10297   (1)|
|* 24 |            TABLE ACCESS FULL         | CCT_LOG_EOMSINTF_201504  | 11576 |   497K|  9536   (1)|
|* 25 |            INDEX FAST FULL SCAN      | IDX_CCT_WF_ACCEPT_201504 | 22933 |   671K|   759   (1)|
|  26 |           TABLE ACCESS FULL          | CCT_LOG_EOMSINTF_201504  |   231K|  4973K|  9534   (1)|
------------------------------------------------------------------------------------------------------
 
优化前：
select count(c.accept_id) as acceptid
  from cct_log_eomsintf_201504 c,
       (select b.accept_id accept_id, max(b.creation_time) creation_time
          from cct_log_eomsintf_201504 b
         where exists (select 1
                  from cct_log_eomsintf_201504 a
                 where a.exception_info like '%启动保障机制%'
                   and a.accept_id = b.accept_id)
         group by b.accept_id) d,
       cct_wf_accept_201504 e
where c.accept_id = d.accept_id
   and c.creation_time = d.creation_time
   and c.accept_id = e.accept_id
   and e.ring <> 128
   and e.ring <> 119
   and c.exception_info like '%启动保障机制%'
 
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |     1 |    24 | 29371   (1)|
|   1 |  SORT AGGREGATE             |                          |     1 |    24 |            |
|   2 |   VIEW                      |                          |     1 |    24 | 29371   (1)|
|*  3 |    FILTER                   |                          |       |       |            |
|   4 |     HASH GROUP BY           |                          |     1 |   120 | 29371   (1)|
|*  5 |      HASH JOIN RIGHT SEMI   |                          | 14719 |  1724K| 29370   (1)|
|*  6 |       TABLE ACCESS FULL     | CCT_LOG_EOMSINTF_201504  | 11576 |   271K|  9536   (1)|
|*  7 |       HASH JOIN             |                          | 61608 |  5775K| 19832   (1)|
|*  8 |        HASH JOIN            |                          | 11575 |   836K| 10297   (1)|
|*  9 |         TABLE ACCESS FULL   | CCT_LOG_EOMSINTF_201504  | 11576 |   497K|  9536   (1)|
|* 10 |         INDEX FAST FULL SCAN| IDX_CCT_WF_ACCEPT_201504 | 22933 |   671K|   759   (1)|
|  11 |        TABLE ACCESS FULL    | CCT_LOG_EOMSINTF_201504  |   231K|  4973K|  9534   (1)|
--------------------------------------------------------------------------------------------- 
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
     139867  consistent gets
          0  physical reads
        768  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
分析：
很明显，CCT_LOG_EOMSINTF_201504表不应该被扫描三次，并且还是全表扫描。如果这个表非常大，在OLAP系统就容易搞死人了。
那么怎么想办法，只让CCT_LOG_EOMSINTF_201504被扫描一次呢？答案，使用with as的方式来优化。
 
优化后：
with t1 as
(select accept_id, creation_time, exception_info
    from cct_log_eomsintf_201504
   where exception_info like '%启动保障机制%')
select count(c.accept_id) as acceptid
  from t1 c,
       (select b.accept_id accept_id, max(b.creation_time) creation_time
          from t1 b
         where exists (select 1 from t1 a where a.accept_id = b.accept_id)
         group by b.accept_id) d,
       cct_wf_accept_201504 e
where c.accept_id = d.accept_id
   and c.creation_time = d.creation_time
   and c.accept_id = e.accept_id
   and e.ring <> 128
   and e.ring <> 119
 
优化后：
 
Execution Plan
---------------------------------------------------------
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     1 |    84 |  9580   (1)|
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |
|   2 |   LOAD AS SELECT               | CCT_WF_ACCEPT_201504        |       |       |            |
|*  3 |    TABLE ACCESS FULL           | CCT_LOG_EOMSINTF_201504     | 11576 |   361K|  9536   (1)|
|   4 |   SORT AGGREGATE               |                             |     1 |    84 |            |
|   5 |    NESTED LOOPS                |                             |     1 |    84 |    44   (7)|
|*  6 |     HASH JOIN                  |                             |     1 |    66 |    42   (8)|
|   7 |      VIEW                      |                             | 11576 |   373K|    13   (0)|
|   8 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D69AB_7BCA41BF | 11576 |   361K|    13   (0)|
|   9 |      VIEW                      |                             | 11576 |   373K|    28   (8)|
|  10 |       HASH GROUP BY            |                             | 11576 |   418K|    28   (8)|
|* 11 |        HASH JOIN RIGHT SEMI    |                             | 11576 |   418K|    27   (4)|
|  12 |         VIEW                   |                             | 11576 |   158K|    13   (0)|
|  13 |          TABLE ACCESS FULL     | SYS_TEMP_0FD9D69AB_7BCA41BF | 11576 |   361K|    13   (0)|
|  14 |         VIEW                   |                             | 11576 |   260K|    13   (0)|
|  15 |          TABLE ACCESS FULL     | SYS_TEMP_0FD9D69AB_7BCA41BF | 11576 |   361K|    13   (0)|
|* 16 |     TABLE ACCESS BY INDEX ROWID| CCT_WF_ACCEPT_201504        |     1 |    18 |     2   (0)|
|* 17 |      INDEX UNIQUE SCAN         | PK_WFKACCEPT_AID_201504     |     1 |       |     1   (0)|
---------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        123  recursive calls
         11  db block gets
      45740  consistent gets
          1  physical reads
       1768  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
一致读取，从13w下降到4w。

还有像下面的sql，改写方式一致：
select count(*)
  from (select t1.accept_id_1860, count(1)
          from (select a.accept_id_1860, a.accept_id
                  from cct_log_appealintf1860_201504 a
                 where not exists (select 1
                          from cct_wf_accept_201504 b
                         where a.accept_id = b.accept_id)
                   and a.reqtype = '11') t1
         group by t1.accept_id_1860
        intersect
        select t2.accept_id_1860, count(1)
          from cct_log_appealintf1860_201504 t2
         where t2.accept_id_1860 in
               (select a.accept_id_1860
                  from cct_log_appealintf1860_201504 a
                 where not exists (select 1
                          from cct_wf_accept_201504 b
                         where a.accept_id = b.accept_id)
                   and a.reqtype = '11')
           and t2.reqtype = '11'
         group by t2.accept_id_1860) t3
通过实操案例介绍了SQL优化过程中的关键环节，以及相应的优化策略：索引优化、查询参数优化、表结构优化、存储过程优化、触发器优化等 SQL Query Optimization Made Easy
AI天才研究院
08-07 1121
大型数据库系统中处理复杂查询语句，是一个十分繁重的任务。这项工作需要根据查询需求、索引选择、数据分布、查询语言特性等多种因素综合分析设计出高效运行的查询语句。本文主要通过实操案例介绍了SQL优化过程中的关键环节，以及相应的优化策略。如对查询进行分类、基本的查询语句优化方法及工具，熟悉索引优化、查询参数优化、表结构优化、存储过程优化、触发器优化等；对SQL性能调优经验丰富，具有一定的经验积累。
sql案例分析：统计连续登陆、日活、蚂蚁森林、排名等
.
07-28 3818
这样处理的结果是，相同的 group by key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，剩余的能量全部用来领取“p002-沙柳”。问题：查询user_low_carbon表中每日流水记录，条件为：用户在2017年，连续三天（或以上）的天数里，每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
【SQL优化】四月份培训的一个sql优化案例1
Qiuht的技术博客
10-08 291
2015年4月，在对项目组里进行sql优化培训的时候，提到过一个生产环境的sql优化，下面搬过来记录在这里。下面是当时的培训材料： 请问大家： 在accept_id列上建立有普通索引，然后使用如下三种形式的like操作符，哪些会不走索引？ 1，where accept_id like '%0419%' ---查询04月19日的工单； 2，where accept_id li
【sql调优之执行计划】temp table transformation
08-17 591
使用系统临时表的时候，执行计划会有个TEMP TABLE TRANSFORMATION的operation，可以叫做临时表转化，看一个具体的例子，使用hint：/*+ materialize */来强制让oracle创建临时表。...
Oracle优化器:星型转换
ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN
01-15 1377
Oracle 8i中引入了星型转换(star transformation)的优化器新特性以便更有效地处理星型查询。星型查询语句多用于基于星型模型设计的数据仓库应用中。星型模型的称谓源于该种模型以图形化表现时看起来形似一颗海星。这颗星的中央会由一个或多个事实表(fact tables)组成，而各个触角上则分布着多个维度表(dimension tables)，如下图： 星型转换的基本思路是尽量
oracle 临时表子查询,Oracle 12CR2查询转换教程之临时表转换详解
weixin_39997194的博客
04-04 249
前言大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:SQL> show parameter star_transformation_enabledstar_transformation_enabled string FALSESQL> alter session set star_tran...
物化视图反而影响查询性能
ruilinruirui
01-14 397
老外发来邮件说 We are facing varied database response for same query but for different values, the time gap in the execution time (1st 9 min and 2nd 56 sec) for both queries is quite considerable and reason...
SQL优化.pptx
09-18
随着数据量的增长和技术的发展，如何提高SQL执行效率成为了一个重要的课题。本章节将从多个方面详细介绍SQL优化的相关知识点。 #### 二、SELECT语句优化 1. **避免在SELECT子句中使用 * ：** - 使用`SELECT * ...
ORACLE SQL 优化
06-06
#### 四、案例分析 假设有一个大型销售系统，其主要表`sales`包含大量历史交易记录。随着数据量的增长，查询性能成为瓶颈。通过分析发现，大多数查询都集中在`product_id`和`date`两个字段上。 1. **建立复合索引*...
关于ORACLE的SQL优化
04-16
在实际软件项目中，SQL优化通常是一个持续的过程。以下是一个简化的案例： 假设在电商系统中，有一张订单表(Order)，记录所有交易信息。随着业务增长，该表数据量急剧增加，导致查询响应时间变长。通过分析发现，大...
Oracle诊断事件列表
键者天行
02-27 7220
经常有人问，Oracle有哪些诊断事件，各是什么含义，怎样使用？这些问题问得太多太多，Oracle没有过多的说明，实际上也不需要太多的说明。我们可以通过以下代码查询得到Oracle的诊断事件及说明，通过这些，我想就足够了。列举在这里给自己参考。1.代码SET linesize 120SET feedback offSET SERVEROUTPUT OND
oracle学习 sql基本语法(三),教为学：Oracle SQL学习之路（三）：分析函数之统计...
weixin_36472718的博客
04-03 108
教为学：Oracle SQL学习之路(三)：分析函数之统计前言与统计相关的分析函数有哪些？SUMAVGMAX/MINFIRST_VALUE/LAST_VALUE等等。我们从最最最基础的SUM、AVG开始吧。惯例，先上例子。再上结果。SUM(SAL)OVER()--------------29025290252902529025290252902529025290252902529025290252...
ITPUB SQL大赛之BUG（一）
ckawt40802的专栏
03-30 96
由于SQL大赛题目相对比较困难，不但需要使用大量的特性，且SQL实现十分复杂，一般运行时间也会比较长，这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug。 这一篇介绍11.2上优化器处理树形查询的...
Optimizer Transformations: Star Transformation
czmmiao的专栏
06-30 190
Star transformation was introduced in Oracle 8i to process star queries efficiently. These queries are commonly used in data warehouse applications that follow the Star Schema data model. The Star S...
Oracle 自动生成的视图VM_NSO_1
weixin_30783913的博客
09-01 302
作者：Jerry 有时候优化sql的时候，在执行计划中看到有VM_NSO_X的视图，在Oracle定义中，可以吧NSO理解为nested subquery optimizing，功能就是把in转换为join，把not in转换为anti join等，当然转换的时候有一定的限制。下面我们来简单看下会生成VM_NSO_1视图的几个例子 1. 创建2个测试表 SQL> create ta...
性能调优之Transformation
weixin_30952535的博客
04-11 123
优化之Aggregator组件 优化之Custom组件 优化之Joiner组件 优化之Lookup组件 优化之Normalizer组件 优化之Sequence Generator组件 优化之Sorter组件 优化之Source Qualifier组件 优化之SQL组件 优化之XML组件 减少组件报错 转载于:https://www.cnblogs.com/shiliye/...
【沃趣科技】执行计划-5：第一个子操作的变化
woqutechteam的博客
11-19 292
沃趣科技作为国内领先的数据库云平台解决方案提供商，一直致力于企业级数据库云平台产品的研发，为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台，满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队，优质的产品，前沿的技术，贴心的服务赢得了客户的信任与尊重，也获得了市场的认同。 我提出了“子操作按先后顺序执行，递归向下”的解释执行计划的基本策略，...
SQL调优指南笔记5：Query Transformations
In-Memory Computing Technology
11-04 415
本文为SQL Tuning Guide第5章“查询转换”的笔记。 5.1 OR Expansion 5.2 View Merging 5.2.1 Query Blocks in View Merging 5.2.2 Simple View Merging 5.2.3 Complex View Merging 5.3 Predicate Pushing 5.4 Subquery Unnesting 5.5 Query Rewrite with Materialized Views 5.5.1 About Qu
Oracle 12CR2查询转换之临时表转换
10-25 170
在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中: SQL>showparameterstar_transformatio...
掌握DB2 SQL函数：详细使用方法与大全
- NULLIF：如果两个参数相等，则返回null，否则返回第一个参数的值。 - CASE：根据条件来选择返回不同的值。 - ROW_NUMBER：返回一个结果集中每一行的序列号。 在DB2数据库中使用这些函数可以处理数据，完成各种...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值