'Caching' of resultsets in 10.2.0.4 ??

[JChiappa]

Hi All. I´m seeing an 'uncommon' 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