procedure call with multiple children

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Wed, 27 Apr 2005 12:35:46 -0400
Message-ID: <52a152eb050427093526cae1cf@mail.gmail.com>

Hello All,

We have a rather bizare situation.

We have a call to a procedure which has multiple children.

The call looks like this:

call package.procedure(:1,:2)

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

We run this concurently in 12 sessions, and during the time this runs we would have 400+ of the "call package.procdure" in the shared pool.

We fail to understand why this call would not be shared. We examined v$sql_shared_cursor and the only columns marked with Y are:

OPTIMIZER_MISMATCH
STATS_ROW_MISMATCH The first one i can understand, even thow it's a mystery too , we're not changing anything.

The second one however is just not right. How can a call to a procedure be invalidated by STATS ?

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

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

FYI:=20
cursor_sharing is FORCE
9i database

--=20

Christo Kutrovsky
Database/System Administrator
The Pythian Group
--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 27 2005 - 12:40:08 CDT