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(*)
--
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(*)
=== message truncated ===
ATTACHMENT part 2
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
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
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-L FAQ:
http://www.orafaq.net
=== message truncated ===
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
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, 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
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: http://www.orafaq.net
--
Author: Richard Foote
INET: [EMAIL
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
the message BODY, include a line containing: UNSUB ORACLE-L
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
--- -- --
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
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 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--
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 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
Cache a table
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
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).
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
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
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
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
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... Cliff-Clavin-voice 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... /Cliff-Clavin-voice 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 the message BODY, include a line containing: UNSUB
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
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...
Cliff-Clavin-voice
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...
/Cliff-Clavin-voice
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
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 thin client can easily query these tables only
once and store
the results. Nope. No sirree...
Cliff-Clavin-voice
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...
/Cliff
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=subp2=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 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
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
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=subp2=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 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
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
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 of it
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
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
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 ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City
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
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 you want to be removed from). You may also send the HELP command for other information (like
RE: Cache a table
Title: Message
Just to add to Alex's point below, John Beresniewicz wrote a paper
some time ago (about twoor three years) that effectively explains the
different buffer pools, and providestest results of the mid-point
insertion algorithms:
http://www.orapub.com/cgi/genesis.cgi?p1=subp2=abs122
If you
don'talready have a user account on the orapub site, it's quickto
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
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
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 -- 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).
HOW TO CACHE THE TABLE??
I wonder how can I cache a table other than creating it with the cache option.?? Bunyamin K.Karadeniz Database Group / Information Systems Department HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729
RE: HOW TO CACHE THE TABLE??
alter table table_name cache; HTH Mark -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bunyamin K.KaradenizSent: Thursday, May 03, 2001 03:31To: Multiple recipients of list ORACLE-LSubject: HOW TO CACHE THE TABLE?? I wonder how can I cache a table other than creating it with the cache option.?? Bunyamin K.Karadeniz Database Group / Information Systems Department HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729
