索引可以加快查询速度,减少全表扫描的发生。但索引的引入同时也带来很多负面的影响,如索引也需要占用存储空间;插入和删除行的时候,同时也需要更新索引。因此索引的引入也是一把双刃剑,我们需要确保系统中的每一个索引都能得到有效的利用。从Oracle9i开始提供了一个有效监控索引使用的方法,让管理员很轻松就能够了解相关索引的使用情况。
下面通过具体例子了解索引监控的方法。
SQL> create table indtest (id number, name varchar2(20));
Table created.
SQL> insert into indtest values(1, 'John');
1 row created.
SQL> insert into indtest values(2, 'Kitty');
1 row created.
SQL> insert into indtest values(3, 'Mike');
1 row created.
SQL> insert into indtest values(4, 'Peter');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table indtest add(constraint pk_indtest primary key(id));
Table altered.
SQL> select index_name,table_name,monitoring,used from v$object_usage;
no rows selected
SQL> alter index pk_indtest monitoring usage;
Index altered.
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST INDTEST YES NO
SQL> set autotrace on
SQL> select * from indtest where id=1;
ID NAME
---------- --------------------
1 John
Execution Plan
----------------------------------------------------------
Plan hash value: 1349805652
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| INDTEST |
|* 2 | INDEX UNIQUE SCAN | PK_INDTEST |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
372 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST INDTEST YES YES
下面通过具体例子了解索引监控的方法。
SQL> create table indtest (id number, name varchar2(20));
Table created.
SQL> insert into indtest values(1, 'John');
1 row created.
SQL> insert into indtest values(2, 'Kitty');
1 row created.
SQL> insert into indtest values(3, 'Mike');
1 row created.
SQL> insert into indtest values(4, 'Peter');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table indtest add(constraint pk_indtest primary key(id));
Table altered.
SQL> select index_name,table_name,monitoring,used from v$object_usage;
no rows selected
SQL> alter index pk_indtest monitoring usage;
Index altered.
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST INDTEST YES NO
SQL> set autotrace on
SQL> select * from indtest where id=1;
ID NAME
---------- --------------------
1 John
Execution Plan
----------------------------------------------------------
Plan hash value: 1349805652
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| INDTEST |
|* 2 | INDEX UNIQUE SCAN | PK_INDTEST |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
372 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST INDTEST YES YES