如何监控oracle索引的使用详细说明

　　如何监控索引的使用？
　　研究发现，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: [Copy to clipboard]
　　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,
　　aclearcase/" target="_blank" >ccess_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区中语句使用的所有索引
　　[/code]
　　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;
　　[/code]
　　
　　所有基于共享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.
　　
　　下面的PL/SQL块对数据库中的所有索引(SYS和SYSTEM拥有的索引除外)启用监控：
　　[/code]
　　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;
　　/
　　[/code]
　　
　　下面我们来看一下Oracle 9i 这个新特性能不能识别在进行DML操作时外键列上索引的使用情况：
　　以9i中HR模式为例：
　　标准的dept和emp表是一个与外键关联的父子表的例子。这个例子主要想看一下，在父表上删除一个记录，会不会调用子表上外键上的索引。 首先监控HR模式下所有索引的使用，为了便于在主表上删除一条记录，不违反引用完整性约束。我们首先丢弃原有的约束，重新创建支持级联删除的约束.
　　[/code]
　　alter table employees drop constraint emp_dept_fk;
　　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;
　　[/code]
　　注意在此为了方便，我们删除部门id为10的记录。如果你删除其他的部门，可能你还要更改表job_history中相关的约束。
　　现在我们看看索引使用的情况：
　　[/code]
　　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
　　[/code]
　　很明显删除父表上的记录，也利用了子表中相关的索引。
　　
　　v$object_usage 视图的一个异常之处是， 它只能显示属于连接用户的索引的信息。Oracle可能在将来会解决这个问题。如果您的数据库只显示连接用户的对象使用信息，下面的视图(必须被创建为SYS)可用于提供来自任何帐户的所有被监控的索引的信息：
　　[/code]
　　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;
　　[/code]
　　
　　3、最后我们简单的说一下，如何监控最近被使用的索引
　　下列查询将列出最近被访问的索引：
　　[/code]
　　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');
　　[/code]
　　这个过程可能要耗费一定时间，建议在数据库不太繁忙的时候运行。
　　
　　to be continued... 2004-05-28
　　如果大家觉得浏览不方便， 我可以上传文本文件。
　　
　　[ Last edited by amaome on 2004-5-28 at 02:59 PM ]
　　

原文转自：http://www.ltesting.net