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

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


I notice the oracle instance and the uga/pga keep allocating memory per execution ofxmltype.extract() function, until running out of the machine’s available memory, even when thedbms_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?


— John

1 Answer

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.


This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s