| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cache Table
Good question, I'm not fully aware if the cached table (allocated extents)=20=
gets fully pulled in to the db block buffer=2E I would think not, due to it=20=
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 activity=20= is going on, other than with this table=2E One thing that you do have to think=20= about, is that if you are pulling this amount of data in to the db block=20=
cache, what about the rest of the data that needs to be brought in to it=2E If=20= that makes sense=2E Maybe this explains your low hit ratio=2E How large is the=20= physical database, in general the DB_BLOCK_BUFFER should be set to around 2=20=percent of the database=2E
If I were you, try increasing the size of the DB_BLOCK_BUFFER to around 12mb,=20= and don't forget to increase the size of the SHARED_POOL accordingly=2E Monitor=20= the hit ratio constantly through all of this, but don't start straight after=20= changing these parameters=2E Allow the database to run through its normal=20= activity for around a half a day, and check it out then, if your hit ratio is=20= still low, adjust accordingly=2E Its pretty much a matter of trial and error=2E=20=
One thing, do you want to have this table cached permenantly,or is it just=20= every now and then? Is the table volatile, or is the data fairly static? A=20= few things to think about here=2E If the data is static, how about a fully=20=indexed table?=20=
Sorry I've rambled enough, andbody correct me if I'm wrong, I'm still a=20= novice!! Maybe a Guru can add a little extra insight=2E=2E
Cheers
Mark
-----Original Message-----
From: MIME :vivek_sharma_at_inf=2Ecom=20= Sent: Thursday, July 06, 2000 1:36 PM To: ORACLE-L_at_fatcity=2Ecom Subject: Cache Table=20=
Mark Is space Equal to ONLY the Data in the Table Allocated from the db_block_buffers & NOT the SUN of EXTENT Sizes of the Table ?
NOTE - The EXTENT Size can be far greater than Actual Data Size in the Table
> -----Original Message-----
> From: aheda_at_cdotd=2Eernet=2Ein [SMTP:aheda_at_cdotd=2Eernet=2Ein]
> Sent: Thursday, July 06, 2000 2:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: <No subject>
> > but even if the whole table cannot be cached but should my hitratio=20=
should
> be as low as 45% as 40 rows are selected in single block read=2E
> > > Arvind >
> > On Wed, 5 Jul 2000 uucp-relay-delhi!mleith_at_bradmark=2Eco=2Euk wrote:
> > > Your table is too large for your db block buffer=2E > > > > 100,000 rows,=20=
40 rows in a block - 2500 blocks * 4kb =3D 10,000 which is
> around 10mb=2E Your db block buffer is only 8mb, if you want to fully cache
> this table you need to increase your db block buffer=2E > > > > HTH
> > > > Mark
> > > > -----Original Message-----
> > From: MIME :aheda_at_cdotd=2Eernet=2Ein Sent: Wednesday, July 05,
> 2000 7:07 AM
> > To: Mark Leith
> > Cc: ORACLE-L_at_fatcity=2Ecom
> > Subject: RE: <No subject>
> > > > > > > > Arvind
> > For the fist question : I had tried the alter table xxxx cache
> > but even after doing this when I give a select query on the same table i
> > misses the cache and goes to the Disk
> > > > about second question I can give you the size of sga and shared poll
> > sga=3D24Mb
> > shared pool 15Mb
> > data bufers 8Mb
> > table size=3D1 Mb
> > block size 4kb
> > > > > > thanks
> > arvind On Tue, 4 Jul 2000 uucp-relay-delhi!mleith_at_bradmark=2Eco=2Euk wrote:
> > > > > alter table xxxxx cache;
> > > > To undo this statement - alter table xxxxx nocache;
> > > > Need a bit of environment info for your second question please=2E=2E
> > > > HTH
> > > > Mark
> > > > -----Original Message-----
> > > From: MIME :aheda_at_cdotd=2Eernet=2Ein Sent: Tuesday, July 04,
> 2000 3:30 PM
> > > To: ORACLE-L_at_fatcity=2Ecom
> > > Subject: <No subject>
> > > > > 1)=2Ehow can we cache the whole data of a table?
> > > > 2) why do we get a hit ratio of less than 50 % while we execute
> select *
> > > query on a single table contaiining 100000 rows and each db_block
> contains
> > > aroud 40 rows of table=2E
> > > > > > Arvind
> > > > > -- Author: INET: aheda_at_cdotd=2Eernet=2Ein
> > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity=2Ecom (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from)=2E You may
> > > also send the HELP command for other information (like subscribing)=2E
> > > > << File: ENVELOPE=2ETXT >> > << File: ENVELOPE=2ETXT >> > > > > > -- >=20=
Author: > INET: aheda_at_cdotd=2Eernet=2Ein
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity=2Ecom (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from)=2E You may
> also send the HELP command for other information (like subscribing)=2E
-- Author: VIVEK_SHARMA
INET: vivek_sharma_at_inf=2Ecom
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity=2Ecom (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from)=2E You may Received on Thu Jul 06 2000 - 08:26:10 CDT
 |
 |