来吧，cursor: pin s wait on x-凯发app官方网站

共享池大小
col name for a40
set pages 100
select name,round(bytes/1024/1024)size_mb from v$sgainfo;
内存抖动
select component,
oper_type,
round(a.initial_size/1024/1024) initial_size_mb,
round(final_size/1024/1024) final_size_mb,
round((final_size-initial_size)/1024/1024) resize_mb,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') started_time,
to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') end_time
from v$sga_resize_ops a
where start_time >= to_date('2021-12-20 00:00', 'yyyy-mm-dd hh24:mi')
and start_time <= to_date('2021-12-23 23:59', 'yyyy-mm-dd hh24:mi')
order by started_time;
查找引起此事件的会话
select sid,serial#,sql_id,blocking_session,blocking_session_status,event
from v$session where event ='cursor: pin s wait on x';
相关sql
select s.sid, t.sql_text
from v$session s, v$sql t
where s.event like '%cursor: pin s wait on x%'
and t.sql_id = s.sql_id
how to determine the blocking session for event: 'cursor: pin s wait on x' (doc id 786507.1)

awr中看这里

如果硬解析高，这可能表明文字使用率高或引入了许多新的sql。在这种情况下，
请考虑使用绑定变量代替文字。
如果软解析的百分比很高，则检查应用程序以查看它是否正在使用可共享的 sql。
理想情况下，execute to parese 应该接近 100%

还有

高版本诊断请看 blog.chinaunix.net/uid-20687159-id-5853384.html

进一步深入诊断

单机
$ sqlplus "/ as sysdba"
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
quit
rac
$ sqlplus "/ as sysdba"
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 258
quit
获取error stack
$ sqlplus "/ as sysdba"
sql> oradebug setospid <p.spid from above>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit

如果遇到解析错误，即大量ora-923，可以诊断一下

alter system set events '10035 trace name context forever, level 1';
等应用重复
alter system set events '10035 trace name context off';

好像19c数据库的告警日志中不设置此事件就有错误的sql。

剑走偏锋，获取大量硬解析的方法：

select instance_number,top_level_sql_id,sql_id,count(*) from dba_hist_active_sess_history
where in_hard_parse='y' group by instance_number,top_level_sql_id,sql_id
having count(*)>100 order by count(*) desc;

应该找到原因了吧？

阅读(1285) | 评论(0) | 转发(0) |

上一篇：pdb 启动时间及状态

下一篇：凑个数 windows异常宕机，与ora-7445 abort_process_terminated

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们