- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 68 Insurance
- 536.9K On-Premises Infrastructure
- 138.5K Analytics Software
- 38.6K Application Development Software
- 5.9K Cloud Platform
- 109.5K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
SQL patch (or profile force_match) on a system query produced by a refresh mview
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.
select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,
nvl(d.property,0), o.type#, o.subname, d.d_attrs
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.