AI 助理
备案控制台
开发者社区 数据库 文章 正文

latch free:cache buffer handles造成的SQL性能问题

2017-11-12 980
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
视频点播 VOD，流量+存储+转码
简介：
数月之前，一位新疆的同事反映客户的某套单节点数据库存在性能问题，某个SQL长时间运行没有结束，在该SQL运行期间大量回话处于latch free等待中。因为相隔遥遥千里，同事之间是通过Email交流的；他首先给我发来了该问题SQL的相关 explain_log 。其中该语句的执行计划如下:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-----------------------------------------------------------------------------------------------
| Id  | Operation                               |   Name                |  Rows   | Bytes | Cost  |
-----------------------------------------------------------------------------------------------
|   0 |  SELECT  STATEMENT                        |                     |     1 |  1956 |    13 |
|*  1 |  FILTER                                 |                     |       |       |       |
|   2 |   NESTED LOOPS  OUTER                     |                     |     1 |  1956 |    13 |
|   3 |    NESTED LOOPS  OUTER                    |                     |     1 |  1942 |    12 |
|   4 |     NESTED LOOPS  OUTER                   |                     |     1 |  1924 |    11 |
|   5 |      NESTED LOOPS                       |                     |     1 |  1906 |    10 |
|   6 |       NESTED LOOPS                      |                     |     1 |  1856 |     9 |
|   7 |        NESTED LOOPS                     |                     |     5 |  9055 |     8 |
|   8 |         NESTED LOOPS  OUTER               |                     |     5 |  8575 |     7 |
|   9 |          NESTED LOOPS  OUTER              |                     |     5 |  8280 |     6 |
|  10 |           NESTED LOOPS  OUTER             |                     |     5 |  7785 |     5 |
|  11 |            NESTED LOOPS  OUTER            |                     |     5 |  7290 |     4 |
|  12 |             NESTED LOOPS  OUTER           |                     |     5 |  4130 |     3 |
|* 13 |               TABLE  ACCESS  BY  INDEX  ROWID| S_PROD_INT          |     5 |  2960 |     2 |
|* 14 |                INDEX  RANGE SCAN          | S_PROD_INT_M53      |   326 |       |     5 |
|  15 |               TABLE  ACCESS  BY  INDEX  ROWID| S_PROD_LN           |     1 |   234 |     1 |
|* 16 |                INDEX  UNIQUE  SCAN         | S_PROD_LN_P1        |     1 |       |       |
|  17 |              TABLE  ACCESS  BY  INDEX  ROWID | S_PROD_INT_CRSE     |     1 |   632 |     1 |
|* 18 |               INDEX  RANGE SCAN           | S_PROD_INT_CRSE_U1  |     1 |       |     1 |
|  19 |             TABLE  ACCESS  BY  INDEX  ROWID  | S_LIT               |     1 |    99 |     1 |
|* 20 |              INDEX  UNIQUE  SCAN           | S_LIT_P1            |     1 |       |       |
|  21 |            TABLE  ACCESS  BY  INDEX  ROWID   | S_LIT               |     1 |    99 |     1 |
|* 22 |             INDEX  UNIQUE  SCAN            | S_LIT_P1            |     1 |       |       |
|  23 |           TABLE  ACCESS  BY  INDEX  ROWID    | S_PROD_INT_TNTX     |     1 |    59 |     1 |
|* 24 |            INDEX  RANGE SCAN              | S_PROD_INT_TNTX_U1  |     1 |       |     1 |
|  25 |          TABLE  ACCESS  BY  INDEX  ROWID     | S_VOD               |     1 |    96 |     1 |
|* 26 |           INDEX  RANGE SCAN               | S_VOD_U2            |     1 |       |     2 |
|* 27 |         TABLE  ACCESS  BY  INDEX  ROWID      | S_VOD_VER           |     1 |    45 |     1 |
|* 28 |          INDEX  RANGE SCAN                | S_VOD_VER_U1        |     2 |       |     4 |
|  29 |        TABLE  ACCESS  BY  INDEX  ROWID       | S_ISS_OBJ_DEF       |     1 |    50 |     1 |
|* 30 |         INDEX  RANGE SCAN                 | S_ISS_OBJ_DEF_M3    |     1 |       |     2 |
|  31 |       TABLE  ACCESS  BY  INDEX  ROWID        | S_CTLG_CAT          |     1 |    18 |     1 |
|* 32 |        INDEX  UNIQUE  SCAN                 | S_CTLG_CAT_P1       |     1 |       |       |
|  33 |      TABLE  ACCESS  BY  INDEX  ROWID         | S_CTLG_CAT          |     1 |    18 |     1 |
|* 34 |       INDEX  UNIQUE  SCAN                  | S_CTLG_CAT_P1       |     1 |       |       |
|  35 |     TABLE  ACCESS  BY  INDEX  ROWID          | S_CTLG_CAT          |     1 |    14 |     1 |
|* 36 |      INDEX  UNIQUE  SCAN                   | S_CTLG_CAT_P1       |     1 |       |       |
|  37 |   NESTED LOOPS                          |                     |     1 |   137 |     7 |
|  38 |    NESTED LOOPS                         |                     |     4 |   456 |     6 |
|  39 |     NESTED LOOPS                        |                     |     4 |   364 |     5 |
|  40 |      NESTED LOOPS                       |                     |     2 |   142 |     4 |
|  41 |       NESTED LOOPS                      |                     |     1 |    53 |     3 |
|  42 |        NESTED LOOPS                     |                     |     3 |   120 |     2 |
|* 43 |          INDEX  RANGE SCAN                | S_CTLG_CAT_PROD_U1  |     3 |    57 |     2 |
|* 44 |          TABLE  ACCESS  BY  INDEX  ROWID     | S_CTLG_CAT          |     1 |    21 |     1 |
|* 45 |           INDEX  UNIQUE  SCAN              | S_CTLG_CAT_P1       |     1 |       |       |
|* 46 |         TABLE  ACCESS  BY  INDEX  ROWID      | S_CTLG              |     1 |    13 |     1 |
|* 47 |          INDEX  UNIQUE  SCAN               | S_CTLG_P1           |     1 |       |       |
|  48 |        TABLE  ACCESS  BY  INDEX  ROWID       | S_PARTY_CTGRY       |     2 |    36 |     1 |
|* 49 |         INDEX  RANGE SCAN                 | S_PARTY_CTGRY_F1    |     2 |       |     1 |
|  50 |       TABLE  ACCESS  BY  INDEX  ROWID        | S_PARTY_RPT_REL     |     2 |    40 |     1 |
|* 51 |        INDEX  RANGE SCAN                  | S_PARTY_RPTREL_F50  |     2 |       |     1 |
|  52 |      TABLE  ACCESS  BY  INDEX  ROWID         | S_PARTY_PER         |     1 |    23 |     1 |
|* 53 |       INDEX  RANGE SCAN                   | S_PARTY_PER_F1      |     1 |       |     2 |
|* 54 |     INDEX  RANGE SCAN                     | S_PARTY_PER_U1      |     1 |    23 |     3 |
|  55 |   NESTED LOOPS                          |                     |     1 |   157 |     8 |
|  56 |    NESTED LOOPS                         |                     |     1 |   144 |     7 |
|  57 |     NESTED LOOPS                        |                     |     1 |   125 |     6 |
|  58 |      NESTED LOOPS                       |                     |     1 |   104 |     5 |
|  59 |       NESTED LOOPS                      |                     |     1 |    86 |     4 |
|  60 |        NESTED LOOPS                     |                     |     4 |   264 |     3 |
|  61 |         NESTED LOOPS                    |                     |     2 |    86 |     2 |
|* 62 |           INDEX  RANGE SCAN               | S_PARTY_PER_U1      |     2 |    46 |     4 |
|  63 |           TABLE  ACCESS  BY  INDEX  ROWID    | S_POSTN             |     1 |    20 |     1 |
|* 64 |            INDEX  UNIQUE  SCAN             | S_POSTN_P1          |     1 |       |       |
|* 65 |          INDEX  RANGE SCAN                | S_PARTY_PER_U1      |     2 |    46 |     3 |
|* 66 |         INDEX  RANGE SCAN                 | S_PARTY_RPT_REL_U2  |     1 |    20 |     1 |
|* 67 |        INDEX  RANGE SCAN                  | S_PARTY_CTGRY_U1    |     1 |    18 |     1 |
|* 68 |       TABLE  ACCESS  BY  INDEX  ROWID        | S_CTLG_CAT          |     1 |    21 |     1 |
|* 69 |        INDEX  UNIQUE  SCAN                 | S_CTLG_CAT_P1       |     1 |       |       |
|* 70 |      INDEX  RANGE SCAN                    | S_CTLG_CAT_PROD_U1  |     1 |    19 |     1 |
|* 71 |     TABLE  ACCESS  BY  INDEX  ROWID          | S_CTLG              |     1 |    13 |     1 |
|* 72 |      INDEX  UNIQUE  SCAN                   | S_CTLG_P1           |     1 |       |       |
|  73 |   NESTED LOOPS                          |                     |     1 |    58 |     3 |
|  74 |    NESTED LOOPS                         |                     |     3 |   135 |     2 |
|* 75 |      INDEX  RANGE SCAN                    | S_CTLG_CAT_PROD_U1  |     2 |    38 |     2 |
|* 76 |      TABLE  ACCESS  BY  INDEX  ROWID         | S_CTLG_CAT          |     1 |    26 |     1 |
|* 77 |       INDEX  UNIQUE  SCAN                  | S_CTLG_CAT_P1       |     1 |       |       |
|* 78 |     TABLE  ACCESS  BY  INDEX  ROWID          | S_CTLG              |     1 |    13 |     1 |
|* 79 |      INDEX  UNIQUE  SCAN                   | S_CTLG_P1           |     1 |       |       |
-----------------------------------------------------------------------------------------------
该SQL涉及近四十个表和索引的链接操作，而且全部采用嵌套循环链接模式，是典型的siebel应用。这个SQL的执行计划十分复杂，我甚至不愿意去看它；既然从执行计划上进一步分析过于违背“惰性”，我首先想到的还是从wait interface入手，毕竟OWI是"useless"的。因为无法远程操作，反复收集信息会让整个过程十分缓慢，一口气问对方要了10046,errorstack,latches的dump信息，以及该数据库的完整RDA报告。 既然是latch free等待，那么总要先定位到相关的latch才好进一步分析，我们首先看10046 trace:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
cat    siebdb1_ora_15465.trc |grep  "nam='latch free'" |awk  '{print $7}'  |sort|uniq    -c
1 p1=-4611685984823284696
169 p1=-4611685984823317640
1 p1=-4611685988591416672
1 p1=-4611685988592487696
2 p1=-4611685988692060472
1 p1=-4611685988694684144
1 p1=-4611685988705526816
1 p1=-4611685988733017136
1 p1=-4611685988779484112
1 p1=-4611685988784059264
1 p1=-4611685988787329592
1 p1=-4611685988788323152
1 p1=-4611685988800312448
2 p1=-4611685988805347840
24 p1=-4611685988806714976
2 p1=-4611685988837854328
7 p1=-4611685988841028520
1 p1=-4611685988869432648
1 p1=-4611685988871320920
通过以上脚本处理，我们可以看到在执行过程中出现了各种latch free，出现最频繁的是p1=-4611685984823317640的栓。latch free等待事件中p1所代表的是会话所等待栓的地址。-4611685984823317640换算到16进制为C0000007D2F56B78。我们看看这个栓到底是何方神圣:
?
1
2
3
4
5
6
c0000007d2f56b78 cache buffer handles  level =3
        Location  from  where  latch  is  held: kcbzfs:
        Context saved  from  call: 0
        state=busy
  gotten 2734921346 times wait, failed  first  656272521 sleeps 44808579
  gotten 0 times nowait, failed: 0
