If you really want a cool, technical explanation, here's what I did at such a site some years ago (where I had been told that it was NOT the disk IO system, because it was the Shark RAID-4 from IBM themselves, and the client had bought it for slightly less than a B-2 bomber would have cost them (complete with ground staff, runways, buildings and munitions) just two years earlier - so as I could clearly see, it was not the IO system. Right. So I did the 10046 thing on various important and/or demanding reports on the system and it showed that 80-90% of the time went to db file sequential/scattered reads. Then I set out to see whether the IO took such a large amount of time because there were many, but fast, IO's, or because there were few, but slow, IO's. In my case it was the latter - very slow IO's. My handwriting was faster than this zillion-Euro-IO-subsystem (it was more "sub" than "system", I would argue). Then Peter Gram from Miracle went to work, getting access to a small, embedded DB2-database built into the Shart that could actually show the number of IO's per 8-pack and even per disk. Since this was the old Shark, we knew it was using cheap, slow, old disks (7500 RPM). And 8-pack contains 6 data disks, one dedicated parity disk (RAID-4) and one hot spare. So a total of 300 IO's per second per 8-pack was to be expected. Lots of Excel-related work followed, we could see that many of the 8-packs were - surprise, surprise - trying to handle more than 300 IO's per second. Then I had the good fortune of being allowed to tell all this to the board, and one thing they understood was that if you start out bying 8-packs where 2 of the disks are not used for data, you've wasted 25% of your 8-pack investment up front. If, that is, you need the disks mirrored anyway (and of course this RAID-4 system was mirrored). Next, I told them how many of the IO's were sequential (small) writes and how that meant roughly a factor 4 of IO's on the disks (more waste of investment). Then I argued that having 33% more disks than required (8 disks where only 6 worked with data) also meant higher investments in cabinets, controllers, rooms, and what have you. They decided to buy a new system. They bought (for political reasons) the new Shark 800, which can of course be configured with the same, silly 8-pack/RAID-4 configuration) but thank God also as RAID-1+0. Which is what management wanted and had decided on the meeting. My job (and Peter Gram's) was over, and we left the building. A few months later I heard that it was running twice as fast as the old system. Only twice, I asked? Well, that could only mean that they were only getting the benefit of the new, faster 15000 RPM disks, but NOT getting the benefit of the RAID-1+0! So I asked the sys admin guy, and it turned out that he had never really believed all this bad-mouthing of RAID-4 (it had to be RAID-5, he insisted, because the IBM docs said so, even when he ackowledged the presence of a dedicated parity disk), so he had - believe it - configured the whole, new, very expensive Shart 800 SAN as ... RAID-4 (or 5 :-) ). So I got mad and told him what I thought, and he didn't care at all. Then I realised two things: 1) They would have to call us in again sooner because of this, 2) I could never convince this guy anyway. Hey, guess where my consulting outfit is making some decent money currently... and I'm not kidding. Long story, I know. But find out where the time goes, then find out why. The same goes for your considerations regarding dbms_output. It's really not enough to believe or think or to have heard something somewhere. Measure it, and then you can argue sense into most heads around you. Mogens Ken Naim wrote: > 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 //www.freelists.org/archives/oracle-l/ > FAQ is at //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 //www.freelists.org/archives/oracle-l/ > FAQ is at //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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------