Oracle索引的監控

Oracle索引的監控

一.1 BLOG文件結構圖

一.2 前言部分

一.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 掌握oracle中索引的監控方法

② sys.col_usage$的初步瞭解

Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

一.3 相關知識點掃盲(摘自網路)

合理的為資料庫表上建立戰略性索引，可以極大程度的提高查詢效能。但事實上日常中我們所建立的索引並非戰略性索引，恰恰是大量冗餘或是根本沒有用到的索引耗用了大量的儲存空間，導致DML效能低下。應用程式在開發時，可能會建立眾多索引，但是這些索引的使用到底怎麼樣，是否有些索引一直都沒有用到過，這需要我們對這些索引進行監控，以便確定他們的使用情況，併為是否可以清除它們給出依據。

冗餘索引的弊端：

大量冗餘和無用的索引導致整個資料庫效能低下，耗用了大量的CPU與I/O開銷，具體表現如下:

a、浪費大量的儲存空間，尤其是大表的索引，浪費的儲存空間尤其可觀(索引段的維護與管理)

b、增加了DML 操作（UPDATE、INSERT、DELETE）的開銷

c、耗用大量統計資訊(索引)收集的時間

d、結構性驗證時間

f、增加了恢復所需的時間

本文介紹兩種方式：

第一：開啟監控功能；

第二：檢視歷史的執行計劃，進行分析；

一.4 索引監控的方法

一.4.1 方法一：開啟監控功能

1、單個索引監控

a、對於單個索引的監控，可以使用下面的命令來完成

alter index <INDEX_NAME> monitoring usage;

b、關閉索引監控

alter index <INDEX_NAME> nomonitoring usage;

c、觀察監控結果(查詢v$object_usage檢視)

select * from v$object_usage;

2、schema級別索引監控

如果我們想在系統中監控所有的索引，那麼我們可以通過下面指令碼實現監控資料庫所有的索引。注意我們要排除一些系統表的索引、以及LOB indexes。原因有下面兩個：

1：LOB indexes不能修改，否則會報ORA-22864錯誤（ORA-22864: cannot ALTER or DROP LOB indexes）。

2：ORA-00701: object necessary for warmstarting database cannot be altered

ORA-00701: object necessary for warmstarting database cannot be altered

00701. 00000 - "object necessary for warmstarting database cannot be altered"

*Cause: Attempt to alter or drop a database object (table, cluster, or

index) which are needed for warmstarting the database.

*Action: None.

直接執行指令碼來開啟索引監控，當然監控索引時長非常重要，太短的話有可能導致查詢出來的資料有問題，一般建議監控一週後即可，OLAP系統則需要適當延長監控的時間。

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

監控一個月就大概可以知道那些是無用的索引了。

雖然v$object_usage表能記錄索引監控和使用的狀態，但它不能統計索引被使用的次數和頻率，只記錄了在開啟索引監控的時間段索引是否被使用過，這一點要值的注意。

另外需要注意的2點：

① 10g在收集統計資訊時會導致索引被監控、這並非SQL語句產生、而在11g則不會出現這種情況了

② 外來鍵索引不會因為主表的DML操作而被監控到、不要因為該索引沒用而將它給刪了

一.4.1.1 個人實驗

新建1個表TB_LHR_20160622，並建立2個索引：

SYS@raclhr2> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@raclhr2> Create Table TB_LHR_20160622 nologging As select * from dba_objects;

Table created.

SYS@raclhr2> create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

Index created.

SYS@raclhr2> create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

Index created.

查詢v$object_usage檢視，收集統計資訊：

SYS@raclhr2> select * from v$object_usage;

no rows selected

SYS@raclhr2> BEGIN

2 dbms_stats.gather_table_stats(USER,

3 'TB_LHR_20160622',

4 cascade => TRUE,

5 degree => 8);

6 END;

7 /

PL/SQL procedure successfully completed.

SYS@raclhr2> select * from v$object_usage;

no rows selected

開啟索引的監控：

SYS@raclhr2> alter index ind_TB_LHR_20160622_id monitoring usage;

Index altered.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

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

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES NO 06/22/2016 15:15:54

SYS@raclhr2> alter index ind_TB_LHR_20160622_name monitoring usage;

Index altered.

SYS@raclhr2> select count(1) from TB_LHR_20160622 t where t.object_id=88;

COUNT(1)

