number of open files increasing

[Dave Chamberlin]

We are running an Oracle 7.3.4 server under hpux 10.2 . The number of open files on the system has been increasing over time. I increased the nfile kernel parameter back in june from ~9600 to 11600. We recently are reaching this limit. My impression is that Oracle is opening files and not closing them - since we may have 300-500 procs and only 100 datafiles (which would not all be open by a given process). I don't believe that these open files are in use by active procs. I have been told that we open oracle cursors but don't worry about closing them. Could this be causing the problem? Is there any way to keep this problem from getting worse? Any insight would be appreciated.

[Rita C Workman]

Well there isn't enough info here to say you, in fact, have a problem. It may simply be that folks are doing more, or simply not closing out as timely as they should. Increasing the nfile (I generally increase maxusers and let the formula increase nfile & nproc) may be appropriate.
But you'll have to monitor to be sure...

I found some info about Oracle cursors that may help, it had to do with a Visual Basic rdo question, but seemed appropriate for your situation:
"..Oracle opens cursors internally, and they are defined as follows:
A cursor is a synonym for context area - a work area in memory where Oracle stores the current SQL statement. For a query, the area in memory also includes column headings and one row retrieved by the Select Statement.
CAUSE
Oracle provides an API to get the stored procedure information. This API is called, in the case of a stored procedure execution, to get the parameter information. The API generates two additional cursors, which are not released until the logoff request happens. These cursors do not increase when you attempt multiple executions..."

So those Oracle cursors don't look to be an issue.
/rcw

[James R. Ferguson]

Hi Dave:

It's very possible that you have a large number or orphan processes consuming resources.

Take a look at some of the suggestions in this thread:

http://my1.itrc.hp.com/cm/QuestionAnswer/1,1150,0x6f717e990647d4118fee0090279cd0f9,00.html

...JRF...

[Carlos Fernandez Riera · Accepted solution]

The problem ( no problem) is exactly you have 100 datafiles.

Oracle process can read from shared memory ( db_block_buffers) and also from datafiles directly.

When the number of datafiles is high, each process can/need to open more datafiles, so then number of open files increases.

You can check it using glance or gpm , select a oracle process for one user and see open files.