Re: "'Resultcache" effect in 10.2.0.4 ??

From: jlchiappa <jlchiappa_at_gmail.com>
Date: Mon, 27 Jul 2009 09:53:36 -0700 (PDT)
Message-ID: <68e2e528-9627-4e13-bf85-372283ee2fc9_at_a37g2000prf.googlegroups.com>

Mark, I disagree : as I said, if the exact same SQL is run with the exact SAME values 2x (thus NEGATING the possibility of bind variable peeking, the value IS the same for both executions), the first execution is slow and the second is fast... Until now, the only hypothesis explaining the situation really is some kind of cache outside the db (in storage, in file system, whatever), when I get a 10046 trace I will to comprove it, if contrasting 2 executions with the same plans and the same I/Os the 2nd is faster the time-elapsed for the I/Os in the 2nd will be smaller, thus comproving the acting of some "force" outside the db (hardware caching, file system, any). Only to post, my plan of action will be : trace the SQL´s run for the 1st time, clearing all dbcaches , invalidate SQL, etc, after this, and then trace a 2nd execution...

 Regards,

   Chiappa

On 24 jul, 15:06, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jul 23, 5:21 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > 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
> > seehttp://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-driv...
> > (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...- Hide quoted text -
>
> > - Show quoted text -
>
> The problem does sound like it could be related to bind variable
> peeking so I think Joel has provided the best response so far.  I
> would expect the data to exhibit skew for this to be the issue.
>
> To avoid having Oracle change the plan you have several choices
> depending on version and access to code.  Hinting the SQL may be
> necessary but you should also consider if you can generate a working
> set of statistics and lock those statistics in place.
>
> HTH -- Mark D Powell --
Received on Mon Jul 27 2009 - 11:53:36 CDT