"'Resultcache" effect in 10.2.0.4 ??

From: jlchiappa <jlchiappa_at_gmail.com>
Date: Thu, 23 Jul 2009 12:25:17 -0700 (PDT)
Message-ID: <36662af9-38e9-428a-8368-14d9114874d0_at_q11g2000yqi.googlegroups.com>

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 :

1. 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
2. same if the main SQL text is altered (causing a hard parse), so the SQL caching does not explain it
3. 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 Received on Thu Jul 23 2009 - 14:25:17 CDT