Oracle: udpate a XML value in place with sql

Oracle sql provides a simple way to update values in a xml document by sql.
The “updatexml” function can udpate any fields in by xpath.

update SCOTT.DEMO" d
set
myval=updatexml(d.myval,'/myroot/mynode/mytime/text()',to_char(sysdate,'YYYY-MM-DD"T"HH24:Mi:SS.FF3"Z"'))
where id=1234;

to update multiple values at once:

udpatexml(xmltype,xpath1,rep1,xpath2,rep2,...)

Very handy to update large documents.

Find Oracle SQL Profiles causing dynamic_sampling

Recently I hat memory lock issues with statements using dynamic sampling.
But after disabling dynamic_sampling at the system level there were still some statements causing issues with dynamic_sampling.
The execution plan showed that the problematic statements had SQL Profiles. Thanks to a blog from
Cristian Antognini (https://antognini.ch/2008/08/sql-profiles-in-data-dictionary/) all SQL Profiles with with a dynaimic sampling hint could be found.

Demo: Setup a statment with a sqlprofile

exec dbms_sqltune.drop_sql_profile('SQLPROFILE42');
DECLARE
     l_sql               clob;
     BEGIN
     l_sql := q'!select id from t42 where id=:x!';

     dbms_sqltune.import_sql_profile( sql_text => l_sql, 
                                     name => 'SQLPROFILE42',
                                     profile => sqlprof_attr(q'!OPT_PARAM('optimizer_dynamic_sampling' 2)!'
                                                -- ,q'!FULL(@"SEL$1" "T42"@"SEL$1")!'
                                                ,q'!INDEX(@"SEL$1" "T42"@"SEL$1" "I42_3")!'
             ),
             force_match => true );
     end;
/


var x number;
exec :x := 2167658022;

explain plan for select id from t42 where id=:x;
select * from table(dbms_xplan.display(null,null,'ADVANCED'));

Finding the SQL Profiles with dynamic sampling

 
SELECT so.name,extractValue(value(h),'.') AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE 1=1 -- so.name = '&sqlprof'
  AND so.signature = od.signature
  AND so.category = od.category
  AND so.obj_type = od.obj_type
  AND so.plan_id = od.plan_id
  and extractValue(value(h),'.') like '%dynamic%'
;
HINT
----------------------------------------------------------------------------------------------------
OPT_PARAM('optimizer_dynamic_sampling' 2)