Re: Data Warehouse and DB Block Buffers

rad_at_earthling.net wrote in message <6kuqb6$g2e$1_at_nnrp1.dejanews.com>...
>I'm a UNIX systems guy, not a DBA. That said..
>
>Does a data warehouse take much advantage of db block buffers? If it is
>mostly doing full tablescans that are not repetitive from one job to the
next,
>will Oracle be reusing any data in the db block buffers? If a DW does
utilize
>db buffers, how so?

To objectively determine at what level your warehouse stops benefiting from an increase in db_block_buffers,

1. Bounce the instance,
2. Issue a bunch of representative queries,
3. Query the v$sysstat table as in the following;

select trunc((1-(sum(decode(name, 'physical reads',value,0))/ (sum(decode(name,'db_block gets',value,0))+ (sum(decode(name,'consistent gets',value,0))))))*100) "Buffer Hit Ratio" from V$sysstat;

4. Increase the db_block_buffers parameter in init.ora. 5. Bounce the instance, requery, etc.... Until the reported buffer hit ratio levels off.

Then use whatever RAM is leftover for other SGA boosters such as sort_area_size and sort_area_retained_size which would be useful if users issue 'order bys' in their queries (almost a given in a warehousing environment).
This will keep sorting in memory (hopefully) and avoid costly direct disk writes.

>Also, when loading data into a single table on a DW, will the data be
placed
>contigously physically? That is, will full tablescans take advantage of
read
>ahead cache on a high end disk array?
>
>I'm on HPUX 10.2 and Oracle 7.3.4.

If the table is comprised of more than 1 extent or if the table grows (extends) dynamically
during the load then there is no guarantee that the data is placed contiguously.
You may actually benefit from exactly the opposite condition if the table is striped and/or partitioned so that
multiple spindles and controllers can simultaneously service multiple requests on different parts of a single table.

To reduce the impact of full tablescans make sure your db_block_size is 8K or larger and that the
db_file_multiblock_read_count is set appropriately.

Regards,
Craig M. Wall Received on Mon Jun 01 1998 - 14:17:36 CDT