如何监控索引的使用?

  1. 云栖社区>
  2. 博客>
  3. 正文

如何监控索引的使用?

jimbuster 2007-03-21 19:58:00 浏览571
展开阅读全文
 

如何监控索引的使用?

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

1、在oracle8i中,确定使用了那个索引的方法意味着要对存在语共享SQL区中的所有语句运行EXPLIAN PALN,然后查询计划表中的OPERATION列,从而识别有OBJECT_OWNEROBJECT_NAME列所确定的那个索引上的索引访问。
下面是一个监控索引使用的脚本,这个脚本仅仅是一个样品,在某种条件下成立:

   
条件:
   
运行这个脚本的用户拥有权限解释所有的v$sqlarea中的sql,除了不是被SYS装载的。
    plan_table.remarks
能够别用来决定与特权习惯的错误。
   
对所有的共享池中SQL,参数OPTIMIZER_GOAL是一个常量,无视v$sqlarea.optimizer_mode
   
两次快照之间,统计资料被再次分析过。
   
没有语句别截断。
   
所有的对象都是局部的。
   
所有被引用的表或视图或者是被运行脚本的用户所拥有,或者完全有资格的名字或同义词被使用。
   
自从上次快照以来,没有不受"欢迎"的语句被冲洗出共享池(例如,在装载)
   
对于所有的语句, v$sqlarea.version_count = 1 (children)
   
  
脚本:

[Copy to clipboard]

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使用ADDRESSHASH_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记录简单的yesno值,以指出在监控间隔 期间某个索引是否被使用。
  
  
为了演示这个新特性,你可以使用下面的例子:
(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块对数据库中的所有索引(SYSSYSTEM拥有的索引除外)启用监控:
[/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操作时外键列上索引的使用情况:
   
9iHR模式为例:
   
标准的deptemp表是一个与外键关联的父子表的例子。
这个例子主要想看一下,在父表上删除一个记录,会不会调用子表上外键上的索引。 首先监控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]
注意在此为了方便,我们删除部门id10的记录。如果你删除其他的部门,可能你还要更改表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]
这个过程可能要耗费一定时间,建议在数据库不太繁忙的时候运行。
       

 

网友评论

登录后评论
0/500
评论