sameer gupta, January 06, 2021 - 6:32 pm UTC
Thanks for quick response but still my question is
1.if query used by MV is taking 40 seconds but MV creation and refresh after creation MV could take one hour to refresh?
2. I am remotely using prod 4 tables by UAT environment then should i create MV logs on each tables and i should create it on prod or on UAT if on prod that would not work as on Prod we are not allowed any perform activity and if on UAT than it might work! I will try ! Please help me to get answers!
Also i have run my Mview creation as u suggested after enabling trace file ..once done i would like to share with you if u don't mind!
Thanks for all!
January 07, 2021 - 8:38 am UTC
1. I don't why - that's why I asked you to do a trace. This will reveal where the time is going
2. You need to create MV logs on the tables in the source database.
Going across a database link adds complications and may be (part) of the reason the refresh is taking so long.
Why are you going across a database link? Surely you should create the MV on the tables in UAT to test it before deploying to prod?
Trace file Observation
sameer gupta, January 08, 2021 - 6:16 pm UTC
I have no experience in trace file but tried to have a look and seems while inserting records in MV table it's taking entire time .
Sharing u some portion:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.41 0.41 0 0 0 0
Execute 1 4610.59 8337.58 2 27 34 173
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4611.00 8338.00 2 27 34 173
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 154 (recursive depth: 1)
Number of plan statistics captured: 1
--Query like
--
sELECT "A"."ORIGID" "ORIGID","A"."ORG" "ORG",STATUS
FROM
( SELECT ORIGID,STATUS FROM UBSIPMA.A@db_prod PF,UBSIPMA.B@db_prod IR,ORG@db_prod O,ORIG_FILESTATUS_WARNTIME OWT
WHERE CONDITION
UNION ALL
SELECT ORIGID,STATUS FROM UBSIPMA.A@db_prod PF,UBSIPMA.B@db_prod IR,ORG@db_prod O,ORIG_FILESTATUS_WARNTIME OWT
WHERE CONDITIO
UNION ALL
UNION ALL
SELECT ORIGID,STATUS FROM UBSIPMA.A@db_prod PF,UBSIPMA.B@db_prod IR,ORG@db_prod O,ORIG_FILESTATUS_WARNTIME OWT
WHERE CONDITIO
UNION ALLUNION ALL
SELECT ORIGID,STATUS FROM UBSIPMA.A@db_prod PF,UBSIPMA.B@db_prod IR,ORG@db_prod O,ORIG_FILESTATUS_WARNTIME OWT
WHERE CONDITIO
)
----
Rows (1st) Rows (avg) Rows (max) Row Source Operation
466994 466994 466994 FILTER (cr=4 pr=0 pw=0 time=332186634 us starts=1)
466994 466994 466994 NESTED LOOPS (cr=4 pr=0 pw=0 time=331743024 us starts=1 cost=3 size=610 card=1)
467233 467233 467233 NESTED LOOPS (cr=3 pr=0 pw=0 time=343141313 us starts=1 cost=3 size=610 card=1)
656475 656475 656475 FILTER (cr=0 pr=0 pw=0 time=462969608 us starts=1)
656475 656475 656475 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=461868670 us starts=1 cost=2 size=594 card=1)
656475 656475 656475 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=8919883 us starts=1 cost=2 size=61 card=1)
221 221 221 REMOTE ORIGINATOR (cr=0 pr=0 pw=0 time=2502 us starts=1 cost=2 size=35 card=1)
656296 656296 656296 REMOTE B(cr=0 pr=0 pw=0 time=1483317 us starts=221 cost=0 size=26 card=1)
656296 656296 656296 REMOTE PROCESSEDFILE (cr=0 pr=0 pw=0 time=449794774 us starts=656475 cost=0 size=533 card=1)
467233 467233 467233 INDEX RANGE SCAN ORIG_FILESTATUS_WARNTIME_IDX1 (cr=3 pr=0 pw=0 time=3365368 us starts=656475 cost=0 size=0 card=1)(object id 23039485)
466994 466994 466994 TABLE ACCESS BY INDEX ROWID ORIG(cr=1 pr=0 pw=0 time=1299570 us starts=467233 cost=1 size=16 card=1
----
May be you could help me! Is there any solution ??
January 11, 2021 - 2:08 am UTC
Notice the three last lines...all have high "starts" figures. That means that step of the plan was executed that many times.
Thus in your case, you are hopping across a db link hundreds of thousands of times. Depending on the size of the tables and the quality of the network, it be better to drag all of the data across the network just once and do a local hash/merge join.
Mview performance
Ajeet Gupta, May 31, 2021 - 12:56 pm UTC
Can you enable Mvew monitoring and see how many indexes have been used ?
You may kindly send me an email to discuss further... I may try to add some point in your issues.
June 01, 2021 - 1:46 am UTC
FYI - We don't publish email addresses on AskTom
regarding stale stats
Rajeshwaran Jeyabal, June 01, 2021 - 2:32 pm UTC
656475 656475 656475 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=8919883 us starts=1 cost=2 size=61 card=1)
221 221 221 REMOTE ORIGINATOR (cr=0 pr=0 pw=0 time=2502 us starts=1 cost=2 size=35 card=1)
656296 656296 656296 REMOTE B(cr=0 pr=0 pw=0 time=1483317 us starts=221 cost=0 size=26 card=1)
it was this part, the ORIGINATOR table got estimated for 1 row, but actuals were 221 rows.
since estimated for 1 row, picked up nested loop to drive B table, 221 times and returned 656K rows.
the same pattern is been observed for other tables in the join.
by any chance do we got stale stats on "ORIGINATOR" and "B" table on remote database?
June 01, 2021 - 3:22 pm UTC
That's worth checking; though that comment was nearly 5 months ago! I'm hoping they've resolved this by now...