SQL优化一则：CBO查询SQL一次比一次慢

最新推荐文章于 2023-12-19 19:53:11 发布
最新推荐文章于 2023-12-19 19:53:11 发布
阅读量1.7k 收藏
点赞数
分类专栏： Oracle 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lichangzai/article/details/8350638
版权
问题：
今天在开发的新上线一个功能后，下面SQL消耗了大量的CPU，数据库处一度处于满负荷进行，使用率达到100%.赶紧kill掉了正进行的SQL，以免对其它业务产生影响。
通过测试，发现一个很奇怪的问题，SQL在第一次执行时会很快，在连续执行几次后，一次比一次的慢。
甚至到最后一直挂起在那，产生等待。当停掉后，过会（10分钟左右）再执行，第一次又会变快，如果再连接执行几次，又会出现同样的慢直至挂起问题。


下面是测试结果：
--第一次执行会很快，逻辑读是8000多
--第二次执行会变慢，逻辑读是44513，如下：
SQL> Select * 
  2     From (Select Task.Order_Id As Od,
  3                                                      Task.Task_Type As Tp,
  4                                                      Task.Task_Status As Ts,
  5                                                      To_Char(Task.Create_Time, 'yyyy-mm-dd hh24:mi:ss') As Ct,
  6                                                      Task.Create_User As Cu,
  7                                                      To_Char(Task.Handle_Time, 'yyyy-mm-dd hh24:mi:ss') As Ht,
  8                                                      Task.Task_User As Sku,
  9                                                      Task.Id As Ind,
 10                                                      o.Channel_Id
 11                                     From Tk_Taskinfo_Dt Task, Tk_Orders_Dt o
 12                              Where 1 = 1
 13                                      And Task.Order_Id = o.Odrid
 14                                      And Task_Status <> '0002900001'
 15                                      And Task.Task_User = 'tianyh'
 16                                      And Task.Accept_Time >=
 17                                                      To_Date('2012-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 18                                      And ((o.Channel_Id = 'QD00002' And
 19                                                      Task.Task_Type In
 20                                                      ('0002000013', '0002000017', '0002000038', '0002000008',
 21                                                             '0002000031', '0002000004', '0002000012', '0002000016',
 22                                                             '0002000032', '0002000035', '0002000024', '0002000010',
 23                                                             '0002000014', '0002000019', '0002000006', '0002000034',
 24                                                             '0002000041')) Or
 25                                                      (o.Channel_Id = 'QD00003' And
 26                                                      Task.Task_Type In
 27                                                      ('0002000041', '0002000004', '0002000010', '0002000012',
 28                                                             '0002000034', '0002000006', '0002000019', '0002000008',
 29                                                             '0002000031', '0002000014', '0002000017', '0002000016',
 30                                                             '0002000024', '0002000013', '0002000035', '0002000038',
 31                                                             '0002000032')) Or
 32                                                      (o.Channel_Id = 'QD00001' And
 33                                                      Task.Task_Type In
 34                                                      ('0002000041', '0002000016', '0002000019', '0002000017',
 35                                                             '0002000035', '0002000008', '0002000004', '0002000034',
 36                                                             '0002000031', '0002000006', '0002000013', '0002000024',
 37                                                             '0002000032', '0002000014', '0002000038', '0002000010',
 38                                                             '0002000012')))
 39                             Union All
 40                             Select Task.Order_Id As Od,
 41                                                      Task.Task_Type As Tp,
 42                                                      Task.Task_Status As Ts,
 43                                                      To_Char(Task.Create_Time, 'yyyy-mm-dd hh24:mi:ss') As Ct,
 44                                                      Task.Create_User As Cu,
 45                                                      To_Char(Task.Handle_Time, 'yyyy-mm-dd hh24:mi:ss') As Ht,
 46                                                      Task.Task_User As Sku,
 47                                                      Task.Id As Ind,
 48                                                      o.Channel_Id
 49                                     From Tk_Taskinfo_Finish_Dt Task, Tk_Orders_Dt o
 50                              Where 1 = 1
 51                                      And Task.Order_Id = o.Odrid
 52                                      And Task.Task_User = 'tianyh'
 53                                      And Task.Accept_Time >=
 54                                                      To_Date('2012-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 55                                      And ((o.Channel_Id = 'QD00002' And
 56                                                      Task.Task_Type In
 57                                                      ('0002000013', '0002000017', '0002000038', '0002000008',
 58                                                             '0002000031', '0002000004', '0002000012', '0002000016',
 59                                                             '0002000032', '0002000035', '0002000024', '0002000010',
 60                                                             '0002000014', '0002000019', '0002000006', '0002000034',
 61                                                             '0002000041')) Or
 62                                                      (o.Channel_Id = 'QD00003' And
 63                                                      Task.Task_Type In
 64                                                      ('0002000041', '0002000004', '0002000010', '0002000012',
 65                                                             '0002000034', '0002000006', '0002000019', '0002000008',
 66                                                             '0002000031', '0002000014', '0002000017', '0002000016',
 67                                                             '0002000024', '0002000013', '0002000035', '0002000038',
 68                                                             '0002000032')) Or
 69                                                      (o.Channel_Id = 'QD00001' And
 70                                                      Task.Task_Type In
 71                                                      ('0002000041', '0002000016', '0002000019', '0002000017',
 72                                                             '0002000035', '0002000008', '0002000004', '0002000034',
 73                                                             '0002000031', '0002000006', '0002000013', '0002000024',
 74                                                             '0002000032', '0002000014', '0002000038', '0002000010',
 75                                                             '0002000012')))
 76                              Order By Ct Desc)
 77   Where Rownum <= 25;


