locked
Memory leak? Memory usage grows? RRS feed

  • Question

  • User-1880918579 posted

     I've got an application that *SHOULD* do about 250,000 inserts into an oracle database. Each record has around 120 columns.

     after about 10,000 inserts i get

    ContextSwitchDeadlock was detected
    Message: The CLR has been unable to transition from COM context 0x19fe30 to COM context 0x19ffa0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

    I can "OK" through this message and it does around 160,000 inserts then I'm getting: 

    System.OutOfMemoryException was unhandled
      Message="Exception of type 'System.OutOfMemoryException' was thrown."

     on this line "s1 = IRead.ReadLine"

     any idea why? I've tried LOTS of things and have been working on it for 3 days now to no avail...

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim s1 As String

    Dim IRead As New System.IO.StreamReader("C:\Input\SQL.txt")

    'Dim cmd As New OracleCommand(s1,oconn)

    Dim cmd As New OracleCommand

    Dim i As Integer

    'Get Oracle Connection

    Dim OConn As OracleConnection = New OracleConnection("Data Source=XXXXXX;User Id=XXXXXX;Password=XXXXXX;")

    OConn.Open()

     

    'cmd = New OracleCommand()

    cmd.Connection = OConn

    cmd.CommandType = CommandType.Text

    Label1.Text =
    My.Computer.Clock.LocalTimeWhile Not IRead.EndOfStream

    s1 = IRead.ReadLine

    cmd.CommandText = s1

    cmd.ExecuteReader()

    cmd.CommandText = Nothing

    i = i + 1

    If i Mod 5000 = 0 Then

    cmd.CommandText = ("commit")

    cmd.ExecuteReader()

    cmd.CommandText = Nothing

    End If

    End While

    Label2.Text = My.Computer.Clock.LocalTime

    OConn.Close()

    End Sub

    Monday, June 9, 2008 1:42 PM

All replies

  • User-1880918579 posted

    I'm not sure what the standard procedure is for moving or copying a thread but this might help .NET developers using Oracle.

     It turns out that my job was effectively queuing up too many inserts too quickly. I've been working closely with our Oracle admin to develop this. He increased the "Job queue processess" system parameter from the default of 10 to 100 and i have already seen tremendous improvement. He said oracle recomends 600 for production environments. I had 10 because i am still in development however with only 100 i am not experiencing the ram growth during runtime. I can't wait for 600! :D

    Tuesday, June 10, 2008 11:36 AM
  • User-1880918579 posted

    Well the previous post was the soloution for a day. The next day I came in and ran the process again. All things *seem* to have remained unchanged. The same code and the same Oracle database and the memory is running away again. Any ideas? The memory is counting up on the machine running the oracle connection and processing the list of SQL comands. NOT on the machine that houses the Oracle database.

    Thursday, June 12, 2008 9:27 AM