sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle运维监控之index监控笔记

时间:2016-11-21 18:59   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle运维监控需要的一项工作之INDEX索引监控,监控方法详见文章内容。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 关于index的监控

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> 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.Oracleо

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle运维监控之index监控笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1303.html

Oracle研究中心

关键词:

运维工程师之关于index的监控

监控Oracle index索引笔记