Home » RDBMS Server » Performance Tuning » improve performance on updating clob data (Oracle RDBMS, 12.1, Windows)
improve performance on updating clob data [message #676247] Wed, 22 May 2019 19:57 Go to next message
unna
Messages: 12
Registered: October 2018
Junior Member
I have the following dynamic sql to update clob data:

Table tabclob:
EXEC_ID NUMBER
MSG CLOB
MSG_NW CLOB

create index idx_msg_contxt on tabclob (msg) indextype is ctxsys.context;
create index idx_msg_contxt on tabclob (msg_nw) indextype is ctxsys.context;
analyze table tabclob COMPUTE STATISTICS;

declare
begin
for i in (select exec_id, ssnumber
from tabclob a, xmltable('Values/record/record/array/record/record/record/value[@name="IDNumber"]'
passing xmltype(msg)
columns
ssnumber number path '/value' ) b
where contains(msg,'{"IDType">LICENSE}') > 0) loop
update tabclob
set msg_nw = XMLQUERY('copy $res := $i
modify (for $j in $res/Values/record/record/array/record/record/record/value[@name="IDNumber"]
return replace value of node $j with "111111111")
return $res'
PASSING xmltype(msg) AS "i" RETURNING CONTENT).getClobVal()
where exec_id = i.exec_id;
commit;
end loop;
end;
/


This dynamic sql runs at the rate of 18 rows updated per minute, when I ran explain plan I had full table scan and "Unoptimized XML construct detected":

explain sql:
update tabclob
set msg_nw = XMLQUERY('copy $res := $i
modify (for $j in $res/Values/record/record/array/record/record/record/value[@name="IDNumber"]
return replace value of node $j with "111111111")
return $res'
PASSING xmltype(msg) AS "i" RETURNING CONTENT).getClobVal()
where exec_id = '12345';

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 565K| 104M| 20529 (1)| 00:00:01 |
| 1 | UPDATE | TABCLOB | | | | |
| 2 | TABLE ACCESS FULL| TABCLOB | 565K| 104M| 20529 (1)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("EXEC_NUM"=12345)

Note
-----
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


I ran "SET XMLOptimizationCheck ON" and as expected I got "ORA-19022: Unoptimized XML construct detected." error. Without setting XMLOptimizationCheck, the dynamic sql will take forever to run. I like to find out how to improve the performance, any help is appreciated.


Thanks,
Unna

Report message to a moderator

Re: improve performance on updating clob data [message #676249 is a reply to message #676247] Thu, 23 May 2019 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Michel Cadot wrote on Fri, 26 October 2018 17:47
Michel Cadot wrote on Fri, 26 October 2018 15:13

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
If you post what is requested we can SHOW you a query.

Report message to a moderator

Re: improve performance on updating clob data [message #676265 is a reply to message #676247] Thu, 23 May 2019 15:25 Go to previous messageGo to next message
unna
Messages: 12
Registered: October 2018
Junior Member
Readable version:

Table tabclob:
EXEC_ID NUMBER
MSG CLOB
MSG_NW CLOB

create index idx_msg_contxt on tabclob (msg) indextype is ctxsys.context;
create index idx_msg_contxt on tabclob (msg_nw) indextype is ctxsys.context;
analyze table tabclob COMPUTE STATISTICS;

declare
begin
   for i in (select exec_id, ssnumber
             from tabclob a, xmltable('Values/record/record/array/record/record/record/value[@name="IDNumber"]'
                                       passing xmltype(msg)
                                       columns
                                          ssnumber number path '/value' ) b
             where contains(msg,'{"IDType">LICENSE}') > 0) loop
       update tabclob
       set msg_nw = XMLQUERY('copy $res := $i
                              modify (for $j in $res/Values/record/record/array/record/record/record/value[@name="IDNumber"]
				      return replace value of node $j with "111111111")
			      return $res'
                              PASSING xmltype(msg) AS "i" RETURNING CONTENT).getClobVal()
       where exec_id = i.exec_id;
       commit;
   end loop;
end;
/


This dynamic sql runs at the rate of 18 rows updated per minute, when I ran explain plan I had full table scan and "Unoptimized XML construct detected":

explain sql:
update tabclob 
set msg_nw = XMLQUERY('copy $res := $i
                       modify (for $j in $res/Values/record/record/array/record/record/record/value[@name="IDNumber"]
	  		       return replace value of node $j with "111111111")
		       return $res'
  	               PASSING xmltype(msg) AS "i" RETURNING CONTENT).getClobVal()
where exec_id = '12345';

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 565K| 104M| 20529 (1)| 00:00:01 |
| 1 | UPDATE | TABCLOB | | | | |
| 2 | TABLE ACCESS FULL| TABCLOB | 565K| 104M| 20529 (1)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("EXEC_NUM"=12345)

Note
-----
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

[Updated on: Thu, 23 May 2019 15:39]

Report message to a moderator

Re: improve performance on updating clob data [message #676273 is a reply to message #676265] Fri, 24 May 2019 05:10 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
A few things:

analyze table tabclob COMPUTE STATISTICS;

I'd not do this but leverage dbms_stats instead.

If you have no usuable index/index worth using on EXEC_NUM the database has no choice but to full scan that table.

What is the average clob length? If it is huge, that might hurt you, but first off I'd see if you can stop if full scanning the table table.

However without the actual DDL it is impossible to add anything else, there are too many factors in a LOB construct to offer more than this limited information.

Report message to a moderator

Re: improve performance on updating clob data [message #676274 is a reply to message #676273] Fri, 24 May 2019 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is exec_id supposed to be unique?
If it is, why isn't there a primary key / unique index on it?

And if it is then why aren't you just running this:
update tabclob
       set msg_nw = XMLQUERY('copy $res := $i
                              modify (for $j in $res/Values/record/record/array/record/record/record/value[@name="IDNumber"]
				      return replace value of node $j with "111111111")
			      return $res'
                              PASSING xmltype(msg) AS "i" RETURNING CONTENT).getClobVal()
       where contains(msg,'{"IDType">LICENSE}') > 0;

Report message to a moderator

Re: improve performance on updating clob data [message #676294 is a reply to message #676274] Tue, 28 May 2019 09:26 Go to previous messageGo to next message
unna
Messages: 12
Registered: October 2018
Junior Member
Thank you all for your help. Using for loop because I have other columns to update, which is not included in this sample.

I ran analyze statistics couple times and somehow the INDEX RANGE SCAN was used instead of full table scan. I setup scheduler job to run schema statistic analyze to help the performance.


Thanks again for all your help,
Unna.

Report message to a moderator

Re: improve performance on updating clob data [message #676295 is a reply to message #676294] Tue, 28 May 2019 09:33 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're using the analyze command, stop. You should be using the dbms_stats package to gather stats these days (and that's been the case since at least version 10).

If you over simplify your example you will get suggestions that don't necessarily work with the code you haven't told us about. Maybe you should post a more representative example.

Report message to a moderator

Re: improve performance on updating clob data [message #676296 is a reply to message #676295] Tue, 28 May 2019 09:38 Go to previous message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
If you're using the analyze command, stop. You should be using the dbms_stats package to gather stats these days

And before using DBMS_STATS, first delete your statistics using ANALYZE ... DELETE STATISTICS.

Report message to a moderator

Previous Topic: poor performance during switch log file
Next Topic: Consider of load-test invoking only one insert statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 17:46:29 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.