SUMMARY : Session PGA memory max exceeded 2 GB and crashes.

Summary of the solution to my problem.

... Kernel had maxdsiz_64bit set to 2GB and the housekeeping process
was hittingthis limit and core dumping.

... The PK_TRACE package was adding lines to a 'history' collection.

... Each time it added a line, it checked that the current count of
rows in the collection was equal to some configured maximum limit. If it was, reset the current line pointer to zero.

... Unfortunately, the maximum was defined to be zero, so it never
managed to get equal to zero as it was iincremented before the check.

... Each row in the table was being scanned using a FTS, so the
function call was executed for each and every row. With the number of rows in the table, we blew out the data space easily.

... The app supplier has been informed and asked to initialise the max
to something that is non-zero. I have tested this approach and it works fine. In addition, removing the function call from the full scan (using either Jonathan's scalar subquery which is cached or Nigel's 'separate local variable) both work fine.

My thanks to all who replied.

Cheers,
Norman.

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l