Hi,
I migrate databases 12c to 19c. One database have hundred thousands objects. A dbms_mview.refresh produce this query that is very slow on this database.
sql_id 091fb1shwqyn8
select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_obj#=:1 and (d.p_timestamp=nvl(:2, d.p_timestamp) or d.property=2) and o.owner#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj#
With the sql_id I created a sql patch (or sql profile force_match) to inject the hint opt_param('_optimizer_push_pred_cost_based','false').
I execute the select and the plan note says that the sql patch (or profile) is used and the select is fast.
When I run the refresh view, the sql (sql_id 091fb1shwqyn8) is run implicitly by the refresh view, but the sql patch is not used !
Does sql patch is supposed to works in this kind of execution (system query execute implicitly by a refresh view) ?
Thanks a lot.