又是latch: cache buffers chains惹得祸

2018/04/20 16:03
阅读数 75

前言

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

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倍.

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部