索引使得数据库治理人员有可能在数据表的行上过度分配索引,Oracle索引的监控

发布时间:2020-01-12  栏目:数据  评论:0 Comments

owner not in (SYS,SYSTEM,PERFSTAT);

但不管怎样,如果你接手一个以前开发的数据库,而且老数据库在没有考虑到 SQL
访问表的情况下创建了索引,那么这个工具对你是很有用的。INDEX MONITORING
特性的开销非常小,而对定位和丢弃不需要的索引很有帮助。

  研究发现,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 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

一.4.1  方法一:开启监控功能

 

 

1、单个索引监控
 

      
a、对于单个索引的监控,可以使用下面的命令来完成

          
alter index <INDEX_NAME> monitoring usage;

      
b、关闭索引监控

         
alter index <INDEX_NAME> nomonitoring usage;

      
c、观察监控结果(查询v$object_usage视图)

         
select * from v$object_usage;

 

2、schema级别索引监控

 

如果我们想在系统中监控所有的索引,那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB
indexes。原因有下面两个:

1:LOB
indexes不能修改,否则会报ORA-22864错误(ORA-22864: cannot ALTER or DROP
LOB indexes)。

2:ORA-00701:
object necessary for warmstarting database cannot be altered

ORA-00701:
object necessary for warmstarting database cannot be altered

00701.
00000 – “object necessary for warmstarting database cannot be
altered”

*Cause:
Attempt to alter or drop a database object (table, cluster,
or

index)
which are needed for warmstarting the database.

*Action:
None.

 

直接执行脚本来开启索引监控,当然监控索引时长非常重要,太短的话有可能导致查询出来的数据有问题,一般建议监控一周后即可,OLAP系统则需要适当延长监控的时间。

SELECT ‘ALTER
INDEX ‘ ||
owner || ‘.’ ||
index_name || ‘
MONITORING USAGE;’
enable_monitor,

      
‘ALTER
INDEX ‘ ||
owner || ‘.’ ||
index_name ||

      

NOMONITORING USAGE;’
disable_monitor

 
FROM
dba_indexes

 WHERE
INDEX_TYPE != ‘LOB’

  
and
owner IN

      
(SELECT
username FROM
dba_users WHERE
account_status = ‘OPEN’)

  
AND
owner NOT IN (‘SYS’,

                    
‘SYSTEM’,

                    
‘PERFSTAT’,

                    
‘MGMT_VIEW’,

                    
‘MONITOR’,

                    
‘SYSMAN’,

                    
‘DBSNMP’)

  
AND
owner not like ‘%SYS%’;

 

 

监控一个月就大概可以知道那些是无用的索引了。

虽然v$object_usage表能记录索引监控和使用的状态,但它不能统计索引被使用的次数和频率,只记录了在开启索引监控的时间段索引是否被使用过,这一点要值的注意。

另外需要注意的2点:


10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了


外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了

 

  

used

  
这个命令使用V$OBJECT_USAGE 视图和 USAGE
字段来判断索引是否被访问过。你可能期望 USAGE
字段是一个数字值,这样你就可以知道索引被使用的次数,但不幸的是,它的取值只为YES
或NO。

  ———— ———- —- ——————-

  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]
  这个过程可能要耗费一定时间,建议在数据库不太繁忙的时候运行。

一.4.2  方法二:查看历史的执行计划进行分析

虽然v$object_usage表能记录索引监控和使用的状态,但它不能统计索引被使用的次数和频率,只记录了在开启索引监控的时间段索引是否被使用过,因此想详细了解索引的使用情况我们可以利用AWR的一些视图dba_hist_sql_plan和dba_hist_sqlstat来弄清楚数据库访问某个索引的次数、索引访问的类型,如索引范围扫描或索引唯一扫描。

 

