Re: Execute Count in AWR Report

On 7/18/2020 2:05 PM, Jonathan Lewis wrote:
> 12:00:00 - statement not in the library cache
> 12:04:51 - statement executes for the first time and gets into library
> cache
> 12:07:31 - statement finishes first execution and is no longer pinned
> in library cache
> 12:10:00 - statement is flushed from library cache
> 12:13:21 - statement executes for the second time and is reloaded into
> the library cache
> 12:14:52 - statement finishes first execution and is no longer pinned
> in library cache
> 13:00:00 - statement is still in the library cache and is captured in
> the snapshot
>
6 minutes before the statement is flushed from the shared pool looks very, very improbable, unless Ian is running PeopleSoft with 1GB of SGA. Even if we assume moderately small SGA around 16GB, it should take more than 6 minutes for a SQL belonging to an OLTP application to get thrown out from shared pool. The timeline above looks as if constructed by Hercules Poirot. PeopleSoft is an OLTP book keeping application which usually keeps a set of around 300 SQL statements in the shared pool. To tell the truth, I thought of that myself, which is why I mentioned V$SQLSTATS, but discarded that idea as extremely improbable, like Russell's teapot in an orbit around Sun.

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

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