Re: Help with ORA-01000 Error
From: joel garry <joel-garry_at_home.com>
Date: Tue, 18 Jun 2013 09:25:28 -0700 (PDT)
Message-ID: <9e09d07e-d526-4550-8596-84f768d393ba_at_ys5g2000pbc.googlegroups.com>
On Jun 18, 12:37 am, "StrangeCat_at_Work" <stra..._at_cat.com> wrote:
> >Yes default for open cursors is typically too low and should be set to
> >something reasonable ( 750 / 900 fairly common ). Often no big issue
> >going somewhat higher than that.
> >If this is running in a script of some kind doing an "alter session
> >set cursor_sharing=FORCE" ( or an after logon database trigger to do
> >that for these scripts ) could be an acceptable workaround.
> >Creating an external table and doing "1 insert command" processing
> >against the external table is also worth looking at.
> >Something may be wrong with connection management
>
> Thanx for your answer, as already said to joel problem was not disposing
> OledbCommand objects that kept stacking up open cursors at server side...
>
> Thanx also for providing interesting info on forcing cursor sharing (Oracle
> newbie here :) ).
>
> Bye
> ASC
Yes, John's reply incorrectly assumes it isn't the program, but is otherwise correct. Others would strongly disagree with him about the number of cursors (being too high), but whenever I see that I have to say mine are 5000. That's because my app purposefully keeps cursors open, and yet still handles them efficiently and properly, so they are around on demand, making certain queries much faster than expected. So really, it depends on the app. For most cases where you keep upping cursors to 5000, that would indicate some dumb programming error (or some configurations of some access software as to default cursor handling), but if the app is done right, the limit is asymptotic, not ever increasing. How this actually works has changed a lot over Oracle versions, so it really isn't unreasonable to expect a lower cursors than John says, except when you have software that requires otherwise.
Date: Tue, 18 Jun 2013 09:25:28 -0700 (PDT)
Message-ID: <9e09d07e-d526-4550-8596-84f768d393ba_at_ys5g2000pbc.googlegroups.com>
On Jun 18, 12:37 am, "StrangeCat_at_Work" <stra..._at_cat.com> wrote:
> >Yes default for open cursors is typically too low and should be set to
> >something reasonable ( 750 / 900 fairly common ). Often no big issue
> >going somewhat higher than that.
> >If this is running in a script of some kind doing an "alter session
> >set cursor_sharing=FORCE" ( or an after logon database trigger to do
> >that for these scripts ) could be an acceptable workaround.
> >Creating an external table and doing "1 insert command" processing
> >against the external table is also worth looking at.
> >Something may be wrong with connection management
>
> Thanx for your answer, as already said to joel problem was not disposing
> OledbCommand objects that kept stacking up open cursors at server side...
>
> Thanx also for providing interesting info on forcing cursor sharing (Oracle
> newbie here :) ).
>
> Bye
> ASC
Yes, John's reply incorrectly assumes it isn't the program, but is otherwise correct. Others would strongly disagree with him about the number of cursors (being too high), but whenever I see that I have to say mine are 5000. That's because my app purposefully keeps cursors open, and yet still handles them efficiently and properly, so they are around on demand, making certain queries much faster than expected. So really, it depends on the app. For most cases where you keep upping cursors to 5000, that would indicate some dumb programming error (or some configurations of some access software as to default cursor handling), but if the app is done right, the limit is asymptotic, not ever increasing. How this actually works has changed a lot over Oracle versions, so it really isn't unreasonable to expect a lower cursors than John says, except when you have software that requires otherwise.
This is explained a little in the docs under open_cursors and session_cached_cursors, independent parameters that can make some difference. The newest versions even have a client cache to really confuse newbies.
jg
-- _at_home.com is bogus. http://www.informationweek.com/software/enterprise-applications/oracle-wins-case-against-third-party-sup/240156837Received on Tue Jun 18 2013 - 18:25:28 CEST