分享
 
 
 

监视未使用索引

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

作者:gototop

索引可以加快查询的速度,但索引会占用许多存储空间,在插入和删除行的时候,索引还会引入额外的开销,因此确保索引得到有效利用是我们很关注的一个问题。

在Oracle9i之前,要知道一个索引是否被使用是困难的,而Oracle 9i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE。下面我讲具体说明如何使用该方法来鉴别未使用的索引。

一、我们先通过一个例子具体说明“ALTER INDEX MONITORING USAGE”的使用方法:

1、建测试表

create table test(id number(3),name varchar2(10));

insert into test values(1,'aaaaaaaa');

insert into test values(2,'www.ncn.cn');

insert into test values(3,'aadfaaaa');

insert into test values(4,'gototop');

insert into test values(5,'shenzhen');

insert into test values(6,'china');

commit;

alter table test add (constraint test_pk primary key (id));

2、查询v$object_usage(因为没有监视,所以还看不到内容)

column index_name format a12

column monitoring format a10

column used format a4

column start_monitoring format a19

column end_monitoring format a19

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

SQL l

1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage

SQL /

no rows selected

Elapsed: 00:00:00.00

3、开始监控索引的使用情况

SQL alter index test_pk monitoring usage;

Index altered.

Elapsed: 00:00:00.05

4、查询v$object_usage(可以看到正监视中)

SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK YES NO 05/15/2003 13:28:22

Elapsed: 00:00:00.00

5、使用索引进行查询

SQL set autotrace on eXPlain

SQL select * from test where id = 2;

ID NAME

---------- ----------

2 www.ncn.cn

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE Access (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)

SQL set autotrace off

SQL /

ID NAME

---------- ----------

2 www.ncn.cn

Elapsed: 00:00:00.00

SQL

从上我们可以看到确实使用了索引。

6、查询v$object_usage(可以看到索引被使用过,但目前还处于被监视过程中)

SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK YES YES 05/15/2003 13:28:22

Elapsed: 00:00:00.00

7、停止监视,并查询v$object_usage

SQL alter index test_pk nomonitoring usage;

Index altered.

Elapsed: 00:00:05.03

SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK NO YES 05/15/2003 13:28:22 05/15/2003 13:40:00

Elapsed: 00:00:00.64

到此为止,监视结束,MONITORING为NO,END_MONITORING给出了时间戳。

二、v$object_usage视图解释

从上面的例子中我们可以看出,索引的监视信息都是存在在v$objec_usage视图中,该视图的定义如下:

CREATE OR REPLACE VIEW SYS.V$OBJECT_USAGE

(

INDEX_NAME,

TABLE_NAME,

MONITORING,

USED,

START_MONITORING,

END_MONITORING

)

AS

select 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

where io.owner# = userenv('SCHEMAID')

and i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

/

COMMENT ON TABLE SYS.V$OBJECT_USAGE IS

'Record of index usage'

/

GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"

/

下面是该视图列的描述:

INDEX_NAME: sys.obj$.name 中的索引名字

TABLE_NAME: sys.obj$obj$name 中的表名

MONITORING: YES (索引正在被监控), NO (索引没有被监控)

USED: YES (索引已经被使用过), NO (索引没有被使用过)

START_MONITORING: 开始监控的时间

END_MONITORING: 结束监控的时间

所有被使用过至少一次的索引都可以被监控并显示到这个视图中。

三、监视数据库中所有索引的使用情况

1、生成开始/结束监视索引的SQL脚本:

set heading off

set echo off

set feedback off

set pages 10000

spool start_index_monitor.sql

select 'alter index 'owner'.'index_name' monitoring usage;'

from dba_indexes

where owner in ('YOUR','PROD_DB','OWNER','LIST');

spool off

set heading on

set echo on

set feedback on

------------------------------------------------

set heading off

set echo off

set feedback off

set pages 10000

spool stop_index_monitor.sql

select 'alter index 'owner'.'index_name' nomonitoring usage;

'

from dba_indexes

where owner in ('YOUR','PROD_DB','OWNER','LIST');

spool off

set heading on

set echo on

set feedback on

2、进行监视并查询结果:

在业务量比较多的一天上班时运行start_index_monitor.sql,下班前运行stop_index_monitor.sql,之后就可以在各用户自己的v$object_usage视图中看到该SCHEMA下的索引使用情况了:

SQL conn t/t

Connected.

SQL select index_name,table_name,used

2 from v$object_usage

3 where used='NO';

INDEX_NAME TABLE_NAME USED

------------ ------------------------------ ----

TEST_PK TEST NO

1 row selected.

SQL

3、改进结果查寻方法

你也许已经注重到,上面查询结果是需要我们单独查询各SCHEMA中的v$object_usage,其实我们可以通过给v$object_usage视图添加一个owner列来创建一个可以存储所有SHCEMA的v$object_usage视图,不妨叫做v$all_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_mon

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有