关于latch: cache buffers chains的sql优化

最新推荐文章于 2021-03-25 09:01:50 发布
最新推荐文章于 2021-03-25 09:01:50 发布
阅读量315 收藏
点赞数 1
分类专栏： Oracle sql优化 Oracle SQL 优化 文章标签： latch sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/78890781
版权

前段时间，优化了一些耗buffer比较多的sql，但是CPU使用率还是没下来 。

查看操作系统CPU使用率
topas查看CPU使用率

查看awr，发现又有一条超级耗性能的sql冒出来了。
这里写图片描述
这里写图片描述

该SQL每次执行耗费3e多个buffer，结果就是导致内存消耗高，cpu消耗也高。。。

利用工具PLSQL Developer，查询执行该SQL的session
这里写图片描述

数据库的等待事件为latch: cache buffers chains

SQL代码：

select distinct to_char(t5.summaryno) settlementNo,
                to_char(t5.batchtype) settlementType,
                to_char(s.writeouttype) certitype,
                to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
                to_char(t5.commissionpayno) CommissionPayNo,
                to_char(t2.amount) payAmount,
                to_char(t2.currencycode) payCurrency,
                (case
                  when t5.unitcode = '012100' then
                   (select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
                      from mm_dailyreport_detail_td d
                     where d.seqreportno = t1.seqreportno)
                  else
                   to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
                end) payDate,
                to_char(s.batch_id) batchid,
                t2.coreason payErrorMessage,
                '' payedExchangedRate,
                (select distinct o.voucherno
                   from mm_sap_voucher_detail_to o
                  where o.businessno = t2.inpaymentid
                    and o.dailyauditno = t3.dailyauditno) accountingDocuments,
                (case
                  when t2.amount > 0 then
                   '02'
                  when t2.amount < 0 then
                   '03'
                  when t2.amount = 0 then
                   '01'
                end) payStatus,
                to_char(t2.inpaymentid) ebankSequenceNo
  from mm_writeout_to         t1,
       mm_inpayment_td        t2,
       mm_paymentin_events_td t3,
       mm_payablemoney_td     t4,
       mm_batchinfo_td        t5,
       mm_batchinfo_ti        t6,
       mm_writeoutstatus_to   s
 where t2.inpaymentid = t1.businessno
   and t1.id = s.id
   and t3.newno = t2.inpaymentid
   and ((t3.oldno = t4.payableno) or exists
        (select 1
           from mm_paymentin_events_td p
          where p.listno = t3.fatherno
            and t4.payableno = p.oldno))
   and t6.id = t5.seqbatch
   and t6.status = '2'
   and t5.opstatus = '0'
   and t5.serialno = t4.custseq
   and s.batch_id = :1
   and exists (select 1
          from mm_sap_voucher_detail_to o
         where o.businessno = t2.inpaymentid              
           and o.dailyauditno = t3.dailyauditno)
   and s.status in ('00', 'XX')

该sql中有个变量值 s.batch_id = :1，利用系统视图dba_hist_sqlbind找出绑定变量值，在测试环境测试，发现每次执行都只要334个buffer
这里写图片描述
这里写图片描述
执行计划没错？只消耗334个buffer，而且1s内出结果，完全不像awr中记录的。

dba_hist_sqlbind：查询历史绑定变量信息, dba_hist_sqlbind的信息是从v$sql_bind_capture里面采集的。

v$sql_bind_capture view：只保存最后一次捕获SQL的变量信息，两次捕获之间的间隔为900s，受隐藏参数控制

再次利用视图v$sql_bind_capture抓取最新的值，代入sql中执行，发现sql卡住了。。

查看sql特殊执行计划

Plan hash value: 534203852

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID           | MM_DAILYREPORT_DETAIL_TD      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                    | PK_MM_DAILYREPORT_DETAIL_TD   |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |  HASH UNIQUE                           |                               |      1 |      1 |      1 |00:00:00.01 |       8 |  1036K|  1036K|  318K (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID          | MM_SAP_VOUCHER_DETAIL_TO      |      1 |      1 |      1 |00:00:00.01 |       8 |       |       |          |
|*  5 |    INDEX RANGE SCAN                    | IDX_MM_SAP_VOUCHER_DETAIL_TO1 |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   6 |  HASH UNIQUE                           |                               |      1 |      1 |      1 |00:10:57.87 |     335M|   751K|   751K|  359K (0)|
|*  7 |   FILTER                               |                               |      1 |        |    134 |00:27:07.96 |     335M|       |       |          |
|   8 |    TABLE ACCESS BY INDEX ROWID         | MM_PAYABLEMONEY_TD            |      1 |      1 |     21M|00:26:00.03 |     335M|       |       |          |
|   9 |     NESTED LOOPS                       |                               |      1 |      1 |     86M|00:23:12.83 |     322M|       |       |          |
|  10 |      NESTED LOOPS                      |                               |      1 |      1 |     64M|00:13:59.99 |     193M|       |       |          |
|  11 |       MERGE JOIN CARTESIAN             |                               |      1 |      1 |     64M|00:01:05.38 |   22421 |       |       |          |
|  12 |        NESTED LOOPS SEMI               |                               |      1 |      1 |    134 |00:00:00.01 |      72 |       |       |          |
|  13 |         NESTED LOOPS                   |                               |      1 |      1 |    134 |00:00:00.01 |      65 |       |       |          |
|  14 |          NESTED LOOPS                  |                               |      1 |      1 |      1 |00:00:00.01 |      14 |       |       |          |
|  15 |           NESTED LOOPS                 |                               |      1 |      1 |      1 |00:00:00.01 |      11 |       |       |          |
|  16 |            INLIST ITERATOR             |                               |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|  17 |             TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO          |      2 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|* 18 |              INDEX RANGE SCAN          | IDX_WRITEOUTSTATUS_TEST       |      2 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|  19 |            TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|* 20 |             INDEX UNIQUE SCAN          | PK_MM_WRITEOUT_TO             |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|  21 |           TABLE ACCESS BY INDEX ROWID  | MM_INPAYMENT_TD               |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 22 |            INDEX UNIQUE SCAN           | PK_MM_INPAYMENT_TD            |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  23 |          TABLE ACCESS BY INDEX ROWID   | MM_PAYMENTIN_EVENTS_TD        |      1 |      2 |    134 |00:00:00.01 |      51 |       |       |          |
|* 24 |           INDEX RANGE SCAN             | IDX_PAYMENTINE_07             |      1 |      2 |    134 |00:00:00.01 |       4 |       |       |          |
|* 25 |         INDEX RANGE SCAN               | IDX_MM_SAP_VOUCHER_DETAIL_TO1 |      1 |   4538 |      1 |00:00:00.01 |       7 |       |       |          |
|  26 |        BUFFER SORT                     |                               |    134 |    472K|     64M|00:00:00.83 |   22349 |    56M|  2617K|   49M (0)|
|* 27 |         TABLE ACCESS FULL              | MM_BATCHINFO_TD               |      1 |    472K|    481K|00:00:00.01 |   22349 |       |       |          |
|* 28 |       TABLE ACCESS BY INDEX ROWID      | MM_BATCHINFO_TI               |     64M|      1 |     64M|00:11:32.52 |     193M|       |       |          |
|* 29 |        INDEX UNIQUE SCAN               | PK_BATCHINFO_TI               |     64M|      1 |     64M|00:06:50.50 |     129M|       |       |          |
|* 30 |      INDEX RANGE SCAN                  | IDX_PAYABLEMONEY_09           |     64M|      1 |     21M|00:08:45.52 |     129M|       |       |          |
|* 31 |    INDEX RANGE SCAN                    | IDX_PAYMENTINE_TEST           |     21M|      1 |      0 |00:00:22.85 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("D"."SEQREPORTNO"=TO_NUMBER(:B1))
   5 - access("O"."DAILYAUDITNO"=:B1)
       filter(TO_NUMBER("O"."BUSINESSNO")=:B1)
   7 - filter(("T3"."OLDNO"="T4"."PAYABLENO" OR  IS NOT NULL))
  18 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219626143')
  20 - access("T1"."ID"="S"."ID")
  22 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
  24 - access("T3"."NEWNO"="T2"."INPAYMENTID")
  25 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
       filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
  27 - filter("T5"."OPSTATUS"='0')
  28 - filter("T6"."STATUS"='2')
  29 - access("T6"."ID"="T5"."SEQBATCH")
  30 - access("T5"."SERIALNO"="T4"."CUSTSEQ")
  31 - access("P"."LISTNO"=:B1 AND "P"."OLDNO"=:B2)


67 rows selected.

id=11处 ，a-rows并不是0条，而是有64000000条记录。他的父级 的连接方式是 NEST LOOP，

也就是说被驱动表MM_BATCHINFO_TI表要被扫描64000000次。。。

定位到问题点，现在就开始优化吧。

尝试优化1：

利用hint（/+ OPT_PARAM(‘_optimizer_mjc_enabled’,’false’) /）禁用笛卡尔积
这里写图片描述

不见效果，此优化方法失败。

尝试优化2：

利用hint 走hash的连接方式
这里写图片描述

不见效果，此优化方法也失败。

尝试优化3：

从sql代码中看出，该SQLwhere条件又or子查询，尝试利用union改写or

select distinct to_char(t5.summaryno) settlementNo,
                to_char(t5.batchtype) settlementType,
                to_char(s.writeouttype) certitype,
                to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
                to_char(t5.commissionpayno) CommissionPayNo,
                to_char(t2.amount) payAmount,
                to_char(t2.currencycode) payCurrency,
                (case
                  when t5.unitcode = '012100' then
                   (select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
                      from mm_dailyreport_detail_td d
                     where d.seqreportno = t1.seqreportno)
                  else
                   to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
                end) payDate,
                to_char(s.batch_id) batchid,
                t2.coreason payErrorMessage,
                '' payedExchangedRate,
                (select distinct o.voucherno
                   from mm_sap_voucher_detail_to o
                  where o.businessno = t2.inpaymentid
                    and o.dailyauditno = t3.dailyauditno) accountingDocuments,
                (case
                  when t2.amount > 0 then
                   '02'
                  when t2.amount < 0 then
                   '03'
                  when t2.amount = 0 then
                   '01'
                end) payStatus,
                to_char(t2.inpaymentid) ebankSequenceNo
  from mm_writeout_to         t1,
       mm_inpayment_td        t2,
       mm_paymentin_events_td t3,
       mm_payablemoney_td     t4,
       mm_batchinfo_td        t5,
       mm_batchinfo_ti        t6,
       mm_writeoutstatus_to   s
 where t2.inpaymentid = t1.businessno
   and t1.id = s.id
   and t3.newno = t2.inpaymentid
   and t3.oldno = t4.payableno
   and t6.id = t5.seqbatch
   and t6.status = '2'
   and t5.opstatus = '0'
   and t5.serialno = t4.custseq
   and s.batch_id = '1219639828'
   and exists (select 1
          from mm_sap_voucher_detail_to o
         where o.businessno = t2.inpaymentid              
           and o.dailyauditno = t3.dailyauditno)
   and s.status in ('00', 'XX')
union
select distinct to_char(t5.summaryno) settlementNo,
                to_char(t5.batchtype) settlementType,
                to_char(s.writeouttype) certitype,
                to_char(t5.payconfirmsequenceno) payConfirmSequenceNo,
                to_char(t5.commissionpayno) CommissionPayNo,
                to_char(t2.amount) payAmount,
                to_char(t2.currencycode) payCurrency,
                (case
                  when t5.unitcode = '012100' then
                   (select to_char(d.timestamp, 'YYYY-MM-DD HH24:mi:ss')
                      from mm_dailyreport_detail_td d
                     where d.seqreportno = t1.seqreportno)
                  else
                   to_char(t2.opdate, 'YYYY-MM-DD HH24:mi:ss')
                end) payDate,
                to_char(s.batch_id) batchid,
                t2.coreason payErrorMessage,
                '' payedExchangedRate,
                (select distinct o.voucherno
                   from mm_sap_voucher_detail_to o
                  where o.businessno = t2.inpaymentid
                    and o.dailyauditno = t3.dailyauditno) accountingDocuments,
                (case
                  when t2.amount > 0 then
                   '02'
                  when t2.amount < 0 then
                   '03'
                  when t2.amount = 0 then
                   '01'
                end) payStatus,
                to_char(t2.inpaymentid) ebankSequenceNo
  from mm_writeout_to         t1,
       mm_inpayment_td        t2,
       mm_paymentin_events_td t3,
       mm_payablemoney_td     t4,
       mm_batchinfo_td        t5,
       mm_batchinfo_ti        t6,
       mm_writeoutstatus_to   s
 where t2.inpaymentid = t1.businessno
   and t1.id = s.id
   and t3.newno = t2.inpaymentid
   and exists
        (select 1
           from mm_paymentin_events_td p
          where p.listno = t3.fatherno
            and t4.payableno = p.oldno)
   and t6.id = t5.seqbatch
   and t6.status = '2'
   and t5.opstatus = '0'
   and t5.serialno = t4.custseq
   and s.batch_id = '1219639828'
   and exists (select 1
          from mm_sap_voucher_detail_to o
         where o.businessno = t2.inpaymentid              
           and o.dailyauditno = t3.dailyauditno)
   and s.status in ('00', 'XX')

--执行计划：
Plan hash value: 1779884842

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT UNIQUE                           |                               |      1 |      2 |      1 |00:00:00.01 |    1212 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                            |                               |      1 |        |    134 |00:00:00.01 |    1212 |       |       |          |
|   3 |    NESTED LOOPS SEMI                   |                               |      1 |      1 |    134 |00:00:00.01 |    1145 |       |       |          |
|   4 |     NESTED LOOPS                       |                               |      1 |      1 |    134 |00:00:00.01 |    1138 |       |       |          |
|   5 |      NESTED LOOPS                      |                               |      1 |      1 |    134 |00:00:00.01 |     867 |       |       |          |
|   6 |       NESTED LOOPS                     |                               |      1 |      1 |    134 |00:00:00.01 |     463 |       |       |          |
|   7 |        NESTED LOOPS                    |                               |      1 |      1 |    134 |00:00:00.01 |      59 |       |       |          |
|   8 |         NESTED LOOPS                   |                               |      1 |      1 |      1 |00:00:00.01 |      14 |       |       |          |
|   9 |          NESTED LOOPS                  |                               |      1 |      1 |      1 |00:00:00.01 |      11 |       |       |          |
|  10 |           INLIST ITERATOR              |                               |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|  11 |            TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO          |      2 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|* 12 |             INDEX RANGE SCAN           | IDX_WRITEOUTSTATUS_TEST       |      2 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|  13 |           TABLE ACCESS BY INDEX ROWID  | MM_WRITEOUT_TO                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|* 14 |            INDEX UNIQUE SCAN           | PK_MM_WRITEOUT_TO             |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|  15 |          TABLE ACCESS BY INDEX ROWID   | MM_INPAYMENT_TD               |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 16 |           INDEX UNIQUE SCAN            | PK_MM_INPAYMENT_TD            |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  17 |         TABLE ACCESS BY INDEX ROWID    | MM_PAYMENTIN_EVENTS_TD        |      1 |     13 |    134 |00:00:00.01 |      45 |       |       |          |
|* 18 |          INDEX RANGE SCAN              | IDX_PAYMENTINE_08             |      1 |     13 |    134 |00:00:00.01 |       4 |       |       |          |
|  19 |        TABLE ACCESS BY INDEX ROWID     | MM_PAYABLEMONEY_TD            |    134 |      1 |    134 |00:00:00.01 |     404 |       |       |          |
|* 20 |         INDEX UNIQUE SCAN              | PK_MM_PAYABLEMONEY_TD         |    134 |      1 |    134 |00:00:00.01 |     270 |       |       |          |
|  21 |       TABLE ACCESS BY INDEX ROWID      | MM_BATCHINFO_TD               |    134 |      1 |    134 |00:00:00.01 |     404 |       |       |          |
|* 22 |        INDEX RANGE SCAN                | IDX_BATCH_TD_SERIALNO         |    134 |      1 |    134 |00:00:00.01 |     270 |       |       |          |
|* 23 |      INDEX RANGE SCAN                  | IDX_BATCHINFO_TI_01           |    134 |      1 |    134 |00:00:00.01 |     271 |       |       |          |
|* 24 |     INDEX RANGE SCAN                   | IDX_MM_SAP_VOUCHER_DETAIL_TO1 |      1 |   2732 |      1 |00:00:00.01 |       7 |       |       |          |
|  25 |    NESTED LOOPS SEMI                   |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  26 |     NESTED LOOPS                       |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  27 |      NESTED LOOPS                      |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  28 |       NESTED LOOPS                     |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  29 |        NESTED LOOPS                    |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  30 |         NESTED LOOPS                   |                               |      1 |      1 |      0 |00:00:00.01 |      59 |       |       |          |
|  31 |          NESTED LOOPS                  |                               |      1 |      1 |      1 |00:00:00.01 |      14 |       |       |          |
|  32 |           NESTED LOOPS                 |                               |      1 |      1 |      1 |00:00:00.01 |      11 |       |       |          |
|  33 |            INLIST ITERATOR             |                               |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|  34 |             TABLE ACCESS BY INDEX ROWID| MM_WRITEOUTSTATUS_TO          |      2 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|* 35 |              INDEX RANGE SCAN          | IDX_WRITEOUTSTATUS_TEST       |      2 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|  36 |            TABLE ACCESS BY INDEX ROWID | MM_WRITEOUT_TO                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|* 37 |             INDEX UNIQUE SCAN          | PK_MM_WRITEOUT_TO             |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|  38 |           TABLE ACCESS BY INDEX ROWID  | MM_INPAYMENT_TD               |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 39 |            INDEX UNIQUE SCAN           | PK_MM_INPAYMENT_TD            |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 40 |          TABLE ACCESS BY INDEX ROWID   | MM_PAYMENTIN_EVENTS_TD        |      1 |      2 |      0 |00:00:00.01 |      45 |       |       |          |
|* 41 |           INDEX RANGE SCAN             | IDX_PAYMENTINE_08             |      1 |     13 |    134 |00:00:00.01 |       4 |       |       |          |
|  42 |         SORT UNIQUE                    |                               |      0 |      1 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|* 43 |          INDEX RANGE SCAN              | IDX_PAYMENTINE_TEST           |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  44 |        TABLE ACCESS BY INDEX ROWID     | MM_PAYABLEMONEY_TD            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 45 |         INDEX UNIQUE SCAN              | PK_MM_PAYABLEMONEY_TD         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  46 |       TABLE ACCESS BY INDEX ROWID      | MM_BATCHINFO_TD               |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 47 |        INDEX RANGE SCAN                | IDX_BATCH_TD_SERIALNO         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 48 |      INDEX RANGE SCAN                  | IDX_BATCHINFO_TI_01           |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 49 |     INDEX RANGE SCAN                   | IDX_MM_SAP_VOUCHER_DETAIL_TO1 |      0 |   2963 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  12 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
  14 - access("T1"."ID"="S"."ID")
  16 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
  18 - access("T3"."NEWNO"="T2"."INPAYMENTID")
  20 - access("T3"."OLDNO"="T4"."PAYABLENO")
  22 - access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
  23 - access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
  24 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
       filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))
  35 - access((("S"."STATUS"='00' OR "S"."STATUS"='XX')) AND "S"."BATCH_ID"='1219639828')
  37 - access("T1"."ID"="S"."ID")
  39 - access("T2"."INPAYMENTID"=TO_NUMBER("T1"."BUSINESSNO"))
  40 - filter("T3"."FATHERNO" IS NOT NULL)
  41 - access("T3"."NEWNO"="T2"."INPAYMENTID")
  43 - access("P"."LISTNO"="T3"."FATHERNO")
  45 - access("T4"."PAYABLENO"="P"."OLDNO")
  47 - access("T5"."OPSTATUS"='0' AND "T5"."SERIALNO"="T4"."CUSTSEQ")
  48 - access("T6"."STATUS"='2' AND "T6"."ID"="T5"."SEQBATCH")
  49 - access("O"."DAILYAUDITNO"="T3"."DAILYAUDITNO")
       filter("T2"."INPAYMENTID"=TO_NUMBER("O"."BUSINESSNO"))

