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.
The problem appears after a Refresh in all MVs using this statement
Begin
DBMS_MVIEW.refresh ('A,B,C,D,MajorView,E','??????',NULL,TRUE,FALSE,1,0,0,TRUE,TRUE);
End;
Although this refresh runs with no problem and also in an acceptable tie, after it the access to the MajorView becomes extremely slow when compared with access just after its creation.
The same query Q1 tooks now about 40 seconds to run while before it run on 0,4 seconds , it's about a 100 time slower !!! It's 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 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 until the next refresh !!
KEY QUESTION of the PROBLEM : Why MajorView Q1 query become slow after a Refresh ?
Other related questions :
- Is this refresh method and command sintax right ? Is there any hierarchy of refresh ?
- Is there any problem to reference a MV on a query to build another MV ?
- 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 !
---=============================================================
Additional information requested by contributors
---=============================================================
1) Explain Plan - when facing the problem "slow query"
Query duration time : 59 sec
Plan hash value: 1025316596
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
2) Explain Plan after Re-create the MV MajorView (MajorView is MVW_CARTEIRAATIVOHIST)
Query duration time : 0,870sec
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 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Well ... I don't understand why the 2nd Explain Plain is so different from the first case , once the Materialized view does not suffer any change is his creation definition (sql code) !!
EDIT ==> Finally, for any reason, I don't know how, query became slow again then I could run Explain Plan and collect A&E rows for both cases now.
