【原创】由隐式转换引起的数据库性能问题-优快云博客

今天有用户抱怨一个生产数据库的使用速度非常慢，最后发现是由数据类型的隐式转换引起的。下面是排查步骤：

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