improve performance on updating clob data [message #676247] |
Wed, 22 May 2019 19:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/70b29debcf4ca28f21257627f5ba2f9c?s=64&d=mm&r=g) |
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
|
|
|
|
Re: improve performance on updating clob data [message #676265 is a reply to message #676247] |
Thu, 23 May 2019 15:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/70b29debcf4ca28f21257627f5ba2f9c?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: improve performance on updating clob data [message #676274 is a reply to message #676273] |
Fri, 24 May 2019 05:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13925 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;
|
|
|
|
Re: improve performance on updating clob data [message #676295 is a reply to message #676294] |
Tue, 28 May 2019 09:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13925 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.
|
|
|
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
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.
|
|
|