[20170910]imp参数buffer的简单探究2.txt

[20170910]imp参数buffer的简单探究2.txt

--//exp,imp已经很少在用,如果存在8i估计还会用一下,下面因为别人遇到升级忘记家buffer参数(8i),导致导入缓慢,
--//当然还有许多因素,比如存在lob字段,不过还是简单探究参数buffer.
--//上个星期做了参数buffer的简单探究,发现缓存大小除以记录的最大长度=每次插入记录数量.
--//忘记测试缺省是多少,补充测试看看.

1.环境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

alter system set pga_aggregate_target=4G;
--//以前设置太小256M,因为测试环境,connect by方式建表经常报错,设置大一些.

create table t(x number, x2 varchar2(1000),x3 varchar2(1000))  SEGMENT CREATION IMMEDIATE;
insert into t select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e6;
commit ;
exec sys.dbms_stats.gather_table_stats ( OwnName => 'SCOTT',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

SCOTT@book> @ &r/tpt/seg2 scott.t
    SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS     HDRFIL     HDRBLK
---------- ----- ------------ ------------- ------------ ------------------- ------ ---------- ----------
       234 SCOTT T                          TABLE        USERS                29952          4        546

--//234M.

2.导出:
$ exp scott/book tables=T file=t.dmp direct=y buffer=1280000
Export: Release 11.2.0.4.0 - Production on Fri Sep 8 11:48:29 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table                              T    1000000 rows exported
Export terminated successfully without warnings.

3.测试导入:

--//如果检索NESTED_TABLE_SET_REFS,可以找到imp导入执行如下语句.
SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID        SQL_TEXT                                                       EXECUTIONS
------------- ------------------------------------------------------------ ------------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3"         1935
              ) VALUES (:1, :2, :3)

SCOTT@book> alter system flush shared_pool;
System altered.

SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
no rows selected

--//开始测试不同buffer数量的情况:
alter table t rename to t1;
//drop table t purge ;
alter system flush shared_pool;

--//导入不设置buffer参数量.
$ imp scott/book tables=T file=t.dmp

Import: Release 11.2.0.4.0 - Production on Mon Sep 11 09:02:19 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"    1000000 rows imported
Import terminated successfully without warnings.
--//导入很慢.

SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3"      66667
              ) VALUES (:1, :2, :3)

--//前面测试buffer=N,不同N的表格:

N             EXECUTIONS
----------------------------------
8388608       242
4194304       484
2097152       968
1048576       1935
524288        3876
-----------------------------------

--//1000000/66667=14.999925000374998125,每次15条
--//2022*15=30330
--//大约30*1024=30720

--//反向测试看看:
SCOTT@book> alter system flush shared_pool;
System altered.

SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
no rows selected

SCOTT@book> drop table t purge;
Table dropped.

$ imp scott/book tables=T file=t.dmp buffer=30720

SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3"      66667
              ) VALUES (:1, :2, :3)

--//基本一致.这也基本验证我的推断,为什么是30K不是很清楚,如果换1个表也许不对.放弃!!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2144763/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2144763/