Hope someone can shed some light on a memory consumption issue I’m having with a package call that involves used-defined object types.
I have a table-based exception management routine. An interface package, “p_exception_message_get”, gets an exception message from a table and replaces defined substitution variables in the message. The exception message interface package takes the substitution values in the form of a VARRAY parameter consisting of name / value pair object. The VARRAY has a defined limit of 10 objects, but typically only gets instantiated with 2 to 3 elements.
CREATE OR REPLACE
TYPE cvrt_v2.parameter_pair_obj_t AS OBJECT
CREATE OR REPLACE TYPE
cvrt_v2.parameter_var_t AS VARRAY(10) OF parameter_pair_obj_t
I have a method, “validate_all”, in a validation package, “p_validate”, that reads records from a table and performs a validation. I am using the exception message routine “p_exception_message_get.get_error_message” to generate the messages. Finally, I take the generated exception message and log it to a table. This all works.
I can call the routine, “p_validate.validate_all”, directly it runs and completes. (The test I’m running on the validation routine involves approx 21K rows, all are in an error state and all result in an exception message that gets written to a table.).
(I’m running on development box with little competing load. The DB server has approx 15 GB RAM available, approx 1GB RAM is consumed when I run the method, “p_validate.validate_all”. CPU utilization goes to approx 12% while the routine runs and then returns to normal).
However, when I call that same function from within another package, “p_business_validate.validate_all”, proceeds to consume large amounts of memory – all it of 15GB RAM – about 1GB every 5 seconds until the server crashes or the session is killed.
e.g. p_business_validate.validate_all – there is no additional work being done – just a call to the lower-level package.
PROCEDURE validate_all (
p_id IN NUMBER,
p_exception_flag OUT VARCHAR2(1))
My suspicion falls on the user-defined object types. I thought that Oracle should clean them up automatically. – Just to be safe in “p_exception_message_get.get_exception_message” I added a call to delete the elements of the VARRAY and then set the VARRAY variable itself to NULL (after the substitutions are performed). – I’m not sure that doing so makes much of (or any) a difference. Is this event necessary?
Calling the “p_business_validate.validate_all” still kills the RAM on the sever, calling “p_validate.validate_all” still only consumes about 1 to 2 GB RAM and then completes.