文档章节

又是latch: cache buffers chains惹得祸

o
 osc_mervd488
发布于 2018/04/20 16:03
字数 1610
阅读 26
收藏 0

精选30+云产品,助力企业轻松上云!>>>

前言

一大早,客户给我打电话说:

xx,应用很慢,查询数据总是超时,让我看看。。。

根据多年DBA经验,首当其冲的肯定是去查询数据库在这段时间都在干嘛。

分析

导出awr报告分析

1). 数据库在此时间段非常繁忙。
这里写图片描述
2). 查看Top 5 Timed Events,出现了Concurrency等待事件latch: library cache**
这里写图片描述
3). 查看SQL ordered by Gets,不看不知道,一看吓一跳
这里写图片描述
4). *50pwxa3bzp7gkSQL语句

select *
  from (select d.*, rownum as num
          from (SELECT A.BILLNO,
                       A.BILLCODE,
                       A.GETDATE,
                       A.GETUNITCODE,
                       A.GETCODE,
                       A.GETORGANCODE,
                       A.USEORGANCODE,
                       A.USEDATE,
                       A.USEUNITCODE,
                       A.USERCODE,
                       A.CURRENCYCODE,
                       A.AMOUNT,
                       A.NAME,
                       A.NOTES,
                       A.STATUSCODE,
                       A.IFPAGEONHOLE,
                       A.OPCODE,
                       A.OPUNITCODE,
                       A.OPDATE,
                       A.LOCKTIME,
                       A.GETAGENTCODE,
                       (SELECT D.AGENTNAME
                          FROM SYN_MM_AGENTCODE_TC D
                         WHERE D.AGENTCODE = A.GETAGENTCODE) AS GETAGENTNAME,
                       A.USEAGENTCODE,
                       A.OUTSTATUS,
                       CASE A.BILLCODE
                         WHEN 'B2010005' THEN
                          A.FACTBILLCODE
                         ELSE
                          ''
                       END FACTBILLCODE,
                       A.SALES,
                       A.FROMDATE,
                       A.TODATE,
                       (SELECT BILLNAME
                          FROM BD_BILLCODE
                         WHERE BILLCODE = A.BILLCODE) BILLNAME,
                       (SELECT HANDLERNAME
                          FROM BD_HANDLER
                         WHERE HANDLERCODE = A.USERCODE) USERNAME,
                       (SELECT HANDLERNAME
                          FROM BD_HANDLER
                         WHERE HANDLERCODE = A.GETCODE) GETERNAME,
                       (SELECT NO3
                          FROM B_BILLDETAIL
                         WHERE BILLNO = A.BILLNO
                           AND BILLCODE = A.BILLCODE
                           AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICENO,
                       (SELECT NO4
                          FROM B_BILLDETAIL
                         WHERE BILLNO = A.BILLNO
                           AND BILLCODE = A.BILLCODE
                           AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICECODE
                  FROM B_BILL A
                 WHERE 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND TRIM(BILLNO) >= :B1
                   AND TRIM(BILLNO) <= :B2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                 ORDER BY A.BILLNO) d
         where rownum <= 1)
 where num > 0

执行计划:
 Plan hash value: 4085294641

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |  4632 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL             | CHAGENTBASE         |     1 |    56 |     6   (0)| 00:00:01 |
|   2 |  TABLE ACCESS BY INDEX ROWID   | BD_BILLCODE         |     1 |    31 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN            | PK_BD_BILLCODE      |     1 |       |     0   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID   | T_EMPLOYEE_VIEW     |     1 |    26 |     2   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN             | IND_T_EMPLOYEE_VIEW |     1 |       |     1   (0)| 00:00:01 |
|   6 |  TABLE ACCESS BY INDEX ROWID   | T_EMPLOYEE_VIEW     |     1 |    26 |     2   (0)| 00:00:01 |
|*  7 |   INDEX RANGE SCAN             | IND_T_EMPLOYEE_VIEW |     1 |       |     1   (0)| 00:00:01 |
|   8 |  TABLE ACCESS BY INDEX ROWID   | B_BILLDETAIL        |     1 |    50 |     4   (0)| 00:00:01 |
|*  9 |   INDEX RANGE SCAN             | PK_B_BILLDETAIL_02  |     1 |       |     3   (0)| 00:00:01 |
|  10 |  TABLE ACCESS BY INDEX ROWID   | B_BILLDETAIL        |     1 |    50 |     4   (0)| 00:00:01 |
|* 11 |   INDEX RANGE SCAN             | PK_B_BILLDETAIL_02  |     1 |       |     3   (0)| 00:00:01 |
|* 12 |  VIEW                          |                     |     1 |  4632 |     9   (0)| 00:00:01 |
|* 13 |   COUNT STOPKEY                |                     |       |       |            |          |
|  14 |    VIEW                        |                     |     2 |  9238 |     9   (0)| 00:00:01 |
|  15 |     TABLE ACCESS BY INDEX ROWID| B_BILL              | 17395 |  3822K|     9   (0)| 00:00:01 |
|* 16 |      INDEX FULL SCAN           | PK_B_BILL_01        |     2 |       |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   1 - filter("D"."AGENTCODE"=:B1)
   3 - access("BILLCODE"=:B1)
   5 - access("A"."CODE"=:B1)
   7 - access("A"."CODE"=:B1)
   9 - access("BILLNO"=:B1 AND "BILLCODE"=:B2 AND "FACTBILLCODE"=:B3)
  11 - access("BILLNO"=:B1 AND "BILLCODE"=:B2 AND "FACTBILLCODE"=:B3)
  12 - filter("NUM">0)
  13 - filter(ROWNUM<=1)
  16 - filter(TO_NUMBER(TRIM("BILLNO"))>=601710100010 AND 
              TO_NUMBER(TRIM("BILLNO"))<=601710100010)

5). 对sql语句进行分析

1、SQL语句中有很多标量子查询,我们可以利用left join 对其改写。

2、id = 1 为TABLE ACCESS FULL,表示CHAGENTBASE走的是全表扫描,在标量子查询中,主表返回多少
行,子表也跟着被扫描多少次,所以需要对CHAGENTBASE建索引。

3、SQL语句中出现TRIM(BILLNO) >= :B1 AND TRIM(BILLNO) <= :B2,导致ID = 16 为
INDEX FULL SCAN。对主键进行索引全扫描,这种访问方式是最垃圾的。

优化

1)创建索引
create indexIDX_CHAGENTBASE_TEST on CHAGENTBASE (AGENTCODE); 

