ITPub博客
首页 博客 专栏 学院 社区 技术栈
发博

首页 > Linux操作系统 > Linux操作系统 > DDL对于直接路径导出影响

DDL对于直接路径导出影响

原创 Linux操作系统 作者：yangtingkun 时间：2011-04-24 23:54:23 0 删除 编辑

测试发现，当对表执行DDL后，EXP的直接路径，将会读取数据块，而EXPDP的直接路径则不会。

 

 

看一个简单的例子：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               3
free             2

SQL> create table kamus.t_buffer tablespace users
  2  as select * from dba_objects;

Table created.

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               3
free             2
xcur            14

运行直接路径导出：

acdbs:/oracle
 > exp kamus/password file=/oracle/kamus_buffer.dmp direct=y recordlength=65535 tables=t_buffer

Export: Release 10.2.0.4.0 - Production on Mon Mar 7 12:24:10 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table                       T_BUFFER      13754 rows exported
Export terminated successfully without warnings.

检查V$BH视图：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               3
free             2
scur           136
xcur            14

虽然是直接路径，但是Oracle仍然读取了数据文件中的block到内存中。

SQL> alter system flush buffer_cache;

System altered.

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
free           155

刷新共享池，再次运行导出：

acdbs:/oracle
 > exp kamus/password file=/oracle/kamus_buffer.dmp direct=y recordlength=65535 tables=t_buffer

Export: Release 10.2.0.4.0 - Production on Mon Mar 7 12:26:01 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table                       T_BUFFER      13754 rows exported
Export terminated successfully without warnings.

检查V$BH视图：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               3
free           141

这次直接路径生效了，Oracle没有读取数据块。

尝试MOVE TABLE：

SQL> alter table kamus.t_buffer move;

Table altered.

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
free           274
xcur            14

执行导出：

acdbs:/oracle
 > exp kamus/password file=/oracle/kamus_buffer.dmp direct=y recordlength=65535 tables=t_buffer

Export: Release 10.2.0.4.0 - Production on Mon Mar 7 12:26:56 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table                       T_BUFFER      13754 rows exported
Export terminated successfully without warnings.

再次检查V$BH：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
free           140
scur           133
xcur            15

同样直接路径导出读取了大量的数据块。

随后测试了ENABLE ROW MOVEMENT，在T_BUFFER表上创建或删除索引，这些和表相关的DDL语句都会使得随后的EXP直接路径方式读取大量的block到内存中。

测试发现，使用数据泵并不会产生相同的效果：

SQL> alter system flush buffer_cache;

System altered.

利用数据泵导出：

acdbs:/oracle
 > expdp kamus/password directory=d_output tables=t_buffer nologfile=y dumpfile=t_buffer.dp

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 07 March, 2011 15:18:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Starting "KAMUS"."SYS_EXPORT_TABLE_01":  kamus/******** directory=d_output tables=t_buffer nologfile=y dumpfile=t_buffer.dp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "KAMUS"."T_BUFFER"                          1.170 MB   13355 rows
Master table "KAMUS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for KAMUS.SYS_EXPORT_TABLE_01 is:
  /oracle/t_buffer.dp
Job "KAMUS"."SYS_EXPORT_TABLE_01" successfully completed at 15:18:18

检查V$BH视图：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               3
free           101
scur             5
xcur             3

可以看到，正常模式下，数据泵并不会读取大量的BLOCK。下面MOVE TABLE：

SQL> alter table kamus.t_buffer move;

Table altered.

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               2
free           243
xcur            14

执行数据泵的导出：

acdbs:/oracle
 > rm t_buffer.dp
acdbs:/oracle
 > expdp kamus/password directory=d_output tables=t_buffer nologfile=y dumpfile=t_buffer.dp

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 07 March, 2011 15:22:11

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Starting "KAMUS"."SYS_EXPORT_TABLE_01":  kamus/******** directory=d_output tables=t_buffer nologfile=y dumpfile=t_buffer.dp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "KAMUS"."T_BUFFER"                          1.170 MB   13355 rows
Master table "KAMUS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for KAMUS.SYS_EXPORT_TABLE_01 is:
  /oracle/t_buffer.dp
Job "KAMUS"."SYS_EXPORT_TABLE_01" successfully completed at 15:22:21

再次检查V$BH视图：

SQL> select status, count(*) from v$bh where ts# = 5 group by status;

STATUS    COUNT(*)
------- ----------
cr               4
free           121
scur             4
xcur            15

可以看到，内存中的数据块基本上没有增加。

随后又测试了创建索引，结论相同。

简单总结一下，对于直接路径导出，理论上是可以绕过DB_CACHE的，但是对于EXP的直接路径导出而言，可能存在例外的情况。也就是说，如果表执行过DDL操作，那么之后的第一次直接路径导出，会导致大量的BLOCK存储到DB_CACHE中。

 

 

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

0
0
上一篇： 复活节最短源代码比赛
下一篇： ORA-27069和OSD-4026错误引起数据库CRASH
请登录后发表评论 登录
全部评论
yangtingkun
暂无介绍

注册时间：2007-12-29

  • 博文量
    1954
  • 访问量
    10667489

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员