Re: Using NetApp Filers for a DWH

From: Richmond Shee <richmondshee_at_yahoo.com>
Date: Wed, 4 Aug 2004 16:46:51 -0700 (PDT)
Message-ID: <20040804234651.55714.qmail@web50502.mail.yahoo.com>

For your second question: The WAFL technology can have a disastrous effect on full table scans and fast full scans in Oracle10g. You can create this simple mouse trap:-

Create a table and load a bunch of data.
Flush the buffer cache.
Enable 10046 at level 8 for the session.
set timing on
set db_file_multiblock_read_count = 16
perform a full table scan against the table. (this is your baseline)
exit
login again
run a procedure that updates the 1st row of every other block (i.e. odd or even blocks)
repeat steps 2 to 6. Note the elapsed times and compare the two trace files.

According to my tests in Oracle10g, many db file sequential read events showed up in the trace file after the update. I have seen as much as 89% degradation in performance. NetApp blamed this on Oracle as a 10g bug. Oracle kinda admitting it, but until now there is no resolution. However, when the same test was ran in Oracle 9.2.0.4 it didn't produce this problem.

-Richmond Shee.

co-author of Oracle Wait Interface
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

Hemant K Chitale <hkchital_at_singnet.com.sg> wrote: I am looking for notes / experiences / suggestions about using [or NOT using] NetApp Filers
for a DataWarehouse [Oracle 9i].

Two areas of concern I have are :
1. Snapshot areas -- ie, the %age of blocks reserved in a volume to facilitate snapshots.
In a DWH, a very large [probably 100 to 150%] portion of the database blocks could be updated
in a day. How do we size space for Snapshots ? Would frequent Snapshots help ?

2. WAFL and "block-redirection" -- if the Filer actually writes each block seperately on updates,
what the Database might see as "contigous" blocks in an extent where the rows of the table
have been deleted and reloaded [ie all or almost all of the blocks have been affected], might very
well be randomly distributed across the disks. How would multiblock reads fare ?

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://web.singnet.com.sg/~hkchital

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Wed Aug 04 2004 - 18:42:34 CDT