你描述的情况是整个数据库的问题,在没有发现原因前,先不要着急做索引。用statspack做个分析吧,看看你的数据库问题在哪里。 另外,你要想看全数据库的索引使用状况,可以用下边的方法: 1.先打开索引监控 create or replace procedure P_IDX_JK is --监控所有的索引 v_idx_name varchar2(50):=null; v_idx_type varchar2(50):=null; cursor c_idx is select index_name,index_type from user_indexes; begin open c_idx; loop fetch c_idx into v_idx_name,v_idx_type; if v_idx_type<>'LOB' then execute immediate 'alter index '||v_idx_name ||' monitoring usage'; dbms_output.put_line('alter index '||v_idx_name ||' monitoring usage'); end if; exit when c_idx%NOTFOUND; end loop; close c_idx; end P_IDX_JK; / 2.运行系统一段时间,关闭索引监控 create or replace procedure P_IDX_JKCLOSE is --关闭索引监控 v_idx_name varchar2(50):=null; v_idx_type varchar2(50):=null; cursor c_idx is select index_name,index_type from user_indexes; begin open c_idx; loop fetch c_idx into v_idx_name,v_idx_type; if v_idx_type<>'LOB' then execute immediate 'alter index '||v_idx_name ||' nomonitoring usage'; dbms_output.put_line('alter index '||v_idx_name ||' nomonitoring usage'); end if; exit when c_idx%NOTFOUND; end loop; close c_idx; end P_IDX_JKCLOSE; / 3.查询索引使用情况 select * from v$object_usage 我记得有个指示是否used的列
在你的用户下执行 1、set autotrace trace 2、set timing on 然后分别执行你的sql语句(建立索引的和没有建立索引的语句)
呵呵
另外,你要想看全数据库的索引使用状况,可以用下边的方法:
1.先打开索引监控
create or replace procedure P_IDX_JK is
--监控所有的索引
v_idx_name varchar2(50):=null;
v_idx_type varchar2(50):=null;
cursor c_idx is select index_name,index_type from user_indexes;
begin
open c_idx;
loop
fetch c_idx into v_idx_name,v_idx_type;
if v_idx_type<>'LOB' then
execute immediate 'alter index '||v_idx_name ||' monitoring usage';
dbms_output.put_line('alter index '||v_idx_name ||' monitoring usage');
end if;
exit when c_idx%NOTFOUND;
end loop;
close c_idx;
end P_IDX_JK;
/
2.运行系统一段时间,关闭索引监控
create or replace procedure P_IDX_JKCLOSE is
--关闭索引监控
v_idx_name varchar2(50):=null;
v_idx_type varchar2(50):=null;
cursor c_idx is select index_name,index_type from user_indexes;
begin
open c_idx;
loop
fetch c_idx into v_idx_name,v_idx_type;
if v_idx_type<>'LOB' then
execute immediate 'alter index '||v_idx_name ||' nomonitoring usage';
dbms_output.put_line('alter index '||v_idx_name ||' nomonitoring usage');
end if;
exit when c_idx%NOTFOUND;
end loop;
close c_idx;
end P_IDX_JKCLOSE;
/
3.查询索引使用情况
select * from v$object_usage
我记得有个指示是否used的列
1、set autotrace trace
2、set timing on
然后分别执行你的sql语句(建立索引的和没有建立索引的语句)