procedure call with multiple children

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

Other related posts: