Help with ORA-01000 Error

[StrangeCat@Work]

Hi everyone,

we have to convert a fairly big application suite to use Oracle instead of
SQL Server but we do not have much experience on the latter.

After testing our app for some time we optimized all queries to use Oracle
and now everything runs as expected. After all, apart being very big, its
just a lot of INSERTS, UPDATES and DELETES, nothing complicated...

We encountered problems with a small utility we use to build the schema and
to load some "semi-static" data in tables.

Creating the schema (tables, constraints, indexes, sequences and some very
simple triggers) forced us to raise the maximum open cursors to 1500 on our
test box, this solved the problem. I read that the default for max open
cursors is quite low so I thought that on the client production server I
wouldnt have had this problem, and i was right.

The problem arised trying to insert all the data via a script that would
launch around 1500 INSERTS, this wouldnt run and return the ORA-01000
Error...

I read some docs on how to optimize this but found no logical solution...

If I dont have access to Oracle instance parameters the only way looks to be
splitting that long script in smaller ones and open and close the connection
each time... Ugly but should work...

We use dotnet 2.0 with OleDB (cannot move to Oracle Manged providers right
now...).

Any idea/comment highly appreciated.

TIA
ASC

[joel garry]

It's likely either you are using something that needs to be updated,
or are not closing cursors in a manner that your environment considers
rational. See
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/883bc30b-9ea9-40c9-90fb-85ce408eecc9/

jg
--
@home.com is bogus.
http://www.forbes.com/sites/oracle/2013/06/17/complexity-barrier-makes-it-matter-more-than-ever/

[John Hurley]

On Jun 17, 11:29 am, "StrangeCat@Work" <stra...@cat.com> wrote:
> Hi everyone,
>
> we have to convert a fairly big application suite to use Oracle instead of
> SQL Server but we do not have much experience on the latter.
>
> After testing our app for some time we optimized all queries to use Oracle
> and now everything runs as expected. After all, apart being very big, its
> just a lot of INSERTS, UPDATES and DELETES, nothing complicated...
>
> We encountered problems with a small utility we use to build the schema and
> to load some "semi-static" data in tables.
>
> Creating the schema (tables, constraints, indexes, sequences and some very
> simple triggers) forced us to raise the maximum open cursors to 1500 on our
> test box, this solved the problem. I read that the default for max open
> cursors is quite low so I thought that on the client production server I
> wouldnt have had this problem, and i was right.
>
> The problem arised trying to insert all the data via a script that would
> launch around 1500 INSERTS, this wouldnt run and return the ORA-01000
> Error...

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

[StrangeCat@Work]

>It's likely either you are using something that needs to be updated,
>or are not closing cursors in a manner that your environment considers
>rational. See
>http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/883bc30b-9ea9-40c9-90fb-85ce408eecc9/

Thanx a lot for your reply.

That link pointed me to the problem.

I had a loop which kept creating OleDBCommands and running'em but never a
Dispose()!

I believe the "cleanup/closing" of server socket is done there, but you have
to call it :)

Problem solved!

Again THX

Bye
ASC

[StrangeCat@Work]

>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

[joel garry]

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
--
@home.com is bogus.

http://www.informationweek.com/software/enterprise-applications/oracle-wins-case-against-third-party-sup/240156837

[Mladen Gogala]

On Tue, 18 Jun 2013 09:25:28 -0700, joel garry wrote:

> 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.

Basically, with the advent of large memories, when almost everybody has at
least 4GB on the PC, cursors are cheap and closing them obsessively is no
longer required. In addition to that there are pieces of software, which
turn me into a cursor, and are used to write large number of application.
I am talking about Groovy on Grails, Spring MVC, Hibernate and Struts
which all tend to open cursors in profusion. They also generate SQL
statements that turn me into [*EXPLETIVE DELETED*] cursor. The old wisdom
that if an application uses more than 500 open cursors, there is something
wrong with it, no longer applies.
The new wisdom says that applications suck, are regularly written by cheap
incompetent "engineers" who have no idea about the software engineering
and the sound relational design, but we have to support the applications,
no matter what. Software engineer is no longer responsible for the
performance of its creations, now there is a DBA who will work his or her
magic. DBA is responsible for the application performance but has no say
in the application design.


But thought's the slave of life, and life time's fool;
And time, that takes survey of all the world,
Must have a stop.

(Henry IV)



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com