Re: 2GB limit of memory for Oracle on WIndows 32 bit

On Jul 4, 7:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 3, 1:37 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > On Jul 2, 5:49 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> <SNIP>
> > > The first of the above quotes seems to be in agreement with Tom Kyte's
> > > "Expert Oracle One on One" book and the Oracle 8 and Oracle 10g R2
> > > Concepts manual. The second of the above quotes (from another book by
> > > Tom Kyte) seems to be in agreement with the article written by Svend
> > > Jensen on Jonathan Lewis's website.
>
> > > It makes one wonder... is there a correct answer?
>
> > Yes, and it is actually quite simple. There doesn't have to be
> > separate _mechanisms_ for the pools. The _behavior_ of the pools is
> > influenced by the type of access of the objects in the pools. The
> > idea is to segregate the access types - if the pool is going to be
> > thrashed, put objects in recycle that thrash. This has the effect of
> > lowering thrashing in the other pools. Separating out hot and wam
> > areas is more subtle, but still, if you have too many different kinds
> > of objects for just depending on the default LRU mechanism, it
> > supposedly could help. Reread the references carefully with that in
> > mind.
>
> > jg
>
> Curiosity is a huge time sink. I wondered if there are actually

You can say that again! I barely have enough time to read this, much less try it. I salute you! :-)

> different mechanisms for the buffering of the KEEP and RECYCLE buffer
> caches. The test below is rather long, but it seems to indicate, at
> least on Oracle 10.2.0.3 with an 8KB block size, that there is a
> difference in the caching mechanisms for the KEEP and RECYCLE buffer
> caches:
>

...
> Let's try performing full tablescans on all of the test tables:
>
> SELECT
> COUNT(*)
> FROM
> T1
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T2
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T3
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T4
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T1_R
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T2_R
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T3_R
> WHERE
> MY_NUMBER<10;
>
> SELECT
> COUNT(*)
> FROM
> T4_R
> WHERE
> MY_NUMBER<10;
>
> BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS
> ------- --------- ------------- -------------
> KEEP T1 384 2
> RECYCLE T1_IND1_R 256 3
> RECYCLE T1_R 384 372
> KEEP T2 384 176
> RECYCLE T2_IND1_R 256 3
> RECYCLE T2_R 384 372
> KEEP T3 384 373
> RECYCLE T3_IND1_R 256 3
> RECYCLE T3_R 384 548
> KEEP T4 1536 1447
> RECYCLE T4_IND1_R 1024 4
> RECYCLE T4_R 1536 690
>
> At least in this test case, there is a difference in the caching
> mechanisms for the KEEP and RECYCLE buffer caches.
>
> Please let me know if there is a mistake in this test case, other than
> CACHED_BLOCKS exceeds OBJECT_BLOCKS in some cases.

I'm not sure if I'm reading this correctly, but it looks like sometimes the recycle is half the size of keep (like T4) and other times it's the other way round (like T2,T3), and T1 is completely different. I'm a bit sceptical as to whether that shows a different mechanism, or just some other side effects, perhaps something cyclical like every nth time either pool does something backwards, and I would expect various interesting things to occur as the pools are filled and concurrency effects are hit as Oracle tracks updated buffers. I do appreciate the effort to come up with a simple reproducible test case, I don't know if this is close but not quite there. There may be enough variables involved to make this difficult to resolve as a simple test. Or maybe I just missed something obvious in your demo?

To really waste some time, see the
Tuning Oracle at the Block Level; Beginners, Go Away! download at http://www.tusc.com/oracle/download/author_niemiecr.html (Usual disclaimers about stuff found on the internet apply, and I don't mean to start any discussion about the author). In particular, the part about hash bucket chains may give a clue how to follow what is going on in those pools.

jg

--
@home.com is bogus.
"I came up to a stoplight next to a CHP, and my 4-year-old son stuck
his head out the window and said 'I smell bacon!'"
"Did he hear it?"
"Yeah, his windows were rolled down, he just smiled and waved.  I was
totally embarrassed." - heard on radio call-in about things kids said
or did that they learned from their parents.