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

My guess is that you could improve performance if you used host variables - opened the connection , parsed the query, executed multiple times (with different values). Your high CPU usage on the server and long CPU queue length is indicative of not using bind variables. You are forcing Oracle to do a hard parse each and every time and that is highly CPU intensive.

I had a developer who didn't believe in bind variables and on a particular benchmark - importing 1,000 records via his program - it would take 26 minutes and peg the CPU. We changed it so he used bind variables, parsed one, and executed many times and the benchmark went to under 6 minutes with CPU usage peaking occasionally at 10%.

In the first case no one could do any work onthe machine and effectively locked all users out of using the database while the process ran. (the database was on NT which is not that good at multitasking) Once we added bind variables etc. Other users never noticed that the import was occuring. Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Mark Freeman" <mark_freeman_at_asc.aon.com> wrote in message
news:df323d2.0306110543.42858f7b_at_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.