Skip to Main Content
  • Questions

  • Query on a Materialized View becomes very slow after sometime

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ricardo.

Asked: July 06, 2023 - 4:07 am UTC

Last updated: July 07, 2023 - 10:30 am UTC

Version: Oracle DB 21x XE

Viewed 10K+ times! This question is

You Asked

I have Oracle 21c XE using some Materialized Views (MV) for my application. The most used materialized view, MajorView, is based on other secundary MVs A,C,D , i.e. the MajorView is nested MV since it's query references others MVs A,C and D.

In fact these secundaries MVs are also nested MVs. Then the general schema of MVs referencing is as follows :

MV MajorView references A,C D
MV C references A and B
MV D references C
MV E references MajorView
All these views are created fastly, in a very acceptable time, a matter of few seconds.

MajorView has about 400k rows, a query Q1 run over it to recover the few essential rows for the application tooks about 0,4 seconds in normal conditions. That is fine.

For any reason, after some time, the query on this MajorView becomes extremely slow.

Several times along the day, after certain events application events, it is run the following refresh :

Begin
   DBMS_MVIEW.refresh ('A,B,C,D,MajorView,E','??????',NULL,TRUE,FALSE,1,0,0,TRUE,TRUE);
End;


After the problem appears, when, why and which reason I don't know, the Q1 query takes now about 40 seconds to run while before it run on 0,4 seconds, it's about a 100 time slower !!! Unacceptable for the application.

Checking the MVs status after the Refresh with the query :

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE,COMPILE_STATE, 
       to_char(LAST_REFRESH_END_TIME, 'DD/MM/YY HH24:MI:SS') as LAST_REFRESH_END_TIME,
       to_char(LAST_REFRESH_DATE, 'DD/MM/YY HH24:MI:SS') as LAST_REFRESH_DATE,      
       to_number((LAST_REFRESH_END_TIME - LAST_REFRESH_DATE) * 1440 * 60) AS "Time (sec)"
FROM USER_MVIEWS


all STALENESS were "FRESH", ALL COMPILE_STATE were "VALID" .

Troubleshooting this problem I ended up finding that if I Drop and re-Create MajorView again and immediately run Q1 query, the speed become normal, i.e. fast again only 0,4 seconds! Unfortunately this acceptable speed lasts form some time, maybe hours, maybe days !

KEY QUESTION of the PROBLEM : Why MajorView Q1 query become suddenly slow ?

Other related questions :

1) Is the refresh method and command sintax right ? Is there any hierarchy of refresh ?
2) Is there any problem to reference a MV on a query to build another MV ?
3) Can I use Materialized Views in Oracle 21c XE ? I'm using it , but the documentation does not mention it has Materialized Views !!

Thanks for your help !

See the Explain Plans for the Normal Query (fast) and for the Problematic query (slow)

1) Normal Query (fast)

Plan hash value: 4205125506
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                                   |      1 |        |     55 |00:00:00.15 |    5257 |       |       |          |
|*  1 |  HASH JOIN                                   |                                   |      1 |      1 |     55 |00:00:00.15 |    5257 |   873K|   873K| 1362K (0)|
|*  2 |   HASH JOIN                                  |                                   |      1 |      1 |     55 |00:00:00.10 |    5224 |   951K|   951K| 1293K (0)|
|   3 |    NESTED LOOPS                              |                                   |      1 |     17 |     55 |00:00:00.04 |    1665 |       |       |          |
|*  4 |     HASH JOIN                                |                                   |      1 |     25 |     55 |00:00:00.01 |      23 |  1572K|  1572K| 1159K (0)|
|   5 |      NESTED LOOPS OUTER                      |                                   |      1 |      9 |     13 |00:00:00.01 |       8 |       |       |          |
|*  6 |       TABLE ACCESS FULL                      | CARTEIRA                          |      1 |      9 |     13 |00:00:00.01 |       6 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID            | PLGITEMS                          |     13 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |        INDEX UNIQUE SCAN                     | IDX_PLGITEMS_PLGITEM_ID           |     13 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |      TABLE ACCESS FULL                       | CARTEIRAATIVO                     |      1 |    820 |    820 |00:00:00.01 |      15 |       |       |          |
|  10 |     VIEW PUSHED PREDICATE                    | VW_SQ_1                           |     55 |      1 |     55 |00:00:00.06 |    1642 |       |       |          |
|* 11 |      FILTER                                  |                                   |     55 |        |     55 |00:00:00.06 |    1642 |       |       |          |
|  12 |       SORT AGGREGATE                         |                                   |     55 |      1 |     55 |00:00:00.06 |    1642 |       |       |          |
|* 13 |        MAT_VIEW ACCESS BY INDEX ROWID BATCHED| MVW_CARTEIRAATIVOHIST             |     55 |      1 |  28003 |00:00:00.09 |    1642 |       |       |          |
|  14 |         BITMAP CONVERSION TO ROWIDS          |                                   |     55 |        |  33267 |00:00:00.07 |     878 |       |       |          |
|  15 |          BITMAP AND                          |                                   |     55 |        |     55 |00:00:00.05 |     878 |       |       |          |
|  16 |           BITMAP CONVERSION FROM ROWIDS      |                                   |     55 |        |     55 |00:00:00.03 |     487 |       |       |          |
|* 17 |            INDEX RANGE SCAN                  | MVW_CARTEIRAATIVOHIST_IDX_CART_ID |     55 |   1083 |    182K|00:00:00.14 |     487 |       |       |          |
|  18 |           BITMAP CONVERSION FROM ROWIDS      |                                   |     55 |        |     55 |00:00:00.02 |     391 |       |       |          |
|* 19 |            INDEX RANGE SCAN                  | MVW_CARTEIRAATIVOHIST_IDX_ATV     |     55 |   1083 |    136K|00:00:00.10 |     391 |       |       |          |
|* 20 |    MAT_VIEW ACCESS FULL                      | MVW_CARTEIRAATIVOHIST             |      1 |      1 |    268K|00:00:00.22 |    3559 |       |       |          |
|  21 |   TABLE ACCESS FULL                          | ATIVO                             |      1 |      1 |   2356 |00:00:00.01 |      33 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------


