Oracle Database Product Life Cycle Support 19c,18c, 12cR2 Long Term & Innovation Releases !

*** This post has been updated in JUNE 2020 *****

Oracle has just changed their product life cycle support for their database technology especially.

There are two types of releases now: Long Term Release , and Innovation Release

Long Term Release:  are ideal for use cases that benefit from less frequent upgrades to newer releases. This type of release offers highest stability. The long term release will have 5 years of Premier Support.

Innovation Release:  is a release between long term releases. This type of releases will provide new enhancements and capabilities. it will have short period of Premier support which is 2 years. The idea here is to provide cutting edge technologies for rapidly evolving technologies.

So Oracle 20c most probably will be an “innovation” release !

updated in JUNE 2020 the following are support dates:

Oracle 12cR2 support will end 31st March 2022

Oracle 18c (is considered innovation release now !!): 8th June 2021

Oracle 19c: 30th April 2024

Oracle_Product_LifeCycle_Support_JUNE2020

 

 

 

 

 

 

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.