連結：http://blog.itpub.net/28602568/viewspace-1362044/

① 執行儲存

② 呼叫儲存

③ 1,2步驟指令碼

④ 發郵件[日期判斷指令碼]

⑤ 定時任務

① 執行儲存

[root@lottery idx_check]# cat p_idx_check.sql

CREATE OR REPLACE PACKAGE P_IDX_CHECK IS
--INSERT
PROCEDURE P_INSERT(USERNAME VARCHAR2,
                     USERNAME1 VARCHAR2,
                     USERNAME2 VARCHAR2);
--統計使用比
PROCEDURE P_analyze(USERNAME VARCHAR2,
                      USERNAME1 VARCHAR2,
                      USERNAME2 VARCHAR2);

END P_IDX_CHECK;

/

/

CREATE OR REPLACE PACKAGE BODY P_IDX_CHECK IS

/*----------------------------------------------------------

| AUTHOR：lottery |

| USERS ：以月為單位檢查索引的使用情況(郵件反饋)且維護索引 |

| BLOG : |

-----------------------------------------------------------*/

/*

註釋：

工作中是否有一些現有的索引，從來/幾個月都沒有被用過的情況？

索引建立情況存在的情況

1) 初步設計建的; 當時業務加索引有意義，後續業務最佳化，透過其他表或者透過其他方式解決了...

2) 最佳化SQL建的；資料量不太大時最佳化加的，後續生產資料量會越來越大.這樣的索引需要監控是否有意了

3) 殘留的索引； 1,2種情況，或生產上DBA最佳化SQL，並沒有效果，後忙於他事忘了的情況...等等...(可能會發生)

那是不是說這些索引建立了之後就真的都會用到呢？如果沒有用到不會對INSERT UPDATE DELETE有效能問題麼！

？主要的問題來了

怎麼知道哪些索引長時間沒有用過呢？

如下儲存就是為了實現這個情況...

？可能有人就好奇了

我想看索引使用情況直接透過DBA_HIST_SQL_PLAN/GV$SQL_PLAN...等ORACLE自帶的檢視查就好了，還寫什麼儲存呢？

就是因為ORACLE自帶的檢視不是永久儲存的；

且主題想統計幾個月的索引使用情況，若幾個月都不用的索引，且採取的時間包含多次全部業務,那一直沒用的索引是否可以刪除呢？(要根據情況定)

DBA_HIST_SQL_PLAN/GV$SQL_PLAN...等ORACLE自帶的檢視的好處：

①、可以瞭解一定時間的索引情況...能對現有索引瞭解下也是件好事；

②、是以月為單位檢查索引的使用情況(郵件反饋)且維護索引的基礎；

注意一點，刪除索引為了保險最好至少已月為單位去監控一直不用的索引進行清理維護；

大體思路：

上文有講到ORACLE自帶的檢視可以讀取近期的索引使用情況；

所以整個儲存很簡單每日或每幾個小時定時將[ORACLE自帶的檢視可以讀取近期的索引使用情況]的檢視進行INSERT一個表...

[本文用的GV$SQL_PLAN檢視] 以月為單位的去統計下使用情況....再自動發郵件給各組組長分析下...

大體思路看完了相比您應該也知道怎麼做了...廢話不多說了..往下走吧....

*/

PROCEDURE P_INSERT(USERNAME VARCHAR2,

USERNAME1 VARCHAR2,

USERNAME2 VARCHAR2) IS

--傳入需要統計的索引的所在使用者

T_EXIST NUMBER(3 );

BEGIN

-- 判斷保留每日或每幾個小時 GV$SQL_PLAN的中間表MHQ_SQL_PLAN是否存在..

-- 不存在直接路徑建立表,,存在直接INSERT

SELECT COUNT (1)

INTO T_EXIST

FROM USER_TABLES T

WHERE T.TABLE_NAME = 'MHQ_SQL_PLAN'

AND ROWNUM = 1 ;