代入最新值，秒出结果。

后经开发检验，该sql大大改善了系统性能，cpu利用率也随之下降。

DB-Engineer
关注
专栏目录
低效的SQL引发的cache buffers chains latch
zhaoyangjian724的专栏
06-16 622
1.低效的SQL 低效的SQL语句时发生cache buffers chains 锁存器争用的最重要原因。多个进程同时扫描大范围的索引或表时,可能广泛 地发生cache buffers chains latch争用。 低效的SQL语句时怎样引起cache buffers chain latch争用的,我们可以测试进一步进行了解。测试方案如下: 1) 创建cbc_test(id,nam
buffer pool latch和cache buffers chains latch
zhaoyangjian724的专栏
11-14 505
buffer pool latch 是用来保护buffer pool的 每个buffer pool有一个buffer pool latch保护 SQL> select addr,LATCH#,LEVEL#,CHILD#,NAME from v$latch_children where name='buffer pool'; ADDR LATCH# LEVEL# CHILD# NA
buffer cache实验5-latch:cache buffers chain
还不算晕的专栏
02-26 1911
1.CBC latch产生的原理： 一次逻辑读时CBC latch锁及Buffer pin锁的获取和释放过程如下： 1.加Latch X 2.进入hash chain，在相应的BH上加Buffer pin S (0-->1) 3.释放Latch X 4.进行逻辑读--也就是通过BH中的buffer adderss找到数据块在内存中真实位置 ---假如读了1MS 5.加Latch X 6.释放Buffer pin S (1-->0) 0:没锁 1:共享锁 2:独占锁 7.释放Latch X
mysql latch和缓存关系_latch:cachebufferschains等待事件导致的latch争用的原理原因与...
weixin_35761094的博客
01-20 165
latch:cache buffers chains 原理 当一个数据块读入到sga中时，该块的块头(buffer header)会放置在一个hash bucket的链表(hash chain)中。该内存结构由一系列cache buffers chains子latch保护(又名hash latch或者cbc latch)。对Buffer cache中的块，要selelatch:cache buff...
模拟cache buffers chains latch实验
XmallWood的专栏
05-08 821
1。模拟思路：     原理上来说，buffer cache中block的header是被放置到hash chains上，而hash chains又是放在hash bucket中，多个hash bucket被一个cache buffers chains latch保护。当多个session并发访问同一个数据块上的数据，每个session都要首先获得cache buffers chains lat
oracle中的latch: cache buffers chains 与热块
技术螃蟹的博客
01-31 1078
oracle中的latch: cache buffers chains 与热块oracle中的latch: cache buffers chains 与热块ORACLE中的buffer cachelatch: cache buffers chains产生的原因优化措施查找热块 oracle中的latch: cache buffers chains 与热块 ORACLE中的buffer cache oracle中有各种buffer cache事件，buffer cache简单的说就是缓冲区，主要用于缓冲数据块
latch: cache buffers chains-热块的简单模拟实验
KIN ORACLE
12-14 1115
1.创建测试数据. create table t1 as select rownum id from dba_objects; CREATE INDEX T1_IDX ON T1(ID) ; 2.创建两个存储过程. create or replace PROCEDURE pselect2 AS l_num number; BEGIN FOR i IN 1..1000000 LOOP
热点块引发的cache buffers cahins latch
zhaoyangjian724的专栏
06-17 531
热点块引发的Cache buffer Chains latch: SQL语句即便适当进行了调优,有时也无法解决cache buffers cahins latch，若在编写SQL语句时的SQL工作方式, 只是持续扫描少数特定块,则在多个会话同时执行此SQL语句时,就会发生Hot Block引起的cache buffers chains latch争用。 测试方案如下: 1) 创建
latch: cache buffers chains故障处理总结
m0_38048955的博客
03-25 705
客户说数据库的CPU使用率为100%，应用相应迟缓。 发现是latch: cache buffers chains 作祟 故障分析思路 查看等待事件，判断故障起因 1 SQL>select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6 2 order by wait_time desc) where rownum
latch free、buffer latch！
事后诸葛亮的程序人生（微信：zq9017197）
11-03 1863
latch free SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'latch free'; EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 ---
一个笛卡尔积的优化
Angelina7的博客
05-09 4504
原SQL：业务需求需要开发一张表，但是发现执行很慢，我就看了执行计划， MERGE JOIN CARTESIAN发生笛卡尔积，4dp这张表和6cp这张表发生笛卡尔积，生成的结果集3和8mp又发生一次结果集，也就是说 本身没有关系的表发生了笛卡尔积。这里需要声明几点，cp这张表数据将近50万，mp有将近80万的数据,dp也就不到10条数据 mt表有150万数据量 select
深入理解latch: cache buffers chains
cltt980248285的博客
08-03 546
事件背景： 一个客户的数据库发生了宕机事件，查看了数据库的awr报告，原来是由于出现大量的latch: cache buffers chains等待事件导致系统消耗量大量的CPU，最终导致系统hang住； 说...
latch: cache buffers chains---AWR实战分析
mengxiangfeiyang的专栏
05-20 772
latch: cache buffers chains---AWR实战分析                    ---------转载 一：等待事件原理     当一个数据块读入SGA区，相应的buffer header会被放置到hash列表上，我们称其是hash chains,chain在中文的意思是链表，表达的就是关连性，如果一个进程想要访问中修改hash cha
sql调优之执行计划之笛卡尔连接————MERGE JOIN CARTESIAN
热门推荐
yuzhenhuan01的专栏
08-29 1万+
生产环境中遭遇temp空间不足的报错 定位产生异常的语句如下：select * from (select t.*, rownum rn from (select p.video_id, c.path, c.hosts, t.sha1 from converttype c left join tasks...
latch: cache buffers chains导致CPU冲高的问题
wish503的博客
09-27 3740
今天同事分享了一个特别好的案例，所以记录到我的博客 现象： CPU冲高，大量latch: cache buffers chains等待事件. 分析过程： 1.根据等待事件查询出SQL信息。 select t.BLOCKING_SESSION,t.BLOCKING_SESSION_STATUS,t.sql_id,t.SQL_HASH_VALUE,t.* from v$session t w
Oracle HINT
IT古董
08-22 677
转自：http://buralin.iteye.com/blog/1264875 手工指定SQL语句的执行计划     hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：     1) 使用的优化器的类型     2) 基于代价的优化器的优化目标，是all_rows还是first_rows。     3) 表的访
latch: cache buffers chains
最新发布
03-16
latch是Oracle数据库中的一种锁机制，用于保护共享内存结构的一致性。而cache buffers chains是Oracle数据库中的一种缓存机制，用于缓存数据块。它们之间的关系是，当一个进程需要访问一个缓存中的数据块时，它需要获取该数据块所在的cache buffers chains上的latch，以保证数据块的一致性。因此，latch和cache buffers chains是密切相关的。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值