Re: "'Resultcache" effect in 10.2.0.4 ??

From: joel garry <joel-garry_at_home.com>
Date: Thu, 23 Jul 2009 14:21:28 -0700 (PDT)
Message-ID: <778a83f6-38e1-473e-a21c-7bb02efffb16_at_13g2000prl.googlegroups.com>



On Jul 23, 12:25 pm, jlchiappa <jlchia..._at_gmail.com> wrote:
> Iīm seeing this behavior in a coleague database : the original 9.2.0.7
> EE db was upgraded to 10.2.0.4 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 :
>
> a) even if an ALTER SESSION FLUSH BUFFER_CACHE and FLUSH SHARED POOL
> 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 http://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-drives-me-nuts/ (and google for other things on the subject by Lewis, Kyte, etc.)

jg

--
_at_home.com 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