Home » RDBMS Server » Performance Tuning » Query takes long time -need help to improve Performance
Query takes long time -need help to improve Performance [message #118649] Fri, 06 May 2005 13:20 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
I have written a simple procedure which takes parameter of passing range of IDs to the select statement. Records selected are read sequentially, then do the conversion from string of text to CLOB format. And update to the selected record with converted CLOB.

Below is my query.

procedure CORRESP_TEXT_PKG6_2_XML(p_low_correspondence_id NUMBER, p_high_correspondence_id NUMBER)

--*
--* Converts RLN and SLN corresp_type letters CORRESP_DATA.text column to XML format.
--*

is

cursor CORRESP_LOOKUP
is
select cd.corresp_data_id,
cd.text,
cc.request_date,
cc.correspondence_id,
cc.STREET,
cc.STREET2,
cc.CITY,
cc.STATE_CODE,
cc.ZIPCODE,
cc.CLIENT_SERVICE_ID,
cc.CORRESP_TYPE
from CORRESP_DATA cd, CORRESPONDENCE cc
where cd.correspondence_id = cc.correspondence_id
and cc.corresp_type in ('RLN', 'SLN' )
and cd.corresp_element_no in (229, 236, 240)
and text NOT LIKE '<%'
and cc.correspondence_id between p_low_correspondence_id and p_high_correspondence_id

order by cc.CORRESP_TYPE, cd.CORRESPONDENCE_ID, cd.corresp_element_no asc
;

I put the above result in a curor and read them for conversion from text to CLOB.

And I do the update using the below SQL.

procedure UPD_CORR_DATA(l_xmltext VARCHAR2, v_corresp_data_id NUMBER)
--
--* Converts CORRESP_DATA.text column to XML format.
--*

is
l_XMLCLOB CLOB;
v_xml_text varchar2(32000);
begin

v_xml_text := l_xmltext || '</letter>';

DBMS_LOB.createtemporary(l_XMLCLOB, TRUE);
DBMS_LOB.WRITE(l_XMLCLOB, LENGTH(v_xml_text), 1, v_xml_text);

update CORRESP_DATA
set text = l_XMLCLOB
where corresp_data_id = v_corresp_data_id
;

i_TmpCnt := i_TmpCnt + SQL%rowcount;
if i_TmpCnt >= 1000
then
commit;
i_TmpCnt := 0;
end if;

end UPD_CORR_DATA;

When tested for 100 records it takes less than 5 seconds. But when I increase the range, it takes long time and SQL errors out with below
'ORA-1652: Unable to extend temp segments by 5 tablespace TEMP'. I added a new datafile to the TEMP tablespace with a size of 1024M. Even then it takes a long time to process. I have about 15 million records that need to be converted and update to the table.

Any help on this is greatly appreciated.

Re: Query takes long time -need help to improve Performance [message #118652 is a reply to message #118649] Fri, 06 May 2005 13:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Remove the COMMIT inside the loop.
Commit ONCE at the end.
And if you are updating a million records in one load,
and during the next load, there is a very good chance that the statistics are outdated.
Gather stats for tables / indexes after the end of load.

Tom has a great discussion here and explores all possibilites

http://asktom.oracle.com/pls/ask/f?p=4950:8:8951413382331002559::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330
Re: Query takes long time -need help to improve Performance [message #118658 is a reply to message #118649] Fri, 06 May 2005 14:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you are going to update each and every row, why order by in the cursor?

hth
Re: Query takes long time -need help to improve Performance [message #118727 is a reply to message #118658] Sat, 07 May 2005 08:14 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
After selecting I have logic to convert the char string to CLOB and then write to the first selected record. I would not be updating each record that is retrieved from Query. Otherwise your point is vaild. Appreciate your help.

thanks
Ravi
Re: Query takes long time -need help to improve Performance [message #118729 is a reply to message #118652] Sat, 07 May 2005 08:18 Go to previous message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Mahesh,

thanks for the valuable suggestion. I am reading the complete thread of the attached link. I think that provides lot of information which seem to give some solution to me.
I will try to implement your suggestion tomorrow and see any improvement.

thanks
Ravi
Previous Topic: Why does checkpoint only happen at log switch? :(
Next Topic: Oracle Schema slowing performance
Goto Forum:
  


Current Time: Tue Jan 19 22:39:04 CST 2021