当会话需要pin住buffer header时它首先要获去buffer handle，得到buffer handle的过程中首先要抢占cache buffer handles栓，为了避免对于cache buffer handles栓的过度争用，每个会话被允许cache一小撮buffer handles,也叫保留集(reserved set)。该保留集的上限由隐式参数_db_handles_cached(默认为5)所控制，在此基础上会话在执行不是十分复杂的SQL时不必反复申请栓。 同时存在一些十分复杂的语句，需要同时pin住大量的缓存，此时若存在空闲的handle，会话被允许索要手头5个cached buffer handles以外的handle。也为了限制单个会话的资源占用量，Oracle通过内部算法总是尽量让每个进程能够pin住”合理份额“的buffer，由此单个会话同一时间所能持有的总handle数等于db_block_buffers/processes，隐式参数_cursor_db_buffers_pinned指出了该值。另cache buffer handles并没有子栓，仅有父栓本身，所以如果系统中有大量复杂SQL，会话所能cache的buffer handles远不足以满足需求时，该栓就成为整个系统性能的瓶颈，甚至可能出现潜在的死锁/饿死问题。 结合我们从trace中获取的实际信息，cache buffer handles的命中率为1-656272521/2734921346=76%;一般来说如果系统中某个栓的命中率低于95%时，我们就可以认为该栓的争用较为激烈了；而此处一个父栓的命中率到了80%以下，我们很难想象其性能恶化状态。 在同事发给我的RDA中探索，从resource limit栏目中可以看到该实例的会话高水位从未超过2500，而实例设定的processes值则为6000，这是一个很多初级DBA或施工方会犯的错误，认为设定高一些的processes值可以避免出现ora-00020错误从而高枕无忧，殊不知Oracle中许多隐式参数的分配是针对process数来计算的，资源总数是有限的，process数多则单个会话所能操控的理论资源数相应减少，这不仅在Oracle中，几乎是放之四海皆准的原理。 极有可能是过高的processes参数影响了_cursor_db_buffers_pinned，而该问题SQL除了十分复杂外还有全部采用nested loop链接方式的问题，这就要求同时时刻需pin住大量不同表和索引的buffer,于是在SQL执行伊始会话尝试pin住了一部分的buffer,但因为最终达到了_cursor_db_buffers_pinned所指定的上限，该会话在持有cache buffer handles栓的情况下处于hang状态，进一步导致了大量其他会话因latch free等待而阻塞。 当时给同事的最主要建议就是降低processes参数，从该系统的V$resource_limit动态视图来看，将processes设到3000就绰绰有余了；同时我们可以增大隐式参数_db_handles_cached以避免系统中对该栓的争用。 几天后，同事回信告诉我开发人员通过其他途径绕过了该问题，但我还是建议他在未来可能的offline计划中加入修改processes和_db_handles_cached参数的操作。 这个case发生几个月后，我无意中从MOS中发现以下note: 
Hdr: 9328032 10.2.0.3 RDBMS 10.2.0.3 BUFFER CACHE PRODID-5 PORTID-23
Abstract: INCREASE IN CACHE BUFFER CHAINS/HANDLES WAITS

