About EXPDP/IMPDP Parallel Import export detailed test results and parallel parameters of the correct understanding!! __ Test

2010-08-06 20:53 about EXPDP/IMPDP Parallel Import export detailed test results and parallel parameters of the correct understanding.

Due to be ready to do a data migration of around 120G database, use EXPDPAnd IMPDP did a series of tests.
Export environment 4CPU AIX p4-750m 16G Memory
Import Environment 4CPU AIX p6-4g 32G memory 4CPU can be virtual out of 16 threads, you can see 16 virtual CPUs
Storage is the same DS4300 24 block 146G 15K, are all using bare devices, stand-alone import
Exporting tests
Export scripts, modify only parallel=3 numbers, import the same
Nohup EXPDPSystem/manager schemas=kdjm directory=dump_files parallel=3 dumpfile=kdjm2008-11-28_%u.dmp logfile=nnsiexp2008_12_ 28.log&
Export Time Score
1 Parallel Files 1:05
2 parallel 0:56:36
3 Parallel 0:30:41
4 Parallel 0:51
6 Parallel 1:21
Note that no number of parallel values can be exported quickly, period using Sar-ud 5 1000, monitoring disk I/O situation, found in the fastest 3 pipeline time I/O wait for 40-50, 1 pipeline time I/O wait only 5-10, 6 pipe time is 70-80 of I /o Wait, so I think whether it is that kind of platform export to want to faster, must crush I/O ability, try to make I/O wait between 30-50, too much I/O ability may decline, it seems that on this platform, 3 pipeline is the best hehe, The parallel should refer to the number of I/O processes in the generated data file process, or the number of files if the%u parameter is specified.
Import Time:
Import Script
Nohup IMPDP system/manager schemas=kdjm directory=dump_files parallel=12 dumpfile=kdmj2008-12-11_%u.dmp logfile= kdmj2008-12-11.log&
Oracle parameter configuration when importing, when exporting, it seems to have no effect on any parameters OH
Alter system set DB_FILE_MULTIBLOCK_READ_COUNT=256 Scope=spfile;
Alter system set PGA_AGGREGATE_TARGET=4G Scope=spfile;
Alter system set SHARED_POOL_SIZE=4G Scope=spfile;
Alter system set DB_CACHE_SIZE=18G Scope=spfile;
Alter system set SGA_MAX_SIZE=24G Scope=spfile;
Alter system set SGA_TARGET=24G Scope=spfile;
Alter system set PROCESSES=400 Scope=spfile;
Sort Area =1.5g
Alter system set sort_area_size=1610612736 Scope=spfile;
Import time-consuming results
1 parallel, 1 import files 11:27:21
4 Parallel, 4 import files 6:12:32
8 Parallel, 4 import files 4:42:45
12 Parallel, 3 import files 3:42:27
14 Parallel, 3 Import files 4:40:13
16 Parallel, 2 import files 4:39:07
See no, import select reasonable parameters, from 11 hours to 3 hours a little more, the difference is very large, such a gap, as far as possible in the import of the press I/O pressure, is not clear. In fact, when the second half of the import, the SAR monitoring of the I/O pressure is not large, what is the impact of the speed of the import. I saw an error statement that helped me unravel the secret, the statement that made the error is the statement that created the index
This is the error of the statement, should be developed incorrectly to create the index table space to the sysaux, resulting in an error, attention to the last parallel 8
Ora-31685:object type INDEX: "Dbsnmp". Dk_werr "failed due to insufficient privileges. Failing SQL is:
CREATE INDEX "Dbsnmp". Dk_werr "on" KDMJ "." Dk_werr "(" Scy "," Awerr ") PCTFREE Initrans 2 Maxtrans 255 STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT) tablespace "Sysaux" PARALLEL 8
This error, let us know that when the import specified parallel value is actually used to create the degree of parallelism of the index, so the import time to choose a higher degree of parallelism can greatly improve the speed of the creation of the index, thus speeding up the speed of the import. In fact, from the import data, no matter how many parallel values you choose, are all in the 1 hour or so the data is all imported, then query data can be queried, but no index, regardless of that way of import waste time, the most is the time to create an index, we modify the creation of the index of parallelism, so that the speed of the creation of the index greatly increased, Of course the increase to more values, will generate the I/O and the lock and so on competition, thus causes the speed to drop, we saw 14 parallel values and 16 parallel values are not as good as 12, in addition to point out that the import of the parallel value and the export of the parallel value can be completely different, It is estimated that many people, as I have previously understood, think that the number of import exports is strictly equal, but the manual recommends that the number of imports be equal to the number of exports.

If you want to import a reasonable parallel value, pass the test, I think is the number of available CPUs (whether you are virtual or multi-core) the value of the 60-70% is a better value. Of course, if it is possible to test to determine an optimal value bar.