Re: procedure call with multiple children

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Wed, 27 Apr 2005 15:09:59 -0400
Message-ID: <52a152eb050427120923f6faa4@mail.gmail.com>

Oops, forgot to put the list in copy when answering these, here there are:

Yes I can confirm that those are actual real bind variables. The code is JAVA. I have the java code right here:

private static final String PROCEDURE_CALL =3D "call package.procedure(?)";

private void java_function(long parameter1) { Connection conn =3D null;
CallableStatement call =3D null;
try {
  conn=3D DataBaseLink.getDBL().getPooledConnection();   call =3D conn.prepareCall(PROCEDURE_CALL);   call.setLong(1,parameter1);
  call.execute();

}
catch (SQLException e) {
  logger.error("....");
}
...

A grab from the connection pool and a prepare on each execute. Using bind variables however.

At this point we're inclining that this is a bug with Oracle not sharing this cursor. Still the question remains, how can a cursor of a "call" to a procedure be not-shared due to stats ... or optimizer settings that we did not change. Or so we think.

This is 9.2.0.6 in RAC environment, with only 1 node running.

On 4/27/05, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
> ----- Original Message -----
> From: "Christo Kutrovsky" <kutrovsky.oracle_at_gmail.com>
> To: "oracle-l" <oracle-l_at_freelists.org>
> Sent: Wednesday, April 27, 2005 5:35 PM
> Subject: procedure call with multiple children

>=20

> The call looks like this:

>=20

> call package.procedure(:1,:2)

>=20

> As you can see, it uses 2 parameters which are bound via bind variables.

>=20

> And we would have 400+ versions of this sql, but only 50-ish rows in
> v$sql_shared_cursor.

>=20

> And why would we have 400+ versions of this exact same procedure call.

>=20

> FYI:=3D20
> cursor_sharing is FORCE
> 9i database

>=20

> --=3D20
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group
> --
> http://www.freelists.org/webpage/oracle-l

>=20

> Given that you have cursor_sharing =3D force, can you
> confirm that the call is with REAL bind variables and
> not system-generated bind variables - viz: :"SYS_B_0"
> and :"SYS_B_1". Also, what language are you using
> to call the procedure ?

>=20

> Regards

>=20

> Jonathan Lewis

>=20

> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ

>=20

> http://www.jlcomp.demon.co.uk/seminar.html
> Public Appearances - schedule updated April 5th 2005
>=20
>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 27 2005 - 15:14:24 CDT