RE: raid 5 disaster

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@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: