|
Oracle 10g
|
-Data Pump Export (Import) Utilities-
In this issue, I will analyze the loading/unloading of the data
by using Data Pump Export (Import) Utilities.
As mentioned in previous issue, Data Pump Export (Import) Utilities
are newly introduced in Oracle10g. Internal structure of the utilities
is totally different from the one of existing Export/Import functions.
Now, I will actually analyze if Data Pump Export (Import) Utilities
truly enable loading/unloading of the data quickly. One of the indispensable operations
for DBAs is data movement. However, it requires quite a long time
and effort to move terabytes of data. If Data Pump Export (Import) Utilities
enables quick loading/unloading of the data, it will be your ideal tool.
(Test environment)
OS : Miracle Linux 2.4.9-e.9.30mlsmp
Memory : 1GB
CPU : 4
ORACLE : Enterprise Edition Release 10.1.0.2.0
Steps to analyze Data Pump Export (Import) Utilities
1. Use Export to unload the data.
2. Use Export to directly unload the data.
3. Use Data Pump to unload the data.
4. Use Import to load the data.
5. Use Data Pump to load the data.
1. Use Export to unload the data
$ cat exp.sh
exp tpc/tpc file=/opt/u01/app/oradata/pump_dir/exp_normal.dmp \
owner=tpc log=/opt/u01/app/oradata/pump_dir/exp_normal.log
|
2. Use Export to directly unload the data
$ cat exp_d.sh
exp tpc/tpc file=/opt/u01/app/oradata/pump_dir/exp_direct.dmp
owner=tpc log=/opt/u01/app/oradata/pump_dir/exp_direct.log direct=y
|
3. Use Data Pump to unload the data
Tha main function of Data Pump is different from that of Export/Import.
For Export/Import, client processes (such as exp, imp, expdp, impdp)
perform I/Os of all dump files. For Data Pump, on the other hand,
background processes (such as ORACLE.EXE, oracle) perform I/Os of
all dump files. You have to be aware that the dump file needs to be
created in a location where Oracle can identify it, which means
DIRECTORY object.
Now, I create the DIRECTORY object.
SQL> conn / as sysdba
SQL> create directory pump_dir as '/opt/u01/app/oradata/pump_dir';
SQL> grant read,write on directory pump_dir to tpc ;
|
The SQL statements above create DIRECTORY object, read it to TPC user, and
grant write privilege.
Next, I unload the data by using Data Pump.
$ cat exp_dp_p2.sh
expdp tpc/tpc directory=pump_dir dumpfile=exp_pump_p2.dmp schemas=tpc \
logfile=exp_pump_p2.log parallel=2
|
expdp is a new export utility in Data Pump which performs the same of better
than exp.
(Note: exp command will not be supported in future releases.)
- DIRECTORY parameter specifies DIRECTORY object which creates dump file and log file.
- DUMPFILE parameter specifies the dump file name to be created as current FILE parameter does.
- SCHEMAS parameter specifies a specific schema as current OWNER parameter does.
- PARALLEL parameter is available in Enterprise Edition only and specifies the number
of worker processes operating in the background.
4. Use Import to load the data
$ cat imp.sh
imp tpc/tpc file=/opt/u01/app/oradata/pump_dir/exp_direct.dmp \
log=/opt/u01/app/oradata/pump_dir/imp.log full=y
|
5. Use Data Pump to load the data
This process is basically the same as the step 3, using Data Pump to unload the data.
impdp performs the same or better than imp.
(Note: imp command will continue be supported in future releases.)
$ cat imp_dp_p2.sh
impdp tpc/tpc directory=pump_dir dumpfile=exp_pump_p2.dmp schemas=tpc \
logfile=imp_pump_p2.log parallel=2
|
The results are as follows:
=============================================================================
Process Time (min) File size
----------------------------------------------- ---------- ------------------
1. Use Export to unload the data 47 19.86GB
2DUse Export to directly unload the data 32 18.69GB
3DUse Data Pump to unload the data 29 19.87GB
4DUse Import to load the data 63
5DUse Data Pump to load the data 26
=============================================================================
|
The results above prove that the loading/unloading of the data with Data Pump
is faster than that with Export/Import. However, the degree of the improvement
is not so remarkable as I have expected. Unloading of the data with Data Pump
slightly improves, and the loading of the data with Data Pump improves about 2.4 times.
In this test environment, Oracle data file and dump file exist on the same disk, and
that might cause contention between Read and Write I/Os. As a result, the improvement
might not be remarkable. In fact, disk busy rate hits 100% while I'm testing the function.
That's it for today.
Koji Shinkubo
|
|
