文档章节

Monitoring Open and Cached Cursors

巴顿.太一
 巴顿.太一
发布于 2017/09/07 15:58
字数 1852
阅读 9
收藏 0
点赞 0
评论 0

Just about every DBA has had to deal with ora-1000 errors, "Maximum open cursors exceeded." This article will discuss initialization parameters that affect open cursors, the difference between open and cached cursors, closing cursors, and monitoring open and cached cursors.

Open cursors

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

Session cached cursors

There are two main initialization parameters that affect cursors, and many folks get them confused. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There's no relationship between the two parameters.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)

In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

Why cache cursors?

The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.

There's another advantage, though. Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.

Monitoring open cursors

I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:
 

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
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';

 

If you're running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:

 

--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, 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' 
group by s.username, s.machine
order by 1 desc;

 

Tuning OPEN_CURSORS

The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won't have to worry about it. If your sessions are running close to the limit you've set for OPEN_CURSORS, raise it. Your goal in tuning this parameter is to set it high enough that you never get an ora-1000 during normal operations.

If you set OPEN_CURSORS to a high value, this doesn't mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.

To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.

 

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
  2> from v$sesstat a, v$statname b, v$parameter p
  3> where a.statistic# = b.statistic# 
  4> and b.name = 'opened cursors current'
  5> and p.name= 'open_cursors'
  6> group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
            1953         2500

 

After you've increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of your sessions. If you have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then you've likely got a cursor leak in your application code: your application is opening cursors and not closing them when it's done.

There is nothing you, as a DBA, can do to fix a cursor leak. The application developers need to go through the code, find the cursors that are being left open, and close them. As a stopgap, the most you can do is raise OPEN_CURSORS very high and schedule times when all the application sessions will be closed and reopened (eg. by kicking the webserver).

How not to tell if you're closing all your cursors

Frustratingly for developers, the session statistic 'currently open cursors' can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable". The cursor may not actually be closed until Oracle needs the space for another cursor.

So it's not possible to test to see if a complex application is closing all its cursors by starting a session, running a test, and then checking to see if currently open cursors has gone down to 1. Even if the application is closing all its cursors properly, currently open cursors may report that some "closeable" cursors are still open.

One way for application developers to tell if an application is closing all its cursors is to do a single test run, on a dedicated development box, while monitoring "opened cursors cumulative" in v$sesstat for the session that's running the test. Then set OPEN_CURSORS to a value a little bit higher than the peak cursors open during your test run, start a new session, and run through multiple iterations of the same test run. If your application still has a cursor leak, you will see the value of OPEN_CURSORS going up, and you may hit an ORA-1000 after a reasonable number of iterations. (Don't set OPEN_CURSORS too low or it may be used up by recursive SQL; if your single test run opens very few cursors, consider making your test run longer rather than setting OPEN_CURSORS unreasonably low.)

Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

 

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

 

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

 

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid
;

 

Tuning SESSION_CACHED_CURSORS

If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.

 

SQL> select cach.value cache_hits, prs.value all_parses, 
  2> prs.value-cach.value sess_cur_cache_not_used
  3> from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
  4> where cach.statistic# = nm1.statistic#  
  5> and nm1.name = 'session cursor cache hits' 
  6> and prs.statistic#=nm2.statistic#
  7> and nm2.name= 'parse count (total)'
  8> and cach.sid= &sid and prs.sid= cach.sid ;

Enter value for sid: 947
old   8: and cach.sid= &sid and prs.sid= cach.sid
new   8: and cach.sid= 947 and prs.sid= cach.sid

CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
       106        210                     104

 

Monitor this in concurrence with the session cursor cache count.

 

--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;

 

If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and you suspect that the application is re-submitting the same queries for parsing repeatedly, then increasing SESSION_CURSOR_CACHE_COUNT may help with latch contention and give a slight boost to performance. Note that if your application is not resubmitting the same queries for parsing repeatedly, then session_cursor_cache_hits will be low and the session cursor cache count may be maxed out, but caching cursors by session won't help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter won't get you anything.

 

------------------------------------------------------------------

alter system set open_cursors  = 1000 ;

alter system set session_cached_cursors = 1000 scope = spfile; --need restart db

© 著作权归作者所有

共有 人打赏支持
巴顿.太一
粉丝 3
博文 67
码字总数 75265
作品 0
海淀
技术主管
Oracle游标的查询

The initialization parameter OPEN_CURSORS in INITSID.ORA determines the maximum number of cursors per user. Check the parameter specified by executing the following SQL: select ......

jimbuster ⋅ 2005/12/26 ⋅ 0

Oracle参数open_cursors和session_cached_cursor详解

1、opencursors与sessioncached_cursor的作用 opencursors设定每个session(会话)最多能同时打开多少个cursor(游标)。sessioncached_cursor 设定每个session(会话)最多可以缓存多少个关...

余二五 ⋅ 2017/11/14 ⋅ 0

开发cloudify配方文件- Web Management Console

配置Web Management Console Cloudify web management console提供一个基于web的图形用户界面来监控你的应用和其对应的服务。特别在Applications视图下,通过线性图和仪表盘小部件来展示被选...

chape ⋅ 2013/04/10 ⋅ 0

Oracle服务器的Execute to Parse

有台Oracle数据库服务器,发现内存占用得比较厉害,生成awr报告,发现有个指标有点异常 Instance Efficiency Percentages (Target 100%) 里面的Execute to Parse 居然出现负数。找到某大师给...

