FW: Cache Table

From: <mleith_at_bradmark.co.uk>
Date: Thu, 6 Jul 2000 15:56:21 +0100
Message-Id: <10550.111344@fatcity.com>

Whoops, I actually wrote that down whilst reading Advanced Oracle Tuning an= d=20=
Admin, Chapter 9, DB_BLOCK_BUFFERS right at the end of the paragraph=2E I=20= actually meant to take it out of the mail after I wrote it as I felt it=20= sounded wrong=2E It actually says 1 - 2 percent of the size of the _Physical_=20= database=2E

I meant to take it out as it contradicts trial and error in my next=20= paragraph=2E=2E=20=

Novices for ya huh, sorry about that=2E=2E

Any insight on the cache question??

Cheers

Mark

-----Original Message-----

From:	MIME :charliem_at_mwh=2Ecom=20=
Sent:	Thursday, July 06, 2000 3:42 PM
To:	Mark Leith
Subject:	Re: Cache Table

I am NOT trying to start a flame war, so please don't take offense at any=20= comment or question=2E

mleith_at_bradmark=2Eco=2Euk wrote:
> > Good question, I'm not fully aware if the cached table (allocated=20=
extents) gets fully pulled in to the db block buffer=2E I would think not, due=20= to it being a huge waste of space pulling in empty blocks=2E But I can't find=20= anything documented=2E
> > What exactly do you want to cache this table for, and what kind of=20=

activity is going on, other than with this table=2E One thing that you do have=20=
to think about, is that if you are pulling this amount of data in to the db=20=
block cache, what about the rest of the data that needs to be brought in to=20=

it=2E If that makes sense=2E Maybe this explains your low hit ratio=2E How large=20= is=20=
the physical database, in general the DB_BLOCK_BUFFER should be set to around=20= 2 percent of the database=2E

How/where did you arrive at this 2% number?

> > If I were you, try increasing the size of the DB_BLOCK_BUFFER to around=20=
12mb, and don't forget to increase the size of the SHARED_POOL accordingly=2E=20= Monitor the hit ratio constantly through all of this, but don't start=20= straight after changing these parameters=2E Allow the database to run through=20= its normal activity for around a half a day, and check it out then, if your=20= hit ratio is still low, adjust accordingly=2E Its pretty much a matter of trial=20= and error=2E

On my primary PROD server with 6GB RAM, as you can see my DB_BLOCK_BUFFER is=20= slightly ;-) larger than 12MB=2E

Oracle Server Manager Release 2=2E3=2E4=2E0=2E0 - Production

Oracle7 Server Release 7=2E3=2E4=2E3=2E0 - Production With the distributed and parallel query options PL/SQL Release 2=2E3=2E4=2E3=2E0 - Production

SVRMGR> connect internal Connected=2E
SVRMGR> show sga
Total System Global Area 1762049992 bytes

Fixed Size                       39816 bytes
Variable Size                788931648 bytes
Database Buffers             939524096 bytes
Redo Buffers                  33554432 bytes

Received on Thu Jul 06 2000 - 09:56:21 CDT