320385f9277ba393fb.png (232.44 KB, 下载次数: 302)
2020-10-23 14:27 上传
在这些等待事件（除了db cpu）中, 等待次数最高的是cursor: pin S wait on X
生成一下最近4小时内的ash报告进一步分析
70235f927bda0822f.png (208.9 KB, 下载次数: 321)
2020-10-23 14:44 上传
Top Blocking Sessions
- Blocking session activity percentages are calculated with respect to waits on enqueues, latches and "buffer busy" only
- '% Activity' represents the load on the database caused by a particular blocking session
- '# Samples Active' shows the number of ASH samples in which the blocking session was found active.
- 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the blocking session was found active.
Blocking Sid (Inst) | % Activity | Event Caused | % Event | User | Program | # Samples Active | XIDs |
603,30751( 1) | 18.94 | cursor: pin S wait on X | 18.39 | ZLNECHIS | ZLBH.exe | 3,830/11K [ 35%] | 15 |
2017,26607( 1) | 8.13 | cursor: pin S wait on X | 8.13 | ZLNECHIS | ZLBH.exe | 1,339/11K [ 12%] | 0 |
194,40077( 1) | 8.01 | cursor: pin S wait on X | 8.01 | ZLNECHIS | ZLBH.exe | 2,170/11K [ 20%] | 1 |
2265,34357( 1) | 7.60 | cursor: pin S wait on X | 7.60 | ZLNECHIS | ZLBH.exe | 1,478/11K [ 13%] | 0 |
154,57857( 1) | 1.98 | cursor: pin S wait on X | 1.98 | ZLNECHIS | ZLBH.exe | 4,787/11K [ 44%] | 5 |
解决办法：
SQL> selectsid,serial#,username,machine,blocking_session from v$session where sid=603;
SID SERIAL# USERNAME
---------- ----------------------------------------
MACHINE
----------------------------------------------------------------
BLOCKING_SESSION
----------------
603 30751 ZLNECHIS
WORKGROUP\2Y-20141125REWN
2162
SQL> alter system kill session'603,30751' immediate;
System altered.
SQL> selectsid,serial#,username,machine,blocking_session from v$session where sid=603;
no rows selected
| 欢迎光临 重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |