优化数据存储笔记

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 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

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';

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

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;

• 博文量
34
• 访问量
45115