[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

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导入.