2) Problematic query (slow)

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                 |      1 |        |     55 |00:00:18.76 |    5543 |       |       |          |
|*  1 |  FILTER                            |                                 |      1 |        |     55 |00:00:18.76 |    5543 |       |       |          |
|   2 |   HASH GROUP BY                    |                                 |      1 |     20 |  28003 |00:00:18.78 |    5543 |  7679K|  2050K| 7329K (0)|
|*  3 |    HASH JOIN                       |                                 |      1 |  22988 |     18M|00:00:18.69 |    5543 |  7346K|  2119K| 7790K (0)|
|*  4 |     HASH JOIN                      |                                 |      1 |   1050 |  28003 |00:00:00.07 |    3834 |   838K|   838K| 1358K (0)|
|*  5 |      HASH JOIN                     |                                 |      1 |     25 |     55 |00:00:00.01 |      56 |   924K|   924K| 1367K (0)|
|*  6 |       HASH JOIN                    |                                 |      1 |     25 |     55 |00:00:00.01 |      23 |  1236K|  1236K| 1180K (0)|
|   7 |        NESTED LOOPS OUTER          |                                 |      1 |      9 |     13 |00:00:00.01 |       8 |       |       |          |
|*  8 |         TABLE ACCESS FULL          | CARTEIRA                        |      1 |      9 |     13 |00:00:00.01 |       6 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID| PLGITEMS                        |     13 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 10 |          INDEX UNIQUE SCAN         | IDX_PLGITEMS_PLGITEM_ID         |     13 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |        TABLE ACCESS FULL           | CARTEIRAATIVO                   |      1 |    820 |    820 |00:00:00.01 |      15 |       |       |          |
|  12 |       TABLE ACCESS FULL            | ATIVO                           |      1 |   2356 |   2356 |00:00:00.01 |      33 |       |       |          |
|* 13 |      MAT_VIEW ACCESS FULL          | MVW_CARTEIRAATIVOHIST           |      1 |     42 |    268K|00:00:00.23 |    3778 |       |       |          |
|* 14 |     INDEX FAST FULL SCAN           | MVW_CARTEIRAATIVOHIST_IDX_GERAL |      1 |    268K|    268K|00:00:00.22 |    1709 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------


Any help and support are welcome!
Thanks.

and Chris said...

The query is slow because "something's changed". There are many possible changes that could lead to a slower plan, Jonathan Lewis has catalogued many things that could lead to slower SQL; reviewing this may help you:

https://jonathanlewis.wordpress.com/2013/12/23/plan-changes/

So it's tough for us to say exactly why this has happened. Instead, I'll make some observations on the plans which may help you find a solution.

The problem in the slow query is the HASH JOIN at operation 3. This returns 18 million rows and takes ~18 seconds to complete.

The issue is it's joining all the tables, then grouping. In the fast plan, the database groups MVW_CARTEIRAATIVOHIST down to 55 rows first then joins it.

