问题
游标
SQL检索操作返回一组称为结果集的行,这组返回的行都是与SQL语句相匹配的行(零行到多行)。简单地使用SELECT语句,没有办法得到第一行、下一行或前10行。有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的用途所在。游标(cursor)是一个存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
使用游标涉及几个明确的步骤。
❑ 在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句和游标选项。
❑ 一旦声明,就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
❑ 对于填有数据的游标,根据需要取出(检索)各行。
❑ 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的DBMS)。
问题
数据库技术知识库 > ORA-01000:超出打开游标的最大数 > image2021-12-29_18-7-30.png
思路一
1 检查open_cursor参数和当前cursor
show parameter open_cursors
select count(*)from v$open_cursor;
select count(*)from gv$open_cursor;
每个会话可以打开的最大游标数,也就是SQL语句数。推荐将该参数设置为1500。
alter system set open_cursors=1500 scope=spfile sid='*';
2 查找子游标数量
Prompt Top 10 hight version:
select rownum rn,t.*
from (select inst_id,
sql_id,
version_count,
round(sum (sharable_mem)/1024/1024,4) mem_mb
from gv$sqlarea
group by inst_id,sql_id,version_count
order by version_count desc,inst_id,sql_id) t
where rownum<=10;
Prompt
Prompt Top 10 from sharable_mem:
select rownum rn,t.*
from (select inst_id,
sql_id,
version_count,
round(sum (sharable_mem)/1024/1024,4) mem_mb
from gv$sqlarea
group by inst_id,sql_id,version_count
order by mem_mb desc,inst_id,sql_id) t
where rownum<=10;
3 查看哪个用户打开的游标多
select s.USERNAME,S.sid,s.SERIAL#,p.SPID, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s,v$process p
where /*user_name = '' and*/ o.sid=s.sid and p.ADDR=s.PADDR
group by s.USERNAME,S.sid,s.SERIAL#,p.SPID, osuser, machine
having count(*) > 2
order by num_curs;
4 查看最后这个sid会话,执行的SQL
select q.sql_id,count(*)
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid = &sid
group by q.sql_id order by 2;
5 查看sql_id的数量
select SQL_ID,count(*) from v$open_cursor group by sql_id having count(*) >4 order by 2;
6 查看sql_txt是否一样
select sql_fulltext from v$sqlarea where sql_id='&sql_id';
思路二
1 查看等待事件
select event,count(*) from gv$session where wait_class<>'Idle' group by event order by 2;
2 查看预警日志
cdump
grep -i ora- alert*.log
3 用户和游标数
select USER_NAME, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (select s.sid from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by user_name;
4 查看mos
ORA-1000 When Running JDBC Application (Doc ID 1077199.6)
5 初步判断客户程序SQL没有关闭游标
请客户协调业务人员排查是否打开cursor后没有正常关闭。
思路三
1 每个会话的游标数
select sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME
from v$open_cursor
where sid in (select s.sid
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null)
group by sid ,sql_text,USER_NAME order by 3;
2 每个sql语句当前已打开和解析或缓存的游标数
select substr(b.sql_text, 0,59),count(*) from v$open_cursor b group by b.sql_text order by 2;
3 很多SQL语句没有绑定变量
set linesize 200 pages 99
col SUBSTR(SQ.SQL_TEXT,0,60) for a65
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT PARSING_SCHEMA_NAME,FORCE_MATCHING_SIGNATURE, COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE != 0
GROUP BY FORCE_MATCHING_SIGNATURE,PARSING_SCHEMA_NAME
HAVING COUNT(*) > 20),
sq AS
(SELECT sql_text,
FORCE_MATCHING_SIGNATURE,
row_number() over(partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE FROM c))
SELECT c.PARSING_SCHEMA_NAME,substr(sq.sql_text,0,60), 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 ;
思路四
1 每个sql语句当前已打开和解析或缓存的游标数
select substr(b.sql_text, 0,59),count(*) from v$open_cursor b group by b.sql_text order by 2;
2 查看占用最多游标sql语句的执行频率
select sql_id, sql_text from v$sql where sql_txt like '&sql_txt';
结论:这条select 语句占用的游标比较多,执行频率也较高,但是没有绑定变量,这边请协调研发人员排查一下,应该就可以解决这个游标问题。
思路五
找出导致错误的语句
select a.value, s.username, s.sid, s.serial#,s.program,s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;
select sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in (select s.sid from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by sid ,sql_text,USER_NAME;
找出导致错误的程序
select s.program,count(*) from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null group by s.program order by 2;
显示哪些查询导致打开游标最大化,请运行以下 SQL 语句:显示打开最大游标但没有正常关闭后续游标的顶部查询
select USER_NAME, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (select s.sid from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null) group by user_name;