Re: Perf advice :)

Posted by ErmanArslansOracleBlog on Dec 18, 2023; 1:54pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Perf-advice-tp12209p12215.html

Fixing this remotely, and hypotactically is a little hard.
But lets try;

*For getting the info about hints , you may check sys.sqlobj$data.. There are some blogposts about it. I didn't try it but check -> https://oradwstories.blogspot.com/2013/07/hints-from-sql-profile.html

**Regarding forcing the profile for those inserts (to the RV$ tables), you may try the following ->

*You may apply the Patch 9488694 and then use  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE -> force_match=>true
(---but don't have much hope for this, because the table name is changing...)

*One other method may be using a wrapper.. A dirty method..(not that stable..) A PLSQL wrapper that does the following;

Predict the name of the upcoming RV$ table (those HEX suffixes may be based on the newest/upcoming object_id), which will be used in your next refresh.
Create that table, and create a SQL profile for the relevant query that will be used in the MV refresh
Drop that table
Refresh the MV

*Another method may be imitating the Oracle 's MV with a PLSQL code, that does the similar thing but using its own way, which is similar to what Oracle does with MVs.

*You may also get that insert to your editor, and try to manipulate its execution plan by setting some parameters in session level.. (in order to do this and work in that direction, a hands-on work is needed..)