大量逻辑读的瓶颈分析和优化

原创 转载请注明出处

我的工作记录： 

昨天早上生产库出现了大约一个小时资源耗尽的情况，分析得出语句如下：

MERGE INTO T_DPD_AGENT_CHANGE A

USING (SELECT A.AGENT_ID,

              A.ENTER_COMPANY_DATE ENTER_DATE,

              A.LEAVE_COMPANY_DATE LEAVE_DATE,

              A.AGENT_STATUS,

              D1.DEPT_ID TEAM,

              D1.PRINCIPAL_ID TEAM_LEADER,

              D2.DEPT_ID DEPT,

              D2.PRINCIPAL_ID DEPT_LEADER,

              D3.DEPT_ID REGION,

              D3.PRINCIPAL_ID REGION_LEADER,

              A.YEAR_MONTH

         FROM T_AGENT_BACKUP A,

              T_DEPT_BACKUP  D1,

              T_DEPT_BACKUP  D2,

              T_DEPT_BACKUP  D3

        WHERE A.AGENT_CATE = 1

          AND A.YEAR_MONTH = D1.YEAR_MONTH

          AND A.YEAR_MONTH = D2.YEAR_MONTH

          AND A.YEAR_MONTH = D3.YEAR_MONTH

          AND A.DEPT_ID = D1.DEPT_ID

          AND D1.PARENT_ID = D2.DEPT_ID

          AND D2.PARENT_ID = D3.DEPT_ID

       UNION

       SELECT A.AGENT_ID,

              A.ENTER_COMPANY_DATE ENTER_DATE,

              A.LEAVE_COMPANY_DATE LEAVE_DATE,

              A.AGENT_STATUS,

              D1.DEPT_ID TEAM,

              D1.PRINCIPAL_ID TEAM_LEADER,

              D2.DEPT_ID DEPT,

              D2.PRINCIPAL_ID DEPT_LEADER,

              D3.DEPT_ID REGION,

              D3.PRINCIPAL_ID REGION_LEADER,

              NULL

         FROM T_AGENT A, T_DEPT D1, T_DEPT D2, T_DEPT D3

        WHERE A.AGENT_CATE = 1

          AND A.DEPT_ID = D1.DEPT_ID

          AND D1.PARENT_ID = D2.DEPT_ID

          AND D2.PARENT_ID = D3.DEPT_ID) T

ON (A.AGENT_ID = T.AGENT_ID AND A.AGENT_STATUS = T.AGENT_STATUS AND A.TEAM = T.TEAM AND A.DEPT = T.DEPT AND A.REGION = T.REGION AND NVL(A.ENTER_DATE, DATE '1900-01-01') = NVL(T.ENTER_DATE, DATE '1900-01-01') AND NVL(A.LEAVE_DATE, DATE '1900-01-01') = NVL(T.LEAVE_DATE, DATE '1900-01-01') AND (A.YEAR_MONTH = T.YEAR_MONTH OR T.YEAR_MONTH IS NULL))

WHEN NOT MATCHED THEN

  INSERT

    (AGENT_ID,

     ENTER_DATE,

     LEAVE_DATE,

     AGENT_STATUS,

     TEAM,

     TEAM_LEADER,

     DEPT,

     DEPT_LEADER,

     REGION,

     REGION_LEADER,

     YEAR_MONTH)

  VALUES

    (T.AGENT_ID,

     T.ENTER_DATE,

     T.LEAVE_DATE,

     T.AGENT_STATUS,

     T.TEAM,

     T.TEAM_LEADER,

     T.DEPT,

     T.DEPT_LEADER,

     T.REGION,

     T.REGION_LEADER,

     T.YEAR_MONTH);

在预生产环境中这个语句的效率极低。

执行计划如下：

--------------------------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

--------------------------------------------------------------------------------------------------------------------

|   1 |  MERGE                     | T_DPD_AGENT_CHANGE |      1 |        |      1 |00:06:05.16 |     180M|   4968 |

|   2 |   VIEW                     |                    |      1 |        |    208K|00:41:23.42 |     180M|   4954 |

|   3 |    NESTED LOOPS OUTER      |                    |      1 |  55530 |    208K|00:41:22.38 |     180M|   4954 |

|   4 |     VIEW                   |                    |      1 |  55530 |    128K|00:00:02.78 |    5403 |   3575 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   5 |      SORT UNIQUE           |                    |      1 |  55530 |    128K|00:00:02.27 |    5403 |   3575 |

|   6 |       UNION-ALL            |                    |      1 |        |    128K|00:00:03.50 |    5403 |   3575 |

|*  7 |        HASH JOIN           |                    |      1 |  48752 |    121K|00:00:02.72 |    4677 |   3027 |

|   8 |         TABLE ACCESS FULL  | T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.10 |     825 |    820 |

|*  9 |         HASH JOIN          |                    |      1 |  74512 |    121K|00:00:01.83 |    3852 |   2207 |

|  10 |          TABLE ACCESS FULL | T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.10 |     819 |      0 |

|* 11 |          HASH JOIN         |                    |      1 |    113K|    121K|00:00:01.04 |    3033 |   2207 |

|  12 |           TABLE ACCESS FULL| T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.05 |     819 |      0 |

|* 13 |           TABLE ACCESS FULL| T_AGENT_BACKUP     |      1 |    114K|    121K|00:00:00.26 |    2214 |   2207 |

|* 14 |        HASH JOIN           |                    |      1 |   6778 |   6976 |00:00:00.13 |     726 |    548 |

|* 15 |         HASH JOIN          |                    |      1 |   2577 |   2336 |00:00:00.04 |     165 |     48 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|  16 |          TABLE ACCESS FULL | T_DEPT             |      1 |   2652 |   2652 |00:00:00.03 |      55 |     48 |

|* 17 |          HASH JOIN         |                    |      1 |   2614 |   2614 |00:00:00.01 |     110 |      0 |

|  18 |           TABLE ACCESS FULL| T_DEPT             |      1 |   2652 |   2652 |00:00:00.01 |      55 |      0 |

|  19 |           TABLE ACCESS FULL| T_DEPT             |      1 |   2652 |   2652 |00:00:00.01 |      55 |      0 |

|* 20 |         TABLE ACCESS FULL  | T_AGENT            |      1 |   6976 |   6976 |00:00:00.05 |     561 |    500 |

|  21 |     VIEW                   |                    |    128K|      1 |    201K|00:39:49.69 |     180M|   1379 |

|* 22 |      TABLE ACCESS FULL     | T_DPD_AGENT_CHANGE |    128K|      1 |    201K|00:39:47.65 |     180M|   1379 |

 

 

红色部分就是瓶颈所在，NEST LOOP JOIN 做了大量的对内部表的FULL SCAN 在内存中逻辑读，因为NEST LOOP JION 每对驱动表中有一行合适的数据就会在内部表中进行一次扫描，这里一共对T_DPD_AGENT_CHANGE表进行了128000此全表扫描，这个不是代码引起的循环，完全是因为NEST LOOP JION实现方式的特点导致。时间花费为39分钟，而整个语句大约在45分钟左右。

所以我们必须改变全表扫描的访问路径，或者改变JION 方式。

这里我使用的是建立联合索引来改变其访问方式

SQL> create index test

2         on  T_DPD_AGENT_CHANGE(AGENT_STATUS ,TEAM,REGION ,LEAVE_DATE,YEAR_MONTH );

然后分析统计数据后重新执行这个语句，执行计划如下：

 

----------------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------------------

|   1 |  MERGE                          | T_DPD_AGENT_CHANGE |      1 |        |      1 |00:00:24.03 |    5624K|