已选择25行。






执行计划
----------------------------------------------------------
Plan hash value: 523130039


-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |    25 |  2650 |   110   (1)| 00:00:02 |       |       |
|*  1 |  COUNT STOPKEY                            |                       |       |       |            |          |       |       |
|   2 |   VIEW                                    |                       |    26 |  2756 |   110   (1)| 00:00:02 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                  |                       |    26 |  3496 |   109  (97)| 00:00:02 |       |       |
|   4 |     UNION-ALL                             |                       |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                        |                       |     1 |   121 |     4   (0)| 00:00:01 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID        | TK_TASKINFO_DT        |     1 |    96 |     2   (0)| 00:00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                  | IDX_TK_TASKINFO3      |     1 |       |     1   (0)| 00:00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|  12 |       NESTED LOOPS                        |                       |    25 |  3375 |   105   (0)| 00:00:02 |       |       |
|  13 |        INLIST ITERATOR                    |                       |       |       |            |          |       |       |
|  14 |         TABLE ACCESS BY GLOBAL INDEX ROWID| TK_TASKINFO_FINISH_DT |    84 |  9240 |    47   (0)| 00:00:01 | ROWID | ROWID |
|* 15 |          INDEX RANGE SCAN                 | IDX$$_08700001        |    84 |       |    20   (0)| 00:00:01 |       |       |
|* 16 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------


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


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      44513  consistent gets
          0  physical reads
        116  redo size
       4125  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed




--第三次的执行计划，逻辑读是257654


执行计划
----------------------------------------------------------
Plan hash value: 523130039


