首页 > Linux操作系统 > Linux操作系统 > DDL对于直接路径导出影响
测试发现,当对表执行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/,如需转载,请注明出处,否则将追究法律责任。