Skip to Main Content
  • Questions
  • MATERIALIZED VIEW Performance Issue!

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sameer.

Asked: January 06, 2021 - 1:55 pm UTC

Last updated: June 01, 2021 - 3:22 pm UTC

Version: PL/SQL Release 12.2.0.1.0 - Production

Viewed 10K+ times! This question is

You Asked

I have created a MV on UAT server and my MV view using a query which has remote connectivity to PROD and select only rights to these tables
which has millions of rows around 10 lakhs in each table but after calculation output of query is 139-150 rows only. query alone without MViews is taking 60
seconds but when I use CREATE MATERIALIZED VIEW NOCOMPRESS NOLOGGING BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND NEXT null USING DEFAULT
LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE as "query" mview creation happens in one hour and after that refresh time
is 20-30 minutes ? which is surely not acceptable as this data is being used for dashboard with 3 minutes delay which MV should take time to refresh!
I don't have privilege to anything to check on prod DB but on UAT I have sufficient access! I have tried many option but didn't work so please help
me to know what is solution and if no solution what is reason behind this? in addition when my mview refresh it shows in explain plan
" INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO abc". Please help me!

I am really stuck here and tried my hard to get it resolved or finding a reason where I can explain to relevant team! Please help!
1. I have tried create table with same query and it took less than a minute.
2. Insert statement also working fine taking same time.
3. I tried MV view refresh option with atomic_refresh=false as well but it didn't work and actually it will not help!

Please let me know if u have any info required!

Note: My mv view query using prod tables(approx 4 tables) with db link from UAT.Prod server has one separate user which has been given below table rights

select count(*) from abc@prod;
--800000
select count(*) from abc1@prod;
--700000
select count(*) from abc2@prod;
--200000

and Chris said...

To update the MV quickly, you want FAST refreshes.

To enable this you must have materialized view logs on all the tables in the query.

Are these present? If they are, have you checked if fast refresh is possible for your query?

As I discuss in this video, not all queries are fash refreshable:



If fast refresh is off the cards for whatever reason, you can investigate why the refresh is taking so long by tracing this process:
exec dbms_monitor.session_trace_enable ( null, null, true, true );
exec dbms_mview.refresh ( 'your_mv' );
exec dbms_monitor.session_trace_disable;


Then reviewing the trace file to see where all the time is going.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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!
Chris Saxon
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 ??

Connor McDonald
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.
Connor McDonald
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?
Chris Saxon
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...

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.