expdp/impdp数据迁移之后，块大小同为8K，块总数增长至5倍左右
linux 5.9 + oracle 10.2.0.5前几天用expdp/impdp做数据迁移，今天发现sql执行比原始库效率低很多，执行计划显示新库的一致性读和物理读大概是原始库的2倍，查了一下原因才发现，两个库块大小同为8K，而且数据量、表结构都是一样的，但新库表的块数大概是原始库表块数的5倍左右，原始库数据量大概4.6G，到新库就变成22G了，有点不明白，expdp、impdp迁移数据为什么会造成数据块暴增？
新库：
SQL> select table_name,blocks from user_tables where table_name = 'STATIC_AGG_10MIN';
TABLE_NAME BLOCKS
------------------------------ ----------
STATIC_AGG_10MIN 2783366
原始库：
SQL> select table_name,blocks from user_tables where table_name = 'STATIC_AGG_10MIN';
TABLE_NAME BLOCKS
------------------------------ ----------
STATIC_AGG_10MIN 592794
新库：
SQL> select segment_name,sum(bytes)/1024/1024/1024 from user_segments
2 where segment_name = 'STATIC_AGG_10MIN'
3 group by segment_name;
SEGMENT_NAME
--------------------------------------------------------------------------------
SUM(BYTES)/1024/1024/1024
-------------------------
STATIC_AGG_10MIN
21.9902344
原始库：
SQL> select segment_name,sum(bytes)/1024/1024/1024 from user_segments
2 where segment_name = 'STATIC_AGG_10MIN'
3 group by segment_name;
SEGMENT_NAME
--------------------------------------------------------------------------------
SUM(BYTES)/1024/1024/1024
-------------------------
STATIC_AGG_10MIN
4.6137085
action plan:
在2个库上收集如下信息：
select * from v$VERSION;
select count(*) from STATIC_AGG_10MIN ;set serveroutput on;
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&OWNER', '&TABNAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
select tablespace_name, allocated_space, reclaimable_space from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL')) Liu Maclean(刘相兵 发表于 2013-12-30 15:59 static/image/common/back.gif
action plan:
在2个库上收集如下信息：
刘大，不好意思，之前我忽略了数据迁移完这几天产生的数据量，我把有共有的数据天数数据先拿出来了，看每个分区的数据块，相差不到1倍。。
新库：
SQL> select * from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select table_name,partition_name,num_rows,blocks,last_analyzed,compression from user_tab_partitions
2 where table_name ='STATIC_AGG_10MIN'
3 order by 1,2;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZE COMPRESS
------------------------------ ------------------------------ ---------- ---------- ------------ --------
STATIC_AGG_10MIN PART_AGG10_20130601 919042 20690 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130602 1021546 23047 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130603 1299701 27653 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130604 1392618 29208 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130605 1455816 30634 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130606 1452309 31228 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131130 4903077 102572 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131201 4619192 97857 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131202 4350720 91076 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131203 4521521 93874 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131204 4169199 88251 30-DEC-13 ENABLED
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZE COMPRESS
------------------------------ ------------------------------ ---------- ---------- ------------ --------
STATIC_AGG_10MIN PART_AGG10_20131205 0 0 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131206 3538914 75258 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131207 2463629 52997 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131208 2971112 63960 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131209 3542887 76003 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131210 3964462 85494 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131211 3904791 84289 30-DEC-13 ENABLED
原始库：
SQL> select * from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select table_name,partition_name,num_rows,blocks,last_analyzed,compression from user_tab_partitions
2 where table_name ='STATIC_AGG_10MIN'
3 order by 1,2;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZE COMPRESS
------------------------------ ------------------------------ ---------- ---------- ------------ --------
STATIC_AGG_10MIN PART_AGG10_20130601 922971 12382 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130602 1032406 13914 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130603 1311091 16124 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130604 1363419 17003 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130605 1442167 17671 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20130606 1479444 17891 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131130 4872479 56219 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131201 4665184 53687 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131202 4352821 50150 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131203 4526756 51362 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131204 4171861 47777 30-DEC-13 ENABLED
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZE COMPRESS
------------------------------ ------------------------------ ---------- ---------- ------------ --------
STATIC_AGG_10MIN PART_AGG10_20131205 0 0 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131206 3552359 40672 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131207 2480805 28635 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131208 2973042 34785 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131209 3563145 41282 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131210 3957169 46711 30-DEC-13 ENABLED
STATIC_AGG_10MIN PART_AGG10_20131211 3918610 46169 30-DEC-13 ENABLED 原因呢？
共有的数据块数相差也很多啊。 qq69033472 发表于 2013-12-30 17:28 static/image/common/back.gif
知道问题出在哪拉，哈哈，谢谢刘大
问题出在哪？ 本帖最后由 lunar 于 2014-1-1 14:54 编辑
licharles 发表于 2014-1-1 14:07 static/image/common/back.gif
问题出在哪？
如果是迁移到11.2.0.2以后的版本，需要注意：
alter system set "_partition_large_extents"=false;
alter system set "_index_partition_large_extents"=false;
如果还是10g的，那么需要注意导出的参数和数据库对象的参数，例如 exp的COMPRESS，table等的 initial extent，next 等等 lunar说的应该就是原因
页:
[1]