Christian asked this question today. Specifically, in his case for Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK. Several of his customers suffer from this issue, and they repeatedly need to ask for backports or merge patches. I have seen this several times as well. Anyway, he is not only one who asked: Why is the fix for bug nnn not included in any RU?
Photo by Priscilla Du Preez 🇨🇦 on Unsplash
Inclusion criteria
In order to be able to include a bug fix into an MRP, CSPU or RU, it has to be RAC Rolling and Standby First applicable. That’s a given rule to NOT break especially the RAC Rolling capabilities. And currently there are some really important projects to make more and more fixes RAC Rolling applicable. Still, right now, this is a clear exclusion criteria. The most prominent example is the Data Pump Bundle Patch. While it can be applied online (don’t mix it with HOT patching) when the instance is up and running, the datapatch part may break the rolling ability.
Why isn’t it included?
In this specific case, Christian asked me about Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK. This is a fix many customer need to prevent SYSAUX from unexpected growth. It adds a new preference to enable/disable AUTO_STATS_ADVISOR_TASK manually.
- TRUE (default) : statistics advisor will run after auto stats collection
- FALSE : auto stats advisor task will be disabled.
You can set this preference to TRUE/FALSE using DBMS_STATS.SET_GLOBAL_PREFS and check the current value set for the preference using DBMS_STATS.GET_PREFS call.
To check the current value for the preference:
select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
TRUE
But in 19c without the fix you will receive an ORA-20001 when executing the above.
You can always check the consumption in SYSAUX with:
SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000 COLUMN OCCUPANT_NAME FORMAT A30 SELECT occupant_name, space_usage_kbytes FROM v$sysaux_occupants ORDER BY space_usage_kbytes DESC;
But back to this important fix, and why it isn’t included.
When you check the README it tells you:
Oracle Database 19 Release 19.32.0.0.260721DBRU
OPTIMIZER Patch for Bug# 26749785 for Generic PlatformsThis patch is NOT RAC Rolling Installable.
This patch is Data Guard Standby-First Installable.
This is it. It can’t be included in an RU.
Without checking further, I am almost certain that it gets build and uploaded proactively. The 19.32 generic fix has an upload date of July 24. This means, you could download it almost instantly with the RU being released. But I understand that some of you need merges, and those need to be built.
On the side, this issue is fixed in Oracle Database 21c, and certainly in Oracle AI Database 26ai as well out of the box since the base release.
Further Links and Information
- (KB132476) How To Disable Optimizer Statistics Advisor From 12.2 Onwards
- (KB125032) Enabling/Disabling AUTO_STATS_ADVISOR_TASK Throwing ORA-20001: Invalid input values for pname
- (KB789367) ZDLRA: SM/ADVISOR using more space in SYSAUX
- Patch 26749785 – README
–Mike
I downloaded and applied this many RU ago, and used it to set the value to False.
As far as I know, that ‘sticks’ and the DB is moved from home to home [ we use out-of-line patching and setup a new ‘home’ for each RU.]
I “slipstream” that fix when applying the RU, this patch and the DPBU into each new home [ after replacing OPatch first ].
I povably don’t need to download and slipstream this into each new home, but just in case I want to revert to “True”, I’m use to doing it now.
Hi Mike,
Thanx for the clear explanation but there is something that triggers me and that is the last remark that the fix is available in 26ai.
We serve a lot of customers and we certainly want them to upgrade to 26ai but the obvious is holding them back and that is the hold up or lack of availability of the on-premise versions, especially standard edition and the Windows versions. SE2 on Linux is planned for october but for Windows 26ai (regardless of edition) it is now CY2027 (which I believe was CY2026 until recently). We understand that there is a Cloud first policy at Oracle but why do these on premise releases take so ridiculously long?
Is this something you can shed some light on or blog about? I am not asking for specific dates (although that would be nice) but we are trying to understand why this is the way it is and what causes this hold up.
Many of our customers do not want to move to the Cloud yet or not at all, why are they being so left out?
Really appreciate if you could clear this up.
Hi Peter,
I see your concern. SE2 is due to in October – but Win got pushed to CY27.
That is unfortunately the reality.
For general “fixed in 26ai”:
This has to do with HOW we fix things. Fixes always go into the MAIN code line at first.
This means, whatever the MAIN codeline is, it will see the fix – and it needs to be backported then “on top”.
In the above case, the fix in 26ai means: When the issue came up, 26ai was not released but MAIN. The fix got made there.
So, it isn’t a statement saying: You must flip to 26ai but rather a regular standard development process.
And I understand that this doesn’t change the situation for customers on Win and other platforms unfortunately.
Cheers,
Mike