Oracle性能诊断艺术-读书笔记
20170525
1537
trcsess.awk 工具，使用类似如下：
awk -f trcsess.awk xxx.trc > xxx.more
1615
tvdztat -i ....trc -o xxx.html
20170526
1604
select t1.pname,t1.pval1,t1.pval2 from sys.aux_stats$ t1
where 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<=1000
order 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 t1
where 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 t1
where t1.table_name='T';
NAME #dst LOW_VALUE HIGH_VALUE DENS #null AVGLEN HISTOGRAM #bkt
------------------------------ ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- --------------- ----------
ID 1000 C102 C20B .001 0 4 NONE 1
VAL1 457 C105 C22160 .002353264 481 3 HEIGHT BALANCED 254
VAL2 6 C20202 C20207 .0005 0 4 FREQUENCY 6
VAL3 6 C20202 C20207 .0005 0 4 FREQUENCY 6
PAD 1000 203F2C5D523371586E695D456775533C565A522A4F3128234F43502F77353026 7E7E7438735D7A464E77205B7B79454D517E384069784521344735457E2F2120 .001 0 251 HEIGHT BALANCED 254
select utl_raw.cast_to_number(low_value) as low_value,
utl_raw.cast_to_number(high_value) as high_value
from user_tab_col_statistics
where 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_KEY
from user_ind_statistics t1
where 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 1
T_VAL1_I 1 2 457 519 508 1 1
T_VAL2_I 1 3 6 1000 174 1 29
BLEVEL 访问叶子快而需要读取的分支块的数量，包括根块
LEAF_BLOCKS 索引中的叶子块数量
DISTINCT_KEYS 索引中唯一键值总数
NUM_ROWS 索引中的键值数，对于主键来说 等同于 DISTINCT_KEYS
CLUSTERING_FACTOR 表明有多少临近的索引条目知道不同的数据块。
如果表中数据和索引的排序是相似的，那么 聚簇因子就小。最小值是表中非空数据的数据块总数
如果表中数据和索引的排序迥异，聚簇因子就非常大。最大值是索引中的键值数
*/
1621
create or replace function clustering_factor(
p_owner in varchar2,
p_table_name in varchar2,
p_column_name in varchar2)
return number is
l_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;
1636
select i.index_name,i.clustering_factor,clustering_factor(user,i.table_name,ic.COLUMN_NAME) as my_clstf
from 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 972
T_VAL1_I 508 508
T_VAL2_I 174 174
1732
锁住对象统计信息
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);
