Oracle中监控索引的使用 （2）

  来源：赛迪网技术社区    作者：amaome

2、 oracle9i中如何确定索引的使用情况

在oracle9i中，情况会简单得多，因为有一个新得字典视图V$SQL_PLAN存储了实际计划，这些计划用于执行共享SQL区中得语句。V$SQL_PLAN视图很类似与计划表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 来识别语句， 而计划表使用用户提供得STATEMENT_ID来识别语句。下面的SQL显示了在一个oracle9i数据库中，由出现在共享SQL区中语句使用的所有索引。

select object_owner, object_name, options, count(*)

from v$sql_plan

where operation='INDEX'

and object_owner!='SYS'

group by object_owner, object_name, operation, options

order by count(*) desc;

所有基于共享SQL区中的信心来识别索引使用情况的方法， 都可能会收集到不完整的信息。共享SQL区是一 个动态结构，除非能对它进行足够频繁的采样， 否则在有关索引使用的情况的信息被收集之前，SQL语句可 能就已经(因为老化)被移出缓存了。oracle9i提供了解决这个问题的方案，即它为alter index提供了一个monitoring usage子句。当启用monitoring usage 时，oralce记录简单的yes或no值，以指出在监控间隔 期间某个索引是否被使用。

为了演示这个新特性，你可以使用下面的例子:

(a) Create and populate a small test table

(b) Create Primary Key index on that table

(c) Query v$object_usage: the monitoring has not started yet

(d) Start monitoring of the index usage

(e) Query v$object_usage to see the monitoring in progress

(f) Issue the SELECT statement which uses the index

(g) Query v$object_usage again to see that the index has been used

(h) Stop monitoring of the index usage

(i) Query v$object_usage to see that the monitoring sDetailed steps:

(a) Create and populate a small test table

create table products (

prod_id number(3),

prod_name_code varchar2(5));

insert into products values(1,'aaaaa');

insert into products values(2,'bbbbb');

insert into products values(3,'ccccc');

insert into products values(4,'ddddd');

commit;

(b) Create Primary Key index on that table

alter table products add (constraint products_pk primary key (prod_id));

(c) Query v$object_usage: the monitoring has not started yet

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;

no rows selected

(d) Start monitoring of the index usage

alter index products_pk monitoring usage;

Index altered.

(e) Query v$object_usage to see the monitoring in progress

select index_name,monitoring,used,start_monitoring,end_monitoring

from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

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

PRODUCTS_PK YES NO 04/25/2001 15:43:13

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.

(f) Issue the SELECT statement which uses the index First, make sure that index will

be used for this statement. Create plan_table in your schema, as required by Oracle

Autotrace utility:

@$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

Use Oracle Autotrace utility to obtain the execution plan:

set autotrace on explain

select * from products where prod_id = 2;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'

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

set autotrace off Now, since you know the index will be used for this query,

issue the actual SELECT statement:

select * from products where prod_id = 2;

PROD_ID PROD_

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

2 bbbbb

(g) Query v$object_usage again to see that the index has been used

select index_name,monitoring,used,start_monitoring,end_monitoring

from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

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

PRODUCTS_PK YES YES 04/25/2001 15:43:13

Note: Column USED='YES'.

(h) Stop monitoring of the index usage

alter index products_pk nomonitoring usage;

Index altered.

(i) Query v$object_usage to see that the monitoring stopped

select index_name,monitoring,used,start_monitoring,end_monitoring

from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

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

PRODUCTS_PK NO YES 04/25/2001 15:43:13 04/25/2001 15:48:44

Note: Column MONITORING='NO', END_MONITORING gives the timestamp.