Parse error 979 or 937 showing in Oracle 12
Article ID: 116677
Updated On:
Products
Issue/Introduction
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
Environment
Business Service Insights 8.3.5.x
Cause
This seems to be 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.
Resolution
In theory you should be able to solve this by turning off this feature of Oracle's optimizer. You would do this 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;
However, this has NOT solved the problem in any environment this has been tested on and Oracle 12.2 continues to modify these queries. An issue was opened with Oracle but they did not provide any solution.
At this time development has created a fix 3-8.3.5.3.10-ParseErrorsOracleFix--1176931-07112019.zip that works around this Oracle problem by using views. This seems to prevent Oracle from modifying the queries. This is the only known solution at this time.
(Note an earlier fix 10 solved the parse error but contained a timestamp problem. This has since been resolved by 3-8.3.5.3.10-ParseErrorsOracleFix--1176931-07112019.zip and this is being bundled up as a cleaner published fix. As long as the fix date is July 11th 2019 or later you should have the newer one).
Feedback
