Oracle xmltype extract function never deallocate/reclaim memory until session down

Question

Hi,

I'm using Oracle 9.2x to do some xmltype data manipulation.

The table as simple as tabls(xml sys.xmltype), with like 10000 rows stored. Now I use a cursor to loop every row, then doing like

table.xml.extract('//.../text()','...').getStringVal();

I notice the oracle instance and the uga/pga keep allocating memory per execution of xmltype.extract() function, until running out of the machine's available memory, even when the dbms_session.free_unused_user_memory() is executed per call of extract().

If the session is closed, then the memory used by the Oracle instance returns right away to as before the execution.

I'm wondering, how to free/deallocate those memory allocated by extract function in same session?

Thanks.

-- John

Answer 1

PL/SQL variables and instantiated objects are some in session memory, which is why your programming is hitting the PGA rather than the SGA. Without knowing some context it is difficult for us to give you some specific advice. The general advice would be to consider how you could reduce the footprint of the variables in your PL/SQL.

For instance, you could include the extract() in the SQL statement rather than doing it in PL/SQL; retrieving just the data you want is always an efficient thing to do. Another possibility would be to use BULK COLLECT with the LIMIT clause to reduce the amount of data you're handling at any one point. A third approach might be to do away with the PL/SQL altogether and just use pure SQL. Pure SQL is way more efficient than switching between SQL and PL/SQL, because sets are better than RBAR. But like I said, because you haven't told us more about what you're trying to achieve we cannot tell whether your CURSOR LOOP is appropriate.