----------

1

SYS@raclhr2> explain plan for select count(1) from TB_LHR_20160622 t where t.object_id=88;

Explained.

SYS@raclhr2> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2688591802

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

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

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

Predicate Information (identified by operation id):

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

2 - access("T"."OBJECT_ID"=88)

14 rows selected.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

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

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

注意：SELECT * FROM V$OBJECT_USAGE; 只能檢視當前使用者下被監控的索引資訊。即使sys、system使用者也不能檢視其它使用者的資訊，如下，但我們可以建立一個檢視來解決這個問題。

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> select * from v$object_usage;

no rows selected

SCOTT@raclhr2> conn / as sysdba

Connected.

SYS@raclhr2> create or replace view vw_INDEX_USAGE_lhr AS

2 SELECT U.NAME OWNER,

3 IO.NAME INDEX_NAME,

4 T.NAME TABLE_NAME,

5 DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

6 DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

7 OU.START_MONITORING START_MONITORING,

8 OU.END_MONITORING END_MONITORING

9 FROM SYS.USER$ U,

10 SYS.OBJ$ IO,

11 SYS.OBJ$ T,

12 SYS.IND$ I,

13 SYS.OBJECT_USAGE OU

14 WHERE I.OBJ# = OU.OBJ#

15 AND IO.OBJ# = OU.OBJ#

16 AND T.OBJ# = I.BO#

17 AND U.USER# = IO.OWNER#;

View created.

SYS@raclhr2> create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

Synonym created.

SYS@raclhr2> grant select on sys.vw_INDEX_USAGE_lhr to public;

Grant succeeded.

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> set line 9999 pagesize 9999

SCOTT@raclhr2> col owner format A10

SCOTT@raclhr2> COL INDEX_NAME FOR A25

SCOTT@raclhr2> COL TABLE_NAME FOR A20

SCOTT@raclhr2> COL MONITORING FOR A10

SCOTT@raclhr2> COL USED FOR A10

SCOTT@raclhr2> COL START_MONITORING FOR A20

SCOTT@raclhr2> COL END_MONITORING FOR A20

SCOTT@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

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

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

取消索引的監控：

SCOTT@raclhr2> CONN / AS SYSDBA

Connected.

SYS@raclhr2> alter index ind_TB_LHR_20160622_id nomonitoring usage;

Index altered.

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

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

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 NO YES 06/22/2016 15:15:54 06/22/2016 15:22:30

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

SYS@raclhr2> alter index ind_TB_LHR_20160622_name nomonitoring usage;

Index altered.

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

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

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 NO YES 06/22/2016 15:15:54 06/22/2016 15:22:30

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 NO NO 06/22/2016 15:22:45 06/22/2016 15:23:12

一.4.1.2 實驗中用到的SQL

drop table TB_LHR_20160622 purge;

Create Table TB_LHR_20160622 nologging As select * from dba_objects;

create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

select * from v$object_usage;

BEGIN

dbms_stats.gather_table_stats(USER,

'TB_LHR_20160622',

cascade => TRUE,

degree => 8);

END;

/

alter index ind_TB_LHR_20160622_id monitoring usage;

alter index ind_TB_LHR_20160622_name monitoring usage;

select count(1) from TB_LHR_20160622 t where t.object_id=88;

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

select * from v$object_usage;

注意：SELECT * FROM V$OBJECT_USAGE; 只能檢視當前使用者下被監控的索引資訊。即使sys、system使用者也不能檢視其它使用者的資訊。

alter index ind_TB_LHR_20160622_id nomonitoring usage;

alter index ind_TB_LHR_20160622_name nomonitoring usage;

--- drop table t purge; 表刪掉後 v$object_usage 中關於監控的資訊也刪除了

----切換使用者後查詢select * from v$object_usage;查詢不到資料，下邊這個SQL可以查詢任何使用者下的索引使用情況

create or replace view vw_INDEX_USAGE_lhr AS

SELECT U.NAME OWNER,

IO.NAME INDEX_NAME,

T.NAME TABLE_NAME,

DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

OU.START_MONITORING START_MONITORING,

OU.END_MONITORING END_MONITORING

FROM SYS.USER$ U,

SYS.OBJ$ IO,

SYS.OBJ$ T,

SYS.IND$ I,

SYS.OBJECT_USAGE OU

WHERE I.OBJ# = OU.OBJ#

