为什么在CLOB上使用REPLACE函数会导致CACHE_LOBS增加...? [英] Why does using REPLACE function on CLOB cause increase in CACHE_LOBS...?
问题描述
作为较大的PL/SQL进程的一部分，我需要在CLOB变量上使用内置的REPLACE函数.我使用的是Oracle 11g R2，该功能可以正常运行，因为它可以按要求进行替换，但是随着过程的运行(大约有250万条记录需要处理)，它的运行速度非常慢-如:
I have a requirement to use the built in REPLACE function on a CLOB variable as part of a larger PL/SQL process. I'm using Oracle 11g R2 and the function works OK, in that it does the replace as required, but as the procedure runs (there are around 2.5 millions records to process), it slows down badly - as in:
- 前20,000条记录:〜12分钟
- 第二条20,000条记录:〜24分钟
- 第二20,000条记录:〜37分钟
- 第20,000条记录:〜52分钟
- 等...
在操作过程中检查V $ TEMPORARY_LOBS显示CACHE_LOBS的值随处理的每一行而增加-我的假设是这意味着与LOBS(在这种情况下为CLOBS)关联的内存一旦使用就不会释放. ?
Checking V$TEMPORARY_LOBS during operation shows that the value for CACHE_LOBS increases with every row processed - my assumption is that this implies that memory associated with LOBS (CLOBS in this case) is not getting released once it has been used...?
使用PL/SQL调试器逐步检查代码，发现每次调用REPLACE函数时，CACHE_LOBS的值增加2.函数调用遵循以下原则:
Stepping through the code using PL/SQL debugger reveals that the value for CACHE_LOBS increases by 2 for every call to the REPLACE function. The function calls are along the lines of:
clobRTFText CLOB;
...
dbms_lob.createtemporary(clobRTFText, TRUE, dbms_lob.call);
...
clobRTFText := REPLACE(clobRTFText, '<CR>', '\par '); <== Causes CACHE_LOBS to increase by 2
...
dbms_lob.freetemporary(clobRTFText); <== Doesn't seem to cause CACHE_LOBS to decrease
好像上面的第三行代码正在动态创建其他CLOB变量.是否因为REPLACE函数期望使用VARCHAR2参数而导致某种隐式类型转换?我尝试使用dbms_lob.copy代替"clobRTFText:= REPLACE ... etc"，但实际上更糟(即CACHE_LOBS更快地增加了).不管是什么原因，对dbms_lob.freetemporary的调用似乎都不会对CACHE_LOBS的值产生任何影响.
It's as though the third line of code above is creating further CLOB variables on the fly. Is that because there is some kind of implicit type conversion occurring due to the REPLACE function expecting a VARCHAR2 parameter? I've tried using dbms_lob.copy instead of "clobRTFText := REPLACE...etc", but it actually was worse (i.e. CACHE_LOBS increased even quicker). Whatever the reason, the call to dbms_lob.freetemporary doesn't seem to make any difference to the value of CACHE_LOBS.
我已经完成了 LOB的PL/SQL语义部分Oracle文档中的内容-它提到了可以在内置函数中使用CLOB和VARCHAR2变量的方式，但是我找不到任何有关这样做的潜在可能导致额外的内存使用的信息.
I've gone through the PL/SQL Semantics for LOBs section of the Oracle documentation - it mentions the way CLOB and VARCHAR2 variables can be used in built-in functions but I can't find anything about doing so potentially causing extra memory usage.
有没有人知道为什么会发生这种情况，或者我怎么做(即使用带有CLOB的REPLACE)而又不会释放内存(假设确实发生了这种情况)?
Does anyone have any ideas why this is happening or how I could do it (i.e. use REPLACE with a CLOB) without it failing to releasing memory (assuming that is indeed what is happening)?
谢谢
推荐答案
为什么要按程序执行此操作?声明式方法似乎可以满足要求.
Why do this procedurally? It seems a declarative approach fulfills the requirements.
UPDATE clob_table SET clob_column = REPLACE(clob_column, '<CR>', '\par ');
您可以提供任何适合您的WHERE子句.
You can supply whatever WHERE clause that suits you.
这篇关于为什么在CLOB上使用REPLACE函数会导致CACHE_LOBS增加...?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
