Oracle xmltype 提取函数在会话关闭之前不会释放/回收内存
我正在使用 Oracle 9.2x 进行一些 xmltype 数据操作。
该表就像 tabls(xml sys.xmltype) 一样简单，存储了大约 10000 行。现在，我使用游标循环每一行，然后像
table.xml.extract('//.../text()','...').getStringVal();
我注意到的那样，oracle 实例和 uga/pga 在每次执行 xmltype.extract() 函数时不断分配内存，直到耗尽机器的可用空间内存，即使每次调用 extract() 都会执行 dbms_session.free_unused_user_memory()。
如果会话关闭，则 Oracle 实例使用的内存立即返回到执行之前的状态。
我想知道如何释放/解除分配同一会话中提取函数分配的内存？
谢谢。
-- 约翰
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
PL/SQL 变量和实例化对象位于会话内存中，这就是您的编程命中 PGA 而不是 SGA 的原因。在不了解一些背景的情况下，我们很难给您一些具体的建议。一般建议是考虑如何减少 PL/SQL 中变量的占用空间。
例如，您可以在 SQL 语句中包含
extract()，而不是在 PL/SQL 中执行；只检索您想要的数据始终是一件高效的事情。另一种可能性是将 BULK COLLECT 与 LIMIT 子句一起使用，以减少在任何一点处理的数据量。第三种方法可能是完全放弃 PL/SQL，只使用纯 SQL。纯 SQL 比 SQL 和 PL/SQL 之间的切换效率更高，因为 集合比 RBAR 更好。但就像我说的，因为你没有告诉我们更多关于你想要实现的目标，我们无法判断你的 CURSOR LOOP 是否合适。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.