Re: Multiple inserts => multiple connections

Its been a long time since I did OCI. Are you exhausting the number of cursors? (not connections) My guess is: You do an array insert (good thing)
You do another array insert
again and again and again
So each one is probably opening a cursor and there is a limit per session (default is 50, but you can make it higher) You need to (conceptually)
prepare the statement, bind in the variables (please use bind variables in all SQL that is DML)
then execute.(commit if that is what you want to do) For the next "round" or set of arrays rebind and execute using the same cursor. You will get more scalability because you will avoid both a hard and soft parse. (see http://asktom.oracle.com for info about host variables etc. )

Jim

"Ole Hansen" <oha_at_city.dk> wrote in message news:3E0B33F9.4090106_at_city.dk...
> I am actually using the Array interface from OCI. Each second I prepare
> the statements using the array interface and then executes them by
> calling OCIStmtExecute as follows:
>
> if ( (status = OCIStmtExecute(
> sc->svchp,
> stmthp,
> errhp,
> (ub4) maxiters,
> (ub4) 0,
> (CONST OCISnapshot *) NULL,
> (OCISnapshot *) NULL,
> OCI_COMMIT_ON_SUCCESS)) != OCI_SUCCESS) {
> ... some error handling...
> }
>
> So, yes - I commit after every OCIStmtExecute but not for every insert
> sinse stmthp is prepared with the array interface and holds 150-200
> statements (rows). I call OCIStmtExecute one time/sec.
>
> The inserts are used to log traffic in realtime so I want to log
> (commit) although some of the inserts could be with errors, ie. I dont
> want to miss all prepared statements just because one has errors in it
> for some reason.
>
> If one row is errornous I still want to insert all the others therefore
> transactions is not an option. I dont want to roll back etc.
>
> It seems like the server gets exhausted by the multiple inserts.
>
> Should I use OCIStmtExecute different then?
>
> And no, this is not ported from SQLServer, I did the code in OCI.
>
> Thanks!
> Ole
>
>
> Jim Kennedy wrote:
> > You have a defect in the code doing the inserts. There is no good
 reason to
> > multiple connections from a client and certainly not to do all those
 inserts
> > on them. (1 connection/insert ugh) A better strategy to do inserts
 faster
> > would be to use the array interface and send over multiple rows to be
> > inserted at a time.
> >
> > My guess is that this is some application ported from SQLServer and it
 opens
> > connections al over the place and probably commits after each one.
> > Jim
> >
> > "Ole Hansen" <oha_at_city.dk> wrote in message
 news:3E0B299D.9040406_at_city.dk...
> >
> >>Hi,
> >>
> >>I have a problem with too many connections/processes. I'm having an
> >>application connecting to the server but for some reason the number of
> >>processes or connections increases to exceed the limit.
> >>
> >>On startup the number of connections is 2-3 but when doing multiple
> >>inserts (150-200 pr. sec.) the number of connections increases until the
> >>server crashes.
> >>
> >>Is it possible to remove or control unused connections from the
> >
> > application?
> >
> >>Br,
> >>Ole
> >>
> >
> >
> >
>
>
Received on Fri Dec 27 2002 - 03:51:34 CET