ORA-00600: Error in query Oracle 12c using WITH

ORA-00600Hello, today we want to share an error ORA-00600 that has arisen in a client that demonstrates how important it is to know the new functionalities of the application with which we work, in this case the Oracle DB.

How did the ORA-00600 error appear?

In an environment, a migration from a database 11.2.0.1 to the latest version 12.2.0.1 was made.

After performing the migration, we encountered an ORA-00600 error in a query. This query has been used by the application that the database serves for quite some time.
The error that was obtained is:
ORA-00600: internal error code, arguments: [15815], [12], [10], [9], [], [], [], [], [], [], [], []

Query Exam

The query is written using the WITH clause, and its syntax is as follows:

WITH
SAWITH0 AS (select sum(T22.CARGA_DOC_ID) as c1,
TRUNC(T31.DAY) as c2
from
GRUPO_GR.TD_2_TIEMPO T31,
GRUPO_RH.TF_CC_ORI_EMPLE T22
where ( T31.DAY_ID = T22.DAY_ID )
group by T31.DAY_ID, TRUNC(T31.DAY)
having 0.0 < sum(T22.CARGA_DOC_ID))
select D1.c1 as c1 from ( select distinct D1.c2 as c1
from
SAWITH0 D1
order by c1 desc ) D1 where rownum <= 1

After performing tests to find out why the error. We realized that the problem was in the use of the WITH clause. When using its content in the FROM clause, the query works correctly:

select D1.c1 as c1 from ( select distinct D1.c2 as c1
from
(select sum(T22.CARGA_DOC_ID) as c1, TRUNC(T31.DAY) as c2
from
GRUPO_GR.TD_2_TIEMPO T31,
GRUPO_RH.TF_CC_ORI_EMPLE T22
where ( T31.DAY_ID = T22.DAY_ID )
group by T31.DAY_ID, TRUNC(T31.DAY)
having 0.0 < sum(T22.CARGA_DOC_ID)
) D1
order by c1 desc ) D1 where rownum <= 1

It does not seem to be a problem of the version. Queries with the WITH clause are allowed from at least Oracle version 9i.

Of course, we get in touch with Oracle through the support page, to solve the doubt we have with the error, since these errors ORA-00600 are the errors that are less documented and it is difficult to track and solve the same.

After several exchanges of information, it is concluded that the problem is that the parameter optimizer_features_enable continues with the value 11.2.0.1 (version before the Upgrade) and did not change with the upgrade to the current version of the database (12.2. 0.1).

Reviewing the Oracle documentation we see that this parameter enables a series of optimizer performance characteristics, which change in each version, and it is clear that we have found one of them.The characteristics of each version can be checked in the following link:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/OPTIMIZER_FEATURES_ENABLE.html#GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C
After reviewing this, the parameter is changed to the new version, and can be done online, with the instruction ALTER SYSTEM:

ALTER SYSTEM SET optimizer_features_enable='12.2.0.1' SCOPE=both

And the query starts working, returning a result:

C1
----------------------
31/07/2018 00:00:00

We hope you liked it, we leave our entries on Oracle in case you are also useful.
Database Team

If you don’t want to miss any of our publications, subscribe GPS Open Source News