Search This Blog

2008-04-18

Oracle Database Import: Strategies for optimum performance

Following options and tuning are able to make Oracle dump file import to be as fast as possible regardless it is using imp, impdp, sqlldr, or other utilities. Most of them are applicable to imp, and impdp however.
  1. Separate tables, and indexes/constraints import
  2. Simultaneous export and import via pipe (mknod, exp file=pipe_file direct=y, imp file=pipe_file buffer=)
  3. Create RAM disk and mount it to keep following files, online redo log, undo tablespace, temp tablespace, system dbf
  4. Transportable tablespace import with exp, or expdp
  5. Multiple imp, or impdp
  6. Tune up SGA & PGA, sort area according to memory constraint
  7. Ensure not consuming memory swapping area, else terminate other process, reduce SGA, or set swap mount point to as small as possible (OS will not able to store UNIX crash dump)
  8. Ensure NFS throughput is higher than total parallel exp/expdp throughput. It must be able to handle all concurrent export. Approach to increase network (wire, or fiber) throughput are aggregate port, multiple cards, Gb trunk, compressed-node-special-file (increase by 5x for data)
  9. Ensure root disk has near 0 activities, especially if swap is located in same physical disk as root disk
  10. Use "kill -SIGTSTP PID" and "kill -SIGCONT PID" to suspend and resume exp/imp process if require so you can identify contention in V$SESSION_WAITS while resolving it in real time without terminating active process
  11. Do not set autoextend on for dbf file. Size them manually
  12. Increase online redo log to switch every 5 - 15 min (benchmark throughput different to determine their different), 1 member (use less mount points), more groups (no contention)
  13. Disable background jobs, for both database (dbms_job) and UNIX (cron)
  14. Practice the whole procedure for 6 times to identify further scenario, constraint, bottleneck, tuning
  15. Tune NFS configuration with larger block size, or check documentation for NFS tuning. This can increase the throughput by 80% compare to initial setting

No comments:

Blog Archive