Re: Export resource usage

Dustin Roberts wrote:
> We do the exporting from production to a test environment. The data is
> definitely different on the production environment when compared to the
> test environment. Some of the tables we export from do experience
> heavy DML activity, so the rollback concern is definitely possible.
> Other tables may only take 10 seconds to export the data, so the
> rollback problem almost becomes a non-issue. Is export a CPU intensive
> process also? Is there a good way to test that? I did an export on
> one of my local test machinges, but it only had about 500,000 rows, and
> was probably 60 - 80 MB, and I noticed that CPU usage in Linux was at
> 100%, but that would of course make sense, considering nothing else was
> really going on at the time so I'm sure Oracle just let it do whatever
> it needed.

It's been a while since I've tested exp, but it definitely can use up a lot of resources. If you think about it, it necessarily is doing a full table scan on every table, and those will be real physical disk accesses saturating your I/O, not to mention exp has to write. If anything else is going on in the db, then you can get excessive cpu usage as Oracle has to maintain read consistency and even pound on latches and such. I've noticed on multiple cpu unix machines exp will often wail on one cpu even with nothing else going on, and noticeable slowing of other things if they are going on. I suppose it depends on your configuration, though. The rules of thumb I use are: Do exp at times of minimal usage; Use DIRECT=Y compress=N recordlength=65535; If you can, kill off sessions where people forgot to log off (for my configurations, this can make a big difference in undo retention); If you are cloning or propagating, consider RMAN (since you have backups anyways), transportable tablespaces (just copy files!) or CTAS (I've seen this be faster than exp/ftp/imp with a fast network between disparate hardware); and always remember, this is a logical backup, which has different purposes than a physical backup.

I get about a G/minute writing to RAID5 (cough, cough), not the latest hardware, hp-ux.

jg

--
@home.com is bogus.
Whoa! Buh-Bye, Scott!  http://www.msnbc.msn.com/id/12467276/