|   2 |   VIEW                          |                    |      1 |        |    215K|00:00:25.91 |    5624K|

|   3 |    NESTED LOOPS OUTER           |                    |      1 |  55530 |    215K|00:00:25.26 |    5624K|

|   4 |     VIEW                        |                    |      1 |  55530 |    128K|00:00:01.61 |    5391 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   5 |      SORT UNIQUE                |                    |      1 |  55530 |    128K|00:00:01.22 |    5391 |

|   6 |       UNION-ALL                 |                    |      1 |        |    128K|00:00:03.22 |    5391 |

|*  7 |        HASH JOIN                |                    |      1 |  48752 |    121K|00:00:02.44 |    4665 |

|   8 |         TABLE ACCESS FULL       | T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.10 |     820 |

|*  9 |         HASH JOIN               |                    |      1 |  74512 |    121K|00:00:01.78 |    3845 |

|  10 |          TABLE ACCESS FULL      | T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.05 |     819 |

|* 11 |          HASH JOIN              |                    |      1 |    113K|    121K|00:00:01.01 |    3026 |

|  12 |           TABLE ACCESS FULL     | T_DEPT_BACKUP      |      1 |  45639 |  48600 |00:00:00.05 |     819 |

|* 13 |           TABLE ACCESS FULL     | T_AGENT_BACKUP     |      1 |    114K|    121K|00:00:00.24 |    2207 |

|* 14 |        HASH JOIN                |                    |      1 |   6778 |   6976 |00:00:00.07 |     726 |

|* 15 |         HASH JOIN               |                    |      1 |   2577 |   2336 |00:00:00.01 |     165 |

 

PLAN_TABLE_OUTPUT                                                                                                                                                                                                         

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|  16 |          TABLE ACCESS FULL      | T_DEPT             |      1 |   2652 |   2652 |00:00:00.01 |      55 |

|* 17 |          HASH JOIN              |                    |      1 |   2614 |   2614 |00:00:00.01 |     110 |

|  18 |           TABLE ACCESS FULL     | T_DEPT             |      1 |   2652 |   2652 |00:00:00.01 |      55 |

|  19 |           TABLE ACCESS FULL     | T_DEPT             |      1 |   2652 |   2652 |00:00:00.01 |      55 |

|* 20 |         TABLE ACCESS FULL       | T_AGENT            |      1 |   6976 |   6976 |00:00:00.02 |     561 |

|  21 |     VIEW                        |                    |    128K|      1 |    215K|00:00:23.09 |    5619K|

|* 22 |      TABLE ACCESS BY INDEX ROWID| T_DPD_AGENT_CHANGE |    128K|      1 |    215K|00:00:21.72 |    5619K|

|* 23 |       INDEX RANGE SCAN          | TEST               |    128K|      1 |   6879K|00:00:17.91 |     420K|

 

红色部分显示了优化后这个访问执行的时间，以前的39分钟变为了不到1分钟，所以加联合索引的效果非常明显。当然我是在预生产环境做的测试

，其数据量和生产差不多，虽然机器性能有出入，但是不会影响优化器对执行计划的判断。

当然也可以通过SQL TRACE 10046 EVENT来查看大量的逻辑读。但要确定瓶颈在哪里出现还是以上的方法为好，我也查看SQL TRACE 发现大量的逻辑读发生在SQL 语句执行阶段，这个也很好理解，在执行过程中确实出现了大量的逻辑读，取回阶段其实只有很少行

所以这个问题解决的方法就是建立联合索引，谢谢!

时间： 2024-09-23 13:31:47

大量逻辑读的瓶颈分析和优化的相关文章

《Oracle数据库性能优化方法论和最佳实践》——2.5　基于资源瓶颈分析的优化方法论