2)标量改成left join
select *
  from (select G.*, rownum as num
          from (SELECT A.BILLNO,
                       A.BILLCODE,
                       A.GETDATE,
                       A.GETUNITCODE,
                       A.GETCODE,
                       A.GETORGANCODE,
                       A.USEORGANCODE,
                       A.USEDATE,
                       A.USEUNITCODE,
                       A.USERCODE,
                       A.CURRENCYCODE,
                       A.AMOUNT,
                       A.NAME,
                       A.NOTES,
                       A.STATUSCODE,
                       A.IFPAGEONHOLE,
                       A.OPCODE,
                       A.OPUNITCODE,
                       A.OPDATE,
                       A.LOCKTIME,
                       A.GETAGENTCODE,
                       /*                       (SELECT D.AGENTNAME
                        FROM SYN_MM_AGENTCODE_TC D
                       WHERE D.AGENTCODE = A.GETAGENTCODE) AS GETAGENTNAME*/
                       D.AGENTNAME AS GETAGENTNAME,
                       A.USEAGENTCODE,
                       A.OUTSTATUS,
                       CASE A.BILLCODE
                         WHEN 'B2010005' THEN
                          A.FACTBILLCODE
                         ELSE
                          ''
                       END FACTBILLCODE,
                       A.SALES,
                       A.FROMDATE,
                       A.TODATE,
                       /*                       (SELECT BILLNAME
                        FROM BD_BILLCODE
                       WHERE BILLCODE = A.BILLCODE) BILLNAME,*/
                       B.BILLNAME,
                       /*                       (SELECT HANDLERNAME
                        FROM BD_HANDLER
                       WHERE HANDLERCODE = A.USERCODE) USERNAME,*/
                       C.HANDLERNAME USERNAME,
                       /*                       (SELECT HANDLERNAME
                        FROM BD_HANDLER
                       WHERE HANDLERCODE = A.GETCODE) GETERNAME,*/
                       E.HANDLERNAME GETERNAME,
                       F.no3         ONLINEINVOICENO,
                       F.no4         ONLINEINVOICECODE
                  FROM B_BILL A
                  LEFT JOIN SYN_MM_AGENTCODE_TC D
                    ON D.AGENTCODE = A.GETAGENTCODE
                  LEFT JOIN BD_BILLCODE B
                    ON B.BILLCODE = A.BILLCODE
                  LEFT JOIN BD_HANDLER C
                    ON C.HANDLERCODE = A.USERCODE
                  LEFT JOIN BD_HANDLER E
                    ON E.HANDLERCODE = A.GETCODE
                  LEFT JOIN B_BILLDETAIL F
                    ON F.BILLNO = A.BILLNO
                   AND F.BILLCODE = A.BILLCODE
                   AND F.FACTBILLCODE = A.FACTBILLCODE
                 WHERE 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND trim(A.BILLNO) >= '601710100010'
                   AND trim(A.BILLNO) <= '601710100010'
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                   AND 3 > 2
                 ORDER BY A.BILLNO)G
         where rownum <= 1)
 where num > 0;

