ITPub博客

首页 > 数据库 > Oracle > 监控索引的使用

监控索引的使用

原创 Oracle 作者:m77m78 时间:2007-06-18 21:25:17 0 删除 编辑
如何监控索引的使用?  研究发现,oracle数据库使用的索引不会超过总数的25%,或者不易他们期望被使用的方式使用。通过 监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能。[@more@]
如何监控索引的使用?  研究发现,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,
  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区中语句使用的所有索引
  [/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 如何监控索引的使用?  研究发现,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,
  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区中语句使用的所有索引
  [/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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34329/viewspace-919210/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle 锁简单介绍
请登录后发表评论 登录
全部评论

注册时间:2008-04-25

  • 博文量
    168
  • 访问量
    732956