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


Other related posts:

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