Plan hash value: 1528527901

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |  4632 |    21   (0)| 00:00:01 |
|*  1 |  VIEW                               |                      |     1 |  4632 |    21   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                     |                      |       |       |            |          |
|   3 |    VIEW                             |                      |     2 |  9238 |    21   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER              |                      |     2 |   832 |    21   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER             |                      |     2 |   770 |    19   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER            |                      |     2 |   718 |    17   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER           |                      |     2 |   614 |    12   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER          |                      |     2 |   562 |    10   (0)| 00:00:01 |
|   9 |          TABLE ACCESS BY INDEX ROWID| B_BILL               | 17395 |  3822K|     9   (0)| 00:00:01 |
|* 10 |           INDEX FULL SCAN           | PK_B_BILL_01         |     2 |       |     8   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| CHAGENTBASE          |     1 |    56 |     1   (0)| 00:00:01 |
|* 12 |           INDEX RANGE SCAN          | IDX_CHAGENTBASE_TEST |     1 |       |     0   (0)| 00:00:01 |
|  13 |         TABLE ACCESS BY INDEX ROWID | T_EMPLOYEE_VIEW      |     1 |    26 |     2   (0)| 00:00:01 |
|* 14 |          INDEX RANGE SCAN           | IND_T_EMPLOYEE_VIEW  |     1 |       |     1   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID  | B_BILLDETAIL         |     1 |    52 |     3   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN            | PK_B_BILLDETAIL_02   |     1 |       |     2   (0)| 00:00:01 |
|  17 |       TABLE ACCESS BY INDEX ROWID   | T_EMPLOYEE_VIEW      |     1 |    26 |     2   (0)| 00:00:01 |
|* 18 |        INDEX RANGE SCAN             | IND_T_EMPLOYEE_VIEW  |     1 |       |     1   (0)| 00:00:01 |
|  19 |      TABLE ACCESS BY INDEX ROWID    | BD_BILLCODE          |     1 |    31 |     1   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN             | PK_BD_BILLCODE       |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   1 - filter("NUM">0)
   2 - filter(ROWNUM<=1)
  10 - filter(TRIM("A"."BILLNO")>='601710100010' AND TRIM("A"."BILLNO")<='601710100010')
  12 - access("D"."AGENTCODE"(+)="A"."GETAGENTCODE")
  14 - access("A"."CODE"(+)="A"."GETCODE")
  16 - access("F"."BILLNO"(+)="A"."BILLNO" AND "F"."BILLCODE"(+)="A"."BILLCODE" AND 
              "F"."FACTBILLCODE"(+)="A"."FACTBILLCODE")
  18 - access("A"."CODE"(+)="A"."USERCODE")
  20 - access("B"."BILLCODE"(+)="A"."BILLCODE")

3) 把Trim去掉

Execution Plan
----------------------------------------------------------
Plan hash value: 1229065410

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |  4632 |    13   (0)| 00:00:01 |
|*  1 |  VIEW                               |                      |     1 |  4632 |    13   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                     |                      |       |       |            |          |
|   3 |    VIEW                             |                      |     1 |  4619 |    13   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER              |                      |     1 |   416 |    13   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER             |                      |     1 |   390 |    11   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER            |                      |     1 |   364 |     9   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER           |                      |     1 |   308 |     8   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER          |                      |     1 |   277 |     7   (0)| 00:00:01 |
|   9 |          TABLE ACCESS BY INDEX ROWID| B_BILL               |     1 |   225 |     4   (0)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN          | PK_B_BILL            |     1 |       |     3   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| B_BILLDETAIL         |     1 |    52 |     3   (0)| 00:00:01 |
|* 12 |           INDEX RANGE SCAN          | PK_B_BILLDETAIL_02   |     1 |       |     2   (0)| 00:00:01 |
|  13 |         TABLE ACCESS BY INDEX ROWID | BD_BILLCODE          |     1 |    31 |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN          | PK_BD_BILLCODE       |     1 |       |     0   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID  | CHAGENTBASE          |     1 |    56 |     1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN            | IDX_CHAGENTBASE_TEST |     1 |       |     0   (0)| 00:00:01 |
|  17 |       TABLE ACCESS BY INDEX ROWID   | T_EMPLOYEE_VIEW      |     1 |    26 |     2   (0)| 00:00:01 |
|* 18 |        INDEX RANGE SCAN             | IND_T_EMPLOYEE_VIEW  |     1 |       |     1   (0)| 00:00:01 |
|  19 |      TABLE ACCESS BY INDEX ROWID    | T_EMPLOYEE_VIEW      |     1 |    26 |     2   (0)| 00:00:01 |
|* 20 |       INDEX RANGE SCAN              | IND_T_EMPLOYEE_VIEW  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   1 - filter("NUM">0)
   2 - filter(ROWNUM<=1)
  10 - access("A"."BILLNO"='601710100010')
  12 - access("F"."BILLNO"(+)='601710100010' AND "F"."BILLCODE"(+)="A"."BILLCODE" AND
              "F"."FACTBILLCODE"(+)="A"."FACTBILLCODE")
       filter("F"."BILLNO"(+)="A"."BILLNO")
  14 - access("B"."BILLCODE"(+)="A"."BILLCODE")
  16 - access("D"."AGENTCODE"(+)="A"."GETAGENTCODE")
  18 - access("A"."CODE"(+)="A"."GETCODE")
  20 - access("A"."CODE"(+)="A"."USERCODE")