rudy_gao ⋅ 2014/07/10 ⋅ 0

Troubleshooting Open Cursor Issues

25 Troubleshooting Open Cursor Issues Similar to any application that uses Oracle Database as backend repository, Oracle Identity Manager runs several SQL statements. For every ......

Oscarfff ⋅ 2016/09/18 ⋅ 0

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列4

CURSORSHARING 参数 (8.1.6 以上) 这个参数需要小心使用。如果它被设为FORCE,那么Oracle会尽可能用系统产生的绑定变量来替换原来SQL中的literals部分。对于很多仅仅是literal不一样的相似的...

bisal ⋅ 2013/09/02 ⋅ 0

python ssh tunnel

安装 sshtunnel 使用示例

flyking ⋅ 2016/10/24 ⋅ 0

SQL Server中查看哪些游标未释放

SQL SERVER提供了一个动态管理函数sys.dmexeccursors,返回有关在数据库中打开的游标的信息。 一、语法 dmexeccursors (session_id | 0 ) 二、参数说明 session_id | 0:会话的 ID。 1、如果...

学习也休闲 ⋅ 2016/03/16 ⋅ 0

Teiid 7.5 Beta2,数据虚拟化系统

Teiid 7.5 Beta2 发布了,该版本包含一些新特性和很多的bug修复,值得关注的有: Window function support - Teiid and pushdown support was added for SQL 2003 OLAP window functions. T......

红薯 ⋅ 2011/08/01 ⋅ 1

SnapRealm类分析

SnapRealm类说明: struct SnapRealm { srt srnode; 描述snap的seq、snap创建的时间、SnapInfo以及snaplinkt相关内容 MDCache *mdcache; 描述Snap在内存中的存储状态 CInode inode; 描述sna...

linuxhunter ⋅ 2016/07/22 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

OSChina 周三乱弹 —— 这样的女人私生活太混乱了

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @ 胖达panda :你经历过体验到人生的大起大落吗?我一朋友在10秒内体验了,哈哈。@小小编辑 请点一首《almost lover》送给他。 《almost love...

小小编辑 ⋅ 43分钟前 ⋅ 7

自己动手写一个单链表

文章有不当之处,欢迎指正,如果喜欢微信阅读,你也可以关注我的微信公众号:好好学java,获取优质学习资源。 一、概述 单向链表(单链表)是链表的一种,其特点是链表的链接方向是单向的,对...

公众号_好好学java ⋅ 48分钟前 ⋅ 0

Centos7重置Mysql 8.0.1 root 密码

问题产生背景: 安装完 最新版的 mysql8.0.1后忘记了密码,向重置root密码;找了网上好多资料都不尽相同,根据自己的问题总结如下: 第一步:修改配置文件免密码登录mysql vim /etc/my.cnf 1...

豆花饭烧土豆 ⋅ 今天 ⋅ 0

熊掌号收录比例对于网站原创数据排名的影响[图]

从去年下半年开始,我在写博客了,因为我觉得业余写写博客也还是很不错的,但是从2017年下半年开始,百度已经推出了原创保护功能和熊掌号平台,为此,我也提交了不少以前的老数据,而这些历史...

原创小博客 ⋅ 今天 ⋅ 0

LVM讲解、磁盘故障小案例

LVM LVM就是动态卷管理,可以将多个硬盘和硬盘分区做成一个逻辑卷,并把这个逻辑卷作为一个整体来统一管理,动态对分区进行扩缩空间大小,安全快捷方便管理。 1.新建分区,更改类型为8e 即L...

蛋黄Yolks ⋅ 今天 ⋅ 0

Hadoop Yarn调度器的选择和使用

一、引言 Yarn在Hadoop的生态系统中担任了资源管理和任务调度的角色。在讨论其构造器之前先简单了解一下Yarn的架构。 上图是Yarn的基本架构,其中ResourceManager是整个架构的核心组件,它负...

p柯西 ⋅ 今天 ⋅ 0

uWSGI + Django @ Ubuntu

创建 Django App Project 创建后, 可以看到路径下有一个wsgi.py的问题 uWSGI运行 直接命令行运行 利用如下命令, 可直接访问 uwsgi --http :8080 --wsgi-file dj/wsgi.py 配置文件 & 运行 [u...

袁祾 ⋅ 今天 ⋅ 0

JVM堆的理解

在JVM中,我们经常提到的就是堆了,堆确实很重要,其实,除了堆之外,还有几个重要的模块,看下图: 大 多数情况下,我们并不需要关心JVM的底层,但是如果了解它的话,对于我们系统调优是非常...

不羁之后 ⋅ 昨天 ⋅ 0

推荐:并发情况下:Java HashMap 形成死循环的原因

在淘宝内网里看到同事发了贴说了一个CPU被100%的线上故障,并且这个事发生了很多次,原因是在Java语言在并发情况下使用HashMap造成Race Condition,从而导致死循环。这个事情我4、5年前也经历...

码代码的小司机 ⋅ 昨天 ⋅ 2

聊聊spring cloud gateway的RetryGatewayFilter

序 本文主要研究一下spring cloud gateway的RetryGatewayFilter GatewayAutoConfiguration spring-cloud-gateway-core-2.0.0.RC2-sources.jar!/org/springframework/cloud/gateway/config/G......

go4it ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部