生产系统-oracle调优之索引小例
背景介绍：
接到客户反馈现象：某市某信息管理信息系统，部分功能项响应缓慢，希望我们给予解决。
一、操作中发现响应时长问题
（1）、功能项中列表界面响应时长5s左右
（2）、功能项中单条信息界面响应时长3s左右
点击单条记录信息，响应达到接近3s。
二、调优过程记录
（1）、功能项中列表界面响应时长调优
以一小时为单位生成ADDM报告，锁定一项关注点，如下：
摘自报告：
ADDM报告中的建议：
建议案 3: SQL 优化
估计的收益为 .04 个活动会话, 占总活动的 11.11\%。
--------------------------------
操作
对 SELECT 语句 (SQL_ID 为 "5q54sctfu8t9k") 运行 SQL 优化指导。
相关对象
SQL_ID 为 5q54sctfu8t9k 的 SQL 语句。
SELECT t.*,
(select name from server where server = t.server) as
serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and
dic_group ='caseType')as submCategoryName,
(select cunit_name from COMMISSIONEDUNIT c where
c.cunit_id=t.departmentcode) as cUnitName,
(select u.name from usr u where u.id=t.authorizedperson)as
authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION
and (d.dic_group = 'FISection' or d.dic_group = 'PSection' or
d.dic_group = 'FTSection' or d.dic_group = 'RSection')
) as sectionName
FROM submission t left join case c on t.caseid=c.caseid
WHERE t.state=0
and t.submcode = :1
and t.server = :2
and t.flag = :3
order by t.CREATEDATE
原理
SQL 在 CPU, I/O 和集群等待上花费的时间占其数据库时间的 100%。这部分数据库
时间可通过 SQL 优化指导进行改善。
原理
此 SQL 的数据库时间由以下部分构成: SQL 执行占 100%, 语法分析占 0%, PL/SQL
执行占 0%, Java 执行占 0%。
原理
SQL_ID 为 "5q54sctfu8t9k" 的 SQL 语句执行了 3913 次, 每次执行平均用时 0.03
6 秒。
对addm报告中的建议持怀疑态度，继续查看tomcat信息。
再次结合系统响应时间。
系统响应时间：5s多
尝试在tomcat中获取响应5s的日志记录。
通过tomcat捕获日志
数字：2,4,14,
2015-06-09 10:58:35,468 [catalina-exec-1640] INFO [jdbc.sqlonly] - select submId submId,server as server,serverName as serverName,caseId caseId,submCaseName as
submCaseName,cUnitName as cUnitName, sectionName sectionName,submCode submCode,acceptCode acceptCode,serialNo
serialNo, commissionCode commissionCode,submName submName,section section,departmentCode departmentCode,
departmentPhone departmentPhone,address address,postNo postNo,faxNo faxNo,garrison garrison,
submittedBy submittedBy,contact contact,credentialType credentialType,credentialNumber credentialNumber,
position position,submittedByOther submittedByOther,otherContact otherContact,otherCredentialType
otherCredentialType, otherCredentialNumber otherCredentialNumber,otherPosition otherPosition,submitDate
submitDate,requestProject requestProject, submSample submSample,oriConclusion oriConclusion,comments
comments,submState submState, flag flag,createdBy createdBy,submitDate submidDate,caseBrief
caseBrief,state state,SLSJ SLSJ, investigationCode as investigationCode,authorizedPersonName
authorizedPersonName,SUBM_TIMEOUT(submid) submTimeOut, noAcceptCode noAcceptCode,QQIdenName
QQIdenName,agreedMatter agreedMatter,archiveCode archiveCode,checkerName checkerName, orderDate
orderDate from ( SELECT s.*, ROWNUM RN FROM ( SELECT t.*,(select name from server where server
= t.server) as serverName,c.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT
c where c.cunit_id=t.departmentcode) as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as
authorizedPersonName, (select u.name from usr u where u.id=t.checker)as checkerName, (select
dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection' or
d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName
FROM submission t left join case c on t.caseid=c.caseid left join usr u on t.slr=u.id WHERE
1=1 and t.flag='0' and t.state=0 and t.server = 120000000000 and submcode in (select su.submcode
from submission su where su.state='0' and su.submcode = submcode and su.submstate='0400' and
su.server = 120000000000) and t.section in ( '2' , '4' , '14' ) order by t.isOldData,t.CREATEDATE
desc ) s WHERE ROWNUM <= 40 ) a WHERE RN > 0
2015-06-09 10:58:36,248 [catalina-exec-1635] INFO [jdbc.sqlonly] - select tg.*,(select name from usr where id = tg.ANALYSTASSIGNED)as assigenedName,(select name
from usr where id = tg.ANALYSTASSOCIATE)as associateName,(select name from usr where id = tg.analystThird)as
analystThirdName from testgroup tg where tg.testGroupId = 515396092345569
2015-06-09 10:58:36,248 [catalina-exec-1635] INFO [jdbc.sqlonly] - select ts.* ,(select sampleAcceptCode from sample s where s.SAMPLEID= ts.sampleid) as sampleAcceptCode
from testsample ts where 1=1 and ts.state <> 1 and ts.state is not null and ts.testGroupId
= 515396092345569
2015-06-09 10:58:36,264 [catalina-exec-1635] INFO [jdbc.sqlonly] - select t.* from usr t where t.id = 515396075524904
2015-06-09 10:58:36,264 [catalina-exec-1635] INFO [jdbc.sqlonly] - select t.* from usr t where t.id = 3474691
2015-06-09 10:58:36,264 [catalina-exec-1635] INFO [jdbc.sqlonly] - select t.*,(select name from usr where id = (select tg.analystAssigned from testGroup tg where
tg.testGroupId = t.testGroupId))as testOrName from test t where 1=1 and t.testgroupid = 515396092345569
and t.id =(select max(id) from test te where te.testgroupid=515396092345569)
2015-06-09 10:58:36,279 [catalina-exec-1635] INFO [jdbc.sqlonly] - select * from protocol WHERE id in ( '120' )
2015-06-09 10:58:36,295 [catalina-exec-1635] INFO [jdbc.sqlonly] - select * from device WHERE id in ( '' )
2015-06-09 10:58:36,295 [catalina-exec-1635] INFO [jdbc.sqlonly] - select * from testmember WHERE testGroupId = 515396092345569
2015-06-09 10:58:36,295 [catalina-exec-1635] INFO [jdbc.sqlonly] - SELECT ID AS id, DIC_NAME AS dicName, DIC_VALUE AS dicValue, DIC_GROUP AS dicGroup, DIC_SUPER_ID
AS dicSuperId, DIC_TYPE AS dicType, DIC_ORDER AS dicOrder, DIC_STATE AS dicState, DIC_ALIAS_NAME
AS dicAliasName FROM DICTIONARY WHERE DIC_STATE = '1' AND DIC_TYPE = '1' AND DIC_GROUP = 'tech'
ORDER BY DIC_ORDER
2015-06-09 10:58:36,295 [catalina-exec-1635] INFO [jdbc.sqlonly] - select s.*,(select MEMBERNAME from member m where m.memberid=s.targetid) as targetName from
sample s where 1=1 and state=0 and s.sampleId = 515396092300846
2015-06-09 10:58:36,295 [catalina-exec-1635] INFO [jdbc.sqlonly] - select s.*,(select MEMBERNAME from member m where m.memberid=s.targetid) as targetName from
sample s where 1=1 and state=0 and s.sampleId = 515396092300847
2015-06-09 10:58:36,357 [catalina-exec-1647] INFO [jdbc.sqlonly] - select c.*,c.type as caseType,(select dic_name from dictionary where dic_value = c.category
and dic_group ='caseType')as categoryName from case c where caseId=515396092342084
2015-06-09 10:58:36,357 [catalina-exec-1647] INFO [jdbc.sqlonly] - select s.*,(select MEMBERNAME from member m where m.memberid=s.targetid) as targetName from
sample s where 1=1 and state=0 and acceptState !='0' and s.submissionid = 515396092342084 and
flag = '0' order by s.sampleAcceptCode , s.sampleCode
2015-06-09 10:58:36,685 [catalina-exec-1626] INFO [jdbc.sqlonly] - select identifyId,server,code,submId,introduction,path,bookClassify bookClassify, (select u.name
from usr u where u.id=authorizedperson) as authorizedPersonName, (select u.name from usr u
where u.id=reChecker) as reCheckerName, checkOut,argument,result,identifyState,testOr,reChecker,reCheckTime,
authorizedPerson,signTime, auditor,auditTime,createdBy,createDate,bookType, content contents,testGroups
testGroups,currYear currYear,signed signed from IDENTIFYBOOK where identifyId = 515396092345575
2015-06-09 10:58:36,685 [catalina-exec-1626] INFO [jdbc.sqlonly] - select s.*,ca.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=s.departmentcode)
as cUnitName, (select se.name from server se where se.server=s.server) as serverName, (select
u.name from usr u where u.id=s.authorizedperson)as authorizedPersonName, (select u.name from
usr u where u.id=s.rechecker)as recheckerName, (select u.name from usr u where u.id=s.checker)as
checkerName, (select u.name from usr u where u.id=s.createdBy)as createdByName, (select dic_name
from dictionary d where d.dic_value = s.SECTION and (d.dic_group = 'FISection' or d.dic_group
= 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName, (select
cir.operator from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId) and cir.objectid = s.submId)
as receiveName, (select name from usr where id =((select cir.operator from CIRCULATION cir
where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.operatetime = (select max(cir.operatetime)
from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.objectid
= s.submId) and cir.objectid = s.submId)))as receiveByName, (select cir.operateopinion from
CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0500' and cir.operatetime
= (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep
= '0500' and cir.objectid = s.submId ) and cir.objectid = s.submId) as receiveOpinion, (select
cir.operatetime from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId ) and cir.objectid = s.submId)
as receiveTime, (select cir.operateopinion from CIRCULATION cir where cir.operatetype = '0100'
and cir.operatestep = '0800' and cir.operatetime = (select max(cir.operatetime) from CIRCULATION
cir where cir.operatetype = '0100' and cir.operatestep = '0800' and cir.objectid = s.submId
) and cir.objectid = s.submId) as approvalOpinion from submission s left join case ca on s.caseid=ca.caseid
where s.submId=515396092300845 and s.state=0
2015-06-09 10:58:37,512 [catalina-exec-1647] INFO [jdbc.sqlonly] - select s.*,(select MEMBERNAME from member m where m.memberid=s.targetid) as targetName from
sample s where 1=1 and state=0 and acceptState !='0' and s.submissionid = 515396092342084 and
flag = '1' order by s.sampleAcceptCode , s.sampleCode
2015-06-09 10:58:37,621 [catalina-exec-1626] INFO [jdbc.sqlonly] - select tem_id temId,tem_server temServer,tem_test_type temTestType, tem_name temName,tem_section
temSection, tem_test_type temTestType,tem_content temContents,tem_type temType,tem_order temOrder,createdBy
createdBy,createDate createDate from booktemplate b WHERE b.tem_server = 120000000000 and b.tem_section
= '10' and b.tem_type = '1900'
2015-06-09 10:58:39,664 [catalina-exec-1621] INFO [jdbc.sqlonly] - select s.*,ca.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=s.departmentcode)
as cUnitName, (select se.name from server se where se.server=s.server) as serverName, (select
u.name from usr u where u.id=s.authorizedperson)as authorizedPersonName, (select u.name from
usr u where u.id=s.rechecker)as recheckerName, (select u.name from usr u where u.id=s.checker)as
checkerName, (select u.name from usr u where u.id=s.createdBy)as createdByName, (select dic_name
from dictionary d where d.dic_value = s.SECTION and (d.dic_group = 'FISection' or d.dic_group
= 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName, (select
cir.operator from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId) and cir.objectid = s.submId)
as receiveName, (select name from usr where id =((select cir.operator from CIRCULATION cir
where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.operatetime = (select max(cir.operatetime)
from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.objectid
= s.submId) and cir.objectid = s.submId)))as receiveByName, (select cir.operateopinion from
CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0500' and cir.operatetime
= (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep
= '0500' and cir.objectid = s.submId ) and cir.objectid = s.submId) as receiveOpinion, (select
cir.operatetime from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId ) and cir.objectid = s.submId)
as receiveTime, (select cir.operateopinion from CIRCULATION cir where cir.operatetype = '0100'
and cir.operatestep = '0800' and cir.operatetime = (select max(cir.operatetime) from CIRCULATION
cir where cir.operatetype = '0100' and cir.operatestep = '0800' and cir.objectid = s.submId
) and cir.objectid = s.submId) as approvalOpinion from submission s left join case ca on s.caseid=ca.caseid
where s.submId=515396092342084 and s.state=0
2015-06-09 10:58:48,946 [catalina-exec-1626] INFO [jdbc.sqlonly] - select identifyId,server,code,submId,introduction,path,bookClassify bookClassify, (select u.name
from usr u where u.id=authorizedperson) as authorizedPersonName, (select u.name from usr u
where u.id=reChecker) as reCheckerName, checkOut,argument,result,identifyState,testOr,reChecker,reCheckTime,
authorizedPerson,signTime, auditor,auditTime,createdBy,createDate,bookType, content contents,testGroups
testGroups,currYear currYear,signed signed from IDENTIFYBOOK where identifyId = 515396081737766
2015-06-09 10:58:48,915 [catalina-exec-1641] INFO [jdbc.sqlonly] - SELECT ID AS id, DIC_NAME AS dicName, DIC_VALUE AS dicValue, DIC_GROUP AS dicGroup, DIC_SUPER_ID
AS dicSuperId, DIC_TYPE AS dicType, DIC_ORDER AS dicOrder, DIC_STATE AS dicState FROM DICTIONARY
WHERE DIC_TYPE = 1 AND DIC_GROUP IN ( 'FISection' , 'PSection' , 'FTSection' , 'RSection' )
ORDER BY DIC_ORDER
2015-06-09 10:58:46,107 [catalina-exec-1638] INFO [jdbc.sqlonly] - select s.*,ca.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=s.departmentcode)
as cUnitName, (select se.name from server se where se.server=s.server) as serverName, (select
u.name from usr u where u.id=s.authorizedperson)as authorizedPersonName, (select u.name from
usr u where u.id=s.rechecker)as recheckerName, (select u.name from usr u where u.id=s.checker)as
checkerName, (select u.name from usr u where u.id=s.createdBy)as createdByName, (select dic_name
from dictionary d where d.dic_value = s.SECTION and (d.dic_group = 'FISection' or d.dic_group
= 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName, (select
cir.operator from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId) and cir.objectid = s.submId)
as receiveName, (select name from usr where id =((select cir.operator from CIRCULATION cir
where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.operatetime = (select max(cir.operatetime)
from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400' and cir.objectid
= s.submId) and cir.objectid = s.submId)))as receiveByName, (select cir.operateopinion from
CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0500' and cir.operatetime
= (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep
= '0500' and cir.objectid = s.submId ) and cir.objectid = s.submId) as receiveOpinion, (select
cir.operatetime from CIRCULATION cir where cir.operatetype = '0100' and cir.operatestep = '0400'
and cir.operatetime = (select max(cir.operatetime) from CIRCULATION cir where cir.operatetype
= '0100' and cir.operatestep = '0400' and cir.objectid = s.submId ) and cir.objectid = s.submId)
as receiveTime, (select cir.operateopinion from CIRCULATION cir where cir.operatetype = '0100'
and cir.operatestep = '0800' and cir.operatetime = (select max(cir.operatetime) from CIRCULATION
cir where cir.operatetype = '0100' and cir.operatestep = '0800' and cir.objectid = s.submId
) and cir.objectid = s.submId) as approvalOpinion from submission s left join case ca on s.caseid=ca.caseid
where s.submId=515396092300845 and s.state=0
2015-06-09 10:58:41,568 [catalina-exec-1622] INFO [jdbc.sqlonly] - select m.*, (select sampleCode from sample where objectId = m.memberId)as sampleCode, (select
sampleAcceptCode from sample where objectId = m.memberId)as sampleAcceptCode, (select isSelfCollect
from sample where objectId = m.memberId) as isSelfCollect , (select dic_name from dictionary
where dic_value = m.sex and dic_group = 'sex') as sexName from member m left join submission
su on m.submissionid = su.submid where 1=1 and m.acceptState !='0' AND m.submissionId=515396092313709
and m.flag = '1' AND m.server = 120000000000
2015-06-09 10:58:40,132 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-07037' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,486 [catalina-exec-1641] INFO [jdbc.sqlonly] - select t.* from usr t where t.id = 515396075525039
2015-06-09 10:58:53,470 [catalina-exec-1626] INFO [jdbc.sqlonly] - select t.* from usr t where t.id = 515396075525105
2015-06-09 10:58:53,486 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-07036' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,486 [catalina-exec-1626] INFO [jdbc.sqlonly] - select e.*, (case when e.path is null then 0 else 1 end) hasPhoto from employee e where id=515396075525105
2015-06-09 10:58:53,486 [catalina-exec-1641] INFO [jdbc.sqlonly] - select * from usr WHERE labno = ( select labno from usr WHERE id = 515396075525039 )
2015-06-09 10:58:53,502 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06996' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,502 [catalina-exec-1626] INFO [jdbc.sqlonly] - SELECT ID AS id, DIC_NAME AS dicName, DIC_VALUE AS dicValue, DIC_GROUP AS dicGroup, DIC_SUPER_ID
AS dicSuperId, DIC_TYPE AS dicType, DIC_ORDER AS dicOrder, DIC_STATE AS dicState FROM DICTIONARY
WHERE DIC_TYPE = '1' AND DIC_GROUP = 'tech' AND DIC_VALUE = '0007'
数字：2,4,14,
2015-06-09 10:58:53,502 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06991' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,502 [catalina-exec-1626] INFO [jdbc.sqlonly] - SELECT ID AS id, DIC_NAME AS dicName, DIC_VALUE AS dicValue, DIC_GROUP AS dicGroup, DIC_SUPER_ID
AS dicSuperId, DIC_TYPE AS dicType, DIC_ORDER AS dicOrder, DIC_STATE AS dicState FROM DICTIONARY
WHERE DIC_TYPE = '1' AND DIC_GROUP = 'tech' AND DIC_VALUE = '0007'
2015-06-09 10:58:53,502 [catalina-exec-1641] INFO [jdbc.sqlonly] - SELECT COUNT(SUBMSTATE) As recordTotal FROM submission s left join usr u on s.slr=u.id WHERE
1=1 and s.flag='0' and s.state=0 and s.server = 120000000000 and s.submcode in (select su.submcode
from submission su where su.state='0' and su.submcode = s.submcode and su.submstate='0400'
and su.server = 120000000000) and s.section in ( '2' , '4' , '14' )
2015-06-09 10:58:53,502 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06989' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,517 [catalina-exec-1622] INFO [jdbc.sqlonly] - update sample set sampleId=515396092313711 ,submissionId=515396092313709 ,sampleCode='2015-05908-W0001'
,sampleAcceptCode='DNA-2015-02553-W0001' ,sampleName='物证' ,flag='3' ,createdBy=5450 ,createDate=to_timestamp('05/20/2015
08:02:41.975', 'mm/dd/yyyy hh24:mi:ss.ff3') ,acceptState='1' ,sampleType='0400' ,server=120000000000
,isFTA='0000' where sampleId=515396092313711
2015-06-09 10:58:53,517 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06988' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,517 [catalina-exec-1622] INFO [jdbc.sqlonly] - select s.*,(select MEMBERNAME from member m where to_char(m.memberid)=s.targetid) as targetName
from sample s where 1=1 and state=0 and acceptState !='0' and s.samplecode is not null AND
server = 120000000000 order by s.samplecode,s.sampleacceptcode
2015-06-09 10:58:53,517 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06985' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,517 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06979' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,533 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06976' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,533 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06974' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,533 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06973' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,533 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06972' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,548 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06968' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,548 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06967' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,548 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06966' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,564 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06964' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,564 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06987' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,564 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06961' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,564 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06958' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
数字：2,4,14,
2015-06-09 10:58:53,580 [catalina-exec-1641] INFO [jdbc.sqlonly] - select submId submId,server as server,serverName as serverName,caseId caseId,submCaseName as
submCaseName,cUnitName as cUnitName, sectionName sectionName,submCode submCode,acceptCode acceptCode,serialNo
serialNo, commissionCode commissionCode,submName submName,section section,departmentCode departmentCode,
departmentPhone departmentPhone,address address,postNo postNo,faxNo faxNo,garrison garrison,
submittedBy submittedBy,contact contact,credentialType credentialType,credentialNumber credentialNumber,
position position,submittedByOther submittedByOther,otherContact otherContact,otherCredentialType
otherCredentialType, otherCredentialNumber otherCredentialNumber,otherPosition otherPosition,submitDate
submitDate,requestProject requestProject, submSample submSample,oriConclusion oriConclusion,comments
comments,submState submState, flag flag,createdBy createdBy,submitDate submidDate,caseBrief
caseBrief,state state,SLSJ SLSJ, investigationCode as investigationCode,authorizedPersonName
authorizedPersonName,SUBM_TIMEOUT(submid) submTimeOut, noAcceptCode noAcceptCode,QQIdenName
QQIdenName,agreedMatter agreedMatter,archiveCode archiveCode,checkerName checkerName, orderDate
orderDate from ( SELECT s.*, ROWNUM RN FROM ( SELECT t.*,(select name from server where server
= t.server) as serverName,c.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT
c where c.cunit_id=t.departmentcode) as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as
authorizedPersonName, (select u.name from usr u where u.id=t.checker)as checkerName, (select
dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection' or
d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName
FROM submission t left join case c on t.caseid=c.caseid left join usr u on t.slr=u.id WHERE
1=1 and t.flag='0' and t.state=0 and t.server = 120000000000 and submcode in (select su.submcode
from submission su where su.state='0' and su.submcode = submcode and su.submstate='0400' and
su.server = 120000000000) and t.section in ( '2' , '4' , '14' ) order by t.isOldData,t.CREATEDATE
desc ) s WHERE ROWNUM <= 40 ) a WHERE RN > 0
2015-06-09 10:58:53,580 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06955' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,580 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06953' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,595 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06952' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,595 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06950' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,595 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06944' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,595 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06942' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,611 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06940' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,611 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06937' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,611 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06932' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,626 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06927' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,626 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06922' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,626 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06921' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,626 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06914' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,642 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06913' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,642 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06911' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,642 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06910' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,658 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06909' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,658 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06908' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,658 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06907' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,658 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06906' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
2015-06-09 10:58:53,673 [catalina-exec-1640] INFO [jdbc.sqlonly] - SELECT t.*, (select name from server where server = t.server) as serverName,c.caseName as submCaseName,
(select dic_name from dictionary where dic_value = c.category and dic_group ='caseType')as
submCategoryName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode)
as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName,
(select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection'
or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as
sectionName FROM submission t left join case c on t.caseid=c.caseid WHERE t.state=0 and t.submcode
= '2015-06895' and t.server = 120000000000 and t.flag = '1' order by t.CREATEDATE
程序运行时间： 18205ms
通过捕获的tomcat日志，语句总消耗竟然达到了接近18s！！！判断存在优化空间。
结合ADDM报告建议（开始时，在ADDM报告中提示的一条可疑的sql，在tomcat获取sql中发现刚好40条同功能性语句。），锁定到单页40条显示记录。虽然对业务逻辑不了解，但是并不影响下一步的判断。进一步查看发起统计起始语句，锁定到下面这条查询语句：
select submId submId,server as server,serverName as serverName,caseId caseId,submCaseName as submCaseName,cUnitName as cUnitName, sectionName sectionName,submCode submCode,acceptCode acceptCode,serialNo serialNo, commissionCode commissionCode,submName submName,section section,departmentCode departmentCode, departmentPhone departmentPhone,address address,postNo postNo,faxNo faxNo,garrison garrison, submittedBy submittedBy,contact contact,credentialType credentialType,credentialNumber credentialNumber, position position,submittedByOther submittedByOther,otherContact otherContact,otherCredentialType otherCredentialType, otherCredentialNumber otherCredentialNumber,otherPosition otherPosition,submitDate submitDate,requestProject requestProject, submSample submSample,oriConclusion oriConclusion,comments comments,submState submState, flag flag,createdBy createdBy,submitDate submidDate,caseBrief caseBrief,state state,SLSJ SLSJ, investigationCode as investigationCode,authorizedPersonName authorizedPersonName,SUBM_TIMEOUT(submid) submTimeOut, noAcceptCode noAcceptCode,QQIdenName QQIdenName,agreedMatter agreedMatter,archiveCode archiveCode,checkerName checkerName, orderDate orderDate from ( SELECT s.*, ROWNUM RN FROM ( SELECT t.*,(select name from server where server = t.server) as serverName,c.caseName as submCaseName, (select cunit_name from COMMISSIONEDUNIT c where c.cunit_id=t.departmentcode) as cUnitName, (select u.name from usr u where u.id=t.authorizedperson)as authorizedPersonName, (select u.name from usr u where u.id=t.checker)as checkerName, (select dic_name from dictionary d where d.dic_value = t.SECTION and (d.dic_group = 'FISection' or d.dic_group = 'PSection' or d.dic_group = 'FTSection' or d.dic_group = 'RSection') ) as sectionName FROM submission t left join case c on t.caseid=c.caseid left join usr u on t.slr=u.id WHERE 1=1 and t.flag='0' and t.state=0 and t.server = 120000000000 and submcode in (select su.submcode from submission su where su.state='0' and su.submcode = submcode and su.submstate='0400' and su.server = 120000000000) and t.section in ( '2' , '4' , '14' ) order by t.isOldData,t.CREATEDATE desc ) s WHERE ROWNUM <= 40 ) a WHERE RN > 0
看似冗长的sql语句，其实逻辑很简单。从from作为分隔可以得知，这是通过实时生成的视图信息，返回一些必要的查询结果，如下运行一次sql后的信息，也可以看到运行时间，如下：
生成执行计划：
锁定该语句耗费性能，进一步排查。
首先，对来源视图查询进行排查，如下：
初步排除视图查询性能低下的可能性，再次排查主查询语句。
发现主查询中存在“函数”，进一步查看“函数”情况，如下：
CREATE OR REPLACE FUNCTION SUBM_TIMEOUT(submissionId IN number)
RETURN number IS n_return number(10);
--RETURN varchar2 IS submDate varchar2(1000);
submDate varchar2(1024);
agreedperiod number(10);
BEGIN
--select to_char(s.submitdate,'yyyy-mm-dd hh24:mi:ss') into submDate from submission s where s.submid= submissionId;
select max(t.operatetime) into submDate from circulation t where t.operatetype='0100' and t.objectid=submissionId;
select to_number(s.agreedperiod) into agreedperiod from submission s where s.submid= submissionId;
n_return := agreedperiod - round(to_number(sysdate-to_date(submDate,'yyyy-mm-dd hh24:mi:ss')));
if n_return is null then
n_return := 0;
end if;
return n_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END ;
锁定函数中，max语句，查看单条执行计划及语句耗时，约0.1s。
下面这条语句：
select max(t.operatetime) into submDate from circulation t where t.operatetype='0100' and t.objectid=submissionId;
估算，系统分页查询显示40条记录，约40*0.1=4s，与最先查询跑出的4s-5s问题比较接近。进一步查看circulation表中objectid字段是否建立索引：没有建立！！！
查看circulation表数据量约为1百多万。在objectid列上建立索引。
create index INDEX_OBJECTID on CIRCULATION (OBJECTID)
tablespace TJALIMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
单条查询再次验证，耗时0.027s。
select max(t.operatetime) into submDate from circulation t where t.operatetype='0100' and t.objectid=submissionId;
初步判断，建立索引已经生效了，提升了近5倍的性能。
重新执行整体sql语句，如下：
查询SQL时间已经由4s降至0.249s。
查看应用系统，单击显示响应时间已经由5s降至0.84s。
回看AWR报告，再进一步看看低性能的SQL，如下：
小结：
回看AWR发现，造成性能低下的多条SQL语句中，均含有SUBM_TIMEOUT函数，而该函数中缺少索引，虽然函数中单条查询时间很快，但传参后累加，致使性能低下，这是造成查询性能低下的主要诱因。
（2）、功能项中单条信息界面响应时长调优
虽然列表界面响应时间明显提升了，但是单条记录查看仍感觉有些延迟，在考虑是否有优化的可能性。于是以一小时为单位，再次生成AWR报告，锁定到SQL order by CPU Time，如下：
进一步查看sql语句，如下：
select s.*, (select MEMBERNAME from member m where m.memberid=s.targetid) as targetName from sample s where 1=1 and state=0 and acceptState !='0' and s.submissionid = :1 order by s.sampleAcceptCode , s.sampleCode
查询执行计划SAMPLE走了全表扫，查看一下数据量为近30万。
采取措施：为SAMPLE表建立索引。
create index INDEX_SUBMISSIONID on SAMPLE (SUBMISSIONID)
tablespace TJALIMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
查看执行计划，已经变为走索引。如下：
这里忘了在前面截图了，如果有截图对比就明显了，这里简单文字说明一下，在调整之前该条语句的执行计划中，sample是全表扫描，cost为2000多。而建立索引后，发现cost只为4。单条响应时间不足1s。之前出现3s多，判断可能是由于单条记录信息来自多张表，发起了多条查询语句，牺牲了时间。对一处调整后，对界面响应稍有改善。
单条记录查询时间，由3s下降到1.26s。
再查询一条单条记录，查询时间为1.13s。
再查询一条单条记录，查询时间为1.28s。
单条记录查询响应时间基本控制在了1.2s左右。
三、小结
整体调优前后响应时间对比，简略统计如下：
|
概要 |
调整前响应时间 |
调整后响应时间 |
|
列表记录界面响应 |
5s左右 |
0.84s左右 |
|
单条记录界面响应 |
3s左右 |
1.2s左右 |
至此，对于界面响应缓慢的问题，已经得到相应改善，人为点击时已经没有了那种延迟特别长的感觉了，但后续对于单条查询时间，仍有进一步优化的可能性，后续跟踪，继续优化调整。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。