Re: Processes "Expire" without completing

From: bdbafh <bdbafh_at_gmail.com>
Date: Sun, 20 Jan 2008 06:16:21 -0800 (PST)
Message-ID: <49d05814-8e40-4b39-97b0-df9d1dd04f84@v67g2000hse.googlegroups.com>


On Jan 19, 6:25 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0,
> Windows 2003 Server
> 8GB Memory
> 8 CPU
> Shared Server
>
> In our database warehouse we've have had load processes which after running
> for a period of time just "expire" without successfully completing. The
> ones which get kicked off from a Windows CMD file still display the command
> prompt window even though the process has stopped.
>
> Our off-site DBA has said he hasn't been able to find anything in any log
> files so I'm left to try to figure this puzzle out.
>
> I have noticed that processes seem to "expire" when there are either a lot
> of other processes running at the same time or a few others which seem to be
> using mostly CPU.
>
> I ran one process which nearly ran to completion before it too "expired". I
> ran it in shared server mode and also in dedicated server mode and both
> processes just "expired" without completing and with no indication of an
> error.
>
> When I looked at the memory configuration of the database I noticed this:
>
> o SGA allocation is 5,908 Mb (95.6% of total memory allocation)
> o PGA allocation is 250 Mb (4.4% of total memory allocation)
> o work area size policy is set to AUTO
>
> Is it normal to have such a low PGA allocation out of the total Oracle
> memory? Could there be a problem with not enough PGA for the processes?
>
> I don't have direct access to any of the server log files but if I have the
> session id of a process which "expires", shouldn't the off-site DBA be able
> to look for the logfile for that session and find out if anything is causing
> the session to just end?
>
> Thanks.

What edition of the operating system is in use? How much memory has in fact been committed in the OS, as well as what is the value for virtual memory for the oracle.exe process?

Is this standard or enterprise edition of the Oracle database server software?
Can you configure the Oracle client on the devices used to perform loads to use dedicated_server=on? Can you check in v$session that those sessions in fact are using a dedicated server?

What is the value for the large_pool_size, as the large pool is typically used for supporting shared server processes.

Have you checked udump for trace files, possibly due to ora-4031 errors?
SQL> show parameter udump

check for trace files generated there.

Also, check the listener log for out of memory errors (internal restriction exceeded).

-bdbafh Received on Sun Jan 20 2008 - 08:16:21 CST

Original text of this message