索引监控探究

最新推荐文章于 2024-12-03 22:58:05 发布

有趣是好的

最新推荐文章于 2024-12-03 22:58:05 发布

阅读量299

点赞数 1

CC 4.0 BY-SA版权

分类专栏： ORACLE BASE

本文链接：https://blog.youkuaiyun.com/stephen11111/article/details/52573278

ORACLE BASE 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了数据库中索引监控的重要性和实施方法。通过创建不同的索引并进行监控，可以评估索引的有效性，帮助优化数据库性能。文章还展示了如何使用SQL语句来启用和禁用索引监控。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 索引监控的意义

冗余索引的弊端：
大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:
a、浪费大量的存储空间，尤其是大表的索引，浪费的存储空间尤其可观(索引段的维护与管理)
b、增加了DML 操作（UPDATE、INSERT、DELETE）的开销
c、耗用大量统计信息(索引)收集的时间
d、结构性验证时间
f、增加了恢复所需的时间

SQL> create table t1 as select * from dba_objects
  2  /

Table created.

SQL> desc t1
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                          VARCHAR2(30)
 OBJECT_NAME                        VARCHAR2(128)
 SUBOBJECT_NAME                     VARCHAR2(30)
 OBJECT_ID                      NUMBER
 DATA_OBJECT_ID                     NUMBER
 OBJECT_TYPE                        VARCHAR2(19)
 CREATED                        DATE
 LAST_DDL_TIME                      DATE
 TIMESTAMP                      VARCHAR2(19)
 STATUS                         VARCHAR2(7)
 TEMPORARY                      VARCHAR2(1)
 GENERATED                      VARCHAR2(1)
 SECONDARY                      VARCHAR2(1)
 NAMESPACE                      NUMBER
 EDITION_NAME                       VARCHAR2(30)

SQL> create index ix_object on t1(object_id)
  2  /

Index created.

SQL> create index ix_oname on t1(object_name)
  2  /

Index created.

SQL> select  * from v$object_usage
  2  /

no rows selected

SQL>


3
begin
 dbms_stats.gather_table_stats(user,'t1',cascade=>true,degree=>4);
  3   end;
  4  /

PL/SQL procedure successfully completed.

SQL> select  * from v$object_usage
  2  /

no rows selected



alter index   IX_OBJECT monitoring usage;

alter index IX_ONAME monitoring usage


4
SQL> select  * from  t1  where object_name='SCOTT'
  2  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 704102645

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT        |          |     2 |   194 |     4   (0)| 00
:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     2 |   194 |     4   (0)| 00
:00:01 |

|*  2 |   INDEX RANGE SCAN      | IX_ONAME |     2 |       |     3   (0)| 00
:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='SCOTT')

SQL>


5 此时再去查看，为了方便查看，直接toad中运行了

6  测试索引，并且查看执行计划
QL> select  * from t1 where object_id=2
  2  /

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED        LAST_DDL_TIME      TIMESTAMP       STATUS  T G S
------------------ ------------------ ------------------- ------- - - -
 NAMESPACE EDITION_NAME
---------- ------------------------------
SYS
C_OBJ#
                    2          2 CLUSTER

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED        LAST_DDL_TIME      TIMESTAMP       STATUS  T G S
------------------ ------------------ ------------------- ------- - - -
 NAMESPACE EDITION_NAME
---------- ------------------------------
17-SEP-11      17-SEP-11          2011-09-17:09:46:13 VALID   N N N
     5



Execution Plan
----------------------------------------------------------
Plan hash value: 2854884809

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

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| T
ime |

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

|   0 | SELECT STATEMENT        |       |     1 |    97 |     2   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    97 |     2   (0)| 0
0:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

SQL>

7 此时再去查看

关闭索引的监控

alter index IX_OBJECT nomonitoring  usage
   alter index IX_OBJECT nomonitoring  usage

8 监控所用的索引语句

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%';

10 查询表中有哪些索引

/* Formatted on 2016/6/24 11:32:34 (QP5 v5.256.13226.35510) */
SELECT t.*, i.index_type
  FROM user_ind_columns t, user_indexes i
 WHERE t.index_name = i.index_name AND t.table_name = 'T1'

 select  * from user_ind_columns   where table_name='T1'