Another difference between the plans is the estimated number of rows for ATIVO & MVW_CARTEIRAATIVOHIST (1 vs 2356 and 1 vs 42 respectively).

The change in row estimates suggests that the optimizer has updated stats - likely because these have been gathered. So check to see if these have changed recently:

select table_name, last_analyzed from user_tables


But - as stated - there could be many other reasons why this happens.

Ultimately, if you want to stick with the fast plan you can use SQL plan management to create baselines to "lock it in".

https://blogs.oracle.com/optimizer/post/sql-plan-management-part-1-of-4-creating-sql-plan-baselines

Can I use Materialized Views in Oracle 21c XE?

Absolutely. Though I do question their use here - particularly with such a large chain of MVs built on top of each other. MVs referencing MVs is a valid approach though adds complexity.

MVs are most effective when you aggregate millions of rows down to a handful. 400,000 rows is small in modern database terms. I would expect you to be able to get good enough performance without the MVs on data this size by ensuring you have the correct indexes in place and writing the queries optimally.

Rating

  (4 ratings)

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

Comments

Possible reasons for changing SQL Plan

Ricardo, July 06, 2023 - 2:46 pm UTC

Chris , thanks for your attention and explanations.
I'm striving to to find the reasons why SQL Plain changed. Let me share some points with you :
1) Along the day I run several refreshes on all MV
2) Twice a day, I feed the source tables that compose the MV , then refresh
3) All tables and MV are in the same schema, same machine

Q1) Why after Re-create the MV MVW_CARTEIRAATIVOHIST (MajorView) SQL Plans always choose the fast path ?
Q2) In slow query Plan , how can I know what is the 3rd operation that handles 18M rows ?

Chris Saxon
July 06, 2023 - 4:10 pm UTC

Q1 - I don't know! Like I said, there are lots of possible reasons. Figuring this out without access to your system is hard.

For some reason, when you recreate the MV this changes the row estimates for the plan. Perhaps recreating it resets something.

Q2 Look at A-rows & A-time, Id 3:

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|*  3 |    HASH JOIN                       |                                 |      1 |  22988 |     18M|00:00:18.69 |    5543 |  7346K|  2119K| 7790K (0)|

Q1 and Q2

Ricardo, July 06, 2023 - 5:51 pm UTC

Thanks Chris ,

Regard Q1 - Ok, I understand that it's difficult to find the cause it change the Plan.

Q2 - I asked about the what part of the my query refers to the reported 3rd operation in the plan. Is it possible to know that by looking the Plan ?


Q1 and Q2 (edited)

Ricardo, July 06, 2023 - 7:00 pm UTC

Thanks Chris ,

Sorry for misspelling and typo errors on previous comment. Unfortunately there is no edition of comments in this forum.

Regard Q1 - Ok, I understood the difficult to find the causes of SQL plan changes.

Q2 - In fact I want to know which part of my SQL commands refers to the operation 3 described in the Plan. Is it possible to know that by looking at the Plan ?
Chris Saxon
July 07, 2023 - 10:30 am UTC

If you want to understand plans better, this article may help:

https://blogs.oracle.com/connect/post/how-to-read-an-execution-plan

In the slow plan, operation 3 is a hash join of the two operations below it. Which are the hash join at operation 4 and the index scan of MVW_CARTEIRAATIVOHIST_IDX_GERAL at line 14.

Hash join at 4 is the result of joining all the other tables in the plan. So arguably "which part" of your SQL statement operation 3 refers to is all of it except the group by!

SQL join always combine two tables. In multi-table joins, some of the joins combine tables which are the result of other joins.

This means often you can't say line X in the SQL statement causes problem Y in the plan. The optimizer considers the relationships between all the tables to pick the plan.

The major questions I have from the plans are:

* Why does the optimizer estimate the full scan of MVW_CARTEIRAATIVOHIST on line 13 will return 42 rows when actually it gets 268,000? With a higher row estimate for this, the optimizer may prefer to do the grouping first, then full scan this table as in the fast plan.

* Why are you accessing MVW_CARTEIRAATIVOHIST twice? Once with an aggregate, once without. You may be able to rewrite the query to read it once by changing the aggregate (grouping) function to an analytic/window. I've no idea how without seeing the query though

To Ricrado

Narendra, July 06, 2023 - 8:53 pm UTC

Q2 - In fact I want to know which part of my SQL commands refers to the operation 3 described in the Plan. Is it possible to know that by looking at the Plan ?


@Ricardo, you might want to check the predicate section of the output of DBMS_XPLAN.DISPLAY_CURSOR which should show the join condition for operation 3 in bad plan and that should give you some idea about which tables are participating in the hash join.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.