ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一条关于数据分布的SQL优化

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

原创 Linux操作系统 作者:Xuan_Baby 时间:2012-06-09 09:35:45 0 删除 编辑

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%的块占据绝大部分,表的数据分布严重不均衡.

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

1alter table oper_795.LIST_HOT_KDXQ_2012 enable row movement;

2alter 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 伪列Ora_rowscn
请登录后发表评论 登录
全部评论

注册时间:2012-03-09

  • 博文量
    12
  • 访问量
    30523