IF T_EXIST = 0 THEN

EXECUTE IMMEDIATE ('

CREATE TABLE MHQ_SQL_PLAN TABLESPACE USERS NOLOGGING AS

SELECT P.SQL_ID, P.OBJECT_NAME

FROM GV$SQL_PLAN P

WHERE TO_CHAR(P.TIMESTAMP, ''YYYY-MM-DD HH24'') >

TO_CHAR(SYSDATE - 12 / 24, ''YYYY-MM-DD HH24'')--12個小時內

AND OBJECT_NAME IS NOT NULL

AND P.OPERATION = ''INDEX''

AND OBJECT_OWNER IN (''' ||

UPPER(USERNAME) || ''',''' || UPPER(USERNAME1) ||

''',''' || UPPER(USERNAME2) || ''')' );

ELSE

NULL;

EXECUTE IMMEDIATE ('

INSERT INTO MHQ_SQL_PLAN NOLOGGING

SELECT P.SQL_ID, P.OBJECT_NAME FROM GV$SQL_PLAN P

WHERE TO_CHAR(P.TIMESTAMP, ''YYYY-MM-DD HH24'') >

TO_CHAR(SYSDATE - 12 / 24, ''YYYY-MM-DD HH24'') --12個小時內

AND OBJECT_NAME IS NOT NULL

AND P.OPERATION = ''INDEX''

AND OBJECT_OWNER IN (''' ||

UPPER(USERNAME) || ''',''' || UPPER(USERNAME1) ||

''',''' || UPPER(USERNAME2) || ''')' );

COMMIT;

END IF ;

--判斷SYSDATE是否是當月的最後一天

--如果今天是最後月底將透過MHQ_SQL_PLAN表統計使用比..

IF TO_CHAR(LAST_DAY(SYSDATE ), 'YYYY_MM_DD') /*LASTDAY*/

!= TO_CHAR( SYSDATE, 'YYYY_MM_DD' ) /*TODAY*/

THEN

NULL;

ELSE

P_IDX_CHECK.P_ANALYZE(USERNAME, USERNAME1, USERNAME2); --透過MHQ_SQL_PLAN表統計使用比 步驟

END IF ;

END P_INSERT;

/*

**透過MHQ_SQL_PLAN表統計使用比 步驟

一個月用到索引的記錄已記錄到MHQ_SQL_PLAN表中；

但該表不包含未使用的索引記錄,所以需要與DBA_INDEXES 進行關聯(依據DBA_INDEXES查詢 ∴DBA_INDEXES LEFT JOIN MHQ_SQL_PLAN)

繼續完善帶上表名稱和欄位名稱...具體看遊標處

*/

PROCEDURE P_ANALYZE(USERNAME VARCHAR2,

USERNAME1 VARCHAR2,

USERNAME2 VARCHAR2) IS

P_TABLE_NAME VARCHAR2(100 );

P_INDEX_NAME VARCHAR2(100 );

P_COLUMN_NAME VARCHAR2(4000 );

P_COUNT NUMBER(10 ); --一個月索引的使用次數量

CURSOR C_CONS IS --遊標

SELECT IC.TABLE_NAME,

TO_CHAR(WM_CONCAT(IC.COLUMN_NAME)) COLUMN_NAME,

I.INDEX_NAME,

( CASE

WHEN P.COUNT_ IS NULL THEN

0

ELSE

COUNT_

END) COUNT_

FROM (SELECT INDEX_NAME

FROM DBA_INDEXES I

WHERE I.OWNER IN

(UPPER(USERNAME), UPPER(USERNAME1), UPPER(USERNAME2))) I

LEFT JOIN (SELECT OBJECT_NAME, COUNT(1 ) COUNT_

FROM MHQ_SQL_PLAN P

GROUP BY P.OBJECT_NAME) P

ON I.INDEX_NAME = P.OBJECT_NAME

JOIN DBA_IND_COLUMNS IC

ON I.INDEX_NAME = IC.INDEX_NAME

AND IC.TABLE_OWNER IN

(UPPER(USERNAME), UPPER(USERNAME1), UPPER(USERNAME2))

GROUP BY IC.TABLE_NAME, I.INDEX_NAME, COUNT_

ORDER BY COUNT_, TABLE_NAME;

BEGIN

--列印各列註釋，避免長度不一，看著亂，特加右對齊函式RPAD

DBMS_OUTPUT.PUT_LINE(RPAD( 'TABLE_NAME', 31 , ' ') ||

RPAD( 'INDEX_NAME', 31 , ' ') ||

RPAD( 'COLUMN_NAME', 55 , ' ') ||

RPAD( 'COUNT', 10 , ' '));

DBMS_OUTPUT.PUT_LINE(RPAD( '-', 31 , '-') || RPAD( '-', 31 , '-') ||

RPAD( '-', 55 , '-') || RPAD( '-', 10 , '-'));

-- 列印各列註釋接下來是列印索引的分析情況了

-- 避免如下報錯加DBMS_OUTPUT.ENABLE

-- ERRORS RAISED:

-- -20000, ORU-10027: BUFFER OVERFLOW, LIMIT OF BYTES.

-- -20000, ORU-10028:LINE LENGTH OVERFLOW, LIMIT OF 32767 BYTES PER LINE.

DBMS_OUTPUT.ENABLE( 1000000);

-- 僅僅是設定整個過程的全部輸出緩衝區大小，不能控制是否在客戶端顯示輸出資訊。

--ENABLE具體資訊：

-- PROCEDURE ENABLE (BUFFER_SIZE IN INTEGER DEFAULT 20000);

-- PRAGMA RESTRICT_REFERENCES(ENABLE,WNDS,RNDS);

-- ENABLE CALLS TO PUT, PUT_LINE, NEW_LINE, GET_LINE AND GET_LINES.

-- CALLS TO THESE PROCEDURES ARE NOOPS IF THE PACKAGE HAS

-- NOT BEEN ENABLED. SET DEFAULT AMOUNT OF INFORMATION TO BUFFER.

-- CLEANUP DATA BUFFERED FROM ANY DEAD SESSIONS. MULTIPLE CALLS TO

-- ENABLE ARE ALLOWED.

-- INPUT PARAMETERS:

-- BUFFER_SIZE

-- AMOUNT OF INFORMATION, IN BYTES, TO BUFFER. VARCHAR2, NUMBER AND

-- DATE ITEMS ARE STORED IN THEIR INTERNAL REPRESENTATION. THE

-- INFORMATION IS STORED IN THE SGA. AN ERROR IS RAISED IF THE

-- BUFFER SIZE IS EXCEEDED. IF THERE ARE MULTIPLE CALLS TO ENABLE,

-- THEN THE BUFFER_SIZE IS GENERALLY THE LARGEST OF THE VALUES

-- SPECIFIED, AND WILL ALWAYS BE >= THAN THE SMALLEST VALUE

-- SPECIFIED. CURRENTLY A MORE ACCURATE DETERMINATION IS NOT

-- POSSIBLE. THE MAXIMUM SIZE IS 1,000,000, THE MINIMUM IS 2000.

FOR P_C_CONS IN C_CONS LOOP

--利用C_CONS遊標對應列值

P_TABLE_NAME := P_C_CONS.TABLE_NAME;

P_INDEX_NAME := P_C_CONS.INDEX_NAME;

P_COLUMN_NAME := P_C_CONS.COLUMN_NAME;

P_COUNT := P_C_CONS.COUNT_;

--迴圈列印索引1個月的使用情況詳情

DBMS_OUTPUT.PUT_LINE(RPAD(P_TABLE_NAME, 31, ' ' ) ||

RPAD(P_INDEX_NAME, 31, ' ' ) ||

RPAD(P_COLUMN_NAME, 56, ' ' ) ||

RPAD(P_COUNT, 11, ' ' ));

--PUT_LINE具體資訊

-- PROCEDURE PUT_LINE(A VARCHAR2);

-- PRAGMA RESTRICT_REFERENCES(PUT_LINE,WNDS,RNDS);

-- PUT A PIECE OF INFORMATION IN THE BUFFER FOLLOWED BY AN END-OF-LINE

-- MARKER. WHEN RETRIEVED BY GET_LINE(S), THE NUMBER AND DATE ITEMS

-- WILL BE FORMATED WITH TO_CHAR USING THE DEFAULT FORMATS. IF YOU

-- WANT ANOTHER FORMAT THEN FORMAT IT EXPLICITLY. GET_LINE(S) RETURN

-- "LINES" AS DELIMITED BY "NEWLINES". SO EVERY CALL TO PUT_LINE OR

-- NEW_LINE WILL GENERATE A LINE THAT WILL BE RETURNED BY GET_LINE(S).

-- INPUT PARAMETERS:

-- A

-- ITEM TO BUFFER

-- ERRORS RAISED:

-- -20000, ORU-10027: BUFFER OVERFLOW, LIMIT OF BYTES.

-- -20000, ORU-10028:LINE LENGTH OVERFLOW, LIMIT OF 32767 BYTES PER LINE.

--PUT_LINE另種方式（UTL_FILE）

END LOOP ;

END P_ANALYZE;

-- 擴充套件：

-- DBMS_OUTPUT.ENABLE 和 SET SERVEROUTPUT ON 區別

--

END P_IDX_CHECK;

/
[root@lottery idx_check]#

②

呼叫儲存

[root@lottery idx_check]# cat idx_check.sql
/
set serveroutput on
begin
-- Call the procedure
sys.p_idx_check.p_insert(username => 'FWSC',
username1 => 'FWSB',
username2 => 'FWSW');
end;
/

exit;

③ 1,2步驟指令碼
[root@lottery idx_check]# cat idx_check.sh

#！/bin/sh

#su - oracle

echo
echo
echo ....................BEGIN.........................
date=`date +%Y_%m_%d`
echo $date
echo
echo .........oracle ................

echo
#echo .........EXEC PROCEDURE...........
#su - oracle << EOF
#export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
#sqlplus "地址" @/u01/idx_check/p_idx_check.sql> /u01/idx_check/p_idx_check.log
#EOF

echo
echo .........CALL PROCEDURE...........
su - oracle << EOF
export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
sqlplus "地址" @/u01/idx_check/idx_check.sql> /u01/idx_check/idx_check_RAC.log
EOF


echo
echo .....................END...........................

④ 發郵件[日期判斷指令碼]

[root@lottery idx_check]# cat day_check.sh

#!/bin/sh
today=`date +%d`
last_day=`cal | xargs | awk '{print $NF}'`
if [ "$today" = "$last_day" ]; then
/bin/mail -s "ORACLE_RAC環境以月為單位檢查索引的使用情況(郵件反饋)" QQ@qq.com < /u01/idx_check/idx_check_RAC.log -- -f QQ1@qq.com

# -s 主題

# QQ@qq.com => 收件人

# QQ1@qq.com => 發件人

fi
[root@lottery idx_check]#

⑤ 定時任務

[root@lottery idx_check]# crontab -l

#索引檢查
55 12,23 * * * /bin/sh /u01/idx_check/idx_check.sh > /u01/idx_check/idx_check_RAC.log#避免gv$sqlarea時間久的被清除,可每12,23:55追加sql_plan

#傳送郵件
58 23 * * * /bin/sh /u01/idx_check/day_check.sh

註釋：

--本文只是針對索引使用進行簡單統計
--可以針對filter/MERGE JOIN CARTESIAN/index skip scan 對其整理【表名字可以和索引名字相同】

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle 以月為單位檢查索引的使用情況(郵件反饋)

相關文章