当前位置:网站首页>DBA common SQL statements (5) - latch related
DBA common SQL statements (5) - latch related
2022-04-23 09:57:00 【Grainger】
DBA Commonly used SQL sentence :
DBA Commonly used SQL sentence (1)— Overview information
DBA Commonly used SQL sentence (2)— SGA and PGA
DBA Commonly used SQL sentence (3)- cache、undo、 Indexing and waiting Events
DBA Commonly used SQL sentence (4)- Top SQL
Latch relevant SQL
Check Latch Correlation SQL Query the busiest in the current database Buffer,TCH(Touch) The larger the number, the higher the number of visits
SELECT *
FROM ( SELECT addr,
ts#,
file#,
dbarfil,
dbablk,
tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;
see latch shooting
SELECT name, gets, misses, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = 'cache buffers chains';
Find hot spots in data blocks
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;
Find the database object according to the file number and block number
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;
If in Top 5 Found in latch free Hot block event , It can be downloaded from V$latch_children Query the specific sub Latch Information
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;
See what causes latch: cache buffers chains Of 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://yzsam.com/2022/04/202204230952211234.html
边栏推荐
- 雨生百谷,万物生长
- [educational codeforces round 80] problem solving Report
- ARM调试(1):两种在keil中实现printf重定向到串口的方法
- Explanation of order and primitive root of number theory
- ES-aggregation聚合分析
- 1D / 1D dynamic programming learning summary
- SAP ECC connecting SAP pi system configuration
- 杰理之更准确地确定异常地址【篇】
- 从知识传播的维度对比分析元宇宙
- GCD of p2257 YY (Mobius inversion)
猜你喜欢
Number theory blocking (integer division blocking)
Nine abilities of agile manufacturing in the era of meta universe
Career planning and implementation in the era of meta universe
深度选择器
The central control learning infrared remote control module supports network and serial port control
计算机网络安全实验二|DNS协议漏洞利用实验
Epidemic prevention registration applet
SAP ECC connecting SAP pi system configuration
论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》——3背景
SAP salv14 background output salv data can directly save files and send emails (with sorting, hyperlink and filtering format)
随机推荐
DBA常用SQL语句(3)- cache、undo、索引和等待事件
NEC红外遥控编码说明
Nvidia最新三维重建技术Instant-ngp初探
论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》——5结果
Understand scope
Juc并发编程06——深入剖析队列同步器AQS源码
Realize data value through streaming data integration (1)
Realize data value through streaming data integration (3) - real-time continuous data collection
Comparative analysis of meta universe from the dimension of knowledge dissemination
The central control learning infrared remote control module supports network and serial port control
php 二维数组指定元素相等后相加否则新增
[COCI] Vje š TICA (subset DP)
How to use SQL statement union to get another column of another table when the content of a column in a table is empty
Realizing data value through streaming data integration (4) - streaming data pipeline
formatTime时间戳格式转换
SQL调优系列文章之—SQL调优简介
Expansion of number theory Euclid
Realize data value through streaming data integration (2)
面试官:说几个PHP常用函数,幸好我面试之前看到了这篇文章
ABAP CDs view with association example