Statistics
----------------------------------------------------------
        621  recursive calls
          0  db block gets
        229  consistent gets
         17  physical reads
          0  redo size
       2937  bytes sent via SQL*Net to client
       2086  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

优化效果

可以看出消耗的 buffer cache 从之前的882,856,212.00 降到了229,效率提升了N倍.

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_02', 'SYS'); END;

问题背景: 客户反馈系统突然很慢,查询awr报告 1 658whw2n7xkd2 BEGIN SYS.KUPW$WORKER.MAIN('SYSEXPORTSCHEMA_02', 'SYS'); END; 数据库在取数据块时为了保护内存的数据结构而加了latch(一......

osc_05yddh2u
2019/12/05
1
0
Oracle热快-从认识到定位

必知: Latch集中于Buffer Cache的竞争和Shared Pool的竞争。和Buffer Cache相关的主要Latch竞争有cache buffers chains和cache buffers lru chain,和Shared Pool相关的主要Latch竞争有sha...

Vimeo
2016/02/07
245
0
oracle 体系结构及内存管理 08_Buffer cache

1、buffer cache相关概念 segment:一个表就是一个段; extend:物理上连续的多个块,给段分配空间的单位; block:IO的最小单位,数据文件中的单位,oracle实例创建时指定标准块大小dbblock...

PeakFang-BOK
2018/10/13
57
0
ORACLE 常见等待事件

一. 等待事件的相关知识 1.1 等待事件主要可以分为两类,即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。 1). 空闲等待事件指ORACLE正等待某种工作,在诊断和优化数据库的时候,不用过多...

MR_White
2014/09/02
155
0
[Oracle]Oracle数据库CPU利用率很高解决方案

Oracle数据库经常会遇到CPU利用率很高的情况,这种时候大都是数据库中存在着严重性能低下的SQL语句,这种SQL语句大大的消耗了CPU资源,导致整个系统性能低下。当然,引起严重性能低下的SQL语...

osc_b0nm4mbd
2018/06/29
4
0

没有更多内容

加载失败,请刷新页面

加载更多

平时使用的Lszrz到底是什么协议?说说Xmodem/Ymodem/Zmodem

XMODEM, YMODEM, and ZMODEM 由于平时使用rz/sz较多,r/s好理解,一个send一个receive。但是由不太清楚z是什么意思,故有此文。 sx/rx, sb/rb (b=batch)和sz/rz分别实现了xmodem,ymodem和z...

独钓渔
53分钟前
17
0
真正的强智能时代已经到来。道翰天琼认知智能机器人平台API大脑。

最近,我常说人工智能的寒冬快要来了,提醒业界要做好思想准备,但同时我也说:冬天来了,春天就不会远了…… 2019年6月我写了篇文章《深度学习的问题究竟在哪?》,说到深度学习的一个主要问...

jackli2020
今天
24
0
什么是控制型人格,控制型人格的筛查测试

一、 什么是控制性人格 拥有控制型人格的人,他们会尽力的隐藏自己的意图,但是又会使用很微妙的方式来利用周围人的弱点,进而占取便宜时,使他们能够得到自己想要的东西。这类人的控制欲非常...

蛤蟆丸子
今天
14
0
【Spring】Spring AOP 代理对象生成逻辑源码分析

1. spring aop案例(POJO注入) 1.0 被代理接口 TargetInterface /** * 被代理的接口 * @author Yang ZhiWei */public interface TargetInterface { void show(); String show......

ZeroneLove
今天
36
0
聊聊dubbo-go的gracefulShutdownFilter

序 本文主要研究一下dubbo-go的gracefulShutdownFilter gracefulShutdownFilter dubbo-go-v1.4.2/filter/filter_impl/graceful_shutdown_filter.go type gracefulShutdownFilter struct {......

go4it
今天
30
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部