It’s not easy for me to put the topic into a searchable title for this blog post. The feature or change I will write about today came in actually with Oracle Database 184.108.40.206. But since many of you will move directly from Oracle 220.127.116.11 or 18.104.22.168 to 19c, the Long Term Support release, it may affect you as well. So let me explain a change in Oracle 12.2 and 19c with MV Refresh Stats Collection.
A customer story
The local Support team asked me a few weeks ago whether I have heard about an issue their banking customer had with the upgrade to 19c. Performance went bad despite the fact that they tested. They haven’t used Real Application Testing. And even though Support found a workaround and solution, it turned out that the solution was not permanent.
This all triggered my sense for “let others know” in case you may see a similar issue. After I knew what to look for, I could find blog posts and MOS notes being available already. I will quote or link them below as well.
The customer in this case upgraded from Oracle Database 22.214.171.124 to 19.10.0 on Exadata. Everything went fine upgrade-wise. But after the upgrade there were performance issues seen.
Certainly, the customer opened an SR – and it took a while until the issue became clear.
This was the early diagnosis from Oracle Support:
Customer is observing slowness, mostly due to enq-TX and ITL contention after upgrade to Oracle 19c. It seems to be application related …
Well, I guess this is not what you want to hear in such a situation. But I can follow this first conclusion based on AWR and hanganalyze reports.
MV Refresh Stats Collection
With Oracle Database 126.96.36.199 – a release the customer has not touched – a new package got introduced: DBMS_MVIEW_STATS.
It is used to be able to monitor refresh performance of Materialized Views. You can find a lot more information about how to use the package and its purpose in the Oracle Database 21c Data Warehouse Guide – Monitoring MV Refresh Operations. And as you see below, there were some issues with this package in the past as well (see Further Links and Information).
What the customer didn’t see coming is the fact that a database collects and stores statistics about materialized view refresh operations. There are two core function to define the level of statistics taken, and their retention parameters:
- Database-wide: DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT
- For individual MViews: DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS
The setting causing a lot of noise (and a performance degradation) in this case is set by default with SET_SYSTEM_DEFAULT where you define either the COLLECTION_LEVEL or the RETENTION_PERIOD with. The COLLECTION_LEVEL defaults to TYPICAL. According to the documentation, TYPICAL means “Only basic refresh statistics are collected for the refresh operation. This is the default setting.“.
But now you are coming from 11g or 188.8.131.52 – and I can only guess that this stats collection did not happen in those releases. And there seem to be an issue in some cases which you can read in MOS Note: 2764909.1 – Materialized View Refresh Statistics Causing High CPU and Logical Reads.
The recommendation is clear:
In order to avoid issues, you should disable the collection.
exec DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT('COLLECTION_LEVEL', 'NONE');
And our customer did exactly this following Support’s recommendation.
In addition, the customer – following again Oracle Support’s advice, did also wipe out the existing stats:
truncate table mvref$_stats; truncate table mvref$_run_stats; truncate table mvref$_change_stats; truncate table mvref$_stmt_stats;
At first, all did look fine now.
But only for a little while.
Ups, it happened again …
Well, the customer then took down the database since going to 19.10.0 was not an ideal choice. They patched to January 2022 Release Update, 19.14.0. And then the issue came back.
Of course, this is not what you want, and not what you’d expect.
Support analyzed again, and somebody found this curious bug:
BUG 29451430 – DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT DOES NOT REFLECT IMMEDIATELY
Why is this a curious bug? The above procedure, recommended by Oracle Support and issued by the customer, did miss the necessary COMMIT; at its end. You easily can guess what that meant. See MOS Note: 2515040.1 – DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT dose not reflect immediately for further information.
So once the customer executed the call again followed by a COMMIT; all worked fine.
When I knew what to search for, I found several external posts, e.g. this one here: https://taliphakanozturken.wordpress.com/2022/04/23/materialized-view-refresh-is-very-slow-in-oracle-database-12-2/ explaining the topic as well. Unfortunately, from the SR in this case, things weren’t as obvious as you’d hope. So it took a while to find the root cause of the performance issue.
In brief, the issue is related to the stats collection in order to be able to monitor and diagnose Materialized View refresh performance. If you don’t have MViews, you may not care at all. If you use Materialized Views and experienced slowness in Oracle Database 19c, especially after upgrading to Oracle Database 19c, this may be a topic you should have look into.
And when you turn the feature off, make sure to send an additional COMMIT;.
Further Links and Information
- Bug 31377206 – ORA-1403 While Executing DBMS_MVIEW_STATS.PURGE_REFRESH_STATS
- MOS Note: 2764909.1 – Materialized View Refresh Statistics Causing High CPU and Logical Reads
- DBMS_MVIEW_STATS in Oracle Database 184.108.40.206
- Oracle Database 21c Data Warehouse Guide – Monitoring MV Refresh Operations
- BUG 29451430 – DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT DOES NOT REFLECT IMMEDIATE
- MOS Note: 2515040.1 – DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT dose not reflect immediately