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

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

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