The Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1 is something I planned to write about for quite a while. Roy and I include this topic in the New Features section in our Upgrade / Migrate / Consolidate to Oracle Database 12.2 slide deck for some time. This blog post (in German only) by my former team mate Ulrike Schwinn inspired me to post something by myself.
Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1
First of all you’ll find the feature documented in the Oracle Database 12.2 Database SQL Tuning Guide.
The advisor task (‘Statistics Advisor
‘) runs automatically in the maintenance window. And of course you can also run it on demand. It inspects how optimizer statistics are gathered. The advisor does not gather any optimizer statistics by itself unless you’ll advice it with DBMS_STATS.IMPLEMENT_ADVISOR_TASK
. First it generates a report of findings and recommendations helping you to gather statistics following best practices.
The Optimizer Statistics Advisor checks:
- How to gather statistics?
- When to gather statistics?
- How to improve stats gathering?
Rules for the Optimizer Statistics Advisor
Look up the current rule set it is based on in V$STATS_ADVISOR_RULES
:
SQL> select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1; RULE_ID NAME RULE_TYPE DESCRIPTION ------- ------------------------------------ ---------- --------------------------------------------------------------------------- 0 SYSTEM 1 UseAutoJob SYSTEM Use Auto Job for Statistics Collection 2 CompleteAutoJob SYSTEM Auto Statistics Gather Job should complete successfully 3 MaintainStatsHistory SYSTEM Maintain Statistics History 4 UseConcurrent SYSTEM Use Concurrent preference for Statistics Collection 5 UseDefaultPreference SYSTEM Use Default Preference for Stats Collection 6 TurnOnSQLPlanDirective SYSTEM SQL Plan Directives should not be disabled 7 AvoidSetProcedures OPERATION Avoid Set Statistics Procedures 8 UseDefaultParams OPERATION Use Default Parameters in Statistics Collection Procedures 9 UseGatherSchemaStats OPERATION Use gather_schema_stats procedure 10 AvoidInefficientStatsOprSeq OPERATION Avoid inefficient statistics operation sequences 11 AvoidUnnecessaryStatsCollection OBJECT Avoid unnecessary statistics collection 12 AvoidStaleStats OBJECT Avoid objects with stale or no statistics 13 GatherStatsAfterBulkDML OBJECT Do not gather statistics right before bulk DML 14 LockVolatileTable OBJECT Statistics for objects with volatile data should be locked 15 UnlockNonVolatileTable OBJECT Statistics for objects with non-volatile should not be locked 16 MaintainStatsConsistency OBJECT Statistics of dependent objects should be consistent 17 AvoidDropRecreate OBJECT Avoid drop and recreate object seqauences 18 UseIncremental OBJECT Statistics should be maintained incrementally when it is beneficial 19 NotUseIncremental OBJECT Statistics should not be maintained incrementally when it is not beneficial 20 AvoidOutOfRange OBJECT Avoid Out of Range Histogram endpoints 21 UseAutoDegree OBJECT Use Auto Degree for statistics collection 22 UseDefaultObjectPreference OBJECT Use Default Object Preference for statistics collection 23 AvoidAnalyzeTable OBJECT Avoid using analyze table commands for statistics collection 24 rows selected.
Once the Optimizer Statistics Advisor finds evidence for rules not followed it will give recommendations. You can trigger this via a Optimizer Statistics Advisor task. This task is running as a sub-task of the Automatic Statistics Gathering automatic maintenance job by default. It analyzes the statistics history stored in the data dictionary, the statistics operation log, and the current statistics footprint that exists in SYSAUX
.
The result could be recommendations – and of course the actions to implement these.
Example
This is a very simple testcase with a DBA user in a PDB. I execute some random DML on tables TEST1 and TEST2:
SQL> select inserts, updates, deletes from dba_tab_modifications where table_name in ('TEST1','TEST2'); TABLE_NAME INSERTS UPDATES DELETES ----------- ------- ------- ------- TEST1 333030 0 9999 TEST2 219075 317123 0
Quick check to see whether the stats on table TEST1 and TEST2 are STALE:
SQL> alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm'; Session altered SQL> select sysdate from dual; SYSDATE ------------------------------- 21-Aug-2017 10:42:09am SQL> select table_name, last_analyzed, stale_stats from dba_tab_statistics where table_name in ('TEST1','TEST2'); TABLE_NAME LAST_ANALYZED STA ---------- ------------------------------- --- TEST1 21-Aug-2017 10:27:32am YES TEST2 21-Aug-2017 10:28:09am YES
Let me check if the Optimizer Statistics Advisor has something to recommend:
SET LONG 1000000 SET LONGCHUNKSIZE 100000 SET SERVEROUTPUT ON SET LINE 300 SET PAGES 1000 DECLARE v_tname VARCHAR2(128) := 'TEST_TASK_MIKE'; v_ename VARCHAR2(128) := NULL; v_report CLOB := NULL; v_script CLOB := NULL; BEGIN v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname); DBMS_OUTPUT.PUT_LINE(v_report); END; /
And this is the result:
GENERAL INFORMATION ------------------------------------------------------------------------------- Task Name : TEST_TASK_MIKE Execution Name : EXEC_1 Created : 08-21-17 10:47:11 Last Modified : 08-21-17 10:47:19 ------------------------------------------------------------------------------- SUMMARY ------------------------------------------------------------------------------- For execution EXEC_1 of task TEST_TASK_MIKE, the Statistics Advisor has 2 finding(s). The findings are related to the following rules: AVOIDSTALESTATS, UNLOCKNONVOLATILETABLE. Please refer to the finding section for detailed information. ------------------------------------------------------------------------------- FINDINGS ------------------------------------------------------------------------------- Rule Name: AvoidStaleStats Rule Description: Avoid objects with stale or no statistics Finding: There are 2 object(s) with stale statistics. Schema: HUGO Objects: TEST1 TEST2 Recommendation: Regather statistics on objects with stale statistics. Example: -- Gathering statistics for tables with stale or no statistics in schema, SH: exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO') Rationale: Stale statistics or no statistics will result in bad plans. ---------------------------------------------------- Rule Name: UnlockNonVolatileTable Rule Description: Statistics for objects with non-volatile should not be locked Finding: Statistics are locked on 1 table(s) which are not volatile. Schema: SYS Objects: AQ_SRVNTFN_TABLE_1 Recommendation: Unlock the statistics on non-volatile tables, and use gather statistics operations to gather statistics for these tables. Example: -- Unlocking statistics for 'SH.SALES': dbms_stats.unlock_table_stats('SH', 'SALES'); Rationale: Statistics gathering operations will skip locked objects and may lead to stale or inaccurate statistics. -----------------------------------------------------------------------------------------------------------------------------------
Implementing the advisor’s recommendations
Instead of transforming the examples from the report you could simply implement the advisor’s recommendations:
DECLARE
v_tname VARCHAR2(128) := 'TEST_TASK_MIKE';
v_implementation_result CLOB;
BEGIN
v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/
Let’s double check by creating another advisor task:
DECLARE v_tname VARCHAR2(128) := 'TEST_TASK_MIKE_RECHECK'; v_ename VARCHAR2(128) := NULL; v_report CLOB := NULL; v_script CLOB := NULL; BEGIN v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname); DBMS_OUTPUT.PUT_LINE(v_report); END; /
The stats for HUGO’s tables TEST1 and TEST2 have been refreshed. No further action is needed here.
But interesting enough, the implementation of the advisor task did not unlock the stats on:
GENERAL INFORMATION ------------------------------------------------------------------------------- Task Name : TEST_TASK_MIKE_RECHECK Execution Name : EXEC_2 Created : 08-21-17 10:57:20 Last Modified : 08-21-17 10:57:28 ------------------------------------------------------------------------------- SUMMARY ------------------------------------------------------------------------------- For execution EXEC_2 of task TEST_TASK_MIKE_RECHECK, the Statistics Advisor has 1 finding(s). The findings are related to the following rules: USEDEFAULTPARAMS. Please refer to the finding section for detailed information. ------------------------------------------------------------------------------- FINDINGS ------------------------------------------------------------------------------- Rule Name: UseDefaultParams Rule Description: Use Default Parameters in Statistics Collection Procedures Finding: There are 1 statistics operation(s) using nondefault parameters. Operation: unlock_table_stats(ownname=>'SYS', tabname=>'AQ_SRVNTFN_TABLE_1', stattype=>'ALL'); Recommendation: Use default parameters for statistics operations. Example: -- Gathering statistics for 'SH' schema using all default parameter values: BEGIN dbms_stats.gather_schema_stats('SH'); END; -- Also the non default parameters can be overriden by setting 'PREFERENCE_OVERRIDES_PARAMETER' preference. -- Overriding non default parameters and preferences for all tables in the system and to use dbms_stats for gathering statistics: begin dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end; -- Overriding non default parameters and preferences for 'SH.SALES': begin dbms_stats.set_table_prefs('SH','SALES', 'PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end; Rationale: Using default parameter values for statistics gathering operations is more efficient. -----------------------------------------------------------------------------------------------------------------------------------
The implementation of recommendations does not seem to change attributes or stats when the statistics are locked. And there’s an “exec dbms_stats.
” missing in the operation recommended above. But this is a minor thing.
Scripting the Adivor’s recommendations
Actually I’d rather script the advisor’s recommendations instead of blindly implement them. You can do this simply by:
SQL> spool fixit.sql SQL> select DBMS_STATS.SCRIPT_ADVISOR_TASK('TEST_TASK_MIKE') from dual; SQL> spool off
Afterwards you’ll get this script which tells you all details about how to fix the advisor’s recommendations:
-- Script generated for the recommendations from execution EXEC_1 -- in the statistics advisor task TEST_TASK_MIKE -- Script version 12.2 -- [I did cut off a lot of the comments here] -- Scripts for rule UNLOCKNONVOLATILETABLE -- Rule Description: Statistics for objects with non-volatile should not be locked -- Unlock statistics for objects that are not volatile. begin dbms_stats.unlock_table_stats('SYS', 'AQ_SRVNTFN_TABLE_1'); end; / -- Scripts for rule AVOIDSTALESTATS -- Rule Description: Avoid objects with stale or no statistics -- Gather statistics for those objcts that are missing or have no statistics. -- Scripts for rule MAINTAINSTATSCONSISTENCY -- Rule Description: Statistics of dependent objects should be consistent -- Gather statistics for those objcts that are missing or have no statistics. declare obj_filter_list dbms_stats.ObjectTab; obj_filter dbms_stats.ObjectElem; obj_cnt number := 0; begin obj_filter_list := dbms_stats.ObjectTab(); obj_filter.ownname := 'HUGO'; obj_filter.objtype := 'TABLE'; obj_filter.objname := 'TEST1'; obj_filter_list.extend(); obj_cnt := obj_cnt + 1; obj_filter_list(obj_cnt) := obj_filter; obj_filter.ownname := 'HUGO'; obj_filter.objtype := 'TABLE'; obj_filter.objname := 'TEST2'; obj_filter_list.extend(); obj_cnt := obj_cnt + 1; obj_filter_list(obj_cnt) := obj_filter; dbms_stats.gather_database_stats( obj_filter_list=>obj_filter_list); end; /
Advisor Runtime
You can easily check how long the advisor took to complete:
column ADVISOR_NAME format a28 select advisor_name, round(24 * (execution_end - execution_start),0) IN_HOURS, round(24 * 60 * (execution_end - execution_start),0) IN_MINS, round(24 * 60 * 60 * (execution_end - execution_start),0) IN_SECS from dba_advisor_tasks where task_name='AUTO_STATS_ADVISOR_TASK'; ADVISOR_NAME IN_HOURS IN_MINS IN_SECS ---------------------------- ---------- ---------- ---------- Statistics Advisor 0 0 5
Can you disable it?
Well, not really – but there is a nice workaround:
BEGIN dbms_advisor.set_task_parameter ('AUTO_STATS_ADVISOR_TASK','_AUTO_MMON_INTERVAL',2147483647); dbms_advisor.set_task_parameter ('AUTO_STATS_ADVISOR_TASK','_AUTO_STATS_INTERVAL',2147483647); END; /
This should help as a temporary workaround until the bug mentioned in MOS Note:2305512.1 gets some attention.
Update
It looks now as if this fix has been released for all releases. Once you applied the fix for Unpublished Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK you can simply disable it with:
exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
Please see explicitly my newer blog post in case you wonder about this error still happening in Oracle Database 19c:
Summary
In summary for me the feature is very helpful in practical environments. Instead of trying to find out about any suboptimal stats anywhere the advisor will reveal them and give you recommedations. This will include incremental statistcs collection as well. In modern DBA environments where less DBAs take care on more and more databases you’ll have to automate such hings. And the Oracle Statistics Advisor is a tiny little helper allowing you to save a lot of time.
Final question: Is this extra license functionality?
As far as I see: No. As the DBMS_STATS
package does not belong to any packs – and all functions are part of DBMS_STATS the Optimizer Statistics Advisor seems to be included in the regular Oracle database license – with no edition limitations.
Knwon Issues
Just learned the other day from Sergio (THANKS!) that customers may have trouble with the job tied into the Automatic Job Maintenance – and getting executed millions of times eating a lot of space in SYSAUS. Please check:
–Mike
Good information. Is this advisor also applicable for E-Business Suite databases – i.e. traditionally, Oracle Support not using DBMS_STATS to gather stats for application schemas but instead suggest using FND_STATS. Would the inputs via this advisor supercede that advisory for EBS databases too? Thanks.
Rakesh,
I doubt it – but let me double check with the EBS folks and the Optimizer PM.
Cheers and thanks for raising this topic!
Mike
Rakash,
it can be used and it is supported, but it will report so many issues that it will be of little value.
EBS is very special …
Cheers
Mike
Thanks Mike, for providing a quick clarity.
Indeed, EBS is a special product 🙂
Cheers,
Rakesh
Interesting ….Does this job runs across the Database ..If so ,How long it usually takes?
It depends … of course 😉
But you can monitor the duration of it.
select advisor_name, last_execution, execution_start, execution_end
from dba_advisor_tasks
where task_name=’AUTO_STATS_ADVISOR_TASK’;
This will give you a result such as:
ADVISOR_NAME
——————————————————————————
LAST_EXECUTION
——————————————————————————
EXECUTION_START EXECUTION_END
—————- —————-
Statistics Advisor
EXEC_57
22:00:43 (08/24) 22:00:48 (08/24)
In my case it took 5 seconds.
CHeers
Mike
Hi Mike
I’ve just had the problem described in Doc ID 2305512.1 on almost all of our 12.2 databases.
They are mainly small databases (<20GB) and on one of them (8GB of user data) we found milions of executions of this advisor, 3.5GB of sysaux in use and 20GB of archivelog generated during the week-end maintenance window (who did cause an archiver-error driving us to find this problem). The database was a new one created last june, so the problem happened in less than 2 months
I had to remove the advisor and I think I'll disable it on future 12.2 installations by default.
I think the manual approach is better then the risk of blocking che customers..
Bye
Sergio
Sergio,
you found millions of executions?
Now I’m scared. Let me check – and thanks for the hint.
Kind regards,
Mike
me too 😉
Hello Mike,
I’ve got the same problem on several of my databases (running 12.2 and 18c)
Unfortunately the sysaux has been set to autoextend ON with no maxsize – so the problem is even bigger. The table WRI$_ADV_OBJECTS contains now 90 mio rows.
Some months ago we already applied the workaround provided in Doc ID 2305512.1. It helped for a while and now it happened again.
Doc ID 2305512.1 points to unpublished Bug 26749785 (which i cannot retrieve of course).
Will there be a patch in beneficial time?
Maybe we can discuss this at the DOAG conference in November.
best regards
Dieter
Hi Dieter,
I fear there’s no patch to be expected anytime soon.
The only task I can recommend to you: Open an SR, escalate it, have Support escalate the bug.
I think you need to follow the advice of Oracle Support Document 2305512.1 (SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2305512.1
again – and you may check back with Support why the issue happened again even though the job was disabled.
Cheers,
Mike
Hi Mike,
Oracle sugests a workaround in Support Document 2439129.1:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2439129.1
At least I’m hoping this works till a patch is provided. We’ll test it in the next days.
best regards,
Olaf
Hi,
I would share my experiences, and ask question.
I have dropped AUTO_STATS_ADVISOR_TASK to avoid SYSAUX growing.
After this operation I have errors in alert log (and this is main problem now):
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_11636″
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at “SYS.DBMS_STATS”, line 47214
ORA-06512: at “SYS.DBMS_STATS_ADVISOR”, line 882
ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 20059
ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 22201
ORA-06512: at “SYS.DBMS_STATS”, line 47204
I plan to recreate task with standard command:
EXEC dbms_stats.init_package();
However I would like to set it’s status to DISABLE.
Appreciated if you can share how to DISABLE it and check STATUS.
Regards,
Bartek
Hi Bartek,
please check with Oracle Support as I’m not aware of another workaround as described in:
MOS Note:2305512.1 (SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2305512.1
To me, the note lacks an important piece of information:
I don’t understand how somebody explains how to disable it, then receives and error (as you do), and then needs to recreate it.
Please check with Support on how to solve this.
Cheers,
Mike
Hi Mike, All,
I have raised SR for above described problems and have solution.
Problem with growing SYSAUX.
Short story:
I have dropped AUTO_STATS_ADVISOR_TASK to avoid SYSAUX is growing with no limits.
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2305512.1
After this operation I had errors in alert log (described in previous entry).
Solution:
Task can be recreated by following command (if not exist) (execute as SYSDBA):
EXEC dbms_stats.init_package();
Check if task exist:
SELECT name, ctime, owner from sys.wri$_adv_tasks where name in (‘AUTO_STATS_ADVISOR_TASK’,’INDIVIDUAL_STATS_ADVISOR_TASK’);
In my case both tasks were missing but only AUTO caused errors – anyway both were recreated.
Next there are commands to unlimit or reduce expiration for advisor result as per following command in order to avoid growing SYSAUX (execute as SYSDBA):
EXEC dbms_stats.set_advisor_task_parameter(‘AUTO_STATS_ADVISOR_TASK’,
‘DAYS_TO_EXPIRE’, ‘UNLIMITED’);
EXEC dbms_stats.set_advisor_task_parameter(‘AUTO_STATS_ADVISOR_TASK’,
‘EXECUTION_DAYS_TO_EXPIRE’, 30);
EXEC
dbms_stats.set_advisor_task_parameter(‘INDIVIDUAL_STATS_ADVISOR_TASK’,’DAYS_TO
_EXPIRE’, ‘UNLIMITED’);
EXEC
dbms_stats.set_advisor_task_parameter(‘INDIVIDUAL_STATS_ADVISOR_TASK’,’EXECUTI
ON_DAYS_TO_EXPIRE’, 30);
Cheers,
Bartek
Thanks for the update, Bartek!
Cheers,
Mike
Hi Mike,
I see that a one-off Patch has been released by Oracle Support for Bug # 26749785 for 12.2 through 19.10:
Patch 26749785: PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK
BUT: I downloaded this Patch and there’s no instructions provided in the README for this Patch as to HOW to DISABLE the AUTO_STATS_ADVISOR_TASK after the patch is applied! Can you please advise?! What is the syntax for disabling this task after applying this patch??
Also, for your workaround provided above (without applying this new Patch), how can we confirm that the workaround is in-effect?
When I issue the following select statement, it doesn’t show those 2 hidden parameters with underscores:
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = ‘AUTO_STATS_ADVISOR_TASK’
ORDER BY parameter_name;
So how can we tell what values these two parameters are set to?!
_AUTO_MMON_INTERVAL and _AUTO_STATS_INTERVAL
Thanks!
-Ernst (Ernie)
Hi Ernst,
this MOS Note described how to disable it without the calls I mention:
Oracle Support Document 2686022.1 (How To Disable Optimizer Statistics Advisor From 12.2 Onwards) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2686022.1
exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’,’FALSE’);
once you applied the patch. I will add the link to the note to the blog post.
Cheers,
Mike
Thanks again, Mike!
HOWEVER! We’re not out of the woods yet with this issue and Patch!!
I downloaded the latest p26749785_1910000DBRU_Generic.zip file to try applying it to my 19.10.0 Home on my Windows Test server (patched with all the latest CPUJAN2021 Windows 64-bit Oracle RDBMS patches), and I get the “conflict” below. Bottom line is: as of today (12-Mar-2021), this patch is only available for 19c for non-Windows platforms! The only Windows version of this patch currently available from MOS is for 12.2.0.1.200714 (12.2 patched to CPUJUL2020). SHEESH!!! So I guess we have to wait for 19c Windows 64-bit versions of this patch to be released from MOS?
Please confirm and advise?!
The README.txt for this patch says:
Ensure that 19 Release 19.10.0.0.210119DBRU Patch Set Update (PSU) 32218454 is already applied on the Oracle Database.
… for which there is NO 32218454 PSU in existence anywhere for this release on Windows platforms, only on Linux/Unix/non-Windows platforms!
Danke!
-Ernst.
G:\ORACLE\_PATCHES\19c\p26749785_1910000DBRU_Generic\26749785>opatch prereq CheckConflictAgainstOHWithDetail -ph .
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : G:\Oracle\Product\19.3.0\ee2
Central Inventory : C:\Program Files\Oracle\Inventory
from :
OPatch version : 12.2.0.1.24
OUI version : 12.2.0.7.0
Log file location : G:\Oracle\Product\19.3.0\ee2\cfgtoollogs\opatch\opatch2021-03-12_09-31-42AM_1.log
Invoking prereq “checkconflictagainstohwithdetail”
Prereq “checkConflictAgainstOHWithDetail” failed.
The details are:
Interim patch 26749785 requires prerequisite patch(es) [32218454] which are not present in the Oracle Home.
Apply prerequisite patch(es) [32218454] before applying interim patch 26749785.
Summary of Conflict Analysis:
There are no patches that can be applied now.
Conflicts/Supersets for each patch are:
Patch : 26749785
Conflict with 32062765
Conflict details:
G:\Oracle\Product\19.3.0\ee2\rdbms\admin\prvtstat.plb
OPatch succeeded.
G:\ORACLE\_PATCHES\19c\p26749785_1910000DBRU_Generic\26749785>
Hi Ernst,
as far as I know, there are no one-off patches on Windows. So a patch needs to be included into an RU.
You please need to check with Support whether a fix gets included into a coming RU – or request it.
In this particular case, I’d rather tend to turn the advisor simply off if you see issues with it.
Cheers,
Mike
Hi again Mike,
Thanks for the reply, I’m not sure if you got my subsequent reply, so asking again in-brief here:
1. Patch # 26749785 is not yet available for 19c on Windows x64 platforms. That’s the platform I need it the most. I’ve submitted an SR with MOS asking when it will be available. Hopefully they will instruct me how to request a backport of that Patch for 19.10 on Windows 64-bit servers. I need to be able to apply it to Oracle Homes patched with all CPUJAN2021 patches.
2. For your Workaround above, how do we:
a) find out the current values of those two hidden dba_advisor_parameters? (_AUTO_MMON_INTERVAL and _AUTO_STATS_INTERVAL), and b) how do we UNSET those parameters so the values are back to their default values?!
Vielen danke!
-Ernst
Hi Ernst,
the blog is just my hobby 🙂 Hence, please don’t expect replies as quickly as I’d like to write them.
Regarding the values, please check with an SR.
My guess is that this query should help you:
column parameter_value format a45
column parameter_name format a25
set pages 200
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME = ‘< use stats advisor job name here' AND PARAMETER_VALUE <> ‘UNUSED’
ORDER BY 1;
Cheers,
Mike
Based on the definition of the VIEW try this:
select –b.owner_name as owner,
— a.task_id as task_id,
b.name as task_name,
a.name as parameter_name,
a.value as parameter_value,
decode(a.datatype, 1, ‘NUMBER’,
2, ‘STRING’,
3, ‘STRINGLIST’,
4, ‘TABLE’,
5, ‘TABLELIST’,
‘UNKNOWN’)
as parameter_type,
decode(bitand(a.flags,2), 0, ‘Y’, ‘N’) as is_default,
— decode(bitand(a.flags,4), 0, ‘N’, ‘Y’) as is_output,
— decode(bitand(a.flags,8), 0, ‘N’, ‘Y’) as is_modifiable_anytime,
— dbms_advisor.format_message(a.description) as description,
c.exec_type execution_type
from wri$_adv_parameters a, wri$_adv_tasks b, wri$_adv_def_parameters c
where a.task_id = b.id
and a.name = c.name
and (b.advisor_id = c.advisor_id or c.advisor_id = 0)
and bitand(b.property,4) = 4 /* task property */
— and bitand(a.flags,1) = 0 /* invisible parameter */
— and (bitand(b.property, 32) = 32 or /* system task only parameter */
— bitand(c.flags, 16) = 0)
and b.name in (‘AUTO_STATS_ADVISOR_TASK’)
and a.name in (‘_AUTO_MMON_INTERVAL’, ‘_AUTO_STATS_INTERVAL’, ‘DAYS_TO_EXPIRE’,’EXECUTION_DAYS_TO_EXPIRE’)
Hi again Mike,
Further to my previous two questions… That “workaround” of yours CAUSES PROBLEMS! After setting those two hidden parameters (_AUTO_MMON_INTERVAL and _AUTO_STATS_INTERVAL) on two PDBs, now every 4 hours, we get these ORA- errors in the alert-log, with accompanying trace files! See error messages reported below. So we need to know how to back-out of that “Workaround” of yours please?!
Danke,
-Ernst
2021-03-13T12:01:57.867870-05:00
TESTDB(3):Errors in file E:\ORACLE\diag\rdbms\cdbtst19\cdbtst19\trace\cdbtst19_j000_4548.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_5275″
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at “SYS.DBMS_STATS”, line 51871
ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 24430
ORA-06512: at “SYS.DBMS_STATS”, line 51859
2021-03-13T12:02:00.461648-05:00
DEVDB(4):Errors in file E:\ORACLE\diag\rdbms\cdbtst19\cdbtst19\trace\cdbtst19_j002_3752.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_5275″
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at “SYS.DBMS_STATS”, line 51871
ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 24430
ORA-06512: at “SYS.DBMS_STATS”, line 51859
trc file contents:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_5275″
at 0x0000008BDE3F8930 placed jslv.c@1746
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at “SYS.DBMS_STATS”, line 51871
ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 24430
ORA-06512: at “SYS.DBMS_STATS”, line 51859
Hi Ernst,
if the workaround does not work in your environment, please try a lower value (half of it for instance) as it is set to a fictional maximum.
Otherwise, please use an SR to get advice.
Cheers,
Mike
Hey Mike,
Further to my previous two comments submitted to this blog which you have yet to publish…
I discovered the following on my clients’ Windows 64bit servers running Oracle 19c:
1. The PDBs patched to CPUJUL2020 and prior all experience the massive SYSAUX growth. PLUS: In some trace files generated/updated daily: we get “orasql19.SYM does not match binary”, but NO ORA- error reported in the alert log.
2. The PDBs patched to CPUJAN2021 no longer have massive SYSAUX tablespace growth, and no longer have any trace files with “SYM does not match binary” reported. That message is reported along with dbms_scheduler messages saying auto-stats tasks were running and experiencing this error/warning. See example below.
3. My customers with Linux servers don’t have any trace files with “SYM does not match binary”, and don’t have massive SYSAUX growth, however, they are all now patched to CPUJAN2021, and old trace-files are deleted past 30 days, so I can’t confirm if they had that message in previous trace files before having the January 2021 patches applied.
4. Interestingly, the date-time stamp and size of that orasql19.SYM file on all my Windows servers is identical — on the ones patched to CPUJAN2021 and those patched to CPUJUL2020. But as the trace files report, it’s likely an internal timestamp on the objects inside that .SYM file that are mis-matched with the oracle.exe executable file.
So I’m quite sure the unexpected SYSAUX growth is related or resulting from there being an unpublished BUG where Oracle Development caused a mis-match between the oracle.exe and that orasql19.SYM file. And somehow, it looks like that BUG might have been “quietly” fixed as of the January 2021 CPUs. I could submit another SR on this with MOS, but the fact it looks like it was “quietly” fixed as of CPUJAN2021, it might not be worth my time. I already had a long-running SR about the SYSAUX growth and the auto-stats package which we had to close as unresolved.
I’d be interested to see if your other blog-subscribers have the same “SYM does not match binary” messages reported in their trace files, along with massive unexpected SYSAUX tablespace growth? Please advise?!
Danke,
-Ernst.
trace file contents excerpts here:
*** SESSION ID:(1358.29667) 2021-03-13T20:07:03.658971-05:00
*** CLIENT ID:() 2021-03-13T20:07:03.658971-05:00
*** SERVICE NAME:(SYS$USERS) 2021-03-13T20:07:03.658971-05:00
*** MODULE NAME:(DBMS_SCHEDULER) 2021-03-13T20:07:03.658971-05:00
*** ACTION NAME:(ORA$AT_OS_OPT_SY_128) 2021-03-13T20:07:03.658971-05:00
*** CONTAINER ID:(4) 2021-03-13T20:07:03.658971-05:00
*** 2021-03-13T20:07:03.658971-05:00 (PRODDB(4))
——————————————————————————-
HUNG PROCESS DIAGNOSTIC DUMP BEGIN:
dump requested by process (pid: 14, osid: 3724, DIA0)
trace file: E:\ORACLE\diag\rdbms\cdbpd19c\cdbpd19c\trace\cdbpd19c_dia0_3724.trc
——————————————————————————-
—– Current SQL Statement for this session (sql_id=b6usrg82hwsa3) —–
call dbms_stats.gather_database_stats_job_proc ( )
—– PL/SQL Stack —–
—– PL/SQL Call Stack —–
object line object
handle number name
00007FFBB5B3E208 1800 package body SYS.DBMS_STATS.SET_TABLE_STATS_CALLOUT
00007FFBB5B3E208 17545 package body SYS.DBMS_STATS.SET_TABLE_STATS_AUX
00007FFBB5B3E208 38836 package body SYS.DBMS_STATS.CONSTRUCT_ANALYZE_USING_SQL
00007FFBB5B3E208 39738 package body SYS.DBMS_STATS.GATHER_TABLE_STATS_AUX
00007FFBB5B3E208 41129 package body SYS.DBMS_STATS.GATHER_GROUP_STATS
00007FFBB5B3E208 51678 package body SYS.DBMS_STATS.GATHER_DB_STATS_JOB_PROC_AUX
00007FFBB5B3E208 51782 package body SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
*** 2021-03-13T20:07:03.690174-05:00 (PRODDB(4))
———– messages from pre-loading .sym files:
Symbol file E:\ORACLE\Product\19.3.0\ee1\RDBMS\ADMIN\orasql19.SYM does not match binary.
Symbol TimeStamp=5cef5a1d, Module TimeStamp=5f1bb465 are different
00007FFB1CDF6008 724 package body SYS.DBMS_HEAT_MAP_INTERNAL.HEAT_MAP_TOPN_TABLESPACES
00007FFB1CDF6008 1221 package body SYS.DBMS_HEAT_MAP_INTERNAL.AUTO_ADVISOR_HEATMAP_JOB
00007FFC0AD454F0 228 package body SYS.DBMS_HEAT_MAP.AUTO_ADVISOR_HEATMAP_JOB
00007FFB1221AC58 2791 package body SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC
*** 2021-03-13T20:07:03.690174-05:00 (PRODDB(4))
———– messages from pre-loading .sym files:
Symbol file E:\ORACLE\Product\19.3.0\ee1\RDBMS\ADMIN\orasql19.SYM does not match binary.
Symbol TimeStamp=5cef5a1d, Module TimeStamp=5f1bb465 are different
Hey Mike,
Did you get my previous several comments for review and posting?!
Thanks!
Ernst
Hi Ernst,
please be patient or open SRs. The blog is my private hobby, I try to reply as quickly as possible if my time allows.
Cheers,
Mike
Hi Mike,
I’m looking forward to replies to my previous comments posted to this blog. Sorry to bother you with several updates, but it looks like I’ve pinpointed the SYSAUX excessive growth is resulting from those “orasql19.SYM does not match binary” messages in the trace file, without any ORA- error messages reported in the alert-log.
Anyway, on a freshly patched 19.10.x RDBMS Home on Windows 64bit servers, when I try the set_global_prefs command as you’ve indicated above, it doesn’t like the “pname” provided. See output below. So I believe that Patch # 26749785 still needs to be applied for this command to work to disable the auto-stats advisor task. Right?!
Thanks!
-Ernst (Ernie)
SQL> exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’,’FALSE’);
BEGIN dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’,’FALSE’); END;
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at “SYS.DBMS_STATS”, line 52546
ORA-06512: at “SYS.DBMS_STATS”, line 52794
ORA-06512: at line 1
Hi Ernst,
you please may need to check this with Oracle Support.
Cheers,
Mike
Thanks for the replies to my many comment-posts, Mike. Sorry to bother you with this.
Your query didn’t work. See below updated/corrected query:
set echo on feed on term on pagesize 9999 linesize 200
@dbname.sql
spool dba_advisor_parameters_&DBNAME..sql.log.txt
column Task_name format a40
column parameter_name format a40
column parameter_value format a50
—
SELECT TASK_NAME, PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME != ‘UNUSED’
ORDER BY 1, 2;
spool off
After setting those two hidden advisor parameters (_AUTO_MMON…etc), they do NOT show up in the listing of dba_advisor_parameters.
I am indeed working on a few SR’s with MOS on this, but the responses by Oracle Development are PATHETIC! There is STILL NO Windows x64 version of Patch # 26749785 made available for 19c yet. Also, the “SYM does not match binary” issue is NOT quite resolved as of the CPUJAN2021 patch for 19c on Windows yet either! SHEESH!!!
No cheers yet,
-Ernst
Hi Ernst,
I fear you’ll have to escalate and push this forward with Support.
Our team does not own these procedures – and on Win, there are no one-off patches.
Thanks,
Mike
Done! Thanks Mike. Sorry to trouble you with all my updates. All the best. Ein Prosit!