Oracle Database Product Life Cycle Support 19c,18c and now 12cR2 is extended !

Oracle has just changed their product life cycle support for their database technology especially Oracle 12cR2 !

Per Oracle ….As a one-time exception, Oracle will provide Limited Error Correction at no additional charge, for a period of 16 months after Premier Support Error Correction ends. Limited Error Correction will be in place from December 1, 2020 until March 31, 2022

The strategy is to give Oracle customers the time to upgrade to 19c (which is the stable release).

So far,  18c premier support ends June 2021 …..and 19c premier support ends March 2023

In my opinion this was too late from Oracle since many organizations were shooting for 18c for sometime in their landscape upgrades …..so this means basically any footprint of 18c in your landscape will require another upgrade to 19c !

its clear 12cR2 is a stable release compared to 18c, and even many other Oracle software solutions were late to be compatible with 18c !

 

Oracle_Product_Lifecycle_Support

 

 

 

 

 

 

Oracle SYSAUX size is exponentially expanding after upgrade to Oracle 12cR2

After upgrading Oracle database to 12cR2 SYSAUX table-space was noticed to be exponentially increasing space and table SYS. WRI$_ADV_OBJECTS was largest !

SYSAUX_TBS

Why is that ?

In Oracle 12.2 a new feature “Optimizer Statistics Advisor” [AUTO_STATS_ADVISOR_TASK] runs huge number of times causing SYSAUX tablespace to grow

SOLUTION:

Using the following query I will calculate the number of rows in table SYS. WRI$_ADV_OBJECTS:

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=’AUTO_STATS_ADVISOR_TASK’);

  COUNT(*)

———-

  28251381

Migrate the rows from the current table to a new table

SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=’AUTO_STATS_ADVISOR_TASK’);

Table created.

Then truncate the original table:

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

20703 rows created.

SQL> commit;

Commit complete.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

Index altered.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

Index altered.

SQL> drop table WRI$_ADV_OBJECTS_NEW purge;

Table dropped.

Drop the statistics advisor task from dictionary to refrain from executing:

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

SQL> DECLARE

filter1 CLOB;

BEGIN

filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK’,NULL,NULL,’DISABLE’);

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.