关于index的监控
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 关于index的监控
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
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完全可以删除。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
###### 为了解决上面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的使用频率呢？或者说能大概估算也行？下面继续实验…..
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
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语句，执行计划如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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视图，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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语句：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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#去查询比较好，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
One Response to “关于index的监控”
如果index没用monitoring usage，也可以用你这语句查出来对吧？
还有你说的第一个问题没看到你怎么解决的
Leave a Reply
You must be logged in to post a comment.