Re: Sudden occurrence of SHARED_POOL LATCH waits - DB up since 4/3/2018

From: Chris Taylor <>
Date: Fri, 1 Jun 2018 12:00:02 -0500
Message-ID: <>

I think that's part of it but the table & partitions experiencing the partition exchange aren't in use at the time the partition exchange occurs.

So, we have a view that uses either TABLE1 or TABLE2. Prior to switching the view, we build the new partitions & data in the non-used table - once that table is built, the view is switched to use the newly modified data and then the apps use the View.

I confirmed that the table experiencing the DDL had no open sql against it which would seem to rule out invalidating a lot of SQL.

Definitely seems to have been an issue with one (or more) specific subpools.

Querying the subpools while the jobs were not running shows 7 pools - each with large amounts of free space.


On Fri, Jun 1, 2018 at 7:34 AM, Rich J <> wrote:

> On 2018/05/31 11:29, Chris Taylor wrote:
> Background:
> We've got a database with 4TB of RAM and a 260GB shared pool. Everything
> was running normally until 2 nights ago when a regularly scheduled job that
> does TONS of DDL suddenly blew up the shared pool.
> Trying to query x$ksmsp for shared pool sizes is also causing huge amounts
> of latch waits.
> See Tanel Poder's blog about querying x$ksmsp on a live instance --
> definitely not recommended.
> Also, could you be experiencing large amounts of invalidations with the
> DDL that's causing your contention? I had severe issues with a 11GB
> shared pool (AMM -- turned that noise off a long time ago) hanging the
> instance for 10m while stats collection invalidated 250K bindless SQLs in
> the pool.
> Just a thought...
> Rich

Received on Fri Jun 01 2018 - 19:00:02 CEST

Original text of this message