一 、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;