Re: ADO connection to Oracle gets a listener error, but only on the first few attempts

From: Mark Freeman <mark_freeman_at_asc.aon.com>
Date: 11 Jun 2003 06:43:14 -0700
Message-ID: <df323d2.0306110543.42858f7b@posting.google.com>

Michael, I greatly appreciate your input on this. I've added some comments below.

"Michael D. Long" <michael_d_long_at_bellsouth.net> wrote in message news:<#zVTAGWLDHA.3152_at_TK2MSFTNGP10.phx.gbl>...
> Creating a connection is a very expensive operation for Oracle.
> When you try to create several at one time you may experience
> timeouts. I've seen 12541 and 12560 errors when trying to
> spawn 100 threads in < 1 second, though never at only 8.
>
> You can test whether this is your problem by placing a delay
> of a few seconds between new object instances.

I'll give that a try.

> However, your design doesn't sound like the model of efficiency.
> Your "controller" would be a good idea were it a pool manager
> that maintained a list of running objects and dispatched requests
> to them via method calls. When I first glanced at your post I
> thought that's what you were doing, but the part about spawning
> new workers and passing parameters on the command line made
> me question what you were doing.

The first version of this was a single program. It took 5 days to run. This version uses two executables, with one controlling multiple instances of the other. I did this because I haven't yet worked out how to do it as a single executable controlling multiple threads. It is my intent for the next version to work that way, which should be far more efficient as all of the threads would share one connection to Oracle. I've looked at the Coffee sample program, but must admit that I'm having trouble getting it to sink into my brain.

> You state that you have "thousands required". This sounds like
> some type of batch update, and not a service to respond to ad
> hoc user actions. If so, you could write a single process that
> would perform better with only 1 connection than you will get
> using the approach you have described.

My version that worked that way took 5 days to run. Using the "poor man's multithreading using the shell function" method, it now runs in less than 8 hours on the same hardware. It runs in only 2 hours on a dedicated machine.

Each thread/process makes a WMI connection to a machine and performs a comprehensive hardware and software inventory. This takes a while, especially when the target is unresponsive. The timeouts are brutally long and non-configurable so far as I can tell.

The controller program goes through our hardware database and spawns a thread/process for each machine, passing the name to the spawned program. The spawned program then connects to Oracle, looks up the necessary credentials for the passed host name, and then runs the WMI queries necessary to update the inventory information.

On the NT 4 server I have a available to run this, it can get no more than 8 WMI sessions running concurrently (by the time it gets #8 running, one of the others has already completed). It takes this machine seemingly forever to shell a new process. CPU never breaks 80%, and memory is not an issue, but the Processor Queue is over 5.

On my Win2k notebook, memory is my limiting factor, but I can generally get over 30 of these WMI sessions running concurrently.

Here is the relevant code I am using in this version of the controller program. I'd welcome your comments or suggestions:

    blnRunning = True
    Do While Not mrstOracle.EOF And lfrmStatus.Visible And blnRunning

        DoEvents
        bytLoop = 1
        Do While Not mrstOracle.EOF And bytLoop <= bytMaxThreads And
lfrmStatus.Visible
            If aryThreads(bytLoop) = 0 Then
                Debug.Print mrstOracle.Fields("HostName") & " [Main]"
                If mrstOracle.Fields("HostName") <> "CRL-PWEANT" Then
                    ' Querying this machine causes the program to hang
                    lfrmStatus.Label2.Caption = "Starting thread " &
bytLoop & " of " & bytMaxThreads
                    lfrmStatus.Refresh
                    idProg = Shell(Chr(34) & App.Path &
"\FetchWMI-One.exe" & Chr(34) & " /N" & _
                        mrstOracle("HostName") & " /W" &
CStr(mrstOracle("Which_Table")) & _
                        " /I" & CStr(mrstOracle("ID")),
vbNormalNoFocus)
                    lfrmStatus.Label2.Caption = "Thread " & idProg & "
(" & bytLoop & ") started"
                    If lfrmStatus.ProgressBar1.Value <
lfrmStatus.ProgressBar1.Max Then
                        lfrmStatus.ProgressBar1.Value =
lfrmStatus.ProgressBar1.Value + 1
                    End If
                    lfrmStatus.lblServerNo.Caption =
Format(lfrmStatus.ProgressBar1.Value, "###,##0") & _
                        " of " & Format(lfrmStatus.ProgressBar1.Max,
"###,##0")
                    DoEvents
                    If idProg <> 0 Then
                        aryThreads(bytLoop) = idProg
                    Else
                        NetErrorHandler 0, "Call to FetchWMI-One
failed", gbytErrOut
                    End If
                End If
                mrstOracle.MoveNext
            End If
            bytLoop = bytLoop + 1
        Loop
        lfrmStatus.Label2.Caption = "Waiting for available thread"
        'Wait for at least one thread to complete
        blnRunning = True
        Do While lfrmStatus.Visible And blnRunning
            Wait 1, mblnQuit
            DoEvents
            blnRunning = True
            For bytLoop = 1 To bytMaxThreads
                If Not IsRunning(aryThreads(bytLoop), lngExit) Then
                    aryThreads(bytLoop) = 0
                    If lngExit = 0 Then intSuccessCount =
intSuccessCount + 1
                End If
                If aryThreads(bytLoop) = 0 Then blnRunning = False
            Next
        Loop
        blnRunning = True

    Loop     

    lfrmStatus.Label2.Caption = "Waiting last threads to complete"     lfrmStatus.lblServerNo.Caption = vbNullString     blnRunning = True
    Do While lfrmStatus.Visible And blnRunning

        DoEvents
        blnRunning = False
        For bytLoop = 1 To bytMaxThreads
            If Not IsRunning(aryThreads(bytLoop), lngExit) Then
aryThreads(bytLoop) = 0
            If lngExit = 0 Then intSuccessCount = intSuccessCount + 1
            If aryThreads(bytLoop) > 0 Then blnRunning = True
        Next

    Loop

> "Mark Freeman" <mark_freeman_at_asc.aon.com> wrote in message
> news:df323d2.0306060740.4da77447_at_posting.google.com...
> > I have a VB 6.0 program that runs on a Win2k machine and connects to
> > an Oracle 8i database on an AIX system. It works fine.
> >
> > I split it up into two programs such that one invokes multiple
> > instances of the other. The one that has multiple instances running
> > accesses Oracle to do a query based on a command-line parameter passed
> > by the controller program.
> >
> > When I fire up the controller program, it immediately runs 8 instances
> > of the database access program. The first two or three get an Oracle
> > error and the rest work fine.
> >
> > The controller program ensures that 8 instances of the database access
> > program are always running -- when one instance completes and exists
> > it spawns a new one, until it goes through the thousands required.
> >
> > If I kill off the controller program, let all the access program
> > instances complete, and then run the controller program again, I do
> > not get the errors even though it is doing exactly the same thing as
> > it did before.
> >
> > The issue appears to be one of multiple programs attempting to make
> > Oracle connections at the same time, but only when no connection has
> > been made for some time.
Received on Wed Jun 11 2003 - 08:43:14 CDT