合 Oracle索引的监控

 

0    280    1

Tags：

👉 本文共约9678个字，系统预计阅读时间或需37分钟。

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~O(∩_∩)O\~：

① 掌握oracle中索引的监控方法

② sys.col_usage\$的初步了解

相关知识点扫盲(摘自网络)

合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。 应用程序在开发时，可能会建立众多索引，但是这些索引的使用到底怎么样，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。

冗余索引的弊端：

大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:

a、浪费大量的存储空间，尤其是大表的索引，浪费的存储空间尤其可观(索引段的维护与管理)

b、增加了DML 操作（UPDATE、INSERT、DELETE）的开销

c、耗用大量统计信息(索引)收集的时间

d、结构性验证时间

f、增加了恢复所需的时间

本文介绍两种方式：

第一：开启监控功能；

第二：查看历史的执行计划，进行分析；

索引监控的方法

方法一：开启监控功能

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操作而被监控到、不要因为该索引没用而将它给删了

个人实验

新建1个表TB_LHR_20160622，并创建2个索引：

SYS@raclhr2> select * from v\$version;

BANNER

-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@raclhr2> Create Table TB_LHR_20160622 nologging As select * from dba_objects;

Table created.

SYS@raclhr2> create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

Index created.

SYS@raclhr2> create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

Index created.

查询v\$object_usage视图，收集统计信息：

SYS@raclhr2> select * from v\$object_usage;

no rows selected

SYS@raclhr2> BEGIN

2 dbms_stats.gather_table_stats(USER,

3 'TB_LHR_20160622',

4 cascade => TRUE,

5 degree => 8);

6 END;

7 /

PL/SQL procedure successfully completed.

SYS@raclhr2> select * from v\$object_usage;

no rows selected

开启索引的监控：

SYS@raclhr2> alter index ind_TB_LHR_20160622_id monitoring usage;

Index altered.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v\$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES NO 06/22/2016 15:15:54

SYS@raclhr2> alter index ind_TB_LHR_20160622_name monitoring usage;

Index altered.

SYS@raclhr2> select count(1) from TB_LHR_20160622 t where t.object_id=88;

COUNT(1)

----------

1

SYS@raclhr2> explain plan for select count(1) from TB_LHR_20160622 t where t.object_id=88;

Explained.

SYS@raclhr2> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------

Plan hash value: 2688591802

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX RANGE SCAN| IND_TB_LHR_20160622_ID | 1 | 5 | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."OBJECT_ID"=88)

14 rows selected.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v\$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

注意：SELECT * FROM V\$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息，如下，但我们可以创建一个视图来解决这个问题。

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> select * from v\$object_usage;

no rows selected

SCOTT@raclhr2> conn / as sysdba

Connected.

SYS@raclhr2> create or replace view vw_INDEX_USAGE_lhr AS

2 SELECT U.NAME OWNER,

3 IO.NAME INDEX_NAME,

4 T.NAME TABLE_NAME,

5 DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

6 DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

7 OU.START_MONITORING START_MONITORING,

8 OU.END_MONITORING END_MONITORING

9 FROM SYS.USER\$ U,

10 SYS.OBJ\$ IO,

11 SYS.OBJ\$ T,

12 SYS.IND\$ I,

13 SYS.OBJECT_USAGE OU

14 WHERE I.OBJ# = OU.OBJ#

15 AND IO.OBJ# = OU.OBJ#

16 AND T.OBJ# = I.BO#

17 AND U.USER# = IO.OWNER#;

View created.

SYS@raclhr2> create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

Synonym created.

SYS@raclhr2> grant select on sys.vw_INDEX_USAGE_lhr to public;

Grant succeeded.

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> set line 9999 pagesize 9999

SCOTT@raclhr2> col owner format A10

SCOTT@raclhr2> COL INDEX_NAME FOR A25

SCOTT@raclhr2> COL TABLE_NAME FOR A20

SCOTT@raclhr2> COL MONITORING FOR A10

SCOTT@raclhr2> COL USED FOR A10

SCOTT@raclhr2> COL START_MONITORING FOR A20

SCOTT@raclhr2> COL END_MONITORING FOR A20

SCOTT@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

取消索引的监控：

SCOTT@raclhr2> CONN / AS SYSDBA

Connected.

SYS@raclhr2> alter index ind_TB_LHR_20160622_id nomonitoring usage;

Index altered.

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 NO YES 06/22/2016 15:15:54 06/22/2016 15:22:30

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

SYS@raclhr2> alter index ind_TB_LHR_20160622_name nomonitoring usage;

Index altered.

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 NO YES 06/22/2016 15:15:54 06/22/2016 15:22:30

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 NO NO 06/22/2016 15:22:45 06/22/2016 15:23:12

实验中用到的SQL

drop table TB_LHR_20160622 purge;

Create Table TB_LHR_20160622 nologging As select * from dba_objects;

create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

select * from v\$object_usage;

BEGIN

dbms_stats.gather_table_stats(USER,

'TB_LHR_20160622',

cascade => TRUE,

degree => 8);

END;

/

alter index ind_TB_LHR_20160622_id monitoring usage;

alter index ind_TB_LHR_20160622_name monitoring usage;

select count(1) from TB_LHR_20160622 t where t.object_id=88;

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

select * from v\$object_usage;

注意：SELECT * FROM V\$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息。

alter index ind_TB_LHR_20160622_id nomonitoring usage;

alter index ind_TB_LHR_20160622_name nomonitoring usage;

--- drop table t purge; 表删掉后 v\$object_usage 中关于监控的信息也删除了

----切换用户后查询select * from v\$object_usage;查询不到数据，下边这个SQL可以查询任何用户下的索引使用情况

create or replace view vw_INDEX_USAGE_lhr AS

SELECT U.NAME OWNER,

IO.NAME INDEX_NAME,

T.NAME TABLE_NAME,

DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

OU.START_MONITORING START_MONITORING,

OU.END_MONITORING END_MONITORING

FROM SYS.USER\$ U,

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#

AND U.USER# = IO.OWNER#;

create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

SELECT * FROM syn_INDEX_USAGE_lhr;

批量监控系统的所有索引：

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表能记录索引监控和使用的状态，但它不能统计索引被使用的次数和频率，只记录了在开启索引监控的时间段索引是否被使用过，因此想详细了解索引的使用情况我们可以利用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.object_type = 'INDEX'

and nb.subobject_name is null) created,

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

(SELECT COUNT(1)

FROM dba_ind_columns dic

WHERE dic.index_name = i.index_name

AND dic.table_name = i.table_name

AND dic.INDEX_OWNER = i.owner) count_index_cols

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,

a.count_index_cols,

b.plan_operation,

case

when min_date is null then

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

FROM dba_hist_snapshot nb)

else

min_date

end as min_date,

case

when max_date is null then

(SELECT max(to_char(nb.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS'))

FROM dba_hist_snapshot nb)

else

max_date

end as 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);

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

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

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复