2.5 基于资源瓶颈分析的优化方法论 2.5.1 基于资源瓶颈分析优化方法论简述 Oracle要做优化,大部分人首先会想到瓶颈在哪里?资源瓶颈分析是如此之普及,以至于无论懂还是不懂的人都知道"瓶颈"这个术语,都知道性能优化首先要找到这个瓶颈,然后消除这个瓶颈.数据库系统的资源主要包括:CPU.内存和虚拟内存.I/O子系统.网络子系统. 绝大部分开发人员在写程序的时候都假设资源是无限的,CPU是无限快,内存是无限多,磁盘无限大并且像内存一样快,网络带宽无限并且像光速一样运行.事实上,大家

[20150504]奇怪的逻辑读union or.txt

[20150504]奇怪的逻辑读union or.txt --在优化一个项目时遇到一个奇怪的情况,自己做例子测试看看. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------

Linux转发瓶颈分析、评估、优化与解决方案

线速概念 很多人对这个线速概念存在误解.认为所谓线速能力就是路由器/交换机就像一根网线一样.而这,是不可能的.应该考虑到的一个概念就是延迟.数据包进入路由器或者交换机,存在一个核心延迟操作,这就是选路,对于路由器而言,就是路由查找,对于交换机而言,就是查询MAC/端口映射表,这个延迟是无法避开的,这个操作需要大量的计算机资源,所以不管是路由器还是交换机,数据包在内部是不可能像在线缆上那样近光速传输的.类比一下你经过十字街头的时候,是不是要左顾右盼呢?        那么,设备的线速能力怎么衡量呢

Oracle Arraysize设置对于逻辑读的影响实例分析_oracle

当执行一条SQL查询的时候,为了获得满足的数据,查询在这个过程中完成解析,绑定,执行和提取数据等一系列步骤,这些步骤都是单独执行的,满足条件的数据行必须由数据库返回给应用:对于任何大小的结果集,需要返回的数据行很可能不是在一次往返调用过程中传递给应用的! 每次调用过程中,数据库与客户端之间的往返回路数将一定层次上影响总的响应时间,其中除了提取数据(FETCH)步骤,其余步骤(解析,绑定,执行)都只执行一次,这也是必要的,Oracle需要获得满足查询条件的所有数据结果从而执行多次提取操作. 关于提

【SQL 优化】异常的逻辑读

实验环境 SQL> select * from v$version; BANNER                                                                          -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.

mysql性能优化-慢查询分析、优化索引和配置

目录 一.优化概述 二.查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询   2索引及查询优化 三.配置优化 1)      max_connections 2)      back_log 3)      interactive_timeout 4)      key_buffer_size 5)      query_cache_size 6)      record_buffer_size 7)      read_rnd_b

大型网站性能监测、分析与优化常见问题Q&A

大型网站性能监测.分析与优化常见问题Q&A @tanwen110 (唐文),曾负责腾讯四大平台之一网络媒体平台的整体运维.运营规划工作:曾任百度T7架构师和百度性能优化TOPIC.百度UAQ.APM平台负责人:畅销书<海量运维.运营规划之道>作者: mmTrix创始人,后并入上市公司高升控股(000971.SZ),出任技术VP. 购买链接:http://item.jd.com/11962556.html Q:基于rest的微服务,有什么好的监控方案推荐的,最好是对系统影响最小的 A:自

buffer cache实验9:从buffer caceh中读取数据块解析-从逻辑读到物理读

先来张大图: 所用SQL语句: BYS@ ocm1>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,deptno from bys.test;     FILE#     BLOCK#     DEPTNO ---------- ---------- ----------         4        391         10 就以上图为例,文字描述

浅析Oracle全表扫描下的逻辑读

T1表全表扫描产生逻辑读的分析 做个实验给你演示一下:以表t1为例,对段t1做dump 1.t1表就一条数据 gyj@OCM> select * from t1;      ID NAME ---------- ----------       1 AAAAA 2.找t1段的段头块 gyj@OCM> select  header_file,header_block from dba_segments where segment_name='T1' and owner='GYJ'; HEADER