201705251537trcsess.awk 工具，使用类似如下：awk -f trcsess.awk xxx.trc > xxx.more 1615tvdztat -i ....trc -o xxx.html201705261604select t1.pname,t1.pval1,t1.pval2 from sys.aux_stats$ t1where t1.sname='SYSSTATS_MAIN';select sum(t1.SINGLEBLKRDS) as count,sum(t1.SINGLEBLKRDTIM) time_ms from v$filestat t1;create table t as select rownum as id,round(dbms_random.normal*1000) as val1,100+round(ln(rownum/3.25+2 )) as val2,100+round(ln(rownum/3.25+2 )) as val3,dbms_random.string('p',250) as pad from all_objects where rownum<=1000order by dbms_random.value;update t set val1=null where val1<0;alter table t add constraints t_pk primary key(id);create index t_val1_i on t(val1);create index t_val2_i on t(val2);begin dbms_stats.gather_table_stats( ownname=>user, tabname=>'T', estimate_percent=>100, method_opt =>'for all columns size skewonly', cascade=>true); end;select t1.NUM_ROWS,t1.BLOCKS,t1.EMPTY_BLOCKS,t1.AVG_SPACE,t1.CHAIN_CNT,t1.AVG_ROW_LEN from user_tab_statistics t1where t1.TABLE_NAME='T'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN---------- ---------- ------------ ---------- ---------- ----------- 1000 44 0 0 0 266 /*NUM_ROWS 表中数据的行数BLOCKS 高水位线以下的数据块个数EMPTY_BLOCKS 高水位线以上的数据块个数AVG_SPACE 表中数据块的平均空闲空间（单位：字节）CHAIN_CNT 涉及行迁移、行链接的总行数AVG_ROW_LEN 表中平均每个记录的长度（单位：字节）*/select t1.column_name name,t1.num_distinct "#dst",t1.low_value,t1.high_value,t1.density dens,t1.num_nulls "#null",t1.avg_col_len avglen,t1.HISTOGRAM,t1.num_buckets "#bkt" from user_tab_col_statistics t1where t1.table_name='T';NAME #dst LOW_VALUE HIGH_VALUE DENS #null AVGLEN HISTOGRAM #bkt------------------------------ ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- --------------- ----------ID 1000 C102 C20B .001 0 4 NONE 1VAL1 457 C105 C22160 .002353264 481 3 HEIGHT BALANCED 254VAL2 6 C20202 C20207 .0005 0 4 FREQUENCY 6VAL3 6 C20202 C20207 .0005 0 4 FREQUENCY 6PAD 1000 203F2C5D523371586E695D456775533C565A522A4F3128234F43502F77353026 7E7E7438735D7A464E77205B7B79454D517E384069784521344735457E2F2120 .001 0 251 HEIGHT BALANCED 254select utl_raw.cast_to_number(low_value) as low_value,utl_raw.cast_to_number(high_value) as high_valuefrom user_tab_col_statisticswhere table_name='T'and column_name='VAL1';/* LOW_VALUE HIGH_VALUE---------- ---------- 4 3295*/begin dbms_stats.gather_index_stats(ownname =>user, indname =>'T_VAL1_I',estimate_percent=>100); end; select t1.INDEX_NAME,t1.blevel,t1.LEAF_BLOCKS,t1.DISTINCT_KEYS,t1.NUM_ROWS,t1.CLUSTERING_FACTOR,t1.AVG_LEAF_BLOCKS_PER_KEY,t1.AVG_DATA_BLOCKS_PER_KEYfrom user_ind_statistics t1where t1.TABLE_NAME='T';/*INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY------------------------------ ---------- ----------- ------------- ---------- ----------------- ----------------------- -----------------------T_PK 1 2 1000 1000 972 1 1T_VAL1_I 1 2 457 519 508 1 1T_VAL2_I 1 3 6 1000 174 1 29BLEVEL 访问叶子快而需要读取的分支块的数量，包括根块LEAF_BLOCKS 索引中的叶子块数量DISTINCT_KEYS 索引中唯一键值总数NUM_ROWS 索引中的键值数，对于主键来说 等同于 DISTINCT_KEYSCLUSTERING_FACTOR 表明有多少临近的索引条目知道不同的数据块。如果表中数据和索引的排序是相似的，那么 聚簇因子就小。最小值是表中非空数据的数据块总数如果表中数据和索引的排序迥异，聚簇因子就非常大。最大值是索引中的键值数*/1621create or replace function clustering_factor(p_owner in varchar2,p_table_name in varchar2,p_column_name in varchar2)return number isl_cursor sys_refcursor;l_clustering_factor binary_integer:=0;l_block_nr binary_integer:=0;l_previous_block_nr binary_integer:=0;l_file_nr binary_integer :=0;l_previous_file_nr binary_integer:=0;begin open l_cursor for ' select dbms_rowid.rowid_block_number(rowid) block_nr,'|| ' dbms_rowid.rowid_to_absolute_fno(rowid,'''|| p_owner||''','''|| p_table_name||''') file_nr '|| 'FROM ' ||p_owner||'.'||p_table_name||' ' || 'where '||p_column_name||' is not null '|| ' order by '||p_column_name; loop fetch l_cursor into l_block_nr,l_file_nr; exit when l_cursor%notfound; if (l_previous_block_nr <> l_block_nr or l_previous_file_nr <>l_file_nr ) then l_clustering_factor:=l_clustering_factor+1; end if; l_previous_block_nr:=l_block_nr; l_previous_file_nr:=l_file_nr; end loop; close l_cursor; return l_clustering_factor; end clustering_factor;1636select i.index_name,i.clustering_factor,clustering_factor(user,i.table_name,ic.COLUMN_NAME) as my_clstffrom user_indexes i,user_ind_columns ic where i.table_name='T' and i.index_name=ic.INDEX_NAME;INDEX_NAME CLUSTERING_FACTOR MY_CLSTF------------------------------ ----------------- ----------T_PK 972 972T_VAL1_I 508 508T_VAL2_I 174 1741732锁住对象统计信息dbms_stats.lock_table_stats(ownname=>user,tabname=>'table_name');dbms_stats.lock_schema_stats(ownname=>user);解锁对象统计信息dbms_stats.unlock_table_stats(ownname=>user,tabname=>'table_name')；dbms_stats.unlock_schema_stats(ownname=>user);
转载于:https://www.cnblogs.com/iyoume2008/p/6935160.html
相关资源：Oracle性能诊断艺术.pdf 高清下载