RE: raid 5 disaster

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 13 Aug 2004 08:06:41 -0400
Message-ID: <543DF856D23431489D4B8028C300FBAB08AC938D@exchsen0a1mb>

Mike,

Explain to them that they are using a screwdriver to drive nails. They chose the wrong tool for the job. It is not a database issue. It is an application issue.

That being said, show them how to spool data via a select statement rather than using dbms_output. I would even convert one "report" for them and run the two versions with timing turned on to show them how much faster it is.

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Michael McMullen [mailto:ganstadba_at_hotmail.com] 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 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_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


-----------------------------------------------------------------

----------------------------------------------------------------

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

Received on Fri Aug 13 2004 - 07:02:30 CDT