Oracle数据库——超出打开游标的最大数排查处理

-王二毛-

已于 2025-04-28 14:10:58 修改

阅读量559

点赞数 2

分类专栏：数据库文章标签：数据库 oracle

于 2025-03-02 11:08:56 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45084986/article/details/145959229

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

‌一、Oracle中的游标（Cursor）‌是一种处理数据的方法，用于查看或处理结果集中的数据。游标相当于一个执行了SQL代码后得到的结果集。游标提供了在结果集中逐行前进或后退的能力，每次调用游标的fetch方法时，相当于有一根指针逐行扫过结果集提取出一条结果‌。游标可以分为共享游标和会话游标：
‌1、共享游标‌（Shared Cursor）缓存在SGA的库缓存中，可以细分为父游标（Parent Cursor）和子游标（Child Cursor）。父游标存储SQL文本，而子游标存储可重用的解析树和执行计划‌
‌2、会话游标‌（Session Cursor）是当前会话解析和执行SQL的载体，缓存在PGA中。会话游标以哈希表的方式存储，通过父游标找到对应的子游标，进而执行SQL‌
二、Oracle中的会话（Session）‌是用户与数据库服务器进行交互的一个独立工作环境。每个会话都有其自己的内存区域（PGA）和共享内存区域（SGA）。会话用于在当前环境中解析和执行SQL语句，处理事务等。会话中的操作包括声明游标、打开游标、取数据、关闭游标等步骤‌
三、游标和会话的关联：
会话游标（Session Cursor）是当前会话解析和执行SQL的载体。即会话游标用于在当前会话中解析和执行SQL。
会话游标与会话是一一对应的，不同会话的会话游标之间不能共享。
每个会话在使用的过程中都可能会打开多个游标来执行SQL语句。

查询数据库游标上限

select * from v$parameter where name like '%open_cursors%'
--或
SELECT
    name,
    value
FROM
    v$parameter
WHERE
    name = 'open_cursors';

在这里插入图片描述

查看每个会话游标打开数量

--查看当前会话
SELECT * FROM v$session;

--查看打开的游标数量
SELECT COUNT(*) FROM v$open_cursor;

--查看处于打开状态的游标数量等信息
SELECT * FROM v$open_cursor WHERE cursor_type = 'OPEN';

--要查看特定会话打开的游标数量，可以将v$open_cursor视图与v$session视图关联起来。例如，以下查询显示了每个用户打开的游标数量：
SELECT
    a.sid,
    a.serial#,
    a.username,
    a.osuser,
    COUNT(*) AS open_cursors
FROM
    v$session a,
    v$open_cursor b
WHERE
    a.saddr = b.saddr
GROUP BY
    a.sid, a.serial#, a.username, a.osuser
ORDER BY
    open_cursors DESC;

在这里插入图片描述

查看游标执行的SQL语句

SELECT q.sql_text
FROM v$open_cursor o
JOIN v$sql q ON q.hash_value = o.hash_value
WHERE o.sid = :your_sid; -- 将:your_sid替换为你要查询的会话ID

在这里插入图片描述

修改游标数量

使用ssh登录服务器

切换到 Oracle 用户

su - oracle

或者如果你使用的是 sudo，可以尝试：

--查看当前用户
sudo -i -u oracle

启动 SQLPlus

sqlplus / as sysdba

这条命令会尝试以 sys 用户的身份登录到数据库。如果你需要以其他用户的身份登录，可以使用：

SELECT USER FROM DUAL;	--查看当前用户信息
sqlplus username/password@yourdatabase

在这里插入图片描述

开始修改游标数量

ALTER SYSTEM SET open_cursors = 2000 SCOPE=BOTH;   --SCOPE=BOTH表示立即生效

查看修改后的游标信息

在这里插入图片描述

退出sqlplus

exit

附，有关open_cursor 视图cursor_type 字段值的了解

SELECT * FROM v$open_cursor WHERE cursor_type = 'OPEN';

有关cursor_type 字段值了解：
1、DICTIONARY LOOKUP CURSOR CACHED
这通常指的是在解析SQL语句时，Oracle数据库使用缓存的字典查找游标来加速对数据库对象（如表、列等）的元数据访问。字典查找游标缓存可以减少重复解析相同对象元数据所需的开销。
2、OPEN-RECURSIVE
这通常表示一个游标是递归打开的。在PL/SQL中，递归游标可能出现在递归子程序（如递归函数或过程）中，或者在一个循环中反复打开同一个游标。
3、PL/SQL CURSOR CACHED
这指的是PL/SQL块中声明的游标被缓存起来以便重用。PL/SQL游标缓存允许数据库在多次执行相同的PL/SQL代码时重用游标，从而提高性能。这通常是在PL/SQL包的规范或主体中声明的游标，并且使用了CACHE关键字。
4、OPEN
这表示游标当前处于打开状态。在Oracle中，游标必须首先被打开（使用OPEN语句），然后才能从中提取数据。一旦数据提取完成，游标应该被关闭（使用CLOSE语句）以释放资源。
5、SESSION CURSOR CACHED
如前所述，这表示游标已经被缓存到会话游标缓存中。当游标关闭且满足缓存条件时（如之前被打开过多次），Oracle会将其放入会话游标缓存中以便后续重用。这有助于减少软解析的开销并提高性能。
6、BUNDLE DICTIONARY LOOKUP CACHED
这是一个特定于Oracle某些版本和特性的术语，它指的是对多个字典查找请求进行捆绑并缓存结果。这通常用于优化SQL语句解析过程中的元数据访问。通过捆绑和缓存这些查找请求，Oracle可以减少对系统表的访问次数，从而提高性能。