WITH
tmp1 AS

 (SELECT
i.OWNER INDEX_OWNER,

        
i.table_owner,

        
TABLE_NAME,

        
INDEX_NAME,

        
INDEX_TYPE,

        
(select
nb.created

           
from
dba_objects nb

          
WHERE
nb.owner =
i.owner

            
and
nb.object_name =
i.index_name

            
and
nb.subobject_name is null)
created,

        
(SUM(S.bytes) / 1024 / 1024)
INDEX_MB

   
FROM
DBA_SEGMENTS S,
DBA_INDEXES I

  
WHERE
i.INDEX_NAME =
s.SEGMENT_NAME

    
and
i.owner =
s.owner

    
and
s.owner not like ‘%SYS%’

 
/\and
s.owner = ‘FUNDZ’*/*

  
GROUP BY
i.OWNER,
i.table_owner,
TABLE_NAME,
INDEX_NAME,
INDEX_TYPE

 
HAVING SUM(S.BYTES) > 1024 * 1024),

tmp2
as

 (SELECT
index_owner,

        
index_name,

        
plan_operation,

        
(SELECT min(to_char(nb.begin_interval_time, ‘YYYY-MM-DD
HH24:MI:SS’))

           
FROM
dba_hist_snapshot nb

          
where
nb.snap_id =
v.min_snap_id)
min_date,

        
(SELECT max(to_char(nb.end_interval_time, ‘YYYY-MM-DD
HH24:MI:SS’))

           
FROM
dba_hist_snapshot nb

          
where
nb.snap_id =
v.max_snap_id)
max_date,

        
counts

   
FROM (SELECT
d.object_owner index_owner,

                 
d.object_name index_name,

                 
d.operation || ‘
‘ ||
d.options plan_operation,

                 
min(h.snap_id)
min_snap_id,

                 
max(h.snap_id)
max_snap_id,

                 
COUNT(1)
counts

            
FROM
dba_hist_sql_plan d,
dba_hist_sqlstat h

           
WHERE /\d.object_owner
= ‘FUNDZ’*

                                             
AND \
/*

           
d.operation LIKE ‘%INDEX%’

        
AND
d.sql_id =
h.sql_id

           
GROUP BY
d.object_owner,
d.object_name,
d.operation,
d.options)
v)

SELECT
a.table_owner,

      
a.TABLE_NAME,

      
a.index_owner,

      
a.index_name,

      
a.created,

      
a.INDEX_TYPE,

      
a.INDEX_MB,

      
b.plan_operation,

      
min_date,

      
max_date,

      
counts

 
from
tmp1 a

 
left outer join
tmp2 b

   
on (a.index_owner
=
b.index_owner and
a.index_name =
b.index_name);

 

图片 1 

如上图所示,有一个3.6G大的索引在13号到22号从没使用过,接下来,我们可以继续查询该索引是否联合索引,创建是否合理,分析为何不走该索引,从而判断是否可以删除索引。

另外下边的SQL可以查询出表上列的使用情况:

CREATE OR REPLACE VIEW
VW_COLUMN_USAGE_LHR AS

SELECT
oo.name             owner,

      
o.name              table_name,

      
c.name              column_name,

      
u.equality_preds,

      
u.equijoin_preds,

      
u.nonequijoin_preds,

      
u.range_preds,

      
u.like_preds,

      
u.null_preds,

      
u.timestamp

 
FROM
sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c

 WHERE
o.obj# =
u.obj#

  
AND
oo.user# =
o.owner#

  
AND
c.obj# =
u.obj#

  
AND
c.col# =
u.intcol#

;

 

 

About
Me

………………………………………………………………………………………………………………………………………………………………………………….

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

本文在ITpub(

本文地址:

本文pdf版:
(提取码:ed9b)

小麦苗分享的其它资料:

联系我请加QQ好友(642808185),注明添加缘由


2016-04-06 10:00~ 2016-04-11 19:00 在中行完成

【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

………………………………………………………………………………………………………………………………………………………………………………….

 

 

 

spoolrun_monitor.sql

  set pages 999
  set heading off
  
  spool run_mon.sql
  
  select
    ‘alter index ‘||
    index_name||
    ‘ monitoring usage;’
  from
    dba_indexes
  where
    owner = ‘SCOTT’;
  
  spool off
  
  @run_mon

一.2  前言部分

 

许多Oracle 治理人员只要看见在一个SQL
查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速,但是基于功能的Oracle
索引使得数据库治理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响要害Oracle
数据表的性能。

Oracle9i提供一个简单的方法来打开和关闭索引使用跟踪,那就是MONITORING
USAGE 子句:

Oracle索引的监控

select

在 Oracle9i
之前,监控索引使用的唯一方法是执行他们的程序库缓中的所有SQL,然后手工记下所有被使用的索引。

一.1  BLOG文档结构图

图片 2 

 

在Oracle9i出现以前,没有办法确定SQL查询没有使用的索引。让我们看看Oracle9i提供了什么样的方法让你找到这些索引并删除它们。
过程是相当简单的。Oracle9i有一个工具能够让你使用ALTER
INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。
下面是一段脚本,它能够打开一个系统中所有索引的监视功能: set pages 999;

最近的研究发现 Oracle
数据库所使用的索引从来没有达到过可用索引数的1/4,或者其用法与其开始设计的意图不相同。未用的索引浪费空间,而且还会降低
DML 的速度,尤其是 UPDATE 和 INSERT 语句。

一.4  索引监控的方法

相关文章

留下评论

网站地图xml地图