ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化数据存储笔记

优化数据存储笔记

原创 Linux操作系统 作者:yxg0313 时间:2008-04-07 11:16:22 0 删除 编辑
优化数据存储笔记

 

一 、PCTFREE值计算

1、无数据估计

表最大行长

select table_name "TABLE NAME" ,sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)+1,'D',7,data_length)) "MAX LENGTH" from dba_tab_columns where wner= upper('&schema_owner') group by table_name having sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)+1,'D',7,data_length))>0 order by 2 desc ,1;

估计每列的字节数:average row length

Average growth =(maximum row length – average row length)/2

PCTFREE=maverage growth /maximum row length * 100

2、有数据估计

Analyze table inv.mtl_item_categories  estimate statistics sample 30 percent ;

select table_name ,avg_row_len from dba_tables where wner='INV' and table_name=upper('mtl_item_categories');

PCTFREE=(average growth – average row length)*100/maximum row length

 

二、高水位线 high-water mark HWM

alter  table mtl_item_categories deallocate unused;

 

三、分析表和表统计

 

1、查询属于INV模式的所有表

select  table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed from dba_tables where wner='INV';

2、删除统计数据

exec dbms_stats.delete_schema_stats('INV');

exec dbms_utility.analyze_schema('INV','DELETE');

3、使用DBMS_UTILITY和ESTIMATE方法,必须分析每个表中30%的行的INV的全模式

exec dbms_utility.analyze_schema('INV','ESTIMATE',NULL,30);

4、查询DBA_TABLES,查看所有的统计

select  table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed from dba_tables where wner='INV';

 

四、DBMS_SPACE程序包

    获得关于物理段存储器的统计数据

表未使用空间的信息

declare

v_owner varchar2(30):='&object_owner';

v_name  varchar2(30):='&object_name';

v_type  varchar2(30):='&object_type';

v_tot_blks     number;

v_tot_bytes    number;

v_unused_blks  number;

v_unused_bytes number;

v_p1           number;

v_p2           number;

v_p3           number;

begin

  dbms_space.unused_space(v_owner,v_name,v_type,v_tot_blks,v_tot_bytes,v_unused_blks,v_unused_bytes,v_p1,v_p2,v_p3);

  dbms_output.put_line('Object:'||v_owner||'.'||v_name||' - type:'||v_type);

  dbms_output.put_line('Total number of blocks ='||v_tot_blks);

  dbms_output.put_line('Total number of bytes ='||v_tot_bytes);

  dbms_output.put_line('Total number of unused blocks ='||v_unused_blks);

  dbms_output.put_line('Total number of unused bytes ='||v_unused_bytes);

exception when others then

  dbms_output.put_line(SQLERRM);

end;

/

 

生成特定表的块用法

declare

v_unf      number;

v_unfb     number;

v_fs1      number;

v_fs1b     number;

v_fs2      number;

v_fs2b     number;

v_fs3      number;

v_fs3b     number;

v_fs4      number;

v_fs4b     number;

v_full     number;

v_fullb    number;

v_owner    varchar2(30):='INV';

v_segment  varchar2(30):='MTL_SYSTEM_ITEMS_TL';

begin

 for B in (select tablespace_name,segment_name,segment_type,partition_name from dba_segments   where

           wner=upper(v_owner) and segment_name like '%'||upper(v_segment)||'%')  loop

 dbms_space.space_usage(v_owner,B.segment_name,b.segment_type,v_unf,v_unfb,v_fs1,v_fs1b,v_fs2,v_fs2b,v_fs3,v_fs3b,v_fs4,v_fs4b,

                        v_full,v_fullb,B.partition_name);

 dbms_output.put_line(v_owner || ' ' ||B.segment_type|| ' ' ||B.segment_name);

 dbms_output.put_line('------------------------------------');

 dbms_output.put_line('tablespace                  :'||B.tablespace_name);

 dbms_output.put_line('total unformatted bytes     :'||v_unf||'('||v_unfb||')');

 dbms_output.put_line('total blocks 0-25% free     :'||v_fs1||'('||v_fs1b||')');

 dbms_output.put_line('total blocks 26-50% free    :'||v_fs2||'('||v_fs2b||')');

 dbms_output.put_line('total blocks 51-75% free    :'||v_fs3||'('||v_fs3b||')');

 dbms_output.put_line('total blocks 76-100% free   :'||v_fs4||'('||v_fs4b||')');

 dbms_output.put_line('total full                  :'||v_full||'('||v_fullb||')');

end loop;

end;

/

五、检测和解决行链接

详见专门文档

六、索引段

    1、找出不对称的索引

select index_name,column_name from dba_ind_columns  where table_name='OE_ORDER_LINES_ALL';

    2、分析索引

analyze index OE_ORDER_LINES_N2 validate structure;

    3、确定是否不对称

select round(del_lf_rows_len/lf_rows_len*100) balance_ratio from index_stats where name='OE_ORDER_LINES_N2';

    4、重建索引

analyze index OE_ORDER_LINES_N2 rebuild online;

 

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

请登录后发表评论 登录
全部评论

注册时间:2008-03-06

  • 博文量
    34
  • 访问量
    45115