Perf advice :)

[Laurel]

Hi Dear Erman!
How are you ?
Hope all is fine :)
Hmm.. I got interesting problem and I am stucked. Hope you can give me some hints :)
The MVview attached definition. It consist of many UNION ALLs
When running that as just  select  (without creating view) running fast. (20 sec)
Running separatly these small selects are also fast.
When using the same select but  change to create table as select.... -> very slow  ( 15 min)
Running the analyzer and attaching reccomended  profile on create table as select -> very fast (20 sec) :)

When running this MV refresh DBMS_MVIEW.REFRESH ('"APPS"."XX_PRICE_MV"',method =>'C',atomic_refresh=>FALSE,out_of_place=>TRUE);.
Then we have the INSERT to to intermediate table ( like RV$7B2B59, the table name, sql id changes every time)
Same recommendation oracle is giving when running advisor on this INSERT/CREATE TABLE  and its very fast if accepted  the profile and run manually / so its matches profiles. (20sec)

BUT i cannot use the profile during autorun as   this is MV view and every time complete refresh happens  with INSERT to RV$<> table always new, new  sqlid and profile not macthing and very slow / same as create table .

So I need somehow figure out the hints from reccomended profile to make it fast and add to the view definition.
Tried alot of variations hints to be inserted to the view  and for session level.
Like : opt_param('optimizer_features_enable','11.2.0.4'),(different versions) optimizer_index_cost_adj=300 (for hash join),ORDERED, PARALLEL and even RULE..
Statistics gathering  on all those tables/indexes
NOthing.. all are slow


Is there a chance you look at the view def, fast and slow plan if you can suggest some hints to try to force the good execution plan that i can insert to the view definition. ?
How i can generate hints from the sugested plan that is fast (19sec) and insert it to the view code..?

I would very appeciate any help!
Thx soo very much
Laurel :)


Insert_original_and_reccomended.txtoriginal_creationmv.txt[hidden email]

[ErmanArslansOracleBlog]

Than you Laurel, everything is fine, I hope the same for you.

So your problem is with those Insert statements, right? Insert into RV$... You can't manipulate the access paths of them.. Actually there are good access paths for them, but you can't use those access path due to the changing names of those RV$ tables in every time you refresh your MV.

Good question! Let me check..

But first; , did you try it with out_of_place -> FALSE? Does it give you some extra flexibility , a higher degree of freedom for manipulating the access path of the queries and DMLs running in the background of a MV refresh?

And what is your database 4 digit version?

Again, these are fast comments and questions, I will check your issue more deeply once I have enough time.

[Laurel]

Hi Erman!
Thx for answering!:)
Yes. exactly cannot attach profile  for insert to RV$. The only way to insert hints to the view definition.
The db is 12.1.0.2 version
out of place doesnt help :/ still bad execution
So indeed my question is how to generate hints from the suggested profile ?   as the profile works :)
Tnx alot for the hints!
Have a good week ! :)
Laurel

[ErmanArslansOracleBlog]

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..)

[Laurel]

Hi Erman!
Yes. I tried with force_match=true, we have this patch. Not working as sql is changing all the time-

Thx alot for advices! :)
I will experiment further with this. :)
Take care & Merry Christmas +Happy New Year :)
Have a good week, Laurel :)