RE: Cache a table
In addition to Jared's latest comment, what Oracle version are you on?
Have you considered multiple buffer pools, which are often a much cleaner
solution?
-Mark
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Fri 1/2/2004 12:54 PM
To: Multiple recipients of list ORACLE-L
Cc:
Subject: RE: Cache a table
This thread started on 10/21/2003, with a *lot* of comments.
No time to read it all, so I'll just work with what appears here.
Have considered that this table is simply used a lot and remains
in the cache because it belongs there due to frequent access?
SQL with high LIO's?
Jared
Ravi Kulkarni <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/02/2004 07:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Cache a table
That was what we expected. This is a highly active
PeopleSoft Database. Will it take several months to
push those blocks out ? Though not true, it appears
the nocache had no effect at all ..
-Ravi.
--- "Bobak, Mark" <[EMAIL PROTECTED]> wrote:
> Well, that's not really a surprise, is it? If you
> do CACHE first, and
> cache all the tables blocks, then do NOCACHE, Oracle
> isn't going to
> immediately explicitly flush those blocks. I'd
> expect that as demand
> on the buffer cache increased, the blocks would age
> out. Oracle almost always
> follows the "delay any work I can till later, cause
> with any luck, I won't
> have to do it later, either!" rule.
>
> If you set the table to NOCACHE and then try doing
> other activity which will
> impose a load on the buffer cache, I'd expect to see
> (at least some of) those
> blocks age out.
>
> -Mark
>
>
> -Original Message-
> From: Ravi Kulkarni [mailto:[EMAIL PROTECTED]
> Sent: Wed 12/31/2003 6:34 PM
> To:Multiple recipients of list ORACLE-L
> Cc:
> Subject: Re: Cache a table
> Hi Richard,
>
> Did you test the effect of Nocache after caching ?
> What we noticed is "cache followed by nocache" is
> not
> making the blocks to be flushed out. This has been
> that way for months now in a production database of
> ours.
>
> Thx,
> Ravi.
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bobak, Mark
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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). You may
also send the HELP command for other information (like subscribing).
RE: Cache a table
This thread started on 10/21/2003, with a *lot* of comments. No time to read it all, so I'll just work with what appears here. Have considered that this table is simply used a lot and remains in the cache because it belongs there due to frequent access? SQL with high LIO's? Jared Ravi Kulkarni <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/02/2004 07:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Cache a table That was what we expected. This is a highly active PeopleSoft Database. Will it take several months to push those blocks out ? Though not true, it appears the nocache had no effect at all .. -Ravi. --- "Bobak, Mark" <[EMAIL PROTECTED]> wrote: > Well, that's not really a surprise, is it? If you > do CACHE first, and > cache all the tables blocks, then do NOCACHE, Oracle > isn't going to > immediately explicitly flush those blocks. I'd > expect that as demand > on the buffer cache increased, the blocks would age > out. Oracle almost always > follows the "delay any work I can till later, cause > with any luck, I won't > have to do it later, either!" rule. > > If you set the table to NOCACHE and then try doing > other activity which will > impose a load on the buffer cache, I'd expect to see > (at least some of) those > blocks age out. > > -Mark > > > -Original Message- > From: Ravi Kulkarni [mailto:[EMAIL PROTECTED] > Sent: Wed 12/31/2003 6:34 PM > To: Multiple recipients of list ORACLE-L > Cc: > Subject: Re: Cache a table > Hi Richard, > > Did you test the effect of Nocache after caching ? > What we noticed is "cache followed by nocache" is > not > making the blocks to be flushed out. This has been > that way for months now in a production database of > ours. > > Thx, > Ravi. >
RE: Cache a table
That was what we expected. This is a highly active
PeopleSoft Database. Will it take several months to
push those blocks out ? Though not true, it appears
the nocache had no effect at all ..
-Ravi.
--- "Bobak, Mark" <[EMAIL PROTECTED]> wrote:
> Well, that's not really a surprise, is it? If you
> do CACHE first, and
> cache all the tables blocks, then do NOCACHE, Oracle
> isn't going to
> immediately explicitly flush those blocks. I'd
> expect that as demand
> on the buffer cache increased, the blocks would age
> out. Oracle almost always
> follows the "delay any work I can till later, cause
> with any luck, I won't
> have to do it later, either!" rule.
>
> If you set the table to NOCACHE and then try doing
> other activity which will
> impose a load on the buffer cache, I'd expect to see
> (at least some of) those
> blocks age out.
>
> -Mark
>
>
> -Original Message-
> From: Ravi Kulkarni [mailto:[EMAIL PROTECTED]
> Sent: Wed 12/31/2003 6:34 PM
> To: Multiple recipients of list ORACLE-L
> Cc:
> Subject: Re: Cache a table
> Hi Richard,
>
> Did you test the effect of Nocache after caching ?
> What we noticed is "cache followed by nocache" is
> not
> making the blocks to be flushed out. This has been
> that way for months now in a production database of
> ours.
>
> Thx,
> Ravi.
>
>
>
> --- Richard Foote <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > It depends on how you define an LRU list I guess.
> > When I close my eyes and
> > picture the cache, I still see a LRU in there
> > somewhere. Please note I don't
> > often close my eyes in this manner ;)
> >
> > Also when you say that the CACHE option has no
> > effect, that's also a little
> > questionable. This is just a portion of a post I
> > recently sent to
> > comp.databases.oracle.server in the "Cache A
> Table"
> > thread:
> >
> > Simple demo on 9.2, the BOWIE table is
> approximately
> > 13,000 blocks, SMALL is
> > 117 blocks:
> >
> > SQL> alter table bowie nocache;
> >
> > Table altered.
> >
> > SQL> select object_name, object_id, data_object_id
> > from dba_objects where
> > object
> > _name in ('BOWIE', 'SMALL');
> >
> > OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
> > --- -- --
> > BOWIE31379 31379
> > SMALL31457 31457
> >
> > SQL> select * from bowie; (run with autotrace
> > traceonly)
> >
> > SQL> select count(*) from x$bh where obj=31379;
> >
> > COUNT(*)
> > --
> > 18
> >
> > Note that only the last few blocks from the FTS
> > actually remain in memory.
> > If I repeat the select, I still have the same
> result
> > from x$bh and the same
> > number of *physical reads" occur each time.
> >
> > If I run the same thing with my "small" table
> which
> > has about 117 blocks,
> > the same thing happens
> >
> > SQL> alter table small nocache;
> >
> > Table altered.
> >
> > SQL> select * from small;
> >
> > SQL> select count(*) from x$bh where obj=31457;
> >
> > COUNT(*)
> > --
> > 18
> >
> > Note that again only the last few blocks from the
> > FTS actually remain in
> > memory. If I repeat the select, I still have the
> > same result from x$bh and
> > again the same number of physical reads occur each
> > time.
> >
> > OK, lets change my small table and cache the thing
> > and see if I get a
> > different result ...
> >
> > SQL> alter table small cache;
> >
> > Table altered.
> >
> > SQL> select * from small;
> >
> > SQL> select count(*) from x$bh where obj=31457;
> >
> > COUNT(*)
> > --
> >117
> >
> > I now see that all 117 blocks (that's all data
> > blocks + segment header) are
> > all now cached as expected. Repeated reruns of the
> > select now generate *no*
> > physical I/Os.
> >
> > But what if I now run a select on my "big" BOWIE
> > table, what effect will
> > this have on the SMALL cached blocks ?
> >
> > SQL> select * from bowie;
> >
> > SQL> select count(*) from x$bh where obj=31379;
> >
> > COUNT(*)
> > --
> >
RE: Cache a table
Well, that's not really a surprise, is it? If you do CACHE first, and
cache all the tables blocks, then do NOCACHE, Oracle isn't going to
immediately explicitly flush those blocks. I'd expect that as demand
on the buffer cache increased, the blocks would age out. Oracle almost always
follows the "delay any work I can till later, cause with any luck, I won't
have to do it later, either!" rule.
If you set the table to NOCACHE and then try doing other activity which will
impose a load on the buffer cache, I'd expect to see (at least some of) those
blocks age out.
-Mark
-Original Message-
From: Ravi Kulkarni [mailto:[EMAIL PROTECTED]
Sent: Wed 12/31/2003 6:34 PM
To: Multiple recipients of list ORACLE-L
Cc:
Subject:Re: Cache a table
Hi Richard,
Did you test the effect of Nocache after caching ?
What we noticed is "cache followed by nocache" is not
making the blocks to be flushed out. This has been
that way for months now in a production database of
ours.
Thx,
Ravi.
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> Hi
>
> It depends on how you define an LRU list I guess.
> When I close my eyes and
> picture the cache, I still see a LRU in there
> somewhere. Please note I don't
> often close my eyes in this manner ;)
>
> Also when you say that the CACHE option has no
> effect, that's also a little
> questionable. This is just a portion of a post I
> recently sent to
> comp.databases.oracle.server in the "Cache A Table"
> thread:
>
> Simple demo on 9.2, the BOWIE table is approximately
> 13,000 blocks, SMALL is
> 117 blocks:
>
> SQL> alter table bowie nocache;
>
> Table altered.
>
> SQL> select object_name, object_id, data_object_id
> from dba_objects where
> object
> _name in ('BOWIE', 'SMALL');
>
> OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
> --- -- --
> BOWIE31379 31379
> SMALL31457 31457
>
> SQL> select * from bowie; (run with autotrace
> traceonly)
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 18
>
> Note that only the last few blocks from the FTS
> actually remain in memory.
> If I repeat the select, I still have the same result
> from x$bh and the same
> number of *physical reads" occur each time.
>
> If I run the same thing with my "small" table which
> has about 117 blocks,
> the same thing happens
>
> SQL> alter table small nocache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
> 18
>
> Note that again only the last few blocks from the
> FTS actually remain in
> memory. If I repeat the select, I still have the
> same result from x$bh and
> again the same number of physical reads occur each
> time.
>
> OK, lets change my small table and cache the thing
> and see if I get a
> different result ...
>
> SQL> alter table small cache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
>117
>
> I now see that all 117 blocks (that's all data
> blocks + segment header) are
> all now cached as expected. Repeated reruns of the
> select now generate *no*
> physical I/Os.
>
> But what if I now run a select on my "big" BOWIE
> table, what effect will
> this have on the SMALL cached blocks ?
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 18
>
> Nothing new here, only the last few blocks again
> remain from the BOWIE table
> with the same physical I/Os generated.
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
>117
>
> and thankfully nothing has changed with the SMALL
> table as a result. These
> blocks still remain cached and have not been
> "dislodged" as a result of the
> FTS on the big BOWIE table (as they sit safely
> somewhere near the middle,
> cold side of the LRU)
>
> Finally, what if we play silly buggers and decide to
> cache the big BOWIE
> table ...
>
> SQL> alter table bowie cache;
>
> Table altered.
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 1338
>
> We now see that a whole heap of buffers have now
> been cached, approximately
> 10%. However, aga
Re: Cache a table
Hi Richard,
Did you test the effect of Nocache after caching ?
What we noticed is "cache followed by nocache" is not
making the blocks to be flushed out. This has been
that way for months now in a production database of
ours.
Thx,
Ravi.
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> Hi
>
> It depends on how you define an LRU list I guess.
> When I close my eyes and
> picture the cache, I still see a LRU in there
> somewhere. Please note I don't
> often close my eyes in this manner ;)
>
> Also when you say that the CACHE option has no
> effect, that's also a little
> questionable. This is just a portion of a post I
> recently sent to
> comp.databases.oracle.server in the "Cache A Table"
> thread:
>
> Simple demo on 9.2, the BOWIE table is approximately
> 13,000 blocks, SMALL is
> 117 blocks:
>
> SQL> alter table bowie nocache;
>
> Table altered.
>
> SQL> select object_name, object_id, data_object_id
> from dba_objects where
> object
> _name in ('BOWIE', 'SMALL');
>
> OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
> --- -- --
> BOWIE31379 31379
> SMALL31457 31457
>
> SQL> select * from bowie; (run with autotrace
> traceonly)
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 18
>
> Note that only the last few blocks from the FTS
> actually remain in memory.
> If I repeat the select, I still have the same result
> from x$bh and the same
> number of *physical reads" occur each time.
>
> If I run the same thing with my "small" table which
> has about 117 blocks,
> the same thing happens
>
> SQL> alter table small nocache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
> 18
>
> Note that again only the last few blocks from the
> FTS actually remain in
> memory. If I repeat the select, I still have the
> same result from x$bh and
> again the same number of physical reads occur each
> time.
>
> OK, lets change my small table and cache the thing
> and see if I get a
> different result ...
>
> SQL> alter table small cache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
>117
>
> I now see that all 117 blocks (that's all data
> blocks + segment header) are
> all now cached as expected. Repeated reruns of the
> select now generate *no*
> physical I/Os.
>
> But what if I now run a select on my "big" BOWIE
> table, what effect will
> this have on the SMALL cached blocks ?
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 18
>
> Nothing new here, only the last few blocks again
> remain from the BOWIE table
> with the same physical I/Os generated.
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
>117
>
> and thankfully nothing has changed with the SMALL
> table as a result. These
> blocks still remain cached and have not been
> "dislodged" as a result of the
> FTS on the big BOWIE table (as they sit safely
> somewhere near the middle,
> cold side of the LRU)
>
> Finally, what if we play silly buggers and decide to
> cache the big BOWIE
> table ...
>
> SQL> alter table bowie cache;
>
> Table altered.
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> --
> 1338
>
> We now see that a whole heap of buffers have now
> been cached, approximately
> 10%. However, again the physical I/Os remain
> constant because we are still
> not effectively caching the table (the undocumented
> parameters behind the
> scene kick in to prevent the whole cache from
> flooding).
>
> But the effect on poor SMALL...
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> --
> 1
>
> only one poor block (the header) has survived the
> experience :(
>
> Hope this clears something up !!
>
> Cheers
>
> Richard Foote
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Tuesday, October 21, 2003 10:59 PM
>
>
> > Mike:
> >
> > I guess we are aware there is no concept of LRU or
> MRU in current
> > versions of Oracle and I don't think CACHE option
> will influence the
> > behavior. With the new algorithm the MFU blocks
> are already in the hot
> > end (unless they are read using CR read in that
> case they will be in
> > cold end since we set the _db_aging_freeze_cr to
> TRUE) and we don't
> > need to cache the blocks explicitely.
> >
> > You can monitor the behavior of this using the
> X$BH (espicially the
> > last two columns TCH and TIM).
> >
> >
> >
> >
> > =
> > Have a nice day !!
> >
>
> > Best Regards,
> > K Gopalakrishnan,
> > Bangalore, INDIA.
> > --
> > Please see the official ORACLE
Re: Cache a table
Craig Shalahamer still refers to the cache as LRU/MRU. What has changed
are the algorithms that determine the lifespan and placement of a buffer
in the cache.
www.orapub.com
Jared
Richard Foote <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/23/2003 08:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Cache a table
Hi
It depends on how you define an LRU list I guess. When I close my eyes and
picture the cache, I still see a LRU in there somewhere. Please note I don't
often close my eyes in this manner ;)
Also when you say that the CACHE option has no effect, that's also a little
questionable. This is just a portion of a post I recently sent to
comp.databases.oracle.server in the "Cache A Table" thread:
Simple demo on 9.2, the BOWIE table is approximately 13,000 blocks, SMALL is
117 blocks:
SQL> alter table bowie nocache;
Table altered.
SQL> select object_name, object_id, data_object_id from dba_objects where
object
_name in ('BOWIE', 'SMALL');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--- -- --
BOWIE 31379 31379
SMALL 31457 31457
SQL> select * from bowie; (run with autotrace traceonly)
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Note that only the last few blocks from the FTS actually remain in memory.
If I repeat the select, I still have the same result from x$bh and the same
number of *physical reads" occur each time.
If I run the same thing with my "small" table which has about 117 blocks,
the same thing happens
SQL> alter table small nocache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
18
Note that again only the last few blocks from the FTS actually remain in
memory. If I repeat the select, I still have the same result from x$bh and
again the same number of physical reads occur each time.
OK, lets change my small table and cache the thing and see if I get a
different result ...
SQL> alter table small cache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
I now see that all 117 blocks (that's all data blocks + segment header) are
all now cached as expected. Repeated reruns of the select now generate *no*
physical I/Os.
But what if I now run a select on my "big" BOWIE table, what effect will
this have on the SMALL cached blocks ?
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Nothing new here, only the last few blocks again remain from the BOWIE table
with the same physical I/Os generated.
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
and thankfully nothing has changed with the SMALL table as a result. These
blocks still remain cached and have not been "dislodged" as a result of the
FTS on the big BOWIE table (as they sit safely somewhere near the middle,
cold side of the LRU)
Finally, what if we play silly buggers and decide to cache the big BOWIE
table ...
SQL> alter table bowie cache;
Table altered.
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
1338
We now see that a whole heap of buffers have now been cached, approximately
10%. However, again the physical I/Os remain constant because we are still
not effectively caching the table (the undocumented parameters behind the
scene kick in to prevent the whole cache from flooding).
But the effect on poor SMALL...
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
1
only one poor block (the header) has survived the experience :(
Hope this clears something up !!
Cheers
Richard Foote
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 10:59 PM
> Mike:
>
> I guess we are aware there is no concept of LRU or MRU in current
> versions of Oracle and I don't think CACHE option will influence the
> behavior. With the new algorithm the MFU blocks are already in the hot
> end (unless they are read using CR read in that case they will be in
> cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
> need to cache the blocks explicitely.
>
> You can monitor the behavior of this using the X$BH (espicially the
> last two columns TCH and TIM).
>
>
>
>
> =
> Have a nice day !!
>
> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.
> --
> Please see the official O
RE: Cache a table
Thanks for posting this. I agree LRU and MRU are just simple names for too
many methods to achieve the required functionality.
Waleed
-Original Message-
Sent: Thursday, October 23, 2003 11:34 AM
To: Multiple recipients of list ORACLE-L
Hi
It depends on how you define an LRU list I guess. When I close my eyes and
picture the cache, I still see a LRU in there somewhere. Please note I don't
often close my eyes in this manner ;)
Also when you say that the CACHE option has no effect, that's also a little
questionable. This is just a portion of a post I recently sent to
comp.databases.oracle.server in the "Cache A Table" thread:
Simple demo on 9.2, the BOWIE table is approximately 13,000 blocks, SMALL is
117 blocks:
SQL> alter table bowie nocache;
Table altered.
SQL> select object_name, object_id, data_object_id from dba_objects where
object
_name in ('BOWIE', 'SMALL');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--- -- --
BOWIE31379 31379
SMALL31457 31457
SQL> select * from bowie; (run with autotrace traceonly)
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Note that only the last few blocks from the FTS actually remain in memory.
If I repeat the select, I still have the same result from x$bh and the same
number of *physical reads" occur each time.
If I run the same thing with my "small" table which has about 117 blocks,
the same thing happens
SQL> alter table small nocache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
18
Note that again only the last few blocks from the FTS actually remain in
memory. If I repeat the select, I still have the same result from x$bh and
again the same number of physical reads occur each time.
OK, lets change my small table and cache the thing and see if I get a
different result ...
SQL> alter table small cache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
I now see that all 117 blocks (that's all data blocks + segment header) are
all now cached as expected. Repeated reruns of the select now generate *no*
physical I/Os.
But what if I now run a select on my "big" BOWIE table, what effect will
this have on the SMALL cached blocks ?
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Nothing new here, only the last few blocks again remain from the BOWIE table
with the same physical I/Os generated.
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
and thankfully nothing has changed with the SMALL table as a result. These
blocks still remain cached and have not been "dislodged" as a result of the
FTS on the big BOWIE table (as they sit safely somewhere near the middle,
cold side of the LRU)
Finally, what if we play silly buggers and decide to cache the big BOWIE
table ...
SQL> alter table bowie cache;
Table altered.
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
1338
We now see that a whole heap of buffers have now been cached, approximately
10%. However, again the physical I/Os remain constant because we are still
not effectively caching the table (the undocumented parameters behind the
scene kick in to prevent the whole cache from flooding).
But the effect on poor SMALL...
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
1
only one poor block (the header) has survived the experience :(
Hope this clears something up !!
Cheers
Richard Foote
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 10:59 PM
> Mike:
>
> I guess we are aware there is no concept of LRU or MRU in current
> versions of Oracle and I don't think CACHE option will influence the
> behavior. With the new algorithm the MFU blocks are already in the hot
> end (unless they are read using CR read in that case they will be in
> cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
> need to cache the blocks explicitely.
>
> You can monitor the behavior of this using the X$BH (espicially the
> last two columns TCH and TIM).
>
>
>
>
> =
> Have a nice day !!
>
> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: K Gopalakrishnan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
Re: Cache a table
Hi
It depends on how you define an LRU list I guess. When I close my eyes and
picture the cache, I still see a LRU in there somewhere. Please note I don't
often close my eyes in this manner ;)
Also when you say that the CACHE option has no effect, that's also a little
questionable. This is just a portion of a post I recently sent to
comp.databases.oracle.server in the "Cache A Table" thread:
Simple demo on 9.2, the BOWIE table is approximately 13,000 blocks, SMALL is
117 blocks:
SQL> alter table bowie nocache;
Table altered.
SQL> select object_name, object_id, data_object_id from dba_objects where
object
_name in ('BOWIE', 'SMALL');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--- -- --
BOWIE31379 31379
SMALL31457 31457
SQL> select * from bowie; (run with autotrace traceonly)
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Note that only the last few blocks from the FTS actually remain in memory.
If I repeat the select, I still have the same result from x$bh and the same
number of *physical reads" occur each time.
If I run the same thing with my "small" table which has about 117 blocks,
the same thing happens
SQL> alter table small nocache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
18
Note that again only the last few blocks from the FTS actually remain in
memory. If I repeat the select, I still have the same result from x$bh and
again the same number of physical reads occur each time.
OK, lets change my small table and cache the thing and see if I get a
different result ...
SQL> alter table small cache;
Table altered.
SQL> select * from small;
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
I now see that all 117 blocks (that's all data blocks + segment header) are
all now cached as expected. Repeated reruns of the select now generate *no*
physical I/Os.
But what if I now run a select on my "big" BOWIE table, what effect will
this have on the SMALL cached blocks ?
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
18
Nothing new here, only the last few blocks again remain from the BOWIE table
with the same physical I/Os generated.
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
117
and thankfully nothing has changed with the SMALL table as a result. These
blocks still remain cached and have not been "dislodged" as a result of the
FTS on the big BOWIE table (as they sit safely somewhere near the middle,
cold side of the LRU)
Finally, what if we play silly buggers and decide to cache the big BOWIE
table ...
SQL> alter table bowie cache;
Table altered.
SQL> select * from bowie;
SQL> select count(*) from x$bh where obj=31379;
COUNT(*)
--
1338
We now see that a whole heap of buffers have now been cached, approximately
10%. However, again the physical I/Os remain constant because we are still
not effectively caching the table (the undocumented parameters behind the
scene kick in to prevent the whole cache from flooding).
But the effect on poor SMALL...
SQL> select count(*) from x$bh where obj=31457;
COUNT(*)
--
1
only one poor block (the header) has survived the experience :(
Hope this clears something up !!
Cheers
Richard Foote
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 10:59 PM
> Mike:
>
> I guess we are aware there is no concept of LRU or MRU in current
> versions of Oracle and I don't think CACHE option will influence the
> behavior. With the new algorithm the MFU blocks are already in the hot
> end (unless they are read using CR read in that case they will be in
> cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
> need to cache the blocks explicitely.
>
> You can monitor the behavior of this using the X$BH (espicially the
> last two columns TCH and TIM).
>
>
>
>
> =
> Have a nice day !!
>
> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: K Gopalakrishnan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ:
Re: Cache a table
I have two favorite email signatures. One is used by Gary Dodge who is commencing his 68th quarter at Oracle Corporation. It reads: "Building tomorrow's legacy systems today, one crisis at a time..." And the other is used by a gentleman named Gene Fosnight, formerly of Oracle (now happily retired): "Look, listen, and learn, for an original mistake is as rare as an original idea." on 10/22/03 1:19 PM, Pete Sharman at [EMAIL PROTECTED] wrote: > I think you have mentioned that once or twice. :) > > Pete > "Controlling developers is like herding cats." > Kevin Loney, Oracle DBA Handbook > "Oh no, it's not. It's much harder than that!" > Bruce Pihlamae, long-term Oracle DBA > > > > -Original Message- > Mladen Gogala > Sent: Wednesday, October 22, 2003 8:40 AM > To: Multiple recipients of list ORACLE-L > > > Good idea. I located the meaning of TIM column (time to "age" buffer). BTW, > did I tell you that I like your signature? > > On 10/21/2003 05:39:25 PM, Pete Sharman wrote: >> Have a look at Steve Adam's web site. He probably knows more about >> it >> than >> just about anyone else I know (as usual!) >> >> Pete >> "Controlling developers is like herding cats." >> Kevin Loney, Oracle DBA Handbook >> "Oh no, it's not. It's much harder than that!" >> Bruce Pihlamae, long-term Oracle DBA >> >> >> >> -Original Message- >> Mladen Gogala >> Sent: Wednesday, October 22, 2003 7:14 AM >> To: Multiple recipients of list ORACLE-L >> >> >> Did anyone try to benchmark the touch count based algorithm against >> the old LRU list? LRU list had advantage of being intuitive, while >> touch >> count >> algorithm is depending on many parameters for which I don't exactly >> understand the impact. LRU list parameters were >> essentially defining the desired length of the free buffers list, >> while the >> touch count parameters are all undocumented and are >> signifying the size of touch pool, the interval in which block has to >> >> be touched if the touch count is to increase, the required touch >> count >> >> to be moved to the hot pool and alike. Is it more efficient then the >> previous easy and understandable LRU lists or not? Touch counts are >> visible as TCH in X$BH. I still have no clue what "TIM" is. >> >> On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: >>> Before Oracle 8 and the new touch count algorithm the cache >> attribute >>> made sense. If a small, frequently used table was read by a full >>> scan, it would have been put at the end of the LRU chain eligible >> to >> >>> be aged out immediately, quite possibly by itself if it consisted >> of >> >>> more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or >> 3rd >>> full scan read would already override the previously read blocks. >>> Marking the table as CACHEd prevented that. >>> >>> At 01:09 PM 10/21/2003, you wrote: I always wondered why Oracle thought this was a useful table attribute. My gut feeling is that it is an extra that does little. For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me. And then I remember a specific piece of Oracle documentation >> saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks. Like I said, sounds a little like "here you have it, and here you don't". I'm sure that my impression is wrong and someone will correct me. >> But I doubt I will use the "CACHE" option anytime soon. Tom Mercadante Oracle Certified Professional >>> >>> Wolfgang Breitling >>> Oracle7, 8, 8i, 9i OCP DBA >>> Centrex Consulting Corporation >>> http://www.centrexcc.com >>> >>> -- >>> Please see the official ORACLE-L FAQ: http://www.orafaq.net >>> -- >>> Author: Wolfgang Breitling >>> INET: [EMAIL PROTECTED] >>> >>> Fat City Network Services-- 858-538-5051 http://www.fatcity.com >>> San Diego, California-- Mailing list and web hosting >> services >>> >> - >>> To REMOVE yourself from this mailing list, send an E-Mail message >>> to: [EMAIL PROTECTED] (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). You may also >> send >>> the HELP command for other information (like subscribing). >> Mladen Gogala >> Oracle DBA >> >> >> >> Note: >> This message is for the named person's use only. It may contain >> confidential, proprietary or legally privileged information. No >> confidentiality or privilege is waived or lost by any >> mistransmission. >> If >> you receive this messa
RE: Cache a table
I think you have mentioned that once or twice. :) Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Mladen Gogala Sent: Wednesday, October 22, 2003 8:40 AM To: Multiple recipients of list ORACLE-L Good idea. I located the meaning of TIM column (time to "age" buffer). BTW, did I tell you that I like your signature? On 10/21/2003 05:39:25 PM, Pete Sharman wrote: > Have a look at Steve Adam's web site. He probably knows more about > it > than > just about anyone else I know (as usual!) > > Pete > "Controlling developers is like herding cats." > Kevin Loney, Oracle DBA Handbook > "Oh no, it's not. It's much harder than that!" > Bruce Pihlamae, long-term Oracle DBA > > > > -Original Message- > Mladen Gogala > Sent: Wednesday, October 22, 2003 7:14 AM > To: Multiple recipients of list ORACLE-L > > > Did anyone try to benchmark the touch count based algorithm against > the old LRU list? LRU list had advantage of being intuitive, while > touch > count > algorithm is depending on many parameters for which I don't exactly > understand the impact. LRU list parameters were > essentially defining the desired length of the free buffers list, > while the > touch count parameters are all undocumented and are > signifying the size of touch pool, the interval in which block has to > > be touched if the touch count is to increase, the required touch > count > > to be moved to the hot pool and alike. Is it more efficient then the > previous easy and understandable LRU lists or not? Touch counts are > visible as TCH in X$BH. I still have no clue what "TIM" is. > > On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: > > Before Oracle 8 and the new touch count algorithm the cache > attribute > > made sense. If a small, frequently used table was read by a full > > scan, it would have been put at the end of the LRU chain eligible > to > > > be aged out immediately, quite possibly by itself if it consisted > of > > > more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or > 3rd > > full scan read would already override the previously read blocks. > > Marking the table as CACHEd prevented that. > > > > At 01:09 PM 10/21/2003, you wrote: > >> I always wondered why Oracle thought this was a useful table > >> attribute. > >> > >> My gut feeling is that it is an extra that does little. > >> > >> For example, say we want to keep a code table in memory because it > >> is constantly being hit for column verifiction. By definition, if > >> a table is > >> constantly being queried, it's segments will be in memory because > >> they never > >> age out. That sounds like cacheing to me. > >> > >> And then I remember a specific piece of Oracle documentation > saying > >> that, > >> even though we may mark a table to be "cached", it *still* may be > >> aged out if memory is needed for other data blocks. > >> > >> Like I said, sounds a little like "here you have it, and here you > >> don't". > >> > >> I'm sure that my impression is wrong and someone will correct me. > > >> But I > >> doubt I will use the "CACHE" option anytime soon. > >> > >> Tom Mercadante > >> Oracle Certified Professional > > > > Wolfgang Breitling > > Oracle7, 8, 8i, 9i OCP DBA > > Centrex Consulting Corporation > > http://www.centrexcc.com > > > >-- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > > Author: Wolfgang Breitling > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (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). You may also > send > > the HELP command for other information (like subscribing). > Mladen Gogala > Oracle DBA > > > > Note: > This message is for the named person's use only. It may contain > confidential, proprietary or legally privileged information. No > confidentiality or privilege is waived or lost by any > mistransmission. > If > you receive this message in error, please immediately delete it and > all > copies of it from your system, destroy any hard copies of it and > notify the > sender. You must not, directly or indirectly, use, disclose, > distribute, > print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve > the > right to monitor all e-mail communications through its networks. Any > views > expressed in this message are those of the individual sender, except > where > the message states otherwise and the sende
RE: Cache a table
Title: RE: Cache a table Mladen, John Beresniewicz did some test (last one I know of in 2000 with Oracle 8.0) with diffent values of _db_aging_cool_count _db_aging_freeze_cr _db_aging_hot_criteria _db_aging_stay_count _db_aging_touch_time _db_percent_hot_default _db_percent_hot_keep _db_percent_hot_recycle You may ask if he has more recent results. Alex. -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 21, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: Cache a table Did anyone try to benchmark the touch count based algorithm against the old LRU list? LRU list had advantage of being intuitive, while touch count algorithm is depending on many parameters for which I don't exactly understand the impact. LRU list parameters were essentially defining the desired length of the free buffers list, while the touch count parameters are all undocumented and are signifying the size of touch pool, the interval in which block has to be touched if the touch count is to increase, the required touch count to be moved to the hot pool and alike. Is it more efficient then the previous easy and understandable LRU lists or not? Touch counts are visible as TCH in X$BH. I still have no clue what "TIM" is. On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: > Before Oracle 8 and the new touch count algorithm the cache attribute > made sense. If a small, frequently used table was read by a full > scan, it would have been put at the end of the LRU chain eligible to > be aged out immediately, quite possibly by itself if it consisted of > more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd > full scan read would already override the previously read blocks. > Marking the table as CACHEd prevented that. > > At 01:09 PM 10/21/2003, you wrote: >> I always wondered why Oracle thought this was a useful table >> attribute. >> >> My gut feeling is that it is an extra that does little. >> >> For example, say we want to keep a code table in memory because it >> is >> constantly being hit for column verifiction. By definition, if a >> table is >> constantly being queried, it's segments will be in memory because >> they never >> age out. That sounds like cacheing to me. >> >> And then I remember a specific piece of Oracle documentation saying >> that, >> even though we may mark a table to be "cached", it *still* may be >> aged out >> if memory is needed for other data blocks. >> >> Like I said, sounds a little like "here you have it, and here you >> don't". >> >> I'm sure that my impression is wrong and someone will correct me. >> But I >> doubt I will use the "CACHE" option anytime soon. >> >> Tom Mercadante >> Oracle Certified Professional > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > >-- > Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- > Author: Wolfgang Breitling > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). You may also send > the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California
Re: Cache a table
Good idea. I located the meaning of TIM column (time to "age" buffer). BTW, did I tell you that I like your signature? On 10/21/2003 05:39:25 PM, Pete Sharman wrote: Have a look at Steve Adam's web site. He probably knows more about it than just about anyone else I know (as usual!) Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Mladen Gogala Sent: Wednesday, October 22, 2003 7:14 AM To: Multiple recipients of list ORACLE-L Did anyone try to benchmark the touch count based algorithm against the old LRU list? LRU list had advantage of being intuitive, while touch count algorithm is depending on many parameters for which I don't exactly understand the impact. LRU list parameters were essentially defining the desired length of the free buffers list, while the touch count parameters are all undocumented and are signifying the size of touch pool, the interval in which block has to be touched if the touch count is to increase, the required touch count to be moved to the hot pool and alike. Is it more efficient then the previous easy and understandable LRU lists or not? Touch counts are visible as TCH in X$BH. I still have no clue what "TIM" is. On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: > Before Oracle 8 and the new touch count algorithm the cache attribute > made sense. If a small, frequently used table was read by a full > scan, it would have been put at the end of the LRU chain eligible to > be aged out immediately, quite possibly by itself if it consisted of > more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd > full scan read would already override the previously read blocks. > Marking the table as CACHEd prevented that. > > At 01:09 PM 10/21/2003, you wrote: >> I always wondered why Oracle thought this was a useful table >> attribute. >> >> My gut feeling is that it is an extra that does little. >> >> For example, say we want to keep a code table in memory because it >> is >> constantly being hit for column verifiction. By definition, if a >> table is >> constantly being queried, it's segments will be in memory because >> they never >> age out. That sounds like cacheing to me. >> >> And then I remember a specific piece of Oracle documentation saying >> that, >> even though we may mark a table to be "cached", it *still* may be >> aged out >> if memory is needed for other data blocks. >> >> Like I said, sounds a little like "here you have it, and here you >> don't". >> >> I'm sure that my impression is wrong and someone will correct me. >> But I >> doubt I will use the "CACHE" option anytime soon. >> >> Tom Mercadante >> Oracle Certified Professional > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > >-- > Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- > Author: Wolfgang Breitling > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). You may also send > the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list y
RE: Cache a table
Title: Message
Just to add to Alex's point below, John Beresniewicz wrote a paper
some time ago (about two or three years) that effectively explains the
different buffer pools, and provides test results of the mid-point
insertion algorithms:
http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs122
If you
don't already have a user account on the orapub site, it's quick to
create one, and, of course, free.
Cheers,
Melanie
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]Sent: Tuesday, October 21,
2003 5:34 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Cache a table
John,Tom,
There is a difference between pools {DEFAULT vs. KEEP and
RECYCLE}. By default only DEFAULT pool use "mid-point"
insert. It is controlled by hidden parameters _db_percent_hot_default (Percent of default buffer pool considered hot)
default 50 _db_percent_hot_keep
(Percent of keep buffer pool considered hot) default
0 _db_percent_hot_recycle (Percent of recycle buffer
pool considered hot) default 0
Table's attribute "CACHE" controls whether blocks read by FTS
will be placed on LRU or MRU end of the LRU list, but only up to
_small_table_threshold (threshold level of table size for forget-bit enabled
during scan), default is 2% of db_block_size.
Alex.
-Original Message- From:
Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 21, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Cache a table
I always wondered why Oracle thought this was a useful table
attribute.
My gut feeling is that it is an extra that does little.
For example, say we want to keep a code table in memory
because it is constantly being hit for column verifiction. By
definition, if a table is constantly being queried, it's segments will be in
memory because they never age out. That sounds like cacheing to
me.
And then I remember a specific piece of Oracle documentation
saying that, even though we may mark a table to be "cached", it *still* may be
aged out if memory is needed for other data blocks.
Like I said, sounds a little like "here you have it, and here
you don't".
I'm sure that my impression is wrong and someone will correct
me. But I doubt I will use the "CACHE" option anytime soon.
Tom Mercadante Oracle Certified
Professional
-Original Message- Sent:
Tuesday, October 21, 2003 2:54 PM To: Multiple
recipients of list ORACLE-L
My understanding is that the KEEP and RECYCLE Pools are just
'names' in the sense that they are placeholders for assigning an object
to the BUFFER_POOL { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging'
algorithms for KEEP and RECYCLE are exactly the same. Assigning a specific
object to one of these named pools segregates objects by
retention-requirements. Thus, KEEP does not imply a different treatment of the
Buffers - rather it makes sure that objects that you would like to 'keep'
around are specifically directed to a common pool and vice
versa
Does anyone have additional information that can verify this?
I heard this from a knowledgeable Oracle instructor in an Oracle Tuning
training Class.
John Kanagaraj DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are
entirely mine and do not reflect those of my employer or customers
**
>-Original Message- >From: Tim Gorman [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, October 21, 2003 6:59 AM >To: Multiple recipients of list ORACLE-L >Subject: Re: Cache a table >
> >Good points, Arup.
> >Actually, I would argue that
there is better reason to >consider using
the >RECYCLE pool than to consider how to "cache"
tables or use the >KEEP pool. >The advantage of effective use of the RECYCLE pool is better
>behavior in the >rest
of the Buffer Cache... > >When you think of it, the default DEFAULT buffer pool and
the >KEEP pool have >essentially the same purpose: long-term caching of
blocks. >What keeps them >from accomplishing that mission but objects whose blocks waste
>space and >energy
cycling into and out from the Buffer Cache? > >It's kind of like a school teacher
admonishing his/her class that "a >troublesome few
have ruined things for everybody". When I was in >school, "troublemakers" were segregated from the rest of the class,
>sometimes cumulatively into a separate classroom
(we called ourselves >"the mentals"
>and read Mad magazines all the time, which accounts for a
RE: Cache a table
Title: RE: Cache a table
Sure, Wolfgang. Thank you for the correction.
Alex.
-Original Message-
From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cache a table
I suppose you mean "2% of db_block_buffers"
At 03:34 PM 10/21/2003, you wrote:
>John,Tom,
>
>There is a difference between pools {DEFAULT vs. KEEP and RECYCLE}. By
>default only DEFAULT pool use "mid-point" insert. It is controlled by
>hidden parameters _db_percent_hot_default (Percent of default buffer
>pool considered hot) default 50
>_db_percent_hot_keep (Percent of keep buffer pool considered
>hot) default 0
>_db_percent_hot_recycle (Percent of recycle buffer pool considered hot)
>default 0
>
>Table's attribute "CACHE" controls whether blocks read by FTS will be
>placed on LRU or MRU end of the LRU list, but only up to
>_small_table_threshold (threshold level of table size for forget-bit
>enabled during scan), default is 2% of db_block_size.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
RE: Cache a table
I suppose you mean "2% of db_block_buffers"
At 03:34 PM 10/21/2003, you wrote:
John,Tom,
There is a difference between pools {DEFAULT vs. KEEP and RECYCLE}.
By default only DEFAULT pool use "mid-point" insert. It is controlled by
hidden parameters
_db_percent_hot_default (Percent of default buffer pool considered hot)
default 50
_db_percent_hot_keep(Percent of keep buffer pool considered
hot)default 0
_db_percent_hot_recycle (Percent of recycle buffer pool considered hot)
default 0
Table's attribute "CACHE" controls whether blocks read by FTS will be
placed on LRU or MRU end of the LRU list, but only up to
_small_table_threshold (threshold level of table size for forget-bit
enabled during scan), default is 2% of db_block_size.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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). You may
also send the HELP command for other information (like subscribing).
RE: Cache a table
Have a look at Steve Adam's web site. He probably knows more about it than just about anyone else I know (as usual!) Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Mladen Gogala Sent: Wednesday, October 22, 2003 7:14 AM To: Multiple recipients of list ORACLE-L Did anyone try to benchmark the touch count based algorithm against the old LRU list? LRU list had advantage of being intuitive, while touch count algorithm is depending on many parameters for which I don't exactly understand the impact. LRU list parameters were essentially defining the desired length of the free buffers list, while the touch count parameters are all undocumented and are signifying the size of touch pool, the interval in which block has to be touched if the touch count is to increase, the required touch count to be moved to the hot pool and alike. Is it more efficient then the previous easy and understandable LRU lists or not? Touch counts are visible as TCH in X$BH. I still have no clue what "TIM" is. On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: > Before Oracle 8 and the new touch count algorithm the cache attribute > made sense. If a small, frequently used table was read by a full > scan, it would have been put at the end of the LRU chain eligible to > be aged out immediately, quite possibly by itself if it consisted of > more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd > full scan read would already override the previously read blocks. > Marking the table as CACHEd prevented that. > > At 01:09 PM 10/21/2003, you wrote: >> I always wondered why Oracle thought this was a useful table >> attribute. >> >> My gut feeling is that it is an extra that does little. >> >> For example, say we want to keep a code table in memory because it >> is >> constantly being hit for column verifiction. By definition, if a >> table is >> constantly being queried, it's segments will be in memory because >> they never >> age out. That sounds like cacheing to me. >> >> And then I remember a specific piece of Oracle documentation saying >> that, >> even though we may mark a table to be "cached", it *still* may be >> aged out >> if memory is needed for other data blocks. >> >> Like I said, sounds a little like "here you have it, and here you >> don't". >> >> I'm sure that my impression is wrong and someone will correct me. >> But I >> doubt I will use the "CACHE" option anytime soon. >> >> Tom Mercadante >> Oracle Certified Professional > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > >-- > Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- > Author: Wolfgang Breitling > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). You may also send > the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing). -- Please see the official OR
RE: Cache a table
Title: RE: Cache a table
John,Tom,
There is a difference between pools {DEFAULT vs. KEEP and RECYCLE}.
By default only DEFAULT pool use "mid-point" insert. It is controlled by hidden parameters
_db_percent_hot_default (Percent of default buffer pool considered hot) default 50
_db_percent_hot_keep (Percent of keep buffer pool considered hot) default 0
_db_percent_hot_recycle (Percent of recycle buffer pool considered hot) default 0
Table's attribute "CACHE" controls whether blocks read by FTS will be placed on LRU or MRU end of the LRU list, but only up to _small_table_threshold (threshold level of table size for forget-bit enabled during scan), default is 2% of db_block_size.
Alex.
-Original Message-
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 21, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cache a table
I always wondered why Oracle thought this was a useful table attribute.
My gut feeling is that it is an extra that does little.
For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me.
And then I remember a specific piece of Oracle documentation saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks.
Like I said, sounds a little like "here you have it, and here you don't".
I'm sure that my impression is wrong and someone will correct me. But I doubt I will use the "CACHE" option anytime soon.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
My understanding is that the KEEP and RECYCLE Pools are just 'names' in the sense that they are placeholders for assigning an object to the BUFFER_POOL { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for KEEP and RECYCLE are exactly the same. Assigning a specific object to one of these named pools segregates objects by retention-requirements. Thus, KEEP does not imply a different treatment of the Buffers - rather it makes sure that objects that you would like to 'keep' around are specifically directed to a common pool and vice versa
Does anyone have additional information that can verify this? I heard this from a knowledgeable Oracle instructor in an Oracle Tuning training Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **
>-Original Message-
>From: Tim Gorman [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, October 21, 2003 6:59 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Cache a table
>
>
>Good points, Arup.
>
>Actually, I would argue that there is better reason to
>consider using the
>RECYCLE pool than to consider how to "cache" tables or use the
>KEEP pool.
>The advantage of effective use of the RECYCLE pool is better
>behavior in the
>rest of the Buffer Cache...
>
>When you think of it, the default DEFAULT buffer pool and the
>KEEP pool have
>essentially the same purpose: long-term caching of blocks.
>What keeps them
>from accomplishing that mission but objects whose blocks waste
>space and
>energy cycling into and out from the Buffer Cache?
>
>It's kind of like a school teacher admonishing his/her class that "a
>troublesome few have ruined things for everybody". When I was in
>school, "troublemakers" were segregated from the rest of the class,
>sometimes cumulatively into a separate classroom (we called ourselves
>"the mentals"
>and read Mad magazines all the time, which accounts for a lot, then and
>now). Nowadays, I'm sure that such a measure isn't considered
>for fear of
>lawsuit for hurting the "self-esteem" of the poor dears.
>Never mind the
>confusion between the useless feel-good phrase "self-esteem"
>and the more
>useful and thought-provoking phrase "self-respect". Oh well,
>better stop
>now...
>
>Anyway, marking a table as CACHE and placing it in a KEEP
>buffer pool which
>is large enough to accommodate all of the used blocks is the
>closest thing
>to pinning a table into the Buffer Cache as you'll get, as
>Arup described.
>
>Of course, there is little benefit from such a move, as Arup
>also mentioned.
>
>Just yesterday, I visited a customer who had a series of SQL
Re: Cache a table
BTW, a good text to really understand the "touch counts" is found here: http://cslibrary.stanford.edu/105/LinkedListProblems.pdf It's not oracle specific, but gives you a hint how do things work. On 10/21/2003 05:14:28 PM, Mladen Gogala wrote: Did anyone try to benchmark the touch count based algorithm against the old LRU list? LRU list had advantage of being intuitive, while touch count algorithm is depending on many parameters for which I don't exactly understand the impact. LRU list parameters were essentially defining the desired length of the free buffers list, while the touch count parameters are all undocumented and are signifying the size of touch pool, the interval in which block has to be touched if the touch count is to increase, the required touch count to be moved to the hot pool and alike. Is it more efficient then the previous easy and understandable LRU lists or not? Touch counts are visible as TCH in X$BH. I still have no clue what "TIM" is. On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: Before Oracle 8 and the new touch count algorithm the cache attribute made sense. If a small, frequently used table was read by a full scan, it would have been put at the end of the LRU chain eligible to be aged out immediately, quite possibly by itself if it consisted of more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd full scan read would already override the previously read blocks. Marking the table as CACHEd prevented that. At 01:09 PM 10/21/2003, you wrote: I always wondered why Oracle thought this was a useful table attribute. My gut feeling is that it is an extra that does little. For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me. And then I remember a specific piece of Oracle documentation saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks. Like I said, sounds a little like "here you have it, and here you don't". I'm sure that my impression is wrong and someone will correct me. But I doubt I will use the "CACHE" option anytime soon. Tom Mercadante Oracle Certified Professional Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e- mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies
Re: Cache a table
Did anyone try to benchmark the touch count based algorithm against the old LRU list? LRU list had advantage of being intuitive, while touch count algorithm is depending on many parameters for which I don't exactly understand the impact. LRU list parameters were essentially defining the desired length of the free buffers list, while the touch count parameters are all undocumented and are signifying the size of touch pool, the interval in which block has to be touched if the touch count is to increase, the required touch count to be moved to the hot pool and alike. Is it more efficient then the previous easy and understandable LRU lists or not? Touch counts are visible as TCH in X$BH. I still have no clue what "TIM" is. On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote: Before Oracle 8 and the new touch count algorithm the cache attribute made sense. If a small, frequently used table was read by a full scan, it would have been put at the end of the LRU chain eligible to be aged out immediately, quite possibly by itself if it consisted of more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd full scan read would already override the previously read blocks. Marking the table as CACHEd prevented that. At 01:09 PM 10/21/2003, you wrote: I always wondered why Oracle thought this was a useful table attribute. My gut feeling is that it is an extra that does little. For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me. And then I remember a specific piece of Oracle documentation saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks. Like I said, sounds a little like "here you have it, and here you don't". I'm sure that my impression is wrong and someone will correct me. But I doubt I will use the "CACHE" option anytime soon. Tom Mercadante Oracle Certified Professional Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
RE: Cache a table
Thanks for correction.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Tim Gorman
Sent: Tuesday, October 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L
The switch being referred to occurred with 8i, where Oracle
went to the "touch-count" algorithm. See
"http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=papers_main
" for paper #136.
I guess "most frequently used" is a good way to describe it
-- nice choice of words!
> Tom,
>
> I think you are correct, if we are talking about Oracle 9,
> where oracle switched from "most recently used" to "most
> frequently used" algorithm.
> But, prior to that, it seems possible to think of
> scenarios, where "cache" would be helpful. May be, that's
> one of the reasons, why oracle changed algorithm.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
>
> -Original Message-
> Mercadante, Thomas F
> Sent: Tuesday, October 21, 2003 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
> I always wondered why Oracle thought this was a useful
> table attribute.
> My gut feeling is that it is an extra that does little.
>
> For example, say we want to keep a code table in memory
> because it is constantly being hit for column verifiction.
> By definition, if a table is
> constantly being queried, it's segments will be in memory
> because they never
> age out. That sounds like cacheing to me.
>
> And then I remember a specific piece of Oracle
> documentation saying that,
> even though we may mark a table to be "cached", it *still*
> may be aged out
> if memory is needed for other data blocks.
>
> Like I said, sounds a little like "here you have it, and
> here you don't".
>
> I'm sure that my impression is wrong and someone will
> correct me. But I doubt I will use the "CACHE" option
> anytime soon.
> Tom Mercadante
> Oracle Certified Professional
>
>
> -Original Message-
> Sent: Tuesday, October 21, 2003 2:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> My understanding is that the KEEP and RECYCLE Pools are
> just 'names' in the
> sense that they are placeholders for assigning an object
> to the BUFFER_POOL
> { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging'
> algorithms for KEEP and RECYCLE are exactly the same.
> Assigning a specific object to one of
> these named pools segregates objects by
> retention-requirements. Thus, KEEP
> does not imply a different treatment of the Buffers -
> rather it makes sure
> that objects that you would like to 'keep' around are
> specifically directed
> to a common pool and vice versa
>
> Does anyone have additional information that can verify
> this? I heard this
> from a knowledgeable Oracle instructor in an Oracle Tuning
> training Class.
>
> John Kanagaraj
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Disappointment is inevitable, but Discouragement is
> optional!
> ** The opinions and facts contained in this message are
> entirely mine and do
> not reflect those of my employer or customers **
>
> >-Original Message-
> >From: Tim Gorman [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, October 21, 2003 6:59 AM
> >To: Multiple recipients of list ORACLE-L
> >Subject: Re: Cache a table
> >
> >
> >Good points, Arup.
> >
> >Actually, I would argue that there is better reason to
> >consider using the
> >RECYCLE pool than to consider how to "cache" tables or
> use the >KEEP pool.
> >The advantage of effective use of the RECYCLE pool is
> better >behavior in the
> >rest of the Buffer Cache...
> >
> >When you think of it, the default DEFAULT buffer pool and
> the >KEEP pool have
> >essentially the same purpose: long-term caching of
> blocks. >What keeps them
> >from accomplishing that mission but objects whose blocks
> waste >space and
> >energy cycling into and out from the Buffer Cache?
> >
> >It's kind of like a school teacher admonishing his/her
> class that "a >troublesome few have ruined things for
> everybody". When I was >in school,
> >"troublemakers" were segregated from the rest of the
> class, sometimes >cumulatively into a separate classroom
> (we called ourselves >"the mentals"
> >and read Mad magazines all the time, which accounts for a
> lot, then and >now). Nowadays, I'm sure that such a
> measure isn't considered >for fear of
> >lawsuit for hurting the "self-esteem" of the
RE: Cache a table
Before Oracle 8 and the new touch count algorithm the cache attribute made sense. If a small, frequently used table was read by a full scan, it would have been put at the end of the LRU chain eligible to be aged out immediately, quite possibly by itself if it consisted of more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or 3rd full scan read would already override the previously read blocks. Marking the table as CACHEd prevented that. At 01:09 PM 10/21/2003, you wrote: I always wondered why Oracle thought this was a useful table attribute. My gut feeling is that it is an extra that does little. For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me. And then I remember a specific piece of Oracle documentation saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks. Like I said, sounds a little like "here you have it, and here you don't". I'm sure that my impression is wrong and someone will correct me. But I doubt I will use the "CACHE" option anytime soon. Tom Mercadante Oracle Certified Professional Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
RE: Cache a table
The switch being referred to occurred with 8i, where Oracle
went to the "touch-count" algorithm. See
"http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=papers_main
" for paper #136.
I guess "most frequently used" is a good way to describe it
-- nice choice of words!
> Tom,
>
> I think you are correct, if we are talking about Oracle 9,
> where oracle switched from "most recently used" to "most
> frequently used" algorithm.
> But, prior to that, it seems possible to think of
> scenarios, where "cache" would be helpful. May be, that's
> one of the reasons, why oracle changed algorithm.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
>
> -Original Message-
> Mercadante, Thomas F
> Sent: Tuesday, October 21, 2003 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
> I always wondered why Oracle thought this was a useful
> table attribute.
> My gut feeling is that it is an extra that does little.
>
> For example, say we want to keep a code table in memory
> because it is constantly being hit for column verifiction.
> By definition, if a table is
> constantly being queried, it's segments will be in memory
> because they never
> age out. That sounds like cacheing to me.
>
> And then I remember a specific piece of Oracle
> documentation saying that,
> even though we may mark a table to be "cached", it *still*
> may be aged out
> if memory is needed for other data blocks.
>
> Like I said, sounds a little like "here you have it, and
> here you don't".
>
> I'm sure that my impression is wrong and someone will
> correct me. But I doubt I will use the "CACHE" option
> anytime soon.
> Tom Mercadante
> Oracle Certified Professional
>
>
> -Original Message-
> Sent: Tuesday, October 21, 2003 2:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> My understanding is that the KEEP and RECYCLE Pools are
> just 'names' in the
> sense that they are placeholders for assigning an object
> to the BUFFER_POOL
> { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging'
> algorithms for KEEP and RECYCLE are exactly the same.
> Assigning a specific object to one of
> these named pools segregates objects by
> retention-requirements. Thus, KEEP
> does not imply a different treatment of the Buffers -
> rather it makes sure
> that objects that you would like to 'keep' around are
> specifically directed
> to a common pool and vice versa
>
> Does anyone have additional information that can verify
> this? I heard this
> from a knowledgeable Oracle instructor in an Oracle Tuning
> training Class.
>
> John Kanagaraj
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Disappointment is inevitable, but Discouragement is
> optional!
> ** The opinions and facts contained in this message are
> entirely mine and do
> not reflect those of my employer or customers **
>
> >-Original Message-
> >From: Tim Gorman [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, October 21, 2003 6:59 AM
> >To: Multiple recipients of list ORACLE-L
> >Subject: Re: Cache a table
> >
> >
> >Good points, Arup.
> >
> >Actually, I would argue that there is better reason to
> >consider using the
> >RECYCLE pool than to consider how to "cache" tables or
> use the >KEEP pool.
> >The advantage of effective use of the RECYCLE pool is
> better >behavior in the
> >rest of the Buffer Cache...
> >
> >When you think of it, the default DEFAULT buffer pool and
> the >KEEP pool have
> >essentially the same purpose: long-term caching of
> blocks. >What keeps them
> >from accomplishing that mission but objects whose blocks
> waste >space and
> >energy cycling into and out from the Buffer Cache?
> >
> >It's kind of like a school teacher admonishing his/her
> class that "a >troublesome few have ruined things for
> everybody". When I was >in school,
> >"troublemakers" were segregated from the rest of the
> class, sometimes >cumulatively into a separate classroom
> (we called ourselves >"the mentals"
> >and read Mad magazines all the time, which accounts for a
> lot, then and >now). Nowadays, I'm sure that such a
> measure isn't considered >for fear of
> >lawsuit for hurting the "self-esteem" of the poor dears.
> >Never mind the
> >confusion between the useless feel-good phrase
> "self-esteem" >and the more
> >useful and thought-provoking phrase &qu
RE: Cache a table
Tom,
I think you are correct, if we are talking about Oracle 9, where oracle
switched from "most recently used" to "most frequently used" algorithm.
But, prior to that, it seems possible to think of scenarios, where
"cache" would be helpful. May be, that's one of the reasons, why oracle
changed algorithm.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Mercadante, Thomas F
Sent: Tuesday, October 21, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L
I always wondered why Oracle thought this was a useful table attribute.
My gut feeling is that it is an extra that does little.
For example, say we want to keep a code table in memory because it is
constantly being hit for column verifiction. By definition, if a table
is
constantly being queried, it's segments will be in memory because they
never
age out. That sounds like cacheing to me.
And then I remember a specific piece of Oracle documentation saying
that,
even though we may mark a table to be "cached", it *still* may be aged
out
if memory is needed for other data blocks.
Like I said, sounds a little like "here you have it, and here you
don't".
I'm sure that my impression is wrong and someone will correct me. But I
doubt I will use the "CACHE" option anytime soon.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
My understanding is that the KEEP and RECYCLE Pools are just 'names' in
the
sense that they are placeholders for assigning an object to the
BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to
one of
these named pools segregates objects by retention-requirements. Thus,
KEEP
does not imply a different treatment of the Buffers - rather it makes
sure
that objects that you would like to 'keep' around are specifically
directed
to a common pool and vice versa
Does anyone have additional information that can verify this? I heard
this
from a knowledgeable Oracle instructor in an Oracle Tuning training
Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine
and do
not reflect those of my employer or customers **
>-Original Message-
>From: Tim Gorman [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, October 21, 2003 6:59 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Cache a table
>
>
>Good points, Arup.
>
>Actually, I would argue that there is better reason to
>consider using the
>RECYCLE pool than to consider how to "cache" tables or use the
>KEEP pool.
>The advantage of effective use of the RECYCLE pool is better
>behavior in the
>rest of the Buffer Cache...
>
>When you think of it, the default DEFAULT buffer pool and the
>KEEP pool have
>essentially the same purpose: long-term caching of blocks.
>What keeps them
>from accomplishing that mission but objects whose blocks waste
>space and
>energy cycling into and out from the Buffer Cache?
>
>It's kind of like a school teacher admonishing his/her class that "a
>troublesome few have ruined things for everybody". When I was
>in school,
>"troublemakers" were segregated from the rest of the class, sometimes
>cumulatively into a separate classroom (we called ourselves
>"the mentals"
>and read Mad magazines all the time, which accounts for a lot, then and
>now). Nowadays, I'm sure that such a measure isn't considered
>for fear of
>lawsuit for hurting the "self-esteem" of the poor dears.
>Never mind the
>confusion between the useless feel-good phrase "self-esteem"
>and the more
>useful and thought-provoking phrase "self-respect". Oh well,
>better stop
>now...
>
>Anyway, marking a table as CACHE and placing it in a KEEP
>buffer pool which
>is large enough to accommodate all of the used blocks is the
>closest thing
>to pinning a table into the Buffer Cache as you'll get, as
>Arup described.
>
>Of course, there is little benefit from such a move, as Arup
>also mentioned.
>
>Just yesterday, I visited a customer who had a series of SQL
>statements that
>were executing some 10 million times _each_ per day, averaging
>about 20-1500
>LIOs per execution. They each had a 99.999% "buffer cache hit
>ratio", yet strangely enough the performance on the server is
>absolute crap
>because the eight brand-new 2Ghz CPUs on the server are busy
>as hell with no
>time to spare for anything.
>
>Well, you kno
RE: Cache a table
I always wondered why Oracle thought this was a useful table attribute.
My gut feeling is that it is an extra that does little.
For example, say we want to keep a code table in memory because it is
constantly being hit for column verifiction. By definition, if a table is
constantly being queried, it's segments will be in memory because they never
age out. That sounds like cacheing to me.
And then I remember a specific piece of Oracle documentation saying that,
even though we may mark a table to be "cached", it *still* may be aged out
if memory is needed for other data blocks.
Like I said, sounds a little like "here you have it, and here you don't".
I'm sure that my impression is wrong and someone will correct me. But I
doubt I will use the "CACHE" option anytime soon.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
My understanding is that the KEEP and RECYCLE Pools are just 'names' in the
sense that they are placeholders for assigning an object to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to one of
these named pools segregates objects by retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers - rather it makes sure
that objects that you would like to 'keep' around are specifically directed
to a common pool and vice versa
Does anyone have additional information that can verify this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning training Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
>-Original Message-
>From: Tim Gorman [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, October 21, 2003 6:59 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Cache a table
>
>
>Good points, Arup.
>
>Actually, I would argue that there is better reason to
>consider using the
>RECYCLE pool than to consider how to "cache" tables or use the
>KEEP pool.
>The advantage of effective use of the RECYCLE pool is better
>behavior in the
>rest of the Buffer Cache...
>
>When you think of it, the default DEFAULT buffer pool and the
>KEEP pool have
>essentially the same purpose: long-term caching of blocks.
>What keeps them
>from accomplishing that mission but objects whose blocks waste
>space and
>energy cycling into and out from the Buffer Cache?
>
>It's kind of like a school teacher admonishing his/her class that "a
>troublesome few have ruined things for everybody". When I was
>in school,
>"troublemakers" were segregated from the rest of the class, sometimes
>cumulatively into a separate classroom (we called ourselves
>"the mentals"
>and read Mad magazines all the time, which accounts for a lot, then and
>now). Nowadays, I'm sure that such a measure isn't considered
>for fear of
>lawsuit for hurting the "self-esteem" of the poor dears.
>Never mind the
>confusion between the useless feel-good phrase "self-esteem"
>and the more
>useful and thought-provoking phrase "self-respect". Oh well,
>better stop
>now...
>
>Anyway, marking a table as CACHE and placing it in a KEEP
>buffer pool which
>is large enough to accommodate all of the used blocks is the
>closest thing
>to pinning a table into the Buffer Cache as you'll get, as
>Arup described.
>
>Of course, there is little benefit from such a move, as Arup
>also mentioned.
>
>Just yesterday, I visited a customer who had a series of SQL
>statements that
>were executing some 10 million times _each_ per day, averaging
>about 20-1500
>LIOs per execution. They each had a 99.999% "buffer cache hit
>ratio", yet strangely enough the performance on the server is
>absolute crap
>because the eight brand-new 2Ghz CPUs on the server are busy
>as hell with no
>time to spare for anything.
>
>Well, you know and I know that they simply need more CPUs,
>which is what HP
>is busy telling them, today right as we "speak". Moreover, Oracle
>Consulting is shoulder to shoulder with them, nodding their
>heads. No way
>does the crap custom-built application need to be altered in
>any minor way,
>so that it doesn't keep performing the same useless validation
>query on the
>same set of static lookup tables over and over again for each
>row inserted,
>when the JDBC t
RE: Cache a table
My understanding is that the KEEP and RECYCLE Pools are just 'names' in the
sense that they are placeholders for assigning an object to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to one of
these named pools segregates objects by retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers - rather it makes sure
that objects that you would like to 'keep' around are specifically directed
to a common pool and vice versa
Does anyone have additional information that can verify this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning training Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
>-Original Message-
>From: Tim Gorman [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, October 21, 2003 6:59 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Cache a table
>
>
>Good points, Arup.
>
>Actually, I would argue that there is better reason to
>consider using the
>RECYCLE pool than to consider how to "cache" tables or use the
>KEEP pool.
>The advantage of effective use of the RECYCLE pool is better
>behavior in the
>rest of the Buffer Cache...
>
>When you think of it, the default DEFAULT buffer pool and the
>KEEP pool have
>essentially the same purpose: long-term caching of blocks.
>What keeps them
>from accomplishing that mission but objects whose blocks waste
>space and
>energy cycling into and out from the Buffer Cache?
>
>It's kind of like a school teacher admonishing his/her class that "a
>troublesome few have ruined things for everybody". When I was
>in school,
>"troublemakers" were segregated from the rest of the class, sometimes
>cumulatively into a separate classroom (we called ourselves
>"the mentals"
>and read Mad magazines all the time, which accounts for a lot, then and
>now). Nowadays, I'm sure that such a measure isn't considered
>for fear of
>lawsuit for hurting the "self-esteem" of the poor dears.
>Never mind the
>confusion between the useless feel-good phrase "self-esteem"
>and the more
>useful and thought-provoking phrase "self-respect". Oh well,
>better stop
>now...
>
>Anyway, marking a table as CACHE and placing it in a KEEP
>buffer pool which
>is large enough to accommodate all of the used blocks is the
>closest thing
>to pinning a table into the Buffer Cache as you'll get, as
>Arup described.
>
>Of course, there is little benefit from such a move, as Arup
>also mentioned.
>
>Just yesterday, I visited a customer who had a series of SQL
>statements that
>were executing some 10 million times _each_ per day, averaging
>about 20-1500
>LIOs per execution. They each had a 99.999% "buffer cache hit
>ratio", yet strangely enough the performance on the server is
>absolute crap
>because the eight brand-new 2Ghz CPUs on the server are busy
>as hell with no
>time to spare for anything.
>
>Well, you know and I know that they simply need more CPUs,
>which is what HP
>is busy telling them, today right as we "speak". Moreover, Oracle
>Consulting is shoulder to shoulder with them, nodding their
>heads. No way
>does the crap custom-built application need to be altered in
>any minor way,
>so that it doesn't keep performing the same useless validation
>query on the
>same set of static lookup tables over and over again for each
>row inserted,
>when the JDBC thin client can easily query these tables only
>once and store
>the results. Nope. No sirree...
>
>
>It's a little-known fact that Java code actually has the consistency of
>concrete, once in production. There are so many interdependencies from
>shared modules and RPCs that people are terrified of modifying
>anything,
>probably for good reason. Far easier to shift blame or say
>"hear hear" when
>the vendor proposes another 4-8 CPUs.
>
>Ah, I believe I'll have another beer when you're ready, Sammy...
>
>
>Anyway, first tune the SQL. Then, tune to the application to
>get rid of
>unnecessary SQL. Then and only then, consider tuning the
>Buffer Cache to
>segregate "bad" tables to the RECYCLE pool or "pinning" tables
>to the KEEP
>pool. Reversing the order is a great way to convert a happy
>application
>capable of running on a small server to an unhappy applica
Re: Cache a table
OOPS! Fat finger it is, indeed. It should be MRU end of the LRU list, not LRU end. Thanks for the correction, Mike. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 8:39 AM > Hi, > > I'm sure you know this but you had some finger trouble there. Just to > clarify it for others; Tables with the CACHE option are placed at the Most > Recently Used end of the LRU list. > > Cheers, > Mike Hately > > -Original Message- > Sent: 21 October 2003 12:21 > To: Multiple recipients of list ORACLE-L > > > Never. Altering the table to cache does not gurantee that it will be always > be available in the cache. It simply means the table will be placed in the > Least recently used end of the LRU list and it will age away as time goes > by, just like any other table. > > A better approach is to use KEEP pool and place teh table (and all other > tables that are accessed frequently) there. This is particualrly true for > datawarehouses wherethe lookup tables or small dimension tables can be > placed in KEEP pool. > > Ah, come tho think about it, actually there is one situation where I will > consider the CACHE option, when I restart the instance and want the hit > ratio to look good :) > > HTH. > > Arup Nanda > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 21, 2003 3:39 AM > > > > Hi all, > > > > when you would consider to put a table a cache... > > > > rgds > > > > gb > > > > E mail Disclaimer > > You agree that you have read and understood this disclaimer and you agree to be bound by its terms. > > The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. > > This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. > > No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. > > CE Electric UK Funding Company > Lloyds Court > 78 Grey Street > Newcastle upon Tyne > NE1 6AF > Registered in England and Wales: Number 3476201 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hately, Mike (LogicaCMG) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
Re: Cache a table
Good points, Arup. Actually, I would argue that there is better reason to consider using the RECYCLE pool than to consider how to "cache" tables or use the KEEP pool. The advantage of effective use of the RECYCLE pool is better behavior in the rest of the Buffer Cache... When you think of it, the default DEFAULT buffer pool and the KEEP pool have essentially the same purpose: long-term caching of blocks. What keeps them from accomplishing that mission but objects whose blocks waste space and energy cycling into and out from the Buffer Cache? It's kind of like a school teacher admonishing his/her class that "a troublesome few have ruined things for everybody". When I was in school, "troublemakers" were segregated from the rest of the class, sometimes cumulatively into a separate classroom (we called ourselves "the mentals" and read Mad magazines all the time, which accounts for a lot, then and now). Nowadays, I'm sure that such a measure isn't considered for fear of lawsuit for hurting the "self-esteem" of the poor dears. Never mind the confusion between the useless feel-good phrase "self-esteem" and the more useful and thought-provoking phrase "self-respect". Oh well, better stop now... Anyway, marking a table as CACHE and placing it in a KEEP buffer pool which is large enough to accommodate all of the used blocks is the closest thing to pinning a table into the Buffer Cache as you'll get, as Arup described. Of course, there is little benefit from such a move, as Arup also mentioned. Just yesterday, I visited a customer who had a series of SQL statements that were executing some 10 million times _each_ per day, averaging about 20-1500 LIOs per execution. They each had a 99.999% "buffer cache hit ratio", yet strangely enough the performance on the server is absolute crap because the eight brand-new 2Ghz CPUs on the server are busy as hell with no time to spare for anything. Well, you know and I know that they simply need more CPUs, which is what HP is busy telling them, today right as we "speak". Moreover, Oracle Consulting is shoulder to shoulder with them, nodding their heads. No way does the crap custom-built application need to be altered in any minor way, so that it doesn't keep performing the same useless validation query on the same set of static lookup tables over and over again for each row inserted, when the JDBC thin client can easily query these tables only once and store the results. Nope. No sirree... It's a little-known fact that Java code actually has the consistency of concrete, once in production. There are so many interdependencies from shared modules and RPCs that people are terrified of modifying anything, probably for good reason. Far easier to shift blame or say "hear hear" when the vendor proposes another 4-8 CPUs. Ah, I believe I'll have another beer when you're ready, Sammy... Anyway, first tune the SQL. Then, tune to the application to get rid of unnecessary SQL. Then and only then, consider tuning the Buffer Cache to segregate "bad" tables to the RECYCLE pool or "pinning" tables to the KEEP pool. Reversing the order is a great way to convert a happy application capable of running on a small server to an unhappy application demanding a huge server... on 10/21/03 5:21 AM, Arup Nanda at [EMAIL PROTECTED] wrote: > Never. Altering the table to cache does not gurantee that it will be always > be available in the cache. It simply means the table will be placed in the > Least recently used end of the LRU list and it will age away as time goes > by, just like any other table. > > A better approach is to use KEEP pool and place teh table (and all other > tables that are accessed frequently) there. This is particualrly true for > datawarehouses wherethe lookup tables or small dimension tables can be > placed in KEEP pool. > > Ah, come tho think about it, actually there is one situation where I will > consider the CACHE option, when I restart the instance and want the hit > ratio to look good :) > > HTH. > > Arup Nanda > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 21, 2003 3:39 AM > > >> Hi all, >> >> when you would consider to put a table a cache... >> >> rgds >> >> gb >> >> >> Want to chat instantly with your online friends? Get the FREE Yahoo! >> Messenger http://mail.messenger.yahoo.co.uk >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: =?iso-8859-1?q?Gunnar=20Berglund?= >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- 858-538-5051 http://www.fatcity.com >> San Diego, California-- Mailing list and web hosting services >> - >> To REMOVE yourself from this mailing list, send an E-Mail message >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
RE: Cache a table
>From 9.2 doc: The LRU Algorithm and Full Table Scans When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table. Waleed -Original Message- Sent: Tuesday, October 21, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Mike: I guess we are aware there is no concept of LRU or MRU in current versions of Oracle and I don't think CACHE option will influence the behavior. With the new algorithm the MFU blocks are already in the hot end (unless they are read using CR read in that case they will be in cold end since we set the _db_aging_freeze_cr to TRUE) and we don't need to cache the blocks explicitely. You can monitor the behavior of this using the X$BH (espicially the last two columns TCH and TIM). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
RE: Cache a table
Mike: I guess we are aware there is no concept of LRU or MRU in current versions of Oracle and I don't think CACHE option will influence the behavior. With the new algorithm the MFU blocks are already in the hot end (unless they are read using CR read in that case they will be in cold end since we set the _db_aging_freeze_cr to TRUE) and we don't need to cache the blocks explicitely. You can monitor the behavior of this using the X$BH (espicially the last two columns TCH and TIM). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
Re: Cache a table
when the table is 1] frequently accessed 2] relatively small Regards B S Pradhan On Tue, 21 Oct 2003 Gunnar Berglund wrote : >Hi all, > >when you would consider to put a table a cache... > >rgds > >gb > > >Want to chat instantly with your online friends? Get the FREE Yahoo! >Messenger http://mail.messenger.yahoo.co.uk >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: =?iso-8859-1?q?Gunnar=20Berglund?= > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (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). You may >also send the HELP command for other information (like subscribing).
RE: Cache a table
Hi, I'm sure you know this but you had some finger trouble there. Just to clarify it for others; Tables with the CACHE option are placed at the Most Recently Used end of the LRU list. Cheers, Mike Hately -Original Message- Sent: 21 October 2003 12:21 To: Multiple recipients of list ORACLE-L Never. Altering the table to cache does not gurantee that it will be always be available in the cache. It simply means the table will be placed in the Least recently used end of the LRU list and it will age away as time goes by, just like any other table. A better approach is to use KEEP pool and place teh table (and all other tables that are accessed frequently) there. This is particualrly true for datawarehouses wherethe lookup tables or small dimension tables can be placed in KEEP pool. Ah, come tho think about it, actually there is one situation where I will consider the CACHE option, when I restart the instance and want the hit ratio to look good :) HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 3:39 AM > Hi all, > > when you would consider to put a table a cache... > > rgds > > gb E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
Re: Cache a table
Never. Altering the table to cache does not gurantee that it will be always be available in the cache. It simply means the table will be placed in the Least recently used end of the LRU list and it will age away as time goes by, just like any other table. A better approach is to use KEEP pool and place teh table (and all other tables that are accessed frequently) there. This is particualrly true for datawarehouses wherethe lookup tables or small dimension tables can be placed in KEEP pool. Ah, come tho think about it, actually there is one situation where I will consider the CACHE option, when I restart the instance and want the hit ratio to look good :) HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 3:39 AM > Hi all, > > when you would consider to put a table a cache... > > rgds > > gb > > > Want to chat instantly with your online friends? Get the FREE Yahoo! > Messenger http://mail.messenger.yahoo.co.uk > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: =?iso-8859-1?q?Gunnar=20Berglund?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). You may also send the HELP command for other information (like subscribing).
