Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how can I limit the disk I/O? I am not running the operation in parallel and have already read Oracle's Datapump Performance documentation. Is there anything else I can do?

This is on Windows 2008 R2 x64.

share|improve this question
2  
My problem turned out to be specifically with the initial stages of the export before it starts writing to the export files. I upgraded to 11.2.0.2 from 11.2.0.1, switched to statistical estimating and throttled the method to the lowest resource manager group and that seemed to solve the problem. The other answers to this question may be useful for others. – Leigh Riffel Jan 10 '11 at 15:09
Leigh add that as an answer :) – Brian Ballsun-Stanton Jan 10 '11 at 23:45

3 Answers

up vote 2 down vote accepted

Send it over the network

While extensive searching found no way to limit disk io natively (and serverFault will probably have more details on that) a "natural" way of limiting IO is to send the output directly across the network without ever touching the local disk. This then allows you to take advantage of all the network IO throttling resources out there. (If you want to be really sneaky, try to set up the network resource on the local machine, but that may be an overly-cute solution.)

share|improve this answer
+1 Great idea, unfortunately I'm already writing directly to a network share. I suppose I could request a remote drive on a slower disk or from a server with a slower network connection, but from my testing it looks like the problem is mostly at the start of the export when it is determining what/how much data is being exported. I changed from block estimates to statistics estimates, but that still didn't help enough. – Leigh Riffel Jan 9 '11 at 5:22
Nono, if you're writing directly to a network share, use something like: serverfault.com/questions/14806/… to limit the throughput of the link. – Brian Ballsun-Stanton Jan 9 '11 at 5:44
Not the answer to my specific problem, but certainly a good answer to the question as presented. – Leigh Riffel Jan 11 '11 at 0:45
up vote 1 down vote

De-tune the datapump

The docs list a number of methods for improving the performance of a data pump, including suggested alterations to the SGA, and considerations about encryption and compression. By inverting this advice, especially about compression and SGA, and then limiting CPU usage, IO usages may be reduced as a consequence.

share|improve this answer
Nice idea, but it seems that undoing most performance improving devices would decrease overall database performance not just the datapump operation making this unacceptable. – Leigh Riffel Jan 9 '11 at 3:43
up vote 0 down vote

My problem turned out to be specifically with the initial stages of the export before it starts writing to the export files. I upgraded to 11.2.0.2 from 11.2.0.1, switched to statistical estimating and throttled the method to the lowest resource manager group and that seemed to solve the problem. The other answers to this question may be useful for others.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.