We've started getting long DB wait times with the query below that is causing all of our APEX Apps to come to a crawl. Oracle Enterprise Manager points to the query below as the culprit. Has anyone experienced this and how did you fix it?
declare
nlns number := 99999999999;
l_clob CLOB;
lines htp.htbuf_arr;
l_buff varchar2(32767);
l_clob_init boolean:= false;
l_file varchar2(5);
l_doc_info varchar2(1000);
begin
OWA.GET_PAGE(lines, nlns);
if (nlns > 1) then
for i in 1..nlns loop
if (length(lines(i)) > 0) then
if ((lengthb(l_buff) + lengthb(lines(i))) > 32766) then
if (NOT l_clob_init) then
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
l_clob_init:=true;
end if;
dbms_lob.writeappend(l_clob, length2(l_buff), l_buff);
l_buff := lines(i);
else
l_buff := l_buff || lines(i);
end if;
end if;
end loop;
end if;
if (l_clob_init) then
dbms_lob.writeappend(l_clob, length2(l_buff), l_buff);
l_buff := '';
end if;
begin
:1 := l_buff;
exception when value_error then
if (not l_clob_init) then
dbms_lob.createtemporary(l_clob, true);
dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
l_clob_init:=true;
end if;
dbms_lob.writeappend(l_clob, length2(l_buff), l_buff);
end;
:2 := l_clob;
if (wpg_docload.is_file_download) then
l_file:='TRUE';
wpg_docload.get_download_file(l_doc_info);
else
l_file := 'FALSE';
end if;
:3 := l_file;
:4 := l_doc_info;
end;