当前位置:网站首页>DBA常用SQL语句 (5) - Latch 相关
DBA常用SQL语句 (5) - Latch 相关
2022-04-23 09:52:00 【Grainger】
DBA常用SQL语句:
DBA常用SQL语句(3)- cache、undo、索引和等待事件
Latch 相关SQL
检查 Latch 的相关 SQL查询当前数据库最繁忙的 Buffer,TCH(Touch)越大表示访问次数越高
SELECT *
FROM ( SELECT addr,
ts#,
file#,
dbarfil,
dbablk,
tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;
查看 latch 的命中率
SELECT name, gets, misses, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = 'cache buffers chains';
查找数据块中的热点块
SELECT *
FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH)
TOUCHTIME
FROM X$BH B, DBA_OBJECTS O
WHERE B.OBJ = O.DATA_OBJECT_ID
AND B.TS# > 0
GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
ORDER BY SUM(TCH) DESC)
WHERE ROWNUM <= 10;
根据文件号和块号查找数据库对象
select owner, segment_name, partition_name, tablespace_name
from dba_extents
where relative_fno = &v_dba_rfile and &v_dba_block between block_id and block_id +
blocks - 1;
如果在 Top 5 中发现 latch free 热点块事件时,可以从 V$latch_children 中查询具体的子Latch 信息
SELECT *
FROM (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,
immediate_misses imiss, spin_gets sgets
FROM v$latch_children
WHERE NAME = 'cache buffers chains'
ORDER BY sleeps DESC)
WHERE ROWNUM < 11;
查看引起 latch: cache buffers chains 的 sql
select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
3 4 5 6 CURRENT_FILE# fn,
CURRENT_BLOCK# blockn
from v$active_session_history ash
, all_objects o
where event like 'latch: cache buffers chains'
and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
current_block#, o.object_name,o.object_type
order by count(*) desc )where rownum <=10;
版权声明
本文为[Grainger]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1986061
边栏推荐
- [COCI] lattice (dichotomy + tree divide and conquer + string hash)
- 论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》
- 2022年制冷与空调设备运行操作考试练习题及模拟考试
- Odoo server setup notes
- Where is int a = 1 stored
- SQL调优系列文章之—SQL性能方法论
- Golang force buckle leetcode 396 Rotation function
- 《谷雨系列》空投
- LeetCode 1249. Minimum Remove to Make Valid Parentheses - FB高频题1
- Planning and construction of industrial meta universe platform
猜你喜欢
《Redis设计与实现》
个人主页软件Fenrus
Random neurons and random depth of dropout Technology
Interviewer: let's talk about some commonly used PHP functions. Fortunately, I saw this article before the interview
Nvidia最新三维重建技术Instant-ngp初探
How to obtain geographical location based on photos and how to prevent photos from leaking geographical location
论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》——5结果
C language: expression evaluation (integer promotion, arithmetic conversion...)
The sap export excel file opens and shows that the file format and extension of "XXX" do not match. The file may be damaged or unsafe. Do not open it unless you trust its source. Do you still want to
Custom login failure handling
随机推荐
杰理之通常程序异常情况有哪些?【篇】
PHP笔记(一):开发环境配置
构建元宇宙时代敏捷制造的九种能力
Setnx command execution failed due to full redis memory
面试官:说几个PHP常用函数,幸好我面试之前看到了这篇文章
Amazon cloud technology entry Resource Center, easy access to the cloud from 0 to 1
杰理之AES能256bit吗【篇】
GCD of p2257 YY (Mobius inversion)
[2020wc Day2] F. Clarice picking mushrooms (subtree and query, light and heavy son thought)
MacOS下使用CLion编译调试MySQL8.x
構建元宇宙時代敏捷制造的九種能力
Compile and debug mysql8 with clion under MacOS x
1D / 1D dynamic programming learning summary
2022年广东省安全员A证第三批(主要负责人)考试试题及答案
SAP salv14 background output salv data can directly save files and send emails (with sorting, hyperlink and filtering format)
[Niuke practice match 68] fans of Niuniu (matrix fast power cycle matrix optimization)
JS node operation, why learn node operation
杰理之有时候发现内存被篡改,但是没有造成异常,应该如何查找?【篇】
个人主页软件Fenrus
Integral function and Dirichlet convolution