Home » RDBMS Server » Performance Tuning » open cursors
open cursors [message #271296] Mon, 01 October 2007 03:28 Go to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hi Friends,

when i execute the below statement,


SELECT sid, user_name, COUNT(*) "Cursors per session"
FROM v$open_cursor
GROUP BY sid, user_name


I have about 379 as result.

In my init.ora, the parameter "open_cursors=400".

What factors should be taken into consideration to increase this value?
Re: open cursors [message #271297 is a reply to message #271296] Mon, 01 October 2007 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The number of cursors that can be opened at the same time?

By the way, open_cursor is the number of cursors per session.

Regards
Michel
Re: open cursors [message #271302 is a reply to message #271297] Mon, 01 October 2007 03:44 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hi Michel,

thanks,

this means that if we take the below output (in red), the user mnswcp is connected to the database and is using 34 cursors over the total of 400 that I have.

SID USER_NAME Cursors per session
15 MNSWCP 34



From which I read, if there is a cursor problem, I should get "ORA-01000 Open Cursor error".

Fortunately, till now I haven,t get this error. But to be on the safe side, I want to increase the open cursors value.

Can you please tell me which factors should be taken into consideration to increase this value.


Thx


Re: open cursors [message #271303 is a reply to message #271296] Mon, 01 October 2007 03:48 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Also,

I came across this

"The maximum value of this parameter is operating system dependent"

from "http://www.oracleanalyzer.com/archives/2003/12/"

Re: open cursors [message #271311 is a reply to message #271302] Mon, 01 October 2007 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is using 34 cursors over the total of 400 that I have.

that he can open.

Quote:
Can you please tell me which factors should be taken into consideration to increase this value.

Quote:
The number of cursors that can be opened at the same time

This is application dependent. See the code.

Quote:
The maximum value of this parameter is operating system dependent

Yes and then?

Regards
Michel
Re: open cursors [message #271517 is a reply to message #271302] Mon, 01 October 2007 16:48 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Apparently you don't need to increase that parameter.

Given 400 as open cursor limit per session , and 34 as the current open cursors for that user, the session has margin for 366 additional cursors to open.

In other words , every session has 400 cursors to open.

Best Regards
Ignacio

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:40] by Moderator

Report message to a moderator

Re: open cursors [message #271537 is a reply to message #271517] Mon, 01 October 2007 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to confirm what I said.

Regards
Michel
Re: open cursors [message #271544 is a reply to message #271537] Tue, 02 October 2007 00:29 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hi Friends,

I made a statistics yesterday and I noticed that over the total (open_cursors) of 400 that I have in the parameter file, an average of 350-375 are being used.

Fortunately, till now I haven't come across the error 'ORA-01000 Open Cursor error'.

Can I increase it, to be on the safe side?


Thx
Re: open cursors [message #271562 is a reply to message #271544] Tue, 02 October 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the topic.

Regards
Michel
Re: open cursors [message #271567 is a reply to message #271562] Tue, 02 October 2007 01:19 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
which topic?

Re: open cursors [message #271569 is a reply to message #271567] Tue, 02 October 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This one.

Regards
Michel
Re: open cursors [message #271572 is a reply to message #271569] Tue, 02 October 2007 01:43 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
I didnt get you Sir
Re: open cursors [message #271586 is a reply to message #271572] Tue, 02 October 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't get you more.

Regards
Michel
Re: open cursors [message #271589 is a reply to message #271569] Tue, 02 October 2007 02:13 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hi All,

it's said that 'The maximum value of this parameter is operating system dependent'.

I have Solaris 8 and oracle 9db. 600M SGA, 3GB Physical memory. Can you please tell me the maximum value I can set for this parameter.


Thx
Dirish
Re: open cursors [message #271590 is a reply to message #271562] Tue, 02 October 2007 02:15 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Sir,

you asked me to read "this topic". But you haven't sent me any topic/mail/URL.

Thx.
Dirish
Re: open cursors [message #271606 is a reply to message #271590] Tue, 02 October 2007 03:10 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please tell me the maximum value I can set for this parameter.

No, I can't. Ask Oracle or try all values you can.

Quote:
you asked me to read "this topic". But you haven't sent me any topic/mail/URL.

I answered:
Quote:
This one.

That is "this very one you are currently writing in".

Regards
Michel
Previous Topic: Lots of 'Row Lock Waits" on the indexes, but no on the based table
Next Topic: Tuning query (insert using select)
Goto Forum:
  


Current Time: Wed Dec 07 08:22:58 CST 2016

Total time taken to generate the page: 0.10635 seconds