RE: raid 5 disaster

Raid5 is notoriously slow for writes and instead of just selecting and
spooling the data they are making several copies of the data (with logging
to boot) converting the statement to simple sql scripts with spool
statements followed by the select statements will avoid the write penalty of
raid5 and will gain the performance benefit of only using the sql engine (no
context switching etc.). Now if they are not inclined to rewrite the reports
you can always throw money at the problem and switch from raid5 to raid 10
which will speed everything up tremendously but it isn't the optimal
solution as you will see a better performance gains be redesigning the
reports (you could also do both if you want to shock and amaze them).

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael McMullen
Sent: Thursday, August 12, 2004 2:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: raid 5 disaster

Oracle 9.2.0.3
solaris 5.8
raid 5 T3's

I have a server which was created for adhoc purposes. What has happened is
this. Instead of users running adhocs, they have created a package which
creates tables (all logging of course) based on their adhoc query. The
package then uses dbms_output.put_line to write the created table to a file
which then ftp's the file to the appropriate client. Roughly 200 reports are
written a day and most would be over 1 million lines and total file size
would be ~10Gb. Of course everybody wants their report by 9:00 a.m so the
box is routinely pegged at 60% i/o wait.
So everybody is complaining about database performance. I have stated that
dbms_output was probably the worst way to implement this. As I recall
dbms_output will write out one line at a time. Now would the use of
dbms_output affect other ddl. A truncate of a small table can take 15
minutes. Or is that just a function of the i/o waits? I guess I'm looking
for some really technical explanation as to why there performance is so bad.

Thanks
Mike

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx 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
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: