-
'Caching' of resultsets in 10.2.0.4 ??
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|