Posted by
Laurel on
Dec 14, 2023; 9:34pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Perf-advice-tp12209.html
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]