AND IO.OBJ# = OU.OBJ#

AND T.OBJ# = I.BO#

AND U.USER# = IO.OWNER#;

create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

SELECT * FROM syn_INDEX_USAGE_lhr;

批量監控系統的所有索引：

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

一.4.2 方法二：檢視歷史的執行計劃進行分析

雖然v$object_usage表能記錄索引監控和使用的狀態，但它不能統計索引被使用的次數和頻率，只記錄了在開啟索引監控的時間段索引是否被使用過，因此想詳細瞭解索引的使用情況我們可以利用AWR的一些檢視dba_hist_sql_plan和dba_hist_sqlstat來弄清楚資料庫訪問某個索引的次數、索引訪問的型別，如索引範圍掃描或索引唯一掃描。

WITH tmp1 AS

(SELECT i.OWNER INDEX_OWNER,

i.table_owner,

TABLE_NAME,

INDEX_NAME,

INDEX_TYPE,

(select nb.created

from dba_objects nb

WHERE nb.owner = i.owner

and nb.object_name = i.index_name

and nb.subobject_name is null) created,

(SUM(S.bytes) / 1024 / 1024) INDEX_MB

FROM DBA_SEGMENTS S, DBA_INDEXES I

WHERE i.INDEX_NAME = s.SEGMENT_NAME

and i.owner = s.owner

and s.owner not like '%SYS%'

/*and s.owner = 'FUNDZ'*/

GROUP BY i.OWNER, i.table_owner, TABLE_NAME, INDEX_NAME, INDEX_TYPE

HAVING SUM(S.BYTES) > 1024 * 1024),

tmp2 as

(SELECT index_owner,

index_name,

plan_operation,

(SELECT min(to_char(nb.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS'))

FROM dba_hist_snapshot nb

where nb.snap_id = v.min_snap_id) min_date,

(SELECT max(to_char(nb.end_interval_time, 'YYYY-MM-DD HH24:MI:SS'))

FROM dba_hist_snapshot nb

where nb.snap_id = v.max_snap_id) max_date,

counts

FROM (SELECT d.object_owner index_owner,

d.object_name index_name,

d.operation || ' ' || d.options plan_operation,

min(h.snap_id) min_snap_id,

max(h.snap_id) max_snap_id,

COUNT(1) counts

FROM dba_hist_sql_plan d, dba_hist_sqlstat h

WHERE /*d.object_owner = 'FUNDZ'

AND */

d.operation LIKE '%INDEX%'

AND d.sql_id = h.sql_id

GROUP BY d.object_owner, d.object_name, d.operation, d.options) v)

SELECT a.table_owner,

a.TABLE_NAME,

a.index_owner,

a.index_name,

a.created,

a.INDEX_TYPE,

a.INDEX_MB,

b.plan_operation,

min_date,

max_date,

counts

from tmp1 a

left outer join tmp2 b

on (a.index_owner = b.index_owner and a.index_name = b.index_name);

如上圖所示，有一個3.6G大的索引在13號到22號從沒使用過，接下來，我們可以繼續查詢該索引是否聯合索引，建立是否合理，分析為何不走該索引，從而判斷是否可以刪除索引。

另外下邊的SQL可以查詢出表上列的使用情況：

CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS

SELECT oo.name owner,

o.name table_name,

c.name column_name,

u.equality_preds,

u.equijoin_preds,

u.nonequijoin_preds,

u.range_preds,

u.like_preds,

u.null_preds,

u.timestamp

FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c

WHERE o.obj# = u.obj#

AND oo.user# = o.owner#

AND c.obj# = u.obj#

AND c.col# = u.intcol#

;

About Me

..........................................................................................................................................................................................................

本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

本文地址：

本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

聯絡我請加QQ好友(642808185)，註明新增緣由

於 2016-04-06 10:00~ 2016-04-11 19:00 在中行完成

..........................................................................................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2120752/，如需轉載，請註明出處，否則將追究法律責任。

Oracle索引的監控

一.1 BLOG文件結構圖

一.2 前言部分

一.2.1 導讀和注意事項

一.3 相關知識點掃盲(摘自網路)

一.4 索引監控的方法

一.4.1 方法一：開啟監控功能

一.4.1.1 個人實驗

一.4.1.2 實驗中用到的SQL

一.4.2 方法二：檢視歷史的執行計劃進行分析

相關文章