提高Oracle性能--经验实谈

本人初出茅庐，从事对日软件开发已一年时间了，之前一直在JAVAEYE上面看大家的博客，不敢随意发表任何言论。目前我正好维护一个J2SE的项目，刚好碰到一个障害要求提高Oracle的执行效率问题。本人觉得很有意思，供大家分享！希望大家能够给点鼓励……

 

原来的SQL文需要执行15万条左右的数据记录，执行一个查询条件至少要15分钟以上。原SQL文是：

SELECT 
A.SHHN_CD, 
A.KKR_KB, 
A.DN_KB, 
A.DN_GYBN_KB, 
SUM(A.CS_SRY), 
SUM(A.BR_SRY), 
SUM(A.BR_KNZN_SRY) 
FROM TRNSCTN A, 
SHHN_MST 
WHERE A.HNSHTN_CD='001' 
AND ((A.ZK_SK_CD='1' 
AND A.ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD') 
AND DN_DY<TO_DATE('20080601', 
'YYYYMMDD')) 
OR (A.ZK_SK_CD='1' 
AND A.DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD') 
AND TO_DATE('20080623','YYYYMMDD'))) 
AND DN_GYBN_KB IN('1', 
'2', 
'5', 
'7', 
'C', 
'D', 
'G', 
'H') 
AND DN_KB IN('1', 
'2', 
'3', 
'4') 
AND GY_NB<>'0' 
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD 
AND A.SHHN_CD=SHHN_MST.SHHN_CD 
AND SHHN_MST.BMN_CD BETWEEN '000' 
AND '999' ) 
GROUP BY A.SHHN_CD, 
A.KKR_KB, 
A.DN_KB, 
A.DN_GYBN_KB 
ORDER BY A.KKR_KB, 
A.DN_KB, 
A.DN_GYBN_KB 

  

 当我把这段SQL文拷贝到SQLPLUS中，按F5键执行时，我才发现这段SQL文没有用到索引。具体索引如下：

写道

Owner   Name             Type    Columns
KMJ       TRNSCTNFK1   Normal HNSHTN_CD, DN_NB
KMJ       TRNSCTNFK10 Normal HNSHTN_CD, INJZM_KB
KMJ       TRNSCTNFK11 Normal HNSHTN_CD, STI_INJZM_KB
KMJ       TRNSCTNFK12 Normal HNSHTN_CD, GY_NB, AT_DN_NB
KMJ      TRNSCTNFK13  Normal HNSHTN_CD, SHHN_CD, ZK_KSHN_DY, DN_DY
KMJ      TRNSCTNFK14  Normal HNSHTN_CD, TRHKSK_CD, TRHKSK_KB, DN_KB, GY_NB, STI_INJZM_KB
KMJ      TRNSCTNFK15  Normal HNSHTN_CD, TRHKSK_CD, DN_KB, SKY_DY, CHH_DY
KMJ      TRNSCTNFK2    Normal HNSHTN_CD, TRHKSK_CD, DN_DY
KMJ      TRNSCTNFK3    Normal HNSHTN_CD, SHHN_CD, DN_DY
KMJ      TRNSCTNFK4    Normal HNSHTN_CD, ZK_KSHN_DY
KMJ      TRNSCTNFK5    Normal HNSHTN_CD, SYS_SHR_DY, DN_KB
KMJ      TRNSCTNFK6    Normal HNSHTN_CD, SKY_DY
KMJ      TRNSCTNFK7    Normal HNSHTN_CD, TGT_DY, DN_KB
KMJ      TRNSCTNFK8    Normal HNSHTN_CD, DN_DY, DN_KB
KMJ      TRNSCTNFK9    Normal HNSHTN_CD, TRHKSK_CD, SKY_DY, CHH_DY
KMJ      TRNSCTNPKI    Unique HNSHTN_CD, DN_DY, DN_KB, DN_NB, DN_D, KKR_KB, GY_NB, NY_TN_NB

  我通过一天的测试，发现将原SQL文进行如下优化之后，效率最高，执行时间提高到161.875s。

SELECT
 /*+ index(SHHN_MST TRNSCTNFK4)*/ /*+ index(SHHN_MST TRNSCTNFK8)*/
 A.SHHN_CD,
 A.KKR_KB,
 A.DN_KB,
 A.DN_GYBN_KB,
 SUM(A.CS_SRY),
 SUM(A.BR_SRY),
 SUM(A.BR_KNZN_SRY)
FROM TRNSCTN A,
 SHHN_MST
WHERE A.HNSHTN_CD='001'
AND ((A.ZK_SK_CD='1'
		AND A.ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')
		AND DN_DY<TO_DATE('20080601',
			 'YYYYMMDD'))
	OR (A.ZK_SK_CD='1'
		AND A.DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')
		AND TO_DATE('20080623','YYYYMMDD')))
AND DN_GYBN_KB IN('1',
	 '2',
	 '5',
	 '7',
	 'C',
	 'D',
	 'G',
	 'H')
AND DN_KB IN('1',
	 '2',
	 '3',
	 '4')
AND GY_NB<>'0'
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD
	AND A.SHHN_CD=SHHN_MST.SHHN_CD
	AND SHHN_MST.BMN_CD BETWEEN '000'
	AND '999' )
GROUP BY A.SHHN_CD,
 A.KKR_KB,
 A.DN_KB,
 A.DN_GYBN_KB
ORDER BY A.KKR_KB,
 A.DN_KB,
 A.DN_GYBN_KB

  

日方对这样的效率已基本满意，但是这仅仅时SQL文执行的时间，要是加上JAVA执行的时间的话也得要5分钟。作为天生追求完美的程序员来说似乎有点不甘休。于是我将上述SQL采取了一个最原始的优化，性能倍增，执行时间只需要8.912s。

SELECT  
 A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB,
SUM(A.CS_SRY),
SUM(A.BR_SRY),
SUM(A.BR_KNZN_SRY)
FROM 
(
SELECT 
 *
FROM TRNSCTN
WHERE ZK_SK_CD='1'
AND ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')
AND DN_DY<TO_DATE('20080601',
	 'YYYYMMDD')
  UNION ALL
SELECT 
 * 
FROM TRNSCTN
WHERE ZK_SK_CD='1'
AND DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')
AND TO_DATE('20080623','YYYYMMDD')
) A,
SHHN_MST
WHERE A.HNSHTN_CD='001'
AND DN_GYBN_KB IN('1',
	'2',
	'5',
	'7',
	'C',
	'D',
	'G',
	'H')
AND DN_KB IN('1',
	'2',
	'3',
	'4')
AND GY_NB<>'0'
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD
	AND A.SHHN_CD=SHHN_MST.SHHN_CD
	AND SHHN_MST.BMN_CD BETWEEN '000'
	AND '999' )
GROUP BY A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB
ORDER BY A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB

 其实我对ORACLE并没有开发经验，只是出于个人爱好，研究点皮毛。为什么这样的执行效率最高，我并不是特别清楚，希望大家给点建议！！！！！！！

 

    本人已将次文的详细叙述过程放到http://rdqwhr.iteye.com/admin/blogs/208382，敬请查阅！！！

评论

2 楼 rendq 2008-06-25  

目前，本人也在自己的环境上对上述SQL文做各种验证，马上就快出炉了，保证给大家提供一个完整的论证过程。

1 楼 Aryang 2008-06-25  

不错，顶。可惜没对改进的措施和方法进行任何叙述或描述，一个字都没有