Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: raid 5 disaster

RE: raid 5 disaster

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 13 Aug 2004 09:20:20 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKKEDKFDAA.mwf@rsiz.com>


I'm trying to think how to make this into a bit more of a Rube Goldberg.

Hmm.......... (that was me thinking)........

Okay, I'm not going to baarf about the FRF, but maybe you could have them upgraded to R50.

Ah, let's see. Okay -- on each client PC, create a local database instance. Run the queries remote from each PC creating the "adhoc" table locally. Then you can run the put_line package locally, so you will have parallelized the process to each individual client recipient. If they still have a problem, they either need a "bigger" client machine or more network bandwidth. (Not your fault, you're just the DBA.)

When each report put_line is done, you can offline the tablespace the "adhoc" tables were created in and drop it including contents. (You can create the tablespace again tomorrow just before you create the "adhoc" table(s) for the query. Thay way you won't have any messy distributed recovery problems and if they manage to trash the local database you just build a new empty one. Since you don't really care about recovery, the local databases can have all the don't log a damn thing switches flipped on and you can go R0 or JBOD. Heck, upgrade each client machine with a Platypus drive. I'm assuming if 200 take up only 10GB, no single one would exceed 8 GB, so your local database can be a memory speed database.

Hmm. I suppose that maybe they create indexes on the "adhoc" tables in preparation to create the "put_line" reports? Then again, maybe not. (Do they create the "adhoc" table in one order and then report on it without benefit of indexes in some other order?)

Since this will end the serial creation of ad hoc tables on YOUR database, any collisions there should be greatly reduced, and both the need for truncating tables and time to truncate tables on YOUR database will be just fine (unless the report load is actually insignificant and your OLTP load is actually the problem).

Now if that last parenthetical is the actual problem, use the standby recovery, cancel, clone, rename STRATEGY (implemented either by hand, with Dataguard, or with any commercial tools or streams type solution you care to use -- the STRATEGY is the same), so that you segregate any report problems from problems you might otherwise be having with YOUR database. (OLTP and reporting on the same database is like having a "toaster-freezer" appliance. The ice cream melts and you just can't get those pop tarts crisp.)

After you do all this, either your reports will start routinely arriving on time or they won't. But just think if it works: You'll have permanent job insurance, immune to any new person or outsider even understanding its architecture in an economic amount of time. Plus, there is a good chance that you will have unburdened your FRF some so it can perform the way it should!

mwf

(I lied, I couldn't help baarfing.)

-----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 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 - 08:16:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US