问题不小,属于比较严重的事故啊。最后发现程序中有很多处使用DB后,未调用closeDB的方法(当然这是封装后的方法),由此引发了的关于OO4O的Session调查。调查结果是从新建一个DB连接开始,Oracle数据库就生成一个session,在操作数据库的过程中,session的状态是ACTIVE,其他空闲时间则是INACTIVE,从网上搜索到关于INACTIVE的说明:Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.无论是ACTIVE还是INACTIVE,session都是没有关闭的状态。当然,此时如果EXE退出或者进程被杀掉,session会自动的被oracle回收掉。先把未调用closeDB的地方补上吧,这时候怪事出现了,虽然调用了closeDB,session并没有消失,还是保持INACTIVE的状态。最后发现GC.Collect()起了作用,也就是说在调用closeDB之后(一定是之后,在closeDB里面写这句话是不起作用的),exe未退出之前,session并没有关闭,只有GC.Collect()之后才会消失。
还是看下代码吧。
Code
1Option Strict On
2Option Explicit On
3
4Imports System.IO
5Imports OracleInProcServer
6Imports System.Runtime.InteropServices
7
8 Public Class BaseDBAccess
9
10 Private oraSession As OracleInProcServer.OraSession
11 Private oraDatabase As OracleInProcServer.OraDatabase
12 Private oraDynaset As OracleInProcServer.OraDynaset
13
14 Private glb_strORA_SERVER As String
15 Private glb_strORA_USER As String
16 Private glb_strORA_PASSWORD As String
17 Private Function connDB(ByVal strORA_SERVER As String, ByVal strORA_USER As String, ByVal strORA_PASSWORD As String) As Boolean
18 Try
19 oraSession = Nothing : oraDatabase = Nothing
20 oraSession = CType(CreateObject("OracleInProcServer.XoraSession"), OracleInProcServer.OraSession)
21 oraDatabase = CType(oraSession.OpenDatabase(strORA_SERVER, strORA_USER & "/" & strORA_PASSWORD, 0), OracleInProcServer.OraDatabase)
22 Return True
23 Catch ex As Exception
24 Throw DoException(ex, True)
25 End Try
26 End Function
27
28 Public Function closeDB() As Boolean
29 Try
30 If Not (oraDatabase Is Nothing) Then
31 If oraDatabase.ConnectionOK Then
32 oraDatabase.Close()
33 End If
34 End If
35
36 oraDatabase = Nothing : oraSession = Nothing
37 htblOraParameters = Nothing
38 Return True
39 Catch ex As Exception
40 Throw DoException(ex)
41 End Try
42 End Function
43 End Class
44
1Option Strict On
2Option Explicit On
3
4Imports System.IO
5Imports OracleInProcServer
6Imports System.Runtime.InteropServices
7
8 Public Class BaseDBAccess
9
10 Private oraSession As OracleInProcServer.OraSession
11 Private oraDatabase As OracleInProcServer.OraDatabase
12 Private oraDynaset As OracleInProcServer.OraDynaset
13
14 Private glb_strORA_SERVER As String
15 Private glb_strORA_USER As String
16 Private glb_strORA_PASSWORD As String
17 Private Function connDB(ByVal strORA_SERVER As String, ByVal strORA_USER As String, ByVal strORA_PASSWORD As String) As Boolean
18 Try
19 oraSession = Nothing : oraDatabase = Nothing
20 oraSession = CType(CreateObject("OracleInProcServer.XoraSession"), OracleInProcServer.OraSession)
21 oraDatabase = CType(oraSession.OpenDatabase(strORA_SERVER, strORA_USER & "/" & strORA_PASSWORD, 0), OracleInProcServer.OraDatabase)
22 Return True
23 Catch ex As Exception
24 Throw DoException(ex, True)
25 End Try
26 End Function
27
28 Public Function closeDB() As Boolean
29 Try
30 If Not (oraDatabase Is Nothing) Then
31 If oraDatabase.ConnectionOK Then
32 oraDatabase.Close()
33 End If
34 End If
35
36 oraDatabase = Nothing : oraSession = Nothing
37 htblOraParameters = Nothing
38 Return True
39 Catch ex As Exception
40 Throw DoException(ex)
41 End Try
42 End Function
43 End Class
44
还有一个问题也很诡异,那就是如果在使用DB连接的过程中如果传入了参数,即使用了OraParameters.Add的方法,但是没有将参数清空,那么即使closeDB之后加了GC.Collect()仍然不能关闭,分析一下觉得这应该跟GC.Collect()的强制回收的机制有关,没有释放参数的DB连接对象不能回收。
从网上搜索了相关问题的帖子,发现遇到这个问题的人很多,可是并没有找到治本的方法。很多方法在讨论如何在oracle数据库上写方法去消除这些session,但那只是救火行为。使用GC.Collect()是个折中的办法,当然在使用DB频繁的情况下,使用GC.Collect()会影响效率,因为本身这个方法是强制回收。我想最好是能找到替代GC.Collect()的方法,因为GC.Collect()的作用域包括整个系统,我们想切断客户端和数据库的一根连接线而不得其法,只好采用挥起大刀将所有能砍断的线全部砍断的笨办法,代价有点儿高。想找到能真正的去完成关闭session的语句命令,目前还没找到,有高人知道的指点一下。当然前面代码中所写的连接DB和关闭DB的方法是否准确也值得探讨。按照代码的理解,执行oraDatabase = Nothing : oraSession = Nothing已经将对象销毁了,为什么oracle数据库端没有接收到这个消息呢,既然GC.Collect()之后,oracle会将session回收掉,那么在执行oraDatabase = Nothing : oraSession = Nothing应该还没有将消息发出,甚至是根本没有销毁这个session连接。GC.Collect()完成了后面的工作。