Listener issue - Slowness

[frueff]

This has been going on for about 2 weeks now without resolution. It is a 10g database running for GE centricity(Electronic Medical Records). We have replaced all hardware 3 times, reloaded the server from bare metal and restored the DB, gathered stats etc. Still experience slowness. What we have discovered is that with a client plugged into the switch that the DB is plugged into, the slowness still occurs, so the network is cleared. If the tnsnames.ora is changed to dedicated mode from shared services then the slowness disappears but we cannot do that for all users due to application issues. Now the application is never slow running from the DB itself. I want to say user load but we hit load early in the day and no slowness for a while. THe DBAs have looked at everything and cannot find a job running that would account for it.
I would appreaciate any help on this.
Thanks
Frank

[davey23uk]

trace the connection, see where the time is being spent

[frueff]

Thank you for the quick response. One thing I neglected to put in is this instance is running on windows 2003 and is fully patched up to this point. Hence reloaded the server from bare metal thinking it was MS causing the problem. I was told by our DBAs that ran a trace and found nothing. The slowness typically lasts for about 4-5 hours and then clears up. During that time we don't see any increase in DB activity, no spikes in resources or I/O.

Is there a way to watch the shared services listener to see what it is doing during the slowness?

I'm just the server guy and have got pulled into this so I apologize if what I am saying doesn't make much sense.
Thanks
Frank

[frueff]

Thank you for the quick response. One thing I neglected to put in is this instance is running on windows 2003 and is fully patched up to this point. Hence reloaded the server from bare metal thinking it was MS causing the problem. I was told by our DBAs that ran a trace and found nothing. The slowness typically lasts for about 4-5 hours and then clears up. During that time we don't see any increase in DB activity, no spikes in resources or I/O.

Is there a way to watch the shared services listener to see what it is doing during the slowness?

I'm just the server guy and have got pulled into this so I apologize if what I am saying doesn't make much sense.
Thanks
Frank

[vnktummala]

Have you checked for any scheduled anti virus full system scan or any windowz scheduled task jobs running on that time?

Thanks,

[frueff]

I turned symantec off at the start which did no good. There are no scheduled jobs. It sure seems that like that is what it is but we can't pin it on anything. A listener trace shows a query being run at the start of the slowness but it never produces enough user I/O to account for the behavior. Someone did mention something about a 'leak' where the memory isn't returned correctly in the SGA. Is there a way to catch someone connecting that would not be coming through the application?
thanks
Frank

[vnktummala]

you need to trace the session with 10046 event or you need to trace all the activity in the database by setting sql_trace=true in the parameter file and user tkprof to analyze the trace files.

Thanks,

[malay_biswal]

Trace the session and also check CPU, Memory usage during peak load.

[frueff]

We increased the shared servers from the default of 34 to 75 with a max of 100. Basically there is a resource constaint in the SGA. New shared servers are not spawning like they are supposed and neither are they tearing down like the are supposed to. One of the first indications is a long shut down time for the DB. The shared server increase does not fix the problem but effectively masks it until we upgrade to 64 bit.

The vendor had known about this since late last year and did not tell. Apparently there were 30-40 other clients having the same problem. They had opened a case with Oracle and I am requesting the bug number which I doubt I will see. Oracle said they will not issue a patch for it. My guess is that it is the vendor code causing the problem.

Funny side note. A co-worker and I were looking at a Oracle for beginners book to figure out what commands to run to see the dispacther and shared servers. The vendor told us that it was impossible. I think for Christmas we will ship them a copy of the book.

Thanks to everyone with their replys it did help us push in the right direction.