Pat Price from Colorado writes to tell us:

I am just wrapping up a gig at a customer where I thought the fix was self evident, but sometime the obvious gets overlooked.

I come on site to find an NT server running Oracle with 2cpu's running up to 235 sessions at which point it croaks, usually about 9:30 am and again around lunchtime, at which point the customer reboots it, twice daily. I am about the 4th DBA to look at it; all the others have failed to pinpoint the problem.

Here's the error it gives with the resolution:

"TNS-12500 TNS:listener failed to start a dedicated server process

Cause: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly.

Action: Turn on tracing at the ADMIN level and re-execute the operation. Verify that the Oracle Server executable is present and has execute permissions enabled. Ensure that the Oracle environment is specified correctly in LISTENER.ORA. The Oracle Protocol Adapter that is being called may not be installed on the local hard drive. Check that the correct Protocol Adapter are successfully linked. If the error persists, contact Oracle Customer Support."

I'm told (by the customer) that the machine has 2 gigs of real memory and examination shows that Oracle soaks up 1.5GB at startup due to unusually large shared pool (1Gig) and db_block_buffers (400Meg).

Upon looking around I found that the machine has actually only 1Gig of real memory (the customer was misinformed) and that it dies when memory usage approaches 1.8GB - not surprising, really. I reduced the shared pool to 300Megs and the db block buffers to 20,000 or 80Megs (it has a 4k blocksize), rebooted and Voila' - fixed - now the server happily runs without reboots and supports 430+ sessions via the same jdbc and users report improved perceived performance.

So how did it get to be like that, I ask myself?

Well, the customer uses a version of TOAD* that, in its tuning section, recommends increasing the db_block_buffers because cache hit ratio is low and increasing the size of the shared pool because the Library Cache Pin Hit Ratio was off, and it appears the DBA blindly followed its advice without considering the finite capacity of the machine.

Remember: Cache hit ratios are useful, but not definitive

*The Tool for Oracle Application Developers.