-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |    25 |  2650 |   110   (1)| 00:00:02 |       |       |
|*  1 |  COUNT STOPKEY                            |                       |       |       |            |          |       |       |
|   2 |   VIEW                                    |                       |    26 |  2756 |   110   (1)| 00:00:02 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                  |                       |    26 |  3496 |   109  (97)| 00:00:02 |       |       |
|   4 |     UNION-ALL                             |                       |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                        |                       |     1 |   121 |     4   (0)| 00:00:01 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID        | TK_TASKINFO_DT        |     1 |    96 |     2   (0)| 00:00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                  | IDX_TK_TASKINFO3      |     1 |       |     1   (0)| 00:00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|  12 |       NESTED LOOPS                        |                       |    25 |  3375 |   105   (0)| 00:00:02 |       |       |
|  13 |        INLIST ITERATOR                    |                       |       |       |            |          |       |       |
|  14 |         TABLE ACCESS BY GLOBAL INDEX ROWID| TK_TASKINFO_FINISH_DT |    84 |  9240 |    47   (0)| 00:00:01 | ROWID | ROWID |
|* 15 |          INDEX RANGE SCAN                 | IDX$$_08700001        |    84 |       |    20   (0)| 00:00:01 |       |       |
|* 16 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------


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




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     257654  consistent gets
          0  physical reads
         64  redo size
       4125  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed






--第三次的执行计划，逻辑读是538909
执行计划
----------------------------------------------------------
Plan hash value: 523130039


-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |    25 |  2650 |   110   (1)| 00:00:02 |       |       |
|*  1 |  COUNT STOPKEY                            |                       |       |       |            |          |       |       |
|   2 |   VIEW                                    |                       |    26 |  2756 |   110   (1)| 00:00:02 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                  |                       |    26 |  3496 |   109  (97)| 00:00:02 |       |       |
|   4 |     UNION-ALL                             |                       |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                        |                       |     1 |   121 |     4   (0)| 00:00:01 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID        | TK_TASKINFO_DT        |     1 |    96 |     2   (0)| 00:00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                  | IDX_TK_TASKINFO3      |     1 |       |     1   (0)| 00:00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS                         |                       |       |       |            |          |       |       |
|  12 |       NESTED LOOPS                        |                       |    25 |  3375 |   105   (0)| 00:00:02 |       |       |
|  13 |        INLIST ITERATOR                    |                       |       |       |            |          |       |       |
|  14 |         TABLE ACCESS BY GLOBAL INDEX ROWID| TK_TASKINFO_FINISH_DT |    84 |  9240 |    47   (0)| 00:00:01 | ROWID | ROWID |
|* 15 |          INDEX RANGE SCAN                 | IDX$$_08700001        |    84 |       |    20   (0)| 00:00:01 |       |       |
|* 16 |        INDEX UNIQUE SCAN                  | PK_TK_ORDERS_ODRID    |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |       TABLE ACCESS BY INDEX ROWID         | TK_ORDERS_DT          |     1 |    25 |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------


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




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     538909  consistent gets
          0  physical reads
          0  redo size
       4125  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed


SQL> 


--如果再次一直快速连续读，SQL就会挂起，产生长时间的等待。
--但每次的执行计划都是一样的。


