Re: "'Resultcache" effect in 10.2.0.4 ??

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...@xxxxxxx> wrote:
On Jul 23, 5:21 pm, joel garry <joel-ga...@xxxxxxxx> wrote:



On Jul 23, 12:25 pm, jlchiappa <jlchia...@xxxxxxxxx> 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
--
@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 --

.

Relevant Pages

  • Re: SQL Server Query Analyzer faster than ADO.Net SQL Data Provider
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... > when compiling a plan. ... > execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.clients)
  • Re: Query Statment is Fast But Stored Procedure is Slow
    ... poor plan the first time it is run. ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)
  • Re: Parameter Sniffing - Need more info
    ... In 6.5, at compile time SQL ... > when compiling a plan. ... this feature allows more efficient stored proc ... > execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
Loading