open cursors

All posts relating to Oracle database administration.

Moderator: Tim...

open cursors

Postby royalyogi » Thu Sep 26, 2013 11:29 am

we are continued getting below alert and current parameter of open_cursor and processes is 1000.
Kindly let help us to understand below points:

1.)Why this value is shows in decimal
2.)To stop this alert we need to increase both parameters or only processes parameter can do?
3.)How to check CURRENT_UTILIZATION utilization of this parameter
4.)what is resolution of this issue
SQL>/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- ---------
processes 119 221 1000 1000

SYS> show parameter open

NAME TYPE VALUE
------------------------------------ ----------- ------
open_cursors integer 1000

Alert: Oracle Database: The number of Open Cursors is high.
Source: BNCT01T
Path: UKSODB1A.cuk;UKSODB1A
Last modified by: System
Last modified time: 9/23/2013 11:20:20 PM Alert description: The number of open cursors has exceeded the defined threshold.
The value that exceeded the threshold is: 906.333333333333.
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: open cursors

Postby Tim... » Thu Sep 26, 2013 6:09 pm

Hi.

It's kind-of impossible to answer this questions without asking more questions. The amount of open cursors you would expect depends a lot on the application and workload. For example...

- If your application has one user, then that user having 1000 open cursors sounds quite extreme. It probably indicates a problem in the code.
- If your application has 1000 users, then only having 1000 open cursors is an extremely low number. :)

Depending on what your application is doing, then it would probably be advisable to increase the number of open cursors allowed.

This hasn't got anything to do with the processes parameter. A single session can have many open cursors.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: open cursors

Postby royalyogi » Thu Sep 26, 2013 6:24 pm

Thanks Tim :D
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: open cursors

Postby Tim... » Thu Sep 26, 2013 6:53 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 7 guests