解决：
1.尝试对所涉及的表的索引进行rebuild,重新分析表，收缩表，但是效果不大。
2.最终在SQL上加上/*+ rule*/提示，执行计划虽然还是一样，但是不会出现上面多次连续执行SQL产生等待的问题。
如下：
SQL> Select /*+ rule*/ * 
  2     From (Select Task.Order_Id As Od,
  3                                                      Task.Task_Type As Tp,
  4                                                      Task.Task_Status As Ts,
  5                                                      To_Char(Task.Create_Time, 'yyyy-mm-dd hh24:mi:ss') As Ct,
  6                                                      Task.Create_User As Cu,
  7                                                      To_Char(Task.Handle_Time, 'yyyy-mm-dd hh24:mi:ss') As Ht,
  8                                                      Task.Task_User As Sku,
  9                                                      Task.Id As Ind,
 10                                                      o.Channel_Id
 11                                     From Tk_Taskinfo_Dt Task, Tk_Orders_Dt o
......
......


结论：
通过oracle CBO查询SQL会产生等待
通过oracle RBO查询SQL会得到很好效果。

不过具体原因还有待研究，而且通过使用RBO后，随着数据量的变量，不知会不会效率变得低下，还有待观察。

通过查找参考，发现有位网友也出现过同样的问题，只不过他第二次查询的执行计划是变了，我这里还是同样的执行计划。

http://blog.sina.com.cn/s/blog_62e039f00100yvs1.html


常飞梦
关注
专栏目录
oracle怎么定位一个sql效率慢的,Oracle中SQL语句执行效率的查找与解决 （1）
weixin_36272649的博客
04-03 1116
Oracle中SQL语句执行效率问题的查找与解决:一、识别占用资源较多的语句的方法(4种方法)1.测试组和最终用户反馈的与反应缓慢有关的问题。2.利用V_$SQLAREA视图提供了执行的细节。(执行、读取磁盘和读取缓冲区的次数)• 数据列EXECUTIONS：执行次数DISK_READS：读盘次数COMMAND_TYPE：命令类型(3:select,2:insert;6:update;7delet...
oracle数据库sql的优化总结
12-16
Oracle数据库SQL优化是一个复杂而重要的主题，涉及到许多技术细节和策略。以下是对标题和描述中提到的优化点的详细说明： 1. **使用`WHERE`少使用`HAVING`**： - `WHERE`子句在SQL查询中用于在数据筛选阶段过滤行...
SQL优化与CBO
JH_Zhai的博客
04-08 1920
SQL优化与CBO SQL从诞生到现在已经有几十年的时间了，尽管前几年nosql一度自我感觉良好号称要去掉sql，却也被现实教做人不得不改口，说是自己其实是not only sql，从这点可以看出sql语言的强大和通用。 说回sql，sql是一种声明式语言，所谓声明式，就是用户只需要告诉机器我要什么样的结果，机器会自己摸索并帮助用户找到结果返回。与之相比的是命令式语言，需要详细告诉机器如何执行，比如常见的编程语言。 那么作为声明式语言，如何帮助用户高效准确地获取到结果，这就是机器的责任。在这其中，SQL优化
基于CBO的SQL优化之被动优化SQL语句（4）
u010099683的博客
05-22 192
适用场景：在版本升级或者应用程序升级后的CBO的执行策略会发生变化，使得CBO因为环境的变化而改变计划的执行，所以此时就需要某种方式来确保CBO执行计划的稳定性，这种方式就是存储大纲，它维持CBO的计划执行稳定性，只要是相同的查询SQL语句都使用相同的执行计划。而对于OLTP系统来说，有大量的插入或者删除操作，对于这样的系统要尽量少应用索引（只在必要的列上使用索引，如主键上创建索引），因为索引在大量DML（增删改查）操作时会触发自身的维护，并且需要储存空间，索引本身也要消耗系统资源。
基于CBO的SQL优化之统计信息（1）
u010099683的博客
05-22 245
而一旦系统部署好，应用系统本身无法改变，我们只能通过对文件存储做合理分布以减少I/O，对表创建合理的索引、建立分区表等，这样的系统性能优化称为被动的性能调整，而这就是作为DBA大多数的工作内容。统计信息是优化器选择最优执行计划的重要依据之一，统计信息包括表的行数、列的数据分布、索引的高度等等。2、系统资源：考虑系统资源的使用情况，SQL_TRACE主要用于检查数据库的异常情况，通过跟踪数据库的活动，找到有问题的语句；2) FIRST_ROWS_n：输出查询的前n行数据，满足最快响应的查询需求。
SQL进阶理论篇（十四）：CBO优化器是如何计算代价的？
最新发布
经年藏殊的博客
12-19 496
对基于代价的CBO优化器，介绍了其可调整的各项代价参数，以及如何调整这些参数，并简单介绍了一下CBO的代价计算模型
Oracle SQL性能优化系列学习一
12-16
- 全表扫描（Full Table Scan, FTS）：Oracle一次性读取多个数据块以遍历所有记录，适合于小表或全表扫描比索引访问更经济的情况。 - 通过ROWID访问：ROWID包含记录的物理位置信息，通过索引快速定位ROWID能提高...
ORACLE数据库中SQL优化解析.pdf
09-19
如CHOOSE会根据数据字典的统计信息来选择RBO或CBO，而ALL_ROWS则旨在最大化吞吐量，FIRST_ROWS_N专注于获取最先的N条记录，FIRST_ROWS着重于快速获取结果的一部分，RULE模式则纯粹采用基于规则的优化策略。...
踩坑CBO，解决那些坑爹的SQL优化问题
weixin_34202952的博客
05-15 404
本文根据DBAplus社群第93期线上分享整理而成。 讲师介绍丁俊 新炬网络首席性能优化专家 SQL审核产品经理 DBAplus社群联合发起人，《剑破冰山-Oracle开发艺术》副主编。 Oracle ACEA，ITPUB开发版资深版主，十年电信行业从业经验。 本次分享大纲： CBO优化器存在哪些坑 CBO优化器坑的解...
基于CBO的SQL优化和Oracle实例优化
朱培(知浅_ZLH)
12-08 4049
SQL优化是数据优化的重要方面，本文将分析Oracle自身的CBO优化，即基于成本的优化方法。Oracle为了自动的优化sql语句需要各种统计数据作为优化基础。外面会通过sql的追踪来分析sql的执行过程，消耗的资源信息。对于数据库的性能问题往往是在系统部署一段时间之后出现的，即大量用户开始使用该系统，系统的数据处理量和各种计算复杂性增加的时候，这个时候往往会追溯到系统的初始设计阶段，所以我们还是
CBO参数学习之查询隐藏参数
数据帝
07-08 1009
set lines 200 set pages 100 col KSPPINM for a45 col KSPPSTVL for a10 col KSPPDESC for a80 select x.ksppinm, y.ksppstvl, x.ksppdesc from x$ksppi x , x$kspp
SQLSERVER排查CPU占用高的情况
dingshen2767的博客
06-12 1324
SQLSERVER排查CPU占用高的情况 今天中午，有朋友叫我帮他看一下数据库，操作系统是Windows2008R2 ,数据库是SQL2008R2 64位 64G内存，16核CPU 硬件配置还是比较高的，他说服务器运行的是金蝶K3软件，数据库实例里有多个数据库 现象 他说是这几天才出现的，而且在每天的某一个时间段才会出现CPU占用高的情况 内存占用不太高，只占用了30个...
基于CBO的SQL优化
weixin_42935801的博客
09-19 1017
基于CBO的SQL优化 应用程序的优化分为很多方面，如硬件，数据库配置，网络宽带等等。这里我们只讨论，如何基于ORACLE 10G数据库（使用CBO优化器），写出最有效率的SQL语句。 CBO介绍： 基于成本的优化器（Cost-Based Optimization） ★计算各种可能的“执行计划”的Cost，从中选择Cost最低的执行方案，作为实际运行方案。 CBO限制：当检索的表达到一定数量之后，...
为什么MySQL做查询语句时，第一次会很慢，但是第二次，第三次就会变快
热门推荐
Ecloss
11-15 1万+
为什么MySQL做查询语句时，第一次会很慢，但是第二次，第三次就会变快 为什么MySQL的查询事务第一次执行会很慢，第二次，第三次就会快很多呢？ 在国外，有个老外这么提问 Hi, I have an sql query which takes 8 seconds in the first run. The next run there after takes 0.5 seconds and a...
[20170406]查询占用内存情况.txt
weixin_34364071的博客
04-06 76
[20170406]查询占用内存情况.txt --//昨天看了链接,http://www.dbaqhs.com/archives/35,发现脚本不对,显示KEEP buffer cache. --//google找到如下脚本: col component format a32 select min(BASEAD...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值