錯誤碼ora1000原因？

----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

分類: script

標簽: ora-1000