【原創】由隱式轉換引起的資料庫效能問題

今天有使用者抱怨一個生產資料庫的使用速度非常慢，最後發現是由資料型別的隱式轉換引起的。下面是排查步驟：

1.看看目前資料庫最大的等待是什麼？發現是db file scattered read ，懷疑是有不正確的全表掃描造成的
sys@FGOSNT>select * from (select event,total_waits,time_waited from v$system_event where wait_class!='Idle' order by 2 desc)
where rownum<=5;
EVENT                                                           TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
db file scattered read                                              56245619    31166953
read by other session                                               30575631    16568231
SQL*Net message to client                                           29673044        4005
db file sequential read                                             22843329     8484794
control file sequential read                                         3171324      413246

2.檢視資料庫中大於6秒的所有操作，發現對錶IF_FGOS_ORG_MSG_STORE的全表掃描非常頻繁
sys@FGOSNT>select opname,target,sql_hash_value,count(*) from v$session_longops group by opname,target,sql_hash_value;
OPNAME              TARGET                         SQL_HASH_VALUE   COUNT(*)
-------------------- ------------------------------ -------------- ----------
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        2671583073          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        1179334446          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        3653467906          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        3264730714          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        2851359828          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE         959356257          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE         805191316          1
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        2462324080        490
Table Scan           FGOSI.IF_FGOS_ORG_MSG_STORE        2540726680          1

11 rows selected.

3.檢視進行全表掃描的SQL，發現SQL比較簡單，是對錶IF_FGOS_ORG_MSG_STORE的查詢，where中message0_.FM_OP_TM>=:1和message0_.FM_AGENT=1是兩個過濾條件
sys@FGOSNT>select sql_text from v$sqltext where hash_value=2462324080 order by address,piece;
SQL_TEXT
----------------------------------------------------------------
select message0_.FM_ID as FM1_15_, message0_.FM_FLTNO as FM2_15_
, message0_.FM_PE_DATE as FM3_15_, message0_.FM_TYPE_A_D as FM4_
15_, message0_.FM_MSG_SUBTYPE as FM5_15_, message0_.FM_MSG_TYPE
as FM6_15_, message0_.FM_INFO as FM7_15_, message0_.FM_INFO_OLD
as FM8_15_, message0_.FM_SHOWINFO as FM9_15_, message0_.FM_SND_D
EPT as FM10_15_, message0_.FM_REV_DEPT as FM11_15_, message0_.FM
_TM_SND as FM12_15_, message0_.FM_OWNER as FM13_15_, message0_.F
M_COMMENT as FM14_15_, message0_.FM_OP_TM as FM15_15_, message0_
.FM_VERSION as FM16_15_, message0_.FM_IS_CALLBACK as FM17_15_, m
essage0_.FM_INFO_TYPE as FM18_15_, message0_.FM_TYPE_I_D as FM19
_15_, message0_.FM_SEG_I_D as FM20_15_, message0_.FM_RELATED_FLT
 as FM21_15_, message0_.FM_AGENT as FM22_15_, message0_.FM_NOTIC
E as FM23_15_, message0_.FM_SUBTYPE_PHRASE_RULE as FM24_15_, mes
sage0_.FM_PE_DATE_LOGIC as FM25_15_ from IF_FGOS_ORG_MSG_STORE m
essage0_ where message0_.FM_OP_TM>=:1 and message0_.FM_AGENT=1 o
rder by message0_.FM_ID

16 rows selected.

4.檢視IF_FGOS_ORG_MSG_STORE表中FM_AGENT的分佈情況，發現表中大部分記錄的FM_AGENT都為1，這個過濾條件用處並不太
sys@FGOSNT>select count(distinct FM_AGENT) from FGOSI.IF_FGOS_ORG_MSG_STORE;
COUNT(DISTINCTFM_AGENT)
---------------------
2

sys@FGOSNT>select FM_AGENT,count(*) from FGOSI.IF_FGOS_ORG_MSG_STORE group by FM_AGENT;
FM_AGENT     COUNT(*)
----------- ---------------------
1            84578
0            2390
             1967

5.用同樣的方法發現IF_FGOS_ORG_MSG_STORE表中每條記錄的FM_OP_TM列上的值都不一樣（High Cardinality），按理說應該是走Index的
sys@FGOSNT>select count(distinct FM_OP_TM) from FGOSI.IF_FGOS_ORG_MSG_STORE;
COUNT(DISTINCTFM_OP_TM)
---------------------
86845

6.這個列上確實是有Index的，而且Index最近也分析過
sys@FGOSNT>select index_name,column_name from dba_ind_columns where table_name='IF_FGOS_ORG_MSG_STORE';
INDEX_NAME                    COLUMN_NAME
------------------------------ ------------------------------
SYS_C008295                    FM_ID
XIF_FGOS_ORG_MSG_OPTM          FM_OP_TM

sys@FGOSNT>select index_name,last_analyzed from dba_indexes where table_name='IF_FGOS_ORG_MSG_STORE';
INDEX_NAME                    LAST_ANALYZE
------------------------------ ------------
SYS_C008295                    23-MAY-08
XIF_FGOS_ORG_MSG_OPTM          23-MAY-08

7.最後檢視錶IF_FGOS_ORG_MSG_STORE的結構發現了問題：FM_OP_TM列是Date型別了，而message0_.FM_OP_TM>=:1沒有使用to_date函式進行顯式資料型別轉換，Oracle使用了隱式轉換，造成了Index無法使用。經與開發人員核實，原來是昨晚有新的程式上線，這個SQL就是新上線的程式中的。不規範的程式對資料庫的影響太大了！！

sys@FGOSNT>desc fgosi.IF_FGOS_ORG_MSG_STORE
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------
 FM_ID                                                 NOT NULL NUMBER(31)
 FM_FLTNO                                                       VARCHAR2(16)
 FM_PE_DATE                                                     DATE
 FM_TYPE_A_D                                                    NUMBER(2)
 FM_MSG_TYPE                                                    NUMBER(6)
 FM_INFO                                                        VARCHAR2(2000)
 FM_SND_DEPT                                                    VARCHAR2(64)
 FM_REV_DEPT                                                    VARCHAR2(400)
 FM_TM_SND                                                      DATE
 FM_RCV_STATUS                                                  VARCHAR2(4000)
 FM_OWNER                                                       VARCHAR2(64)
 FM_COMMENT                                                     VARCHAR2(200)
 FM_OP_TM                                                       DATE
 FM_MSG_SUBTYPE                                                 NUMBER(4)
 FM_VERSION                                                     VARCHAR2(20)
 FM_IS_CALLBACK                                                 NUMBER(2)
 FM_SHOWINFO                                                    VARCHAR2(2000)
 FM_INFO_TYPE                                                   NUMBER(2)
 FM_SEG_I_D                                                     NUMBER(2)
 FM_INFO_OLD                                                    VARCHAR2(2000)
 FM_TYPE_I_D                                                    NUMBER(2)
 FM_RELATED_FLT                                                 VARCHAR2(16)
 FM_AGENT                                                       NUMBER(2)
 FM_NOTICE                                                      VARCHAR2(10)
 FM_SUBTYPE_PHRASE_RULE                                         VARCHAR2(200)
 FM_PE_DATE_LOGIC                                               DATE

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