Re: "'Resultcache" effect in ??

From: joel garry <>
Date: Thu, 23 Jul 2009 14:21:28 -0700 (PDT)
Message-ID: <>

On Jul 23, 12:25 pm, jlchiappa <> wrote:
> Iīm seeing this behavior in a coleague database : the original
> EE db was upgraded to EE in the same Solaris 9 box (big one,
> with 32 processors and 64 Mb of RAM), and since then, for big and
> complexes queries (ie, with subqueries, and/or views from views,
> inline views, WITH clause, the hairy ones) the first execution is
> 'slow', say, 15 minutes or alike, and the second execution is 5 times
> or so faster (say 3 minutes or alike). The details :
> the behavior is the same, so the dbīs buffer caching does not explain
> it
> b) same if the main SQL text is altered (causing a hard parse), so the
> SQL caching does not explain it
> c) strangely, even if the SQL is something like :
> SELECT columns FROM tables WHERE columns IN (complex subquery) AND
> column = 'X'
> if the columnsī clause is altered, or if the WHERE clause is altered,
> or even if the subquery is altered in a small way (example, subquery
> list of columns is altered togheter with , the behavior occurs, only
> if the subquery is altered allowing the return of a diferent resultset
> the 2nd execution is slower again
> d) if the database is shutdown, the next execution becomes 'slow'
> again
> e) the database is not mine, so I could not to get a 10046 trace, and
> canīt post a full SQL case, but consulting the V$SQLnn views we see
> the execution plan as the same in both executions , Iīm trying yet to
> get the trace 10046 from a small but reproductible example
> f) the datafiles are (and was in 9i epoch) in the same IBM Shark
> storage, using RAID-5 with cooked filesystems (no ASM), and with LMT
> tablespaces
> in my opinion :
> 1. the cooked FSs introduces the possibility of OS-caching - it donīt
> showed in 9i, maybe due to the setting of filesystem_options in the
> ocasion
> 2. the fact of the effect disapears after db shutdown donīt denies the
> OS-caching, imho
> 3. only if and when I can get a 10046 trace I will see if OS-caching
> in action, my plan will be :
> - trace 10046 in 2 different session execute the same SQL but with
> small alterations in text forcing a parse each time, and clearing db
> buffer cache each time
> - the plans WILL be the same for both execs, and the I/Os too :
> comparing the times for the I/Os (its registered in the 10046) , if
> the second-execution I/Os times are faster itīs proved the OS-caching
> option
> 4. donīt know if c) denies the OS-caching possibility - anyway, if the
> subquery resultset change but the blocks required are cached in the
> OS, it must not occurs, I think
> Any opinions/comments will be welcome.
> Regards,
> Chiappa

Also check for histograms (the default gathering changed for 10g), and see (and google for other things on the subject by Lewis, Kyte, etc.)


-- is bogus.
The above link shows on my firefox tab as Bind Variable Pee...
Received on Thu Jul 23 2009 - 16:21:28 CDT

Original text of this message