Re: "'Resultcache" effect in 10.2.0.4 ??
Date: Mon, 27 Jul 2009 15:20:50 -0700 (PDT)
Message-ID: <0df4cfe8-6267-4f2c-a95a-93849fe8824a_at_v15g2000prn.googlegroups.com>
On Jul 27, 2:17 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "jlchiappa" <jlchia..._at_gmail.com> wrote in message
>
> news:68e2e528-9627-4e13-bf85-372283ee2fc9_at_a37g2000prf.googlegroups.com...
> 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
>
> Isn't this normal Oracle behaviour? The blocks are retrieved from disk and
> cached in the SGA on the first execution and retrieved from memory on the
> second execution?
>
> Matthias
He did mention in the OP that the number of I/O's (PIO's, I assume...) and the plans are the same, as well as ALTER SESSION FLUSH BUFFER_CACHE and FLUSH SHARED POOL don't make it happen.
Perhaps it is possible that two separate things are happening with the same apparent effect: When he shuts the db, it's slow because it has to load the SGA, when he does it later, a SAN cache or something helps things along. I would think that there would be some way to flood the OS cache he is talking about to show it is responsible. It's just so odd that the 9i didn't show the effect, and 10g does (if I'm rereading it correctly). That's why I suggested bind peeking or something to do with histograms. Hopefully the 10046 will show more exactly where the slow is, over all the combinations of situations. I've been trying to figure out if MBRC could somehow have this effect (different between versions), but I don't think so - but see http://www.freelists.org/post/oracle-l/db-file-multiblock-read-count-and-10g,11 (and read the whole thread). Then of course, it could be some solaris specific thing, I haven't been on solaris in a long time.
Is there any possibility we might find out the storage hardware architecture?
jg
-- _at_home.com is bogus. http://www.amazon.com/tag/kindle/forum/ref=cm_cd_ef_tft_tp?_encoding=UTF8&cdForum=Fx1D7SY3BVSESG&cdThread=Tx1FXQPSF67X1IU&displayType=tagsDetailReceived on Mon Jul 27 2009 - 17:20:50 CDT