| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Michael McMullen
Sent: Thursday, August 12, 2004 2:21 PM
To: oracle-l_at_freelists.org
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 15minutes. 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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |