ora-1000问题排查

----ora-1000问题排查
open_cursor.底层视图是从x$kgllk中来的。并且通过kglhdnsp = 0过滤了sp打开的cursor。会加1号null的易碎解析锁。可能的原因：1.执行的sql语句确实比较多(可能性比较小)。2.sp里面的循环硬解析造成的

1.查找哪些session当前打开的游标数。按游标数desc排序
select a.INST_ID,a.value, s.username, s.sid, s.serial#,s.MACHINE,s.MODULE
from gv$sesstat a, v$statname b, gv$session s
where a.statistic# = b.statistic#
and s.sid = a.sid
and a.INST_ID=s.INST_ID
and b.name in ('opened cursors current')
order by value desc

 

2.查找每个sessioin打开的游标数
SELECT se.sid ,op.sql_text, op.user_name,se.MODULE,count(*) as "OPEN CURSORS"
FROM gv$open_cursor op,gv$session se
where op.INST_ID=se.INST_ID
and op.SADDR=se.SADDR
and op.SID=se.SID
and op.USER_NAME=se.USERNAME
/*and sid=*/
GROUP BY se.sid ,op.sql_text, op.user_name,se.MODULE order by 5 desc ;

 

3.查看系统内的硬解析。
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE, COUNT(*) cnt
FROM gv$sqlarea
WHERE FORCE_MATCHING_SIGNATURE != 0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 10),
sq AS
(SELECT PARSING_SCHEMA_NAME,
sql_text,
FORCE_MATCHING_SIGNATURE,
row_number() over(partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM gv$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE FROM c))
SELECT sq.PARSING_SCHEMA_NAME,
sq.sql_text,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c, sq
WHERE sq.FORCE_MATCHING_SIGNATURE = c.FORCE_MATCHING_SIGNATURE
AND sq.p = 1
ORDER BY c.cnt DESC