*** 01/29/10 05:41 am ***
TAR:
----
3-1375394871

PROBLEM:
--------
Today, and despite no significant change in workload we have increased waits
on these two latches:

29Jan2010_1030_1045 : Top 5 Timed Events



本文转自maclean_007 51CTO博客，原文链接:
http://blog.51cto.com/maclean/1277511

文章标签：
视频点播
SQL
索引
关系型数据库
Oracle
关键词：
SQL性能
SQL buffer
cache SQL
科技小能手
目录
相关文章
猿java
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能？
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL，并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点，包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后，通过具体示例展示了索引如何提升查询速度，并提供了三层B+树的存储容量计算方法。通过这些技巧，可以帮助开发者有效提升数据库查询效率。
猿java
203 2
潇洒洒
|
1月前
|
SQL 数据库 UED
SQL性能提升秘籍：5步优化法与10个实战案例
在数据库管理和应用开发中，SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度，还能降低服务器负载，提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例，帮助构建高效、稳定的数据库应用。
潇洒洒
65 3
潇洒洒
|
1月前
|
SQL IDE 数据库连接
IntelliJ IDEA处理大文件SQL：性能优势解析
在数据库开发和管理工作中，执行大型SQL文件是一个常见的任务。传统的数据库管理工具如Navicat在处理大型SQL文件时可能会遇到性能瓶颈。而IntelliJ IDEA，作为一个强大的集成开发环境，提供了一些高级功能，使其在执行大文件SQL时表现出色。本文将探讨IntelliJ IDEA在处理大文件SQL时的性能优势，并与Navicat进行比较。
潇洒洒
33 4
sunrr
|
1月前
|
SQL 存储 缓存
如何优化SQL查询性能？
【10月更文挑战第28天】如何优化SQL查询性能？
sunrr
153 10
技术自由圈/原疯狂创客圈
|
1月前
|
SQL 关系型数据库 MySQL
惊呆：where 1=1 可能严重影响性能，差了10多倍，快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用，但可能在无真实条件时导致全表扫描，严重影响性能。尼恩建议通过其他条件或SQL子句命中索引，或使用MyBatis的`<where>`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议，帮助大家在面试中展示深厚的技术功底，赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。
技术自由圈/原疯狂创客圈
33 2
最好zzz
|
1月前
|
SQL 缓存 监控
SQL性能提升指南：五大优化策略与十个实战案例
在数据库性能优化的世界里，SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载，提高应用响应速度，甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤，并结合十个实战案例，为你提供一份详尽的性能提升指南。
最好zzz
52 0
1100237741946300
|
2月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中，慢SQL（即执行缓慢的SQL语句）不仅会影响查询性能，还可能对数据库的写入性能产生显著的不利影响
1100237741946300
146 5
何雨晨
|
2月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差？其实可以这样优化
遇到SQL 子查询性能很差？其实可以这样优化
何雨晨
149 2
何雨晨
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
76 1
1100237741946300
|
2月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中，慢SQL（即执行缓慢的SQL语句）不仅会影响查询性能，还可能对数据库的写入性能产生显著的不利影响
1100237741946300
219 0

