Oracle.exe=high CPU utilization on database logoff? Yes, logoff!

Hey everyone, I've got a question that I can't seem to figure out; maybe you can help? I've got an Oracle 8i database on a Windows 2000 Server o/s. For a while, when I was done with an SQL Plus session on my workstation, if I did a "disconnect", it took about 9 seconds for me to get the SQL prompt back. I just thought my system was slow...

Well, I wish I ran to the server as soon as I noticed that- it's not my workstation. It's the server itself. If I connect to SQL Plus, with all the performance counters running, the server (cpu utilization, and all processes' utlilization) barely blinks an eye. As soon as I disconnect from SQL Plus, the CPU utlization jumps to 70%, falls back to about 50%, then jumps up to about 60%, and as soon as the SQL prompt comes back, CPU utilization goes to zero again.

I ran the counters for all the oracle background processes (PMON, LGWR, etc), and they don't move; they're near zero. It's oracle.exe itself that has high CPU % !! What could possibly be happening for there to be such a load for a logoff? The database runs fast otherwise (large queries included), but that logoff is the culprit. Even if I log on to sql plus, and quickly disconnect without doing ANY dml/dll/select ops, the delay is there. I even booted everyone off the system on Sunday night (okay, well, nobody was on but me), and the same thing happens.

That was the extent of my real-life tests, by the way- I opened one sqlplus session on the server, and after I was connected, with perfmon.msc running, I would just issue conn/disc commands over & over again- and it doesn't matter which Oracle user I log in as... I even created a user account for this test, and granted only the create session privlege- so the user had no access to any other tables other than 6 sys tables... hopefully
"eliminating" any triggers that are causing the problem. Same thing... all
the Oracle background processes (perfmon.msc- threads object) are quiet, but the oracle.exe (perfmon.msc- process object) spikes up, and follows the CPU (perfmon.msc- processor object) utilization graph.

The system wasn't always like this... back in October, I applied a patch to our database (from our vendor) that changed some triggers around. I'm wondering if something got added to the schema- but I can't seem to find it. To perform these patches, I logged in as the application's schema owner- not as sys- so I doubt the patches could've added a SYS trigger!

I checked all_triggers and dba_triggers for any event-driven logoff procedures, and didn't find any. I shutdown/started the instance, nothing changed. I disabled the server's virus scanner, nothing changed. I even rebooted the server, and nothing changed. The disks aren't bogging down, and if it was just the internet connection, then oracle.exe itself wouldn't be using up CPU time. This happens for all our applications (slow logoff), too. What's strange is that I have an old Pentium 3, 600 mhz, 512mb RAM, 6gb HD as a test/development server, that also runs Oracle 8i. Although it's a smaller database, it's got no problems- so it's definately not the hardware on the production server!

Are there any triggers I've missed? Any other places I should check for things? What happens when a database connection is closed?

We have auditing running; I searched the dba_audit_session, and found a few
"logoff by cleanup" commands, but we haven't had any of those yet today.
What is a "logoff by cleanup", anyway? Should I turn off auditing? Can that slow a system down? The audit log is in the system tablespace... but even if I don't do a query, if I just connect and disconnect, that shouldn't be writing much to the audit table! We have "audit_trail = true" in the init.ora file; should I comment that line out, and restart the instance, to see what happens? Or... is there a spot in the audit logs that I've missed that would help me?

I even took a peek at v$system_event, but the only timeout values were for the events that "don't matter", like the rdbms ipc message, SQL*Net message to client, pmon timer, etc. There was a "latch free" even that had 800 timeouts with 835 waits... but in all my simple connect/disconnect tests, these numbers didn't budge. Any other places I should look?

I'm really confused because I would've expected a slow connection AND a slow disconnection- or even just a slow connection! But a slow DISconnection? It doesn't make sense. I'd increase the SGA, but that doesn't make sense either- it's not slow to query, it's slow for a disconnection! I've got processes=1000, which is more than adequate- especially if I'm the only one connected! Some more specs on the parameters... buffer cache is 120 mb (8k block, db_block_buffers =15000), dml_locks=2000, shared_pool_size=23 mb. There are 12 datafiles, about 5.1 gb total. Total SGA is 175 mb, which I thought was good (it's 3% of our total tablespace). Large pool is 0 mb; we're not running MTS... plus, I seriously doubt this memory area is used by a database disconnection!

Thanks for any/all clues or ideas or past experience! I've searched the Oracle docs, but can't really find anything that talks about what happens when a user closes their Oracle connection. Nothing in the tuning sections mentions this scenario, either. Anything I've found on google regarding
"oracle.exe high cpu" deals with the background processes (dbw0 etc), not
oracle.exe itself. My main guess is that there's something going on as part of a database disconnection that I don't know about. Sorry this question is so long... I figure the more info I can give at the beginning, the better!

-Thomas Received on Wed Feb 04 2004 - 12:01:08 CST