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 previous message
unna
Messages: 9
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
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: poor performance during switch log file
Next Topic: Consider of load-test invoking only one insert statement
Goto Forum:
  


Current Time: Tue Jan 28 15:20:11 CST 2020