热门文章

最新文章

  • 1
    Flask 框架防止 SQL 注入攻击的方法
  • 2
    Vanna：开源 AI 检索生成框架，自动生成精确的 SQL 查询
  • 3
    SQL自学笔记（3）：SQL里的DCL,DQL都代表什么？
  • 4
    Flink SQL Deduplication 去重以及如何获取最新状态操作
  • 5
    MySQL导入.sql文件后数据库乱码问题
  • 6
    MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
  • 7
    MySQL 高级(进阶) SQL 语句
  • 8
    MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
  • 9
    SQL自学笔记（1）：什么是SQL？有什么用？
  • 10
    SQL慢查询优化策略
  • 1
    SQL脚本相除
    73
  • 2
    0基础学习SQL注入之万能账号密码（BUUctf例题-[极客大挑战 2019]EasySQL1）
    699
  • 3
    SQL脚本利用带关联子查询Update语句更新数据
    137
  • 4
    10分钟了解Flink SQL使用
    401
  • 5
    SQL脚本把多行SQL数据变成一条多列数据
    123
  • 6
    mybatis动态sql
    46
  • 7
    基于若依的ruoyi-nbcio流程管理系统修改代码生成的sql菜单id修改成递增id（谨慎修改，大并发分布式有弊端）
    119
  • 8
    SQL脚本字符串替换
    77
  • 9
    你写的每条SQL都是全表扫描吗
    187
  • 10
    数据库SQL语言实战（六）
    54

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)