Re: procedure call with multiple children

Are you by chance running 8.1.7. We encountered the same problem with our Oracle Apps database where we crashed one day. The number of children hit 32k and overflowed an internal Oracle table.  

As a result we periodically now clear the shared_pool.
>>> Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> 4/27/05 12:35:46 PM
>>>

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 



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