Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Data Warehouse and DB Block Buffers

Re: Data Warehouse and DB Block Buffers

From: Bert Scalzo <bscalz01_at_7-11.com>
Date: Tue, 2 Jun 1998 11:07:34 -0500
Message-ID: <6l186q$797$1@news.ses.cio.eds.com>


I am working on a large data warehousing project nearly identical to your environment. We have found out quite a few items that may help you out.

>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 a certain extent (no pun intended). You need to have a large db buffer cache, but the hit ratio becomes more important than the size. With larger block sizes (16 K max on HP-UX 10.20), each block in the cache takes up a lot of real space. So keep the PCTFREE and PCTUSED set such that data blocks stay full (e.g. PCTFREE=5 and PCTUSED=85, although keep close eye on PCTFREE to avoid excessive chaining).

>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?

Data proximity is paramount. This has nothing to do with initial and next extents
as earlier responses discussed. What becomes important is that rows needed are stored together physically in the same blocks. You could accomplish this in
one of two ways: single table clusters or loading data in sorted order. I chose
not use use the single table cluster since we have lots of insert/update activity.
We re-loaded the data sorted by the primary keys column order. Our OLAP reports ran in 5 minutes instead of 45.

>Thanks for the reply. We have DB block buffers set to 500MB and are using
>PQO. The stripe size in Oracle is set to 16K though the raid stripe is set
to
>64K. (recommended by HP) Can the Oracle multi block read count be set to
>64k?

Be careful with PQO. How many processors do you have? On a 4 CPU K class machine, we found PQO to make things worse. On our 12 CPU V class, PQO is yielding desired effects. Also review the 4 or 5 parallel init.ora paraemters,
they make a huge difference in results (i.e. just setting table degree parallel is
insufficient).

The stripe size settings are also key. If set wrong, a single IO request could cause
2-3 actual IO's based upon stripe boundary mismatches due to overheads. This is way to comples for me to detail in this message. But I would suggest careful
review on the stripe size settings.

>We have read tablespaces on RAID5, indexes on RAID1/0 and logs on RAID1.

We are switching from RAID-5 to RAID-1 across the board. RAID-5 is nice in theory, but it just doesn't deliver in a warehouse.

>My next question is UNIX buffer cache. It is set to 200MB. Would Oracle
>benefit from UNIX cache (using jfs filesystems)? I would think it would
add
>unnecessary overhead since Oracle has it's own buffers. What about "Direct
>IO" on HPUX?

We went RAW across the board for Oracle data files. We found a noticable improvment (we gace that extra 200 megs to db buffer cache). But RAW does complicate the backup/recovery scenario.

Go to Oracle 8.0.4.1 asap. Just the star transformation optimization alone improved our OLAP report run times 500%-1000%!!! Early partitioned table/index testing has shown another 200-400% improvement.

8.04 is stable, get there ...

Bert Scalzo
Oracle DBA
EDS (Dallas)
(972) 604 - 9274 Received on Tue Jun 02 1998 - 11:07:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US