Re: Corrupt Statement In Shared Pool???

Steve-

It does not seem likely you have memory corruption in the library cache, or the overall shared pool either, for that matter. A memory corruption would typically result in a much more dramatic problem, like a process or instance failure. Especially considering the statement works from other client programs, right?

You need to find out what the "hung" session is waiting for. My guess, offhand, is some bug in Powerbuilder, since it is the odd one out. To diagnose this, get a processstate dump on the hung session:

For 7.3...

First find the operating system process id of the hanging session. Having that, do this:

svrmgrl
connect internal
oradebug setospid <o.s. process id> <- eg oradebug setospid 2341 oradebug unlimit
oradebug dump processstate 10

This will produce a trace file constructed of that process id, located in the udump directory. Zip it up and post it here. I will look at it.

Basically, you are going to find a section that contains state object information. What we will be looking for is an indication of what resource that session is waiting for. Hard to describe in detail here, so post the trace and we can go from there.

• ricky

SLSiebenaler wrote:
>
> Recently, we had a production issue in a 7.3.4.4 instance (uptime about 5
> months) where a specific SQL statement would "hang" from a PowerBuilder
> application. The SQL statement run from SQLPlus without problems. The same
> SQL statement from could be executed in TOAD or SQL Navigator. Digging
> further, it was determined that the PB application uses bind variables,
> which may contribute to the SQL statement hanging.
>
> The application team wanted the database bounced, but several hundred users
> were connected and about 20 active. I did flush the shared pool, following
> the "impacted" users disconnecting. Still, the PB application was hanging.
>
> Well, after decision makers approved the instance bounce, the application
> team is claiming success in solving the problem. For me, that just proves
> that the symptom may have been temporarily solved. I am certain it will
> return again.
>
> Has anyone experienced an issue like I describe?
>
> Is there any documentation regarding an Oracle bug?
>
> Can the shared_pool become corrupt? If so, how can we monitor it? Does
> PowerBuilder have any documentation regarding this type of a problem?
>
> I'm really looking to find the root cause of this problem, rather than solve
> by "bouncing" instance.
>
> Steve Siebenaler
> Cincinnati, Ohio
Received on Mon Feb 17 2003 - 19:19:40 CST