Parse error 979 or 937 showing in Oracle 12

Document ID : KB000116677
Last Modified Date : 15/02/2019
Show Technical Document Details
Issue:
Looking at the alert_<db>.log from the BSI database (where <db> is your db name) we see a number of parse errors, like below:

2018-08-27 15:52:09.424000 -03:00 
WARNING: too many parse errors, count=3400 SQL hash=0x24e46870 
PARSE ERROR: ospid=7058, error=979 for statement: 
select /*+INDEX(t IDX_RUL_STAT_RUL_TU_INT_PER_TM) */ t.level_id , to_char(max(t.timestamp),:"SYS_B_0") timestamp , t."ROWID" from t_rule_states t where t.rule_id=:"SYS_B_1" and t.time_unit_name=:"SYS_B_2" and t.interval_length=:"SYS_B_3" and t.is_period=:"SYS_B_4" group by t.level_id 
Additional information: hd=0x79596f38 phd=0x79598498 flg=0x110676 cisid=114 sid=114 ciuid=114 uid=114 

WARNING: too many parse errors, count=6800 SQL hash=0xaab0f9e5 
PARSE ERROR: ospid=7058, error=937 for statement: 
SELECT TO_CHAR(MAX(TIMESTAMP), :"SYS_B_0") TIMESTAMP , RS."ROWID" FROM T_RULE_STATES RS WHERE RS.RULE_ID = :"SYS_B_1" AND RS.TIME_UNIT_NAME = :"SYS_B_2" AND RS.IS_PERIOD = :"SYS_B_3" AND RS.TIMESTAMP < To_Date(:"SYS_B_4",:"SYS_B_5") AND rs.LEVEL_ID <= :"SYS_B_6" 
Additional information: hd=0x795566b0 phd=0x795ac5d8 flg=0x100476 cisid=114 sid=114 ciuid=114 uid=114 

2018-08-27 15:53:04.368000 -03:00 
WARNING: too many parse errors, count=6900 SQL hash=0xaab0f9e5 
PARSE ERROR: ospid=7058, error=937 for statement: 
SELECT TO_CHAR(MAX(TIMESTAMP), :"SYS_B_0") TIMESTAMP , RS."ROWID" FROM T_RULE_STATES RS WHERE RS.RULE_ID = :"SYS_B_1" AND RS.TIME_UNIT_NAME = :"SYS_B_2" AND RS.IS_PERIOD = :"SYS_B_3" AND RS.TIMESTAMP < To_Date(:"SYS_B_4",:"SYS_B_5") AND rs.LEVEL_ID <= :"SYS_B_6" 
Additional information: hd=0x795566b0 phd=0x795ac5d8 flg=0x100476 cisid=114 sid=114 ciuid=114 uid=114 

2018-08-27 15:53:22.358000 -03:00 
WARNING: too many parse errors, count=3500 SQL hash=0x24e46870 
PARSE ERROR: ospid=7058, error=979 for statement: 
select /*+INDEX(t IDX_RUL_STAT_RUL_TU_INT_PER_TM) */ t.level_id , to_char(max(t.timestamp),:"SYS_B_0") timestamp , t."ROWID" from t_rule_states t where t.rule_id=:"SYS_B_1" and t.time_unit_name=:"SYS_B_2" and t.interval_length=:"SYS_B_3" and t.is_period=:"SYS_B_4" group by t.level_id 
Additional information: hd=0x79596f38 phd=0x79598498 flg=0x110676 cisid=114 sid=114 ciuid=114 uid=114 

WARNING: too many parse errors, count=7000 SQL hash=0xaab0f9e5 
PARSE ERROR: ospid=7058, error=937 for statement: 
SELECT TO_CHAR(MAX(TIMESTAMP), :"SYS_B_0") TIMESTAMP , RS."ROWID" FROM T_RULE_STATES RS WHERE RS.RULE_ID = :"SYS_B_1" AND RS.TIME_UNIT_NAME = :"SYS_B_2" AND RS.IS_PERIOD = :"SYS_B_3" AND RS.TIMESTAMP < To_Date(:"SYS_B_4",:"SYS_B_5") AND rs.LEVEL_ID <= :"SYS_B_6" 
Additional information: hd=0x795566b0 phd=0x795ac5d8 flg=0x100476 cisid=114 sid=114 ciuid=114 uid=114 
 
Cause:
This is caused by Oracle 12's "Optimizer" which is sticking a "ROW_ID" in the middle of a number of queries which then breaks them.

Here is some further information from Oracle:

Queries giving the parse error.
       With so far analysis made one observation for the cause of below queries giving the parse error  is not because of code issues and  mostly due to the  Oracle 12.2 optimizer.
          Oracle server 12.2  is dynamically adding the ROWID column for the queries executed server level,

 where as it is missing to add the group by ROWID (observed from the oracle trace collected on prod server) ,
which is expected for some set of queries other wise they wont execute properly.
So mostly the missing to add the group by RowID dynamically by the server causing these parse errors.

For a couple of other queries there is a different optimization applied resulting in parse error.

Probably we have to refactor these 5 to 6 queries to execute well in case Oracle 12.2 optimizer resulting in parse errors.

2. Performance improvement optimizer changes.
https://communities.ca.com/thread/241788872-oracle-12-performance-issues
Oracle 12c Release 2 Performance Some performance degradation could be the result of a possible ORDERED hint regression issue in Oracle 12c Release 2 SQL statements. For Oracle 12c R1 (12.1.0.2), no action is required. For Oracle 12c R2 (12.2.0.1), enable the following parameter and set the optimizer to 12.1.0.2. To improve performance, database administrators can run the following commands:
Resolution:
To solve this we need to turn off the newest optimizer features by running the following using sys as sysdba

sqlplus / as sysdba
ALTER SYSTEM SET "_fix_control" = '17800514:0';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE= '12.1.0.2' SCOPE=BOTH;
Exit;

After I run this I renamed the alert log and recycled the box.
This resolves these group by errors which would be the error=979 and error=937