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 12.2.0.1. But since many of you will move directly from Oracle 11.2.0.4 or 12.1.0.2 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.
Photo by Ralph Mayhew on Unsplash
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 12.1.0.2 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 12.2.0.1 – 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 12.1.0.2 – 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.
Summary
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 12.2.0.1
- 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
–Mike
Hello
more than 4 years ago we had an issue on 12c with some SQL consuming a lot of Cpu triggered by sys.dbms_irefstats.run_sa package. It was the segment advisor triggering at each MV refresh and queries were consuming overall a lot of blocks. The refresh was as well much slower in 12c when compared to 11g.
After investigation Oracle Support came back with an hidder parameter “_mv_refresh_shrink_log” to be set to FALSE. By default in 12c then MV logs were shrinked at each refresh. When this was disabled the segment advisor queries stopped triggering and refresh duration came back to normal. I did not check if this is still valid in 19c but this additional step was not documented at this time.
Stéphane
Hi Stephane,
I can’t tell you but I have not heard anybody complaining about the log size.
But I agree a lot about the advisor …
Cheers, and thanks for the hint!
Mike
Why was commit needed?
See the MOS note I was quoting – it is missing in the procedure.
Mike
I would ask the same thing, since it seemed to be followed by trucate ddl which would cause a commit anyhow. I assume they must have happened in separate sessions.
Bad programmer… tisk tisk. They must have come over from another database with auto-commit – which is something I unfortunately see regularly from mixed db developers. I never understood why you’d want to commit automatically, seems like lazy coding that can get you into trouble easily.
Oh I can’t comment … 🙂 🙂
Cheers,
Mike