Ok, another volley.
I have the same objects and conditions as described below (default block size 8k and
one 16k tablespace with one table).
The 16k table is small ( create table blah (col1 varchar2(10), col2 number) tablespace
my16ktbsp storage(buffer_pool KEEP);)
-- insert data
begin
for i in 1..10000
loop
insert into blah values ('whatever',1);
end loop;
end;
/
commit;
-- result is 10000 records in table blah.
select * from blah;
-- I ran this several times.
Now query for buffer info shows...
SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
16K SUBCACHE BLAH 32
16K SUBCACHE BLAH 32
So, it strongly appears that even though I specified buffer cache of keep, it still
has to go in cache of same block size.
>> [EMAIL PROTECTED] 02/20/03 04:30PM >>>
John,
My earlier assumptions were wrong (usually are, that's why I test).
I set up a 9.2 instance with these parameters:
db_cache_size=300M
db_keep_cache_size=40M
db_16k_cache_size=40M
db_block_size=8192
Then, I created a tablespace MY16KTBSP with a 16k block size.
Then, create table TESTA tablespace MY16KTBSP storage(buffer_cache KEEP);
And this works. Does this really utilize the keep buffer cache? I'm not sure because
it also worked in another test when I had no keep buffer. I'm not sure how to tell
which buffer the blocks are in right now, maybe another list user can help us both
with that.
However, I just read your question again and realized I'm not anywhere in the
neighborhood of addressing your issue which is your 'waits'.
We aren't concerned with the number of waits (yes, but not only), but also the time
spent waiting and why.
A lot more information is needed really. Size and content of the table? Other
indexes on the table? Are statisics up to date and accurate? Is use of this index
really appropriate, sometimes full table scan is faster.
Something else maybe another person could address, I'm not sure that db file
sequential reads are physical only. They may be physical and logical depending on
your blocks already being read into buffer.
I'm thinking it doesn't matter so much which buffer, default, keep, or 16k is being
used, if further analysis/testing shows benefit of this index being in cache, use keep.
Test available scenarios a few times and use which is best.
>>> [EMAIL PROTECTED] 02/20/03 10:54AM >>>
Hi John,
If I'm understanding what I'm reading from the 9i Concepts Guide, I think that
non-default block size objects can only go into the cache with the same block size
(meaning it can't go into keep buffer). I'm making this assumption because we can't
create a tablespace of 16k blocksize until db_16k_cache exists. However, I'm working
on testing this today and will reply with results.
Darrell
>>> [EMAIL PROTECTED] 02/19/03 01:50PM >>>
I have indexes in a 16k page size tablespace.
I have the following init.ora parameters:
db_block_size=4096
db_cache_size=600M
db_keep_cache_size=200M
db_16k_cache_size=200M
If I alter an index to put it in the keep pool, how does Oracle hande the
discrepancy between the 4k default keep buffer and the 16k index block size?
Am I better off keeping the index in the 16k cache or in the db_keep_cache
pool?
Since statspack shows:
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
db file sequential read 2,725,553 0 40,710 15
355.2
I assume my indexes should be cached more to reduce the waits.
Is that correct?
Any advice would be appreciated.
Thanks
John Baylis
Database Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada
> (604) 697-6476 (Office)
> (604) 313-6054 (Cell)
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
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: Darrell Landrum
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: Darrell Landrum
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).
