[20130618]imp(impdp)以及lob的导入问题.txt
前几天要建立一个测试库,数据库很小50G,我想导入这么数据库exp/imp应该很快完成,结果我发现大约花了8个多小时.
我给自己的辩解是测试机器是虚拟机器,cpu2个,内存仅仅2G,性能不好,但是仔细观察我发现问题在于导入的数据lob
字段很多,大部分时间浪费在这个上面,我自己今天做一个测试:
1.建立测试:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id number, image blob);
create table t1 (id number, image varchar2(200));
insert into t select rownum id , lpad('a', 200, 'a') image from dual connect by level insert into t1 select rownum id , lpad('a', 200, 'a') image from dual connect by level commit;
$ exp scott/xxxx tables=(T,T1) file=t.dmp BUFFER=31457280
2.开始imp测试:
SQL> alter system flush shared_pool;
System altered.
SQL> drop table t purge ;
Table dropped.
SQL> drop table t1 purge ;
Table dropped.
$ imp scott/xxxx file=t.dmp BUFFER=31457280 full=y
--很奇怪,表T(包含blob),在imp期间,执行了SELECT /*+NESTED_TABLE_GET_REFS+*/ "IMAGE" FROM "T" WHERE ROWID = :1 ,10000次.
--插入语句INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("ID", "IMAGE") VALUES (:1, :2),10000次.
--而普通表不含blob,仅仅执行insert1次.通过对比可以发现,有无blob,两者导入的方式不同的.
--很明显导入T会很慢.特别是表很大的情况下.
3.impdp呢?
expdp scott/xxxx DUMPFILE=t.dmp directory=DATA_PUMP_DIR tables=(T,T1)
SQL> alter system flush shared_pool;
System altered.
SQL> drop table t purge ;
Table dropped.
SQL> drop table t1 purge ;
Table dropped.
--impdp的输出有点奇怪,EXECUTIONS=0,不知道impdp如何导入,不过很明显impdp显然没有这个问题.
4.补充一点imp的参数BUFFER.
--前面设置参数buffer=30,设置64k如何呢?
alter system flush shared_pool;
drop table t purge ;
drop table t1 purge ;
imp scott/xxxx file=t.dmp BUFFER=65536 full=y
--buffer=64K,表T1的插入次数变多,35次.
总结:看来以后blob字段多,不能使用exp/imp导入.
前几天要建立一个测试库,数据库很小50G,我想导入这么数据库exp/imp应该很快完成,结果我发现大约花了8个多小时.
我给自己的辩解是测试机器是虚拟机器,cpu2个,内存仅仅2G,性能不好,但是仔细观察我发现问题在于导入的数据lob
字段很多,大部分时间浪费在这个上面,我自己今天做一个测试:
1.建立测试:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id number, image blob);
create table t1 (id number, image varchar2(200));
insert into t select rownum id , lpad('a', 200, 'a') image from dual connect by level insert into t1 select rownum id , lpad('a', 200, 'a') image from dual connect by level commit;
$ exp scott/xxxx tables=(T,T1) file=t.dmp BUFFER=31457280
2.开始imp测试:
SQL> alter system flush shared_pool;
System altered.
SQL> drop table t purge ;
Table dropped.
SQL> drop table t1 purge ;
Table dropped.
$ imp scott/xxxx file=t.dmp BUFFER=31457280 full=y
SQL> column sql_text format a80
SQL> select sql_id,child_number,sql_text,Executions,buffer_gets from v$sql where module like 'imp%' and sql_text like '%IMAGE%';
SQL_ID CHILD_NUMBER SQL_TEXT EXECUTIONS BUFFER_GETS
------------- ------------ -------------------------------------------------------------------------------- ---------- -----------
4whs49hkxhfb5 0 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IMAGE" FROM "T" WHERE ROWID = :1 10000 10004
5cvkn9j6gnpwx 0 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("ID", "IMAGE") VALUES (:1, :2) 10000 11827
4gzcp6cntxyct 0 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T1" ("ID", "IMAGE") VALUES (:1, :2) 1 4256
--很奇怪,表T(包含blob),在imp期间,执行了SELECT /*+NESTED_TABLE_GET_REFS+*/ "IMAGE" FROM "T" WHERE ROWID = :1 ,10000次.
--插入语句INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("ID", "IMAGE") VALUES (:1, :2),10000次.
--而普通表不含blob,仅仅执行insert1次.通过对比可以发现,有无blob,两者导入的方式不同的.
--很明显导入T会很慢.特别是表很大的情况下.
3.impdp呢?
expdp scott/xxxx DUMPFILE=t.dmp directory=DATA_PUMP_DIR tables=(T,T1)
SQL> alter system flush shared_pool;
System altered.
SQL> drop table t purge ;
Table dropped.
SQL> drop table t1 purge ;
Table dropped.
SQL> select sql_id,child_number,sql_text,Executions,buffer_gets from v$sql where module like 'Data Pump Worker' and sql_text like '%IMAGE%';
SQL_ID CHILD_NUMBER SQL_TEXT EXECUTIONS BUFFER_GETS
------------- ------------ -------------------------------------------------------------------------------- ---------- -----------
20a3pxrvfxgvn 0 INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("SCOTT"."T" NOT XMLTYPE) ("ID","IMAG 0 5
E") VALUES (NULL,NULL)
c167tw26g2asb 0 INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("SCOTT"."T1" NOT XMLTYPE) ("ID","IMA 0 6
GE") VALUES (NULL,NULL)
--impdp的输出有点奇怪,EXECUTIONS=0,不知道impdp如何导入,不过很明显impdp显然没有这个问题.
4.补充一点imp的参数BUFFER.
--前面设置参数buffer=30,设置64k如何呢?
alter system flush shared_pool;
drop table t purge ;
drop table t1 purge ;
imp scott/xxxx file=t.dmp BUFFER=65536 full=y
SQL> select sql_id,child_number,sql_text,Executions,buffer_gets from v$sql where module like 'imp%' and sql_text like '%IMAGE%';
SQL_ID CHILD_NUMBER SQL_TEXT EXECUTIONS BUFFER_GETS
------------- ------------ -------------------------------------------------------------------------------- ---------- -----------
4whs49hkxhfb5 0 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IMAGE" FROM "T" WHERE ROWID = :1 10000 10004
5cvkn9j6gnpwx 0 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("ID", "IMAGE") VALUES (:1, :2) 10000 11814
4gzcp6cntxyct 0 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T1" ("ID", "IMAGE") VALUES (:1, :2) 35 4268
--buffer=64K,表T1的插入次数变多,35次.
总结:看来以后blob字段多,不能使用exp/imp导入.