SQL> conn roger/roger
Connected.
SQL> create table ht01 as select owner,object_id,object_name from dba_objects;
Table created.
SQL> create index idx_id on ht01(object_id);
Index created.
SQL> show user
USER is "SYS"
SQL> alter index roger.IDX_ID monitoring usage;
Index altered.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
no rows selected
SQL> conn roger/roger
Connected.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
IDX_ID YES NO 09/04/2011 14:29:44
'-- 为何这里只能在当前模式下查询呢？sys为啥查询不到？'
SQL> select text
2 from dba_views
3 where owner='SYS' and view_name=upper('v$object_usage');
TEXT
--------------------------------------------------------------------------------
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 io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
'从上面v$object_usage定义就能看出了，这里userenv为SCHMEAID，故只能查询当前模式的情况。'
SQL> delete from ht01 where object_id >1000 and object_id <1500;
499 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from ht01 where object_id >10000 and object_id < 11000;
895 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
--------------- -------------------- --- --- ------------------- -------------------
IDX_ID HT01 YES YES 09/04/2011 14:29:44
SQL> show user
USER is "ROGER"
SQL> alter index idx_id rebuild;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
--------------- -------------------- --- --- ------------------- -------------------
IDX_ID HT01 NO YES 09/04/2011 14:29:44
从上面我们可以发现，如果index被重建，那么其监控将被停止。 所以这里存在2个很大的问题：
1. 如何确保index重建后也能会监控？我们知道，对于dml操作频繁的表来说，定期重建index是很有必要的；
2. 如何才能知道一个索引的使用情况呢？或者说其使用频率？这里存在一种可能，如果某个索引在过去使用过，但是现在没使用，但是数据字典应该已经记录了其信息，所以通过查询$object_usage就不准确了；另外index的使用频率怎么样？可能是1天使用1万次，也可能是1天使用1次或者更甚至3天才使用1次，那么对于这种使用频率低的index完全可以删除。
###### 为了解决上面2个疑问, 下面就用实验来证明 ######
SQL> select FILE_ID,EXTENT_ID,BLOCK_ID,RELATIVE_FNO,SEGMENT_NAME
2 from dba_extents
3 where SEGMENT_NAME='HT01';
FILE_ID EXTENT_ID BLOCK_ID RELATIVE_FNO SEGMENT_NAME
---------- ---------- ---------- ------------ ---------------
5 0 25 5 HT01
5 1 33 5 HT01
5 2 41 5 HT01
5 3 49 5 HT01
5 4 57 5 HT01
5 5 65 5 HT01
5 6 73 5 HT01
5 7 81 5 HT01
5 8 89 5 HT01
5 9 97 5 HT01
5 10 105 5 HT01
5 11 113 5 HT01
5 12 121 5 HT01
5 13 129 5 HT01
5 14 137 5 HT01
5 15 145 5 HT01
5 16 265 5 HT01
5 17 393 5 HT01
18 rows selected.
SQL> select * from V_$SEGSTAT where OBJ#=51929;
TS# OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE
---------- ---------- ---------- ------------------------------ ---------- ----------
6 51929 51929 logical reads 0 5616
6 51929 51929 buffer busy waits 1 0
6 51929 51929 gc buffer busy 2 0
6 51929 51929 db block changes 3 4720
6 51929 51929 physical reads 4 275
6 51929 51929 physical writes 5 346
6 51929 51929 physical reads direct 6 0
6 51929 51929 physical writes direct 7 275
6 51929 51929 gc cr blocks received 9 0
6 51929 51929 gc current blocks received 10 0
6 51929 51929 ITL waits 11 0
6 51929 51929 row lock waits 12 0
6 51929 51929 space used 14 2013264
6 51929 51929 space allocated 15 3145728
6 51929 51929 segment scans 17 1
15 rows selected.
SQL> conn roger/roger
Connected.
SQL> delete from ht01 where object_id > 5000 and object_id < 6001;
891 rows deleted.
SQL> commit;
Commit complete.
SQL> conn /as sysdba
Connected.
SQL> select * from V_$SEGSTAT where OBJ#=51929;
TS# OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE
---------- ---------- ---------- ------------------------------ ---------- ----------
6 51929 51929 logical reads 0 6576
6 51929 51929 buffer busy waits 1 0
6 51929 51929 gc buffer busy 2 0
6 51929 51929 db block changes 3 5568
6 51929 51929 physical reads 4 277
6 51929 51929 physical writes 5 346
6 51929 51929 physical reads direct 6 0
6 51929 51929 physical writes direct 7 275
6 51929 51929 gc cr blocks received 9 0
6 51929 51929 gc current blocks received 10 0
6 51929 51929 ITL waits 11 0
6 51929 51929 row lock waits 12 0
6 51929 51929 space used 14 1981315
6 51929 51929 space allocated 15 3145728
6 51929 51929 segment scans 17 1
15 rows selected.
SQL> select 5568-4720 from dual;
5568-4720
----------
848
SQL> select * from v$segment_statistics where OBJ#=51929;
OWNER OBJECT_NAM SUBOBJECT_ TABLESPACE TS# OBJ# DATAOBJ# OBJECT_TYP STATISTIC_NAME STATISTIC# VALUE
----- ---------- ---------- ---------- --- ----- ---------- ---------- ------------------------------ ---------- ----------
ROGER HT01 ROGER 6 51929 51929 TABLE logical reads 0 8016
ROGER HT01 ROGER 6 51929 51929 TABLE buffer busy waits 1 0
ROGER HT01 ROGER 6 51929 51929 TABLE gc buffer busy 2 0
ROGER HT01 ROGER 6 51929 51929 TABLE db block changes 3 6736
ROGER HT01 ROGER 6 51929 51929 TABLE physical reads 4 281
ROGER HT01 ROGER 6 51929 51929 TABLE physical writes 5 360
ROGER HT01 ROGER 6 51929 51929 TABLE physical reads direct 6 0
ROGER HT01 ROGER 6 51929 51929 TABLE physical writes direct 7 275
ROGER HT01 ROGER 6 51929 51929 TABLE gc cr blocks received 9 0
ROGER HT01 ROGER 6 51929 51929 TABLE gc current blocks received 10 0
ROGER HT01 ROGER 6 51929 51929 TABLE ITL waits 11 0
ROGER HT01 ROGER 6 51929 51929 TABLE row lock waits 12 0
ROGER HT01 ROGER 6 51929 51929 TABLE space used 14 1949366
ROGER HT01 ROGER 6 51929 51929 TABLE space allocated 15 3145728
ROGER HT01 ROGER 6 51929 51929 TABLE segment scans 17 1
15 rows selected.
SQL> select * from V_$SEGSTAT where OBJ#=51929;
TS# OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE
---------- ---------- ---------- ------------------------------ ---------- ----------
6 51929 51929 logical reads 0 8016
6 51929 51929 buffer busy waits 1 0
6 51929 51929 gc buffer busy 2 0
6 51929 51929 db block changes 3 6736
6 51929 51929 physical reads 4 281
6 51929 51929 physical writes 5 360
6 51929 51929 physical reads direct 6 0
6 51929 51929 physical writes direct 7 275
6 51929 51929 gc cr blocks received 9 0
6 51929 51929 gc current blocks received 10 0
6 51929 51929 ITL waits 11 0
6 51929 51929 row lock waits 12 0
6 51929 51929 space used 14 1949366
6 51929 51929 space allocated 15 3145728
6 51929 51929 segment scans 17 1
15 rows selected.
从上面来看，单纯的从v$segment_statistics或V_$SEGSTAT中的db block changes来判断，根本不准确。
那么到底有没有方法能知道index的使用频率呢？或者说能大概估算也行？下面继续实验…..
SQL> conn roger/roger
Connected.
SQL> delete from ht01 where object_id =50001;
1 row deleted.
SQL> delete from ht01 where object_id =50002;
1 row deleted.
SQL> delete from ht01 where object_id =50003;
1 row deleted.
SQL> delete from ht01 where object_id =50004;
1 row deleted.
SQL> delete from ht01 where object_id =50005;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select a.sql_id,a.child_number,a.object#,b.fetches,b.executions,b.parse_calls
2 from v$sql_plan a,v$sql b
3 where a.sql_id=b.sql_id
4 and a.object_name='HT01';
SQL_ID CHILD_NUMBER OBJECT# FETCHES EXECUTIONS PARSE_CALLS
------------- ------------ ---------- ---------- ---------- -----------
2ujqu04gb065p 0 0 1 1
8s2kwb4s9h6za 0 0 1 1
b9ypygws6cfkt 0 0 1 1
ak08zv7u8t2wr 0 0 1 1
cqyrzv4bc338d 0 0 1 1
SQL> select count(*) from ht01 where object_id > 51000 and object_id < 52000;
COUNT(*)
----------
558
SQL> begin
2 for i in 51000..52000 loop
3 if mod(i,2)=0 then
4 delete from ht01 where object_id > 51000 and object_id < 52000 and object_id =i;
5 commit;
6 end if;
7 end loop;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select count(*) from ht01 where object_id >51000 and object_id <52000;
COUNT(*)
----------
294
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from ht01 where object_id <1000;
COUNT(*)
----------
953
SQL> select count(*) from ht01 where mod(object_id,2) = 0 and object_id < 1000;
COUNT(*)
----------
481
SQL> begin
2 for i in 1..1000 loop
3 if mod(i,2)=0 then
4 delete from ht01 where object_id <1000 and object_id =i;
5 commit;
6 end if;
7 end loop;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select count(*) from ht01 where mod(object_id,2) = 0 and object_id < 1000;
COUNT(*)
----------
0
SQL> select a.sql_id,a.child_number,a.object#,b.fetches,b.executions,b.parse_calls
2 from v$sql_plan a,v$sql b
3 where a.sql_id=b.sql_id
4 and a.object_name='HT01';
SQL_ID CHILD_NUMBER OBJECT# FETCHES EXECUTIONS PARSE_CALLS
------------- ------------ ---------- ---------- ---------- -----------
6zmpr5khvg42x 0 0 500 1
这里我们可以通过其EXECUTIONS 来进行判断，前提是我要知道这个sql语句可能会使用index，
如在这里执行的delete语句，执行计划如下：
SQL> explain plan for delete from ht01 where object_id < 1000 and object_id = :i;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1212071786
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | DELETE | HT01 | | | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_ID | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(1000>TO_NUMBER(:I))
3 - access("OBJECT_ID"=TO_NUMBER(:I))
filter("OBJECT_ID"<1000)
17 rows selected.
不过这里有一点需要注意的是，对于使用了绑定变量的情况，我们要忽略bing peeking的影响。
比如这里的delete语句实际上执行了500次，那么可能其中有1次或2次由于
bing peeking的原因而在其执行计划中未走index。
如果是查历史信息，我们还可以查询相关的hist视图，如下：
SQL> select a.sql_id,a.OPERATION,a.object#,a.object_name,b.fetches_total,b.executions_total,b.parse_calls_total
2 from DBA_HIST_SQL_PLAN a,DBA_HIST_SQLSTAT b
3 where a.sql_id=b.sql_id
4 and a.object_name='HT01'
5 and a.OPERATION ='DELETE';
SQL_ID OPERATION OBJECT# OBJECT_NAME FETCHES_TOTAL EXECUTIONS_TOTAL PARSE_CALLS_TOTAL
------------- --------- ---------- ------------- ------------- ---------------- -----------------
7xpf73f1rj57r DELETE HT01 0 5050 2
SQL> select SQL_TEXT from DBA_HIST_SQLTEXT where sql_id='7xpf73f1rj57r';
SQL_TEXT
--------------------------------------------------------------------------------
DELETE FROM HT01 WHERE OBJECT_ID >51000 AND OBJECT_ID <52000 AND OBJECT_ID =:B1
-- 这是最开始我测试的时候的操作语句。
总的来说，通过如上两种方式来查询判断index的使用频率，我个人认为还是比较准确的，应该是
可以判断出index的使用频率，这里做个简单的总结：
1. 如果是根据v$视图来查询，这样有很大的局限性，因为一段时间后可能sql已经从
shared pool中被clean out了，对于shared pool较大的情况下，我认为可以定期的
进行采样分析，不过根据业务情况，系统负载以及时间段等关系，可能存在较大的差异；
2. 另外一种方式是通过hist视图来查询，我认为这种方式相对比较准确，比如，我想查询某个index
在过去某一天时间内(10g默认值awr快照保留1周)的使用情况，那么需要修改前面的sql语句，加上
SNAP_ID即可，其实这种方式也有一定的局限性和缺陷，因为超过7天的将无法进行查询，不过我想
即使最近7天的快照也足以满足我们的需求了。
下面贴下sql语句：
select SNAP_ID,
to_char(BEGIN_INTERVAL_TIME, 'yyyymmdd hh24:mi:ss') BEGIN_INTERVAL_TIME
from WRM$_SNAPSHOT
where BEGIN_INTERVAL_TIME between to_date('20110903 00', 'yyyymmdd hh24') and
to_date('20110905 23', 'yyyymmdd hh24')
and INSTANCE_NUMBER = 1
order by 2;
select a.sql_id,
a.OPERATION,
a.object#,
a.object_name,
b.fetches_total,
b.executions_total,
b.parse_calls_total
from DBA_HIST_SQL_PLAN a, DBA_HIST_SQLSTAT b
where a.sql_id = b.sql_id
and a.sql_id = '6zmpr5khvg42x'
and b.snap_id > &n
and b.snap_id < &n;
select a.sql_id,
a.OPERATION,
a.object#,
a.object_name,
b.fetches_total,
b.executions_total,
b.parse_calls_total
from DBA_HIST_SQL_PLAN a, DBA_HIST_SQLSTAT b
where a.sql_id = b.sql_id
-- and a.sql_id = '6zmpr5khvg42x'
and a.operation = 'INDEX'
and a.object# = '&id' "index object_id"
and b.snap_id > &n
and b.snap_id < &n;
最好是根据object#去查询比较好，如下：
SQL> select a.sql_id,
2 a.OPERATION,
3 a.object#,
4 a.object_name,
5 b.fetches_total,
6 b.executions_total,
7 b.parse_calls_total
8 from DBA_HIST_SQL_PLAN a, DBA_HIST_SQLSTAT b
9 where a.sql_id = b.sql_id
10 -- and a.sql_id = '6zmpr5khvg42x'
11 and a.operation = 'INDEX'
12 and a.object# = '51930';
SQL_ID OPERATION OBJECT# OBJECT_NAME FETCHES_TOTAL EXECUTIONS_TOTAL PARSE_CALLS_TOTAL
------------- ---------- ---------- ------------ ------------- ---------------- -----------------
7xpf73f1rj57r INDEX 51930 IDX_ID 0 5050 2
发表回复