ITPub博客

首页 > Linux操作系统 > Linux操作系统 > index问题

index问题

原创 Linux操作系统 作者:liujinwei633 时间:2009-09-17 11:12:50 0 删除 编辑

oracle 索引何时需要重建问题的解决

关于索引何时需要重建,我在oracle文档中还没找到明确的说法,
但可以通过一个视图(index_stats),参考里面的数值来判断是否需要重建。

select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct        
from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>10;

del_lf_rows:索引删除行数
lf_rows:索引总行数

这里 round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0) 求的是删除行数除以总行数的百分比,
我在相关书籍上看到是,比率大于10%需要重建,当然看情况而定,也可以是是20%,但不要超过30%。

当然得到这个结果要首先分析索引:
analyze index index_name validate structure;

因为index_stats每次只存入一个索引的统计信息,所以要是很多索引都检测就不方便,
下面是我写的存储过程,用于批量分析。

1,建立临时表用来存放数据
create table index_rebuid (name varchar2(30),del_lf_rows number(12),lf_rows number(12),frag_pct varchar2(10));
2,授予用户权限
grant select  on  dba_ind_columns to 用户名;
3,建立存储过程
create or replace procedure reb
is
tx varchar2(1000);
begin
for i in
(
select 'analyze index '||index_owner||'.'||index_name||' validate structure' as sql_text from dba_ind_columns
where index_owner='用户名')  loop
tx :=i.sql_text;
execute immediate tx;
insert into index_rebuid (name,del_lf_rows,lf_rows,frag_pct)
select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct        
from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>10;
end loop;
commit;
end;
4,运行存储
execute reb;
5,检测
select * from index_rebuid;

oracle 如何判断索引是否被使用

索引会增加io,增加空间,也会增加一些数据库的额外开销,
对于没有用到得索引,应尽量避免不建,建立了的,把没用的索引根据情况删除。有唯一约束,主键的列不要删除。
建议一个表的所以在4-5个左右,不要太多,这是一个参考值,当然还要具体分析,

下面是判断的方法:
1,分析索引
alter index index_name monitoring usage;
2,产看,used是否为YES,当然要观察一段时间才能确定是否被使用。
select table_name,index_name,used from v$object_usage;

下面是我写的批量分析索引是否被使用的存储过程:
1,授予权限
grant select on dba_indexes to 用户名;
2,创建存储过程
create or replace procedure us
is
tx varchar2(1000);
begin
for i in
(
select 'alter index '||index_name||'  monitoring usage' as sql_text from dba_indexes where wner='用户名'
)
loop
tx :=i.sql_text;
execute immediate tx;
end loop;
end;
3,运行存储过程
execute wd;
4,检测
select table_name,index_name,used from v$object_usage;

如果我们新介入一个新环境,对应用不是很了解,对各个表的关系也不了解,即使分析出相关的索引没有被用到,我们在删除时也要谨慎,
如果不是对数据库有很大的影响,在可控的范围内,可暂时不删除。

注:
如果我们在v$object_usage看不到统计信息
运行下面的脚本。
CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and io.owner# = u.user#;

COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 'Record of all index usage';
GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC";
CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE;

索引信息

当索引被分析时, 索引统计信息就放在dba_indexes这个视图中。这个视图里面也存放了很多对结构化查询语言的优化很重要的信息,当然还有很多其他的东西需要注意。 Oracle提供了分析xxx索引验证结构的命令,这个命令会收集一些有关索引的额外统计信息,并且把它放到一个名为index_stats的临时表中。 但是,这些信息需要保存下来,因为每次运行这个命令的时候,原来的信息都会被覆盖掉。

为了得到一个索引的完全数据,我们需要上面两个表的数据。同时,显而易见,其中的某些列比其他列更重要:

* CLUSTERING_FACTOR(簇因素)――这是一个很重要的索引统计参数,因为它表示索引列的存放顺序与表中数据的顺序的相似程度。如果这个参数 很低(大约与表段的dba_segments.blocks数量差不多),则索引键的存放顺序和表中行的顺序一样,索引范围扫描将会非常有效,耗用最小的 磁盘输入输出。 当这个参数增加(最大至dba_tables.num_rows)时,索引键和表中行的顺序相差就会很明显。Oracle的“基于代价的优化”在很大程度 上依赖簇这个参数来决定是否使用索引。

* HEIGHT(高度)――当插入新行时,索引块需要进行拆分。当一个索引结点分裂到一个预定的最高层数时,索引就会扩展一个新层。

* BLOCKS(块数)――这个表示索引用掉的块的数量。它依赖于数据库块的大小(db_block_size)。 在Oracle9i,以及更高的版本中,数据库管理员(DBA)更倾向于用很大的块来构建索引(例如db_32k_block_size),因为这样,索 引扩展新层的可能性就更小。Robin Schumacher已经在他的书《Oracle性能故障解决》中提到了这一点。

“正如你所看到的,只是简单地使用16k的表空间和16k的数据缓存,逻辑读的数量就减少了一半。很明显,在oracle 9i及更高版本中正确使用新的数据缓冲区和多块表空间很值得你在你自己的书刊中进行研究与试验。”

* PCT_USED――这个字段很有迷惑性,因为它看起来和dba_indexes的pct_used列一样,但是意义却不同。正常情况 下,pct_used的极限值是自由列表的未连接极限值,但在index_stats里的pct_used则表示在为b树索引所分配的空间里实际所用到的 百分比。

是否有重建索引的标准?

简单的讲,没有。没有一个百分百完善、权威的索引重建标准。但是,可以从下面所列的几条建议做起:

* 索引层次数大于3
* 占用空间百分比小于75%
* 多于20%的行被删除(空间不会自动被重用)
* 如果针对表有频繁的插入/更新/删除操作,尽管卸载、重排、重载一个表的数据或许能提供更高的性能,但索引将变得非聚簇,性能也将下降(导致需要读的块增加), 这是需要额外执行的维护,并且保持行的正确顺序也非常困难。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/32939/viewspace-614878/,如需转载,请注明出处,否则将追究法律责任。

上一篇: mysql日志
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-11-11

  • 博文量
    17
  • 访问量
    51684