Oracle Database 18c, Standard Edition 2 & ORA-38153

When creating a new Oracle 18c database (Standard Edition 2) with dbca (custom database), I noticed lots of messages in the alert.log file:

Errors in file /u00/app/oracle/diag/rdbms/sn18/SN18/trace/SN18_j002_20332.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_36"
ORA-38153: Software edition is incompatible with SQL plan management.
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 4911
ORA-06512: at "SYS.DBMS_SPM", line 2696
ORA-06512: at line 34

The message appears every 10 minutes.

There is no information available on My Oracle Support on this issue, but the error message of the ORA-38153 is pretty clear: SQL Plan Management is not supported with the Standard Edition 2 of the Oracle database.

oracle@kereru:~/ [SN18] oerr ora 38153
38153, 00000, "Software edition is incompatible with SQL plan management."
// *Cause: SQL plan management could be used only with Oracle Database Enterprise Edition.
// *Action: Ensure that Oracle is linked with the Enterprise Edition options.

However, this general statement is not 100% true; some basic SQL Plan Management is licensed with SE2. The licensing information (https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87) states concerning “SQL Plan Management” in Oracle Database 18c:

SE2 and DBCS SE Summary: Only one SQL plan baseline per SQL statement is allowed and SQL plan evolution is disabled.

SE2 and DBCS SE Details:
1. SQL plan baselines can be created or captured using the following methods:
– Auto capture (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE=TRUE)
– Manual loading from the cursor cache (DBMS_SPM.LOAD_PLANS_FROM CURSOR_CACHE)
– Migration from stored outlines (DBMS_SPM.MIGRATE_STORED_OUTLINE)
– Import using DBMS_SPM.UNPACK_STGTAB_BASELINE

2. All capture and creation methods store only one SQL plan baseline per SQL statement.
3. SQL plan baselines can be exported and imported using DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, and DBMS_SPM.UNPACK_STGTAB_BASELINE.
4. Unused SQL plan baselines are not auto-purged.
5. Alternative SQL execution plans for SQL statements are not added to the SQL plan history.
6. SQL plan baselines can be altered and dropped (DBMS_SPM.ALTER_SQL_PLAN_BASELINE and DBMS_SPM.DROP_SQL_PLAN_BASELINE).

7. The following DBMS_SPM functions and procedures are not allowed: CONFIGURE, LOAD_PLANS_FROM_AWR, LOAD_PLANS_FROM_SQLSET, and all functions and procedures associated with SQL plan evolution.

But the cause – in terms of licensing – “SQL Plan Management not supported” is correct in this specific case, because the database job is started as an “automatic SQL tuning advisor task” – which cannot be licensed in SE2

To my opinion, the message can be ignored. It is just another example, that Oracle uses
the same software for both EE and SE2 and does not have a clear separation between both
editions. Like the fact that by default dbca creates a SE2 database
with the parameter CONTROL_MANAGEMENT_PACK_ACCESS set to “DIAGNOSTIC+TUNING”, although Diagnostic Pack and Tuning Pack cannot be licensed against SE2. And although CONTROL_MANAGEMENT_PACK_ACCESS is set to NONE in my database, all the AWR and tuning stuff is running in the background. And a simple select on a “DBA_HIST%’-AWR-view could turn a SE2 database into a more expensive EE database.

 

Update 13-SEP-2018:

JacekGebal (@GebalJacek) on Twitter: “EXEC dbms_auto_task_admin.disable(‘sql tuning advisor’, NULL, NULL);” solved the issue for him.

 

Deutsche Übersetzung dieses Beitrages

---

Ad (Amazon Link):