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

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

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

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

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

再次检查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/，如需转载，请注明出处，否则将追究法律责任。

上一篇：复活节最短源代码比赛

下一篇： ORA-27069和OSD-4026错误引起数据库CRASH

请登录后发表评论登录

全部评论

yangtingkun

暂无介绍

注册时间：2007-12-29

博文量
1955
访问量

10523569

ITPub博客

DDL对于直接路径导出影响

最新文章