create or replace procedure analyzetable(username varchar2,tablename varchar2) is
--表分析参数
rownum int; --行数
blocksnum int; --表块数目
emptyblocks int; --表中空快数目
chain int; --行链的数目（查看是否有行迁移）
avg_row_len int; --平均行长度
blocksize int; --数据库大小
usedspace number; --占用空间
acturespace number; --实际利用空间
usedpct number(4,2); --空间占用率
nowdate varchar2(20); --时间
--索引分析用到的参数
begin
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') into nowdate from dual;
blocksize:=8192;
dbms_output.put_line('----------------表分析('||nowdate||')---------------------------------');
dbms_output.put_line('');
dbms_output.put_line('计算块大小时，采取8K标准块，分析结果时，注意参考pct_free参数');
dbms_stats.gather_table_stats(upper(username),upper(tablename),CASCADE=>TRUE);
select num_rows,blocks,empty_blocks,chain_cnt,avg_row_len
into rownum,blocksnum,emptyblocks,chain,avg_row_len
from user_tables
where table_name=upper(tablename);
dbms_output.put_line('用户名: '||username);
dbms_output.put_line('表名: '||tablename);
dbms_output.put_line('行数: '||rownum);
dbms_output.put_line('块数: '||blocksnum);
dbms_output.put_line('空快数: '||emptyblocks);
dbms_output.put_line('行链: '||chain);
dbms_output.put_line('行平均长度: '||avg_row_len||'byte');
acturespace:=round(rownum*avg_row_len/1024/1024,3);
usedspace:=round(blocksnum*blocksize/1024/1024,3);
if(usedspace!=0) then
usedpct:=acturespace/usedspace*100;
else
usedpct:=0;
end if;
dbms_output.put_line(tablename||'表占用'||usedspace||'M的存储空间，实际使用'
||acturespace||'M,空间利用率为'||usedpct||'%');
--判断重建索引的理论依据
--1.删除的行数如占总的行数的30%，
--即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理
--2.如果”hight“大于4，可以考虑碎片整理
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') into nowdate from dual;
dbms_output.put_line('----------------索引分析('||nowdate||')---------------------------------');
dbms_output.put_line('');
/*for indname in(select index_name from user_indexes where table_name=upper(tablename)) loop
dbms_stats.gather_table_stats(upper(username),upper(indname.index_name));
end loop;
*/
for temp in(
select index_name,index_type,blevel,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,
to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS') analyze_time
from user_indexes
where table_name=upper(tablename)) loop
dbms_output.put_line('索引名: '||temp.index_name);
dbms_output.put_line('索引类型: '||temp.index_type);
dbms_output.put_line('索引深度: '||temp.blevel);
dbms_output.put_line('叶子块数量: '||temp.leaf_blocks);
dbms_output.put_line('唯一键数量: '||temp.distinct_keys);
dbms_output.put_line('avg_leaf_blocks_per_key: '||temp.avg_leaf_blocks_per_key);
dbms_output.put_line('avg_data_blocks_per_key: '||temp.avg_data_blocks_per_key);
dbms_output.put_line('分析时间: '||temp.analyze_time);
--分析索引
execute immediate 'Analyze index '||temp.index_name||' validate structure';
dbms_output.put_line('***********************');
for idx in(
select name,blocks,height,lf_rows,del_lf_rows
from index_stats) loop
if(idx.name=temp.index_name) then
dbms_output.put_line('索引名称: '||idx.name);
dbms_output.put_line('索引高度: '||idx.height);
dbms_output.put_line('索引块数: '||idx.blocks);
dbms_output.put_line('页块行数: '||idx.lf_rows);
dbms_output.put_line('页块删除行数: '||idx.del_lf_rows);
dbms_output.put_line('索引碎片率：'||round(idx.del_lf_rows/idx.lf_rows*100,2)||'%');
if(idx.height>=4 or idx.del_lf_rows/idx.lf_rows>0.3) then
dbms_output.put_line('建议重建索引： alter index'||temp.index_name||' rebuild nologging parallel 4 online');
end if;
else
dbms_output.put_line('索引：'||temp.index_name||'没有被分析..');
end if;
end loop;
dbms_output.put_line('***********************');
dbms_output.put_line('');
end loop;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') into nowdate from dual;
dbms_output.put_line('----------------索引分析结束('||nowdate||')---------------------------------');
end analyzetable;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26263526/viewspace-748211/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
和空间有关的性能视图
下一篇：
没有了~
请登录后发表评论
登录
全部评论
