| improve performance on updating clob data [message #676247] |
Wed, 22 May 2019 19:57  |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
