一条关于数据分布的SQL优化

SQL text：

select day_id,count(*) from oper_795.LIST_HOT_KDXQ_2012 group by day_id;

执行计划：

 

表的基本信息：

 

分析：day_id总共就3种不同值，执行计划是正确的，因为如果走索引的话，消耗的资源更大.走全表扫描是正确的.但是从表的基本信息上看，总共为380643条记录，但是却占用了差不多7G 的空间，这里明显有问题,说明数据的分布存在问题,应该是由于DML的频繁操作导致很多块都是不满的。使用Oracle自带的包dbms_space分析表数据的存储情况.

set serverout on;
declare
 TOTAL_BLOCKS_1               number;
 TOTAL_BYTES_1                number;
 UNUSED_BLOCKS_1              number;
 UNUSED_BYTES_1               number;
 LAST_USED_EXTENT_FILE_ID_1   NUMBER;
 LAST_USED_EXTENT_BLOCK_ID_1  number;
 LAST_USED_BLOCK_1            number;
 unformatted_blocks_1         NUMBER;
 unformatted_bytes_1          NUMBER;
 fs1_blocks_1                 NUMBER;
 fs1_bytes_1                  NUMBER;
 fs2_blocks_1                 NUMBER;
 fs2_bytes_1                  NUMBER;
 fs3_blocks_1                 NUMBER;
 fs3_bytes_1                  NUMBER;
 fs4_blocks_1                 NUMBER;
 fs4_bytes_1                  NUMBER;
 full_blocks_1                NUMBER;
 full_bytes_1                 NUMBER;
begin
 dbms_space.unused_space(SEGMENT_OWNER=>'OPER_795',SEGMENT_NAME=>'LIST_HOT_KDXQ_2012',SEGMENT_TYPE=>'TABLE',TOTAL_BLOCKS=>TOTAL_BLOCKS_1,TOTAL_BYTES=>TOTAL_BYTES_1,UNUSED_BLOCKS=>UNUSED_BLOCKS_1,UNUSED_BYTES=>UNUSED_BYTES_1,LAST_USED_EXTENT_FILE_ID=>LAST_USED_EXTENT_FILE_ID_1,LAST_USED_EXTENT_BLOCK_ID=>LAST_USED_EXTENT_BLOCK_ID_1,LAST_USED_BLOCK=>LAST_USED_BLOCK_1);
 dbms_output.put_line('TOTAL_BLOCKS='||TOTAL_BLOCKS_1||'; '||'TOTAL_BYTES='||TOTAL_BYTES_1||'; '||'UNUSED_BLOCKS='||UNUSED_BLOCKS_1||'; '||'UNUSED_BYTES='||UNUSED_BYTES_1 ||';'||'LAST_USED_EXTENT_FILE_ID='||LAST_USED_EXTENT_FILE_ID_1||';'||'LAST_USED_EXTENT_BLOCK_ID='||LAST_USED_EXTENT_BLOCK_ID_1||'; '||'LAST_USED_BLOCK='||LAST_USED_BLOCK_1||';' );
 dbms_space.space_usage(SEGMENT_OWNER=>'OPER_795',SEGMENT_NAME=>'LIST_HOT_KDXQ_2012',SEGMENT_TYPE=>'TABLE',unformatted_blocks=>unformatted_blocks_1,unformatted_bytes=>unforMatted_bytes_1,fs1_blocks=>fs1_blocks_1,fs1_bytes=>fs1_bytes_1,fs2_blocks=>fs2_blocks_1,fs2_bytes=>fs2_bytes_1,fs3_blocks=>fs3_blocks_1,fs3_bytes=>fs3_bytes_1,fs4_blocks=>fs4_blocks_1,fs4_bytes=>fs4_bytes_1,full_blocks=>full_blocks_1,full_bytes=>full_bytes_1);
 dbms_output.put_line('unformatted_blocks='||unformatted_blocks_1||'; '||'unformatted_bytes='||unformatted_bytes_1||'; '||'fs1_blocks='||fs1_blocks_1||'; '||'fs1_bytes='||fs1_bytes_1||'; '||'fs2_blocks='||fs2_blocks_1||'; '||'fs2_bytes='||fs2_bytes_1||'; '||'fs3_blocks='||fs3_blocks_1||'; '||'fs3_bytes='||fs3_bytes_1||'; '||'fs4_blocks='||fs4_blocks_1||'; '||'fs4_bytes='||fs4_bytes_1||'; '||'full_blocks='||full_blocks_1||'; '||'full_bytes='||full_bytes_1||';');
end;
/

 

TOTAL_BLOCKS=878080; TOTAL_BYTES=7193231360;
fs1_blocks=275; fs1_bytes=2252800;
fs2_blocks=48; fs2_bytes=393216;
fs3_blocks=1; fs3_bytes=8192;
fs4_blocks=824149;fs4_bytes=6751428608;
full_blocks=52235; full_bytes=427909120;

解释下上面的信息：表总共占用878080个块,使用率为80%~100%的块有275个块，使用率60%~80%的块有48个，使用率40%~60%的块有1个，使用率0%~20%的块为824149个，使用率100%的块为52235个，从这些信息可以看出使用率0%~20%的块占据绝大部分，表的数据分布严重不均衡.

建议：对表进行压缩操作.

1、alter table oper_795.LIST_HOT_KDXQ_2012 enable row movement;

2、alter table  oper_795.LIST_HOT_KDXQ_2012 move;

执行完上面两部步骤之后，SQL的执行时间从20分钟降到5秒,从下面的信息可以看出表的数据分布的块使用率是51235/52224大约为100%,当执行SQL的时候，I/0下降了878080/52224大约16倍，故SQL需要的时间也大幅下降.

TOTAL_BLOCKS=52224; TOTAL_BYTES=427819008;
fs1_blocks=0; fs1_bytes=0;
fs2_blocks=0; fs2_bytes=0;
fs3_blocks=0; fs3_bytes=0;
fs4_blocks=0; fs4_bytes=0;
full_blocks=51235;
full_bytes=419717120;

 

 

 

 

 

 

 

2.jpg

3.jpg

1.jpg

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

转载于:http://blog.itpub.net/26634508/viewspace-732339/