Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: db block size, too big wasting buffer?
In article <998d28f7.0206301512.411700f9_at_posting.google.com>, you said
(and I quote):
>
> 1 - The number of index accesses - Oracle index range scans love large
> blocksizes, and on my 9i systems, I place indexes in a 32k tablespace.
And exactly how do you determine this "number of index accesses" BEFORE you build and use the database?
>
> 2 - The amount of random OLTP access - If you are randomly accessing
> 80-byte rows, it would be a huge waste of db_block_size to read-in
> unwanted row data with large blocksizes.
Have you got an example of one (1) commercial application that "randomly accesses" ANYTHING?
>
> (BTW, this quandry was the main reason for the multi-blockdsize
> feature of 9i)!
Oh no, it wasn't! Check Oracle's own doco.
> On an 8.1.7 Data warehouse, the choice is between 8k and 16k. If your
> db is heavily indexes,, you might think about 16k. I use this query:
>
> select sum(blocks) from dba_segments where segment_type = 'INDEX';
> select sum(blocks) from dba_segments where segment_type = 'TABLE';
You use these two queries to measure the "number of index accesses"? Can you elaborate on how to extrapolate from absolute number of tables + absolute number of indexes to number of index accesses?
And how do you exclude the non-application specific tables and indexes from these numbers? As in the ones owned by SYS, SYSTEM, etc.
-- Cheers Nuno Souto nsouto_at_optushome.com.au.nospamReceived on Mon Jul 01 2002 - 04:05:21 CDT
![]() |
![]() |