Home » RDBMS Server » Performance Tuning » update xml eating up a lot of time (11.2.0.2)
update xml eating up a lot of time [message #567340] Wed, 26 September 2012 16:20 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
update xml eating up a lot of time is there any way to tune
SELECT UPDATEXML(:B3 , '/FCUBS_RES_ENV/FCUBS_BODY/FLD/FN[@TYPE="' || :B2 || 
  '"]', :B1 ) 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    499      0.44       0.90          0          3          0           0
Fetch      499      1.49       2.87          0          0          0         499
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      999      1.93       3.77          0          3          0         499


real code
     SELECT updatexml(l_xml,
                               '/FCUBS_RES_ENV/FCUBS_BODY/FLD/FN[@TYPE="' ||
                               upper(replace(cspkes_misc.fn_getparam(p_parent_list,
                                                                     l_parent_list_clob,
                                                                     'Y',
                                                                     l_cnt,
                                                                     '>'),
                                             '-',
                                             '_')) || '"]/text()',
                               l_fn_str)
                INTO l_xml
                FROM dual;
Re: update xml eating up a lot of time [message #582842 is a reply to message #567340] Tue, 23 April 2013 01:46 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

hello Ayush.

Please try giving values directly rather Using bind variables( and see the Explain plan for it).

Regards,
Kapardi
Re: update xml eating up a lot of time [message #582871 is a reply to message #582842] Tue, 23 April 2013 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58631
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a VERY VERY bad advice.
Do NOT use direct values, USE bind variables (as it is here).

Regards
Michel
Re: update xml eating up a lot of time [message #582872 is a reply to message #582871] Tue, 23 April 2013 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Considering that's a select from dual the explain plan is going to tell you nothing useful.
I suspect the function is the main problem.
Re: update xml eating up a lot of time [message #582873 is a reply to message #567340] Tue, 23 April 2013 03:22 Go to previous message
Michel Cadot
Messages: 58631
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ayush_anand

XML functions and above UPDATE ones are long operations (especially if XML is quite big) but 3.77s for 499 rows does not seem a lot for me (7.5ms per row).

Regards
Michel
Previous Topic: Query Tuning -indexing
Next Topic: alert.log question
Goto Forum:
  


Current Time: Thu Jul 31 02:37:06 CDT 2014

Total time taken to generate the page: 0.15905 seconds