实用心得：Oracle中监控索引的使用

网络整理 - 07-26

研究发现，oracle数据库使用的索引不会超过总数的25%，或者不易他们期望被使用的方式使用。通过监控数据库索引的使用，释放那些未被使用的索引，从而节省维护索引的开销，优化性能。

1、在oracle8i中，确定使用了那个索引的方法意味着要对存在语共享SQL区中的所有语句运行EXPLIAN PALN，然后查询计划表中的OPERATION列，从而识别有OBJECT_OWNER和OBJECT_NAME列所确定的那个索引上的索引访问。

下面是一个监控索引使用的脚本，这个脚本仅仅是一个样品，在某种条件下成立：

条件：

运行这个脚本的用户拥有权限解释所有的v$sqlarea中的sql，除了不是被SYS装载的。

plan_table.remarks能够别用来决定与特权习惯的错误。

对所有的共享池中SQL，参数OPTIMIZER_GOAL是一个常量，无视v$sqlarea.optimizer_mode。

两次快照之间，统计资料被再次分析过。

没有语句别截断。

所有的对象都是局部的。

所有被引用的表或视图或者是被运行脚本的用户所拥有，或者完全有资格的名字或同义词被使用。

自从上次快照以来，没有不受"欢迎"的语句被冲洗出共享池(例如，在装载)。

对于所有的语句， v$sqlarea.version_count = 1 (children)。

脚本:

CODE:

set echo off Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN drop table plan_table; create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)); Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA drop table sqltemp; create table sqltemp ( ADDR VARCHAR2 (16), SQL_TEXT VARCHAR2 (2000), DISK_READS NUMBER, EXECUTIONS NUMBER, PARSE_CALLS NUMBER); set echo on Rem Create procedure to populate the plan_table by executing Rem explain plan...for 'sqltext' dynamically create or replace procedure do_explain ( addr IN varchar2, sqltext IN varchar2) as dummy varchar2 (1100); mycursor integer; ret integer; my_sqlerrm varchar2 (85); begin dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ; dummy:=dummy||''''||addr||''''||' FOR '||sqltext; mycursor := dbms_sql.open_cursor; dbms_sql.parse(mycursor,dummy,dbms_sql.v7); ret := dbms_sql.execute(mycursor); dbms_sql.close_cursor(mycursor); commit; exception -- Insert errors into PLAN_TABLE... when others then my_sqlerrm := substr(sqlerrm,1,80); insert into plan_table(statement_id,remarks) values (addr,my_sqlerrm); -- close cursor if exception raised on EXPLAIN PLAN dbms_sql.close_cursor(mycursor); end; / Rem Start EXPLAINing all S/I/U/D statements in the shared pool declare -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS) cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, PARSE_CALLS from v$sqlarea where command_type in (2,3,6,7) and parsing_schema_id != 0; cursor c2 is select addr, sql_text from sqltemp; addr2 varchar(16); sqltext v$sqlarea.sql_text%type; dreads v$sqlarea.disk_reads%type; execs v$sqlarea.executions%type; pcalls v$sqlarea.parse_calls%type; begin open c1; fetch c1 into addr2,sqltext,dreads,execs,pcalls; while (c1%found) loop insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls); commit; fetch c1 into addr2,sqltext,dreads,execs,pcalls; end loop; close c1; open c2; fetch c2 into addr2, sqltext; while (c2%found) loop do_explain(addr2,sqltext); fetch c2 into addr2, sqltext; end loop; close c2; end; / Rem Generate a report of index usage based on the number of times Rem a SQL statement using that index was executed select p.owner, p.name, sum(s.executions) totexec from sqltemp s, (select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation = 'INDEX') p where s.addr = p.stid group by p.owner, p.name order by 2 desc; Rem Perform cleanup on exit (optional) delete from plan_table where statement_id in ( select addr from sqltemp ); drop table sqltemp;

关于这个脚本，有几个重要的地方需要注意，即它可能一起明显的开销，因此，应该在仔细地进行权衡后才把它应用到繁忙的生产应用系统中区。

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

(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));

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.

下面的PL/SQL块对数据库中的所有索引(SYS和SYSTEM拥有的索引除外)启用监控：

declare

l_sql varchar2(128);

begin

for rec in

(select 'alter index '||owner.||'.'||index_name||' monitoring usage' mon

from dba_indexes

where owner not in ('SYS', 'SYSTEM')

and index_type='NORMAL') loop

l_sql:=rec.mon;

execute immediate l_sql;

end loop;

end;

下面我们来看一下Oracle 9i 这个新特性能不能识别在进行DML操作时外键列上索引的使用情况：

以9i中HR模式为例：

标准的dept和emp表是一个与外键关联的父子表的例子。这个例子主要想看一下，在父表上删除一个记录，会不会调用子表上外键上的索引。首先监控HR模式下所有索引的使用，为了便于在主表上删除一条记录，不违反引用完整性约束。我们首先丢弃原有的约束，重新创建支持级联删除的约束.

alter table employees add constraint emp_dept_fk foreign key (department_id) references departments on delete cascade;alter table job_history drop constraint jhist_emp_fk;alter table job_history add constraint jhist_emp_fk foreign key(employee_id) references employees on delete cascade;delete from departments where department_id=10;

注意在此为了方便，我们删除部门id为10的记录。如果你删除其他的部门，可能你还要更改表job_history中相关的约束。

现在我们看看索引使用的情况：

select index_name, table_name, monitoring, used from v$object_usage where used='YES' INDEX_NAME TABLE_NAME MON USE ------------------------------ -------------------- --- --- DEPT_ID_PK DEPARTMENTS YES YES EMP_EMP_ID_PK EMPLOYEES YES YES EMP_DEPT_FK EMPLOYEES YES YES

很明显删除父表上的记录，也利用了子表中相关的索引。

v$object_usage 视图的一个异常之处是，它只能显示属于连接用户的索引的信息。Oracle可能在将来会解决这个问题。如果您的数据库只显示连接用户的对象使用信息，下面的视图(必须被创建为SYS)可用于提供来自任何帐户的所有被监控的索引的信息：

create or replace view V$ALL_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 i.obj#=ou.obj# and io.obj#=ou.obj# and t.obj#=i.bo#; grant select on v$all_object_usage to public; create public synonym v$all_object_usage for v$all_object_usage;

3、最后我们简单的说一下，如何监控最近被使用的索引

下列查询将列出最近被访问的索引：

column owner format a20 trunc column segment_name format a30 trunc select distinct b.owner, b.segment_name from x$bh a, dba_extents b where b.file_id=a.dbafil and a.dbablk between b.block_id and b.block_id+blocks-1 and segment_type='INDEX' and b.owner not in ('SYS','SYSTEM');

这个过程可能要耗费一定时间，建议在数据库不太繁忙的时候运行。