'Caching' of resultsets in 10.2.0.4 ??
Hi All. Iīm seeing an 'uncommon' behavior in a coleague database : the original 220.127.116.11 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.
Click Here to Expand Forum to Full Width