ORA-01795 — PARSE ERROR: ospid=17974, error=1795 for statement

After an application upgraded over the weekend and we have started to see the following errors in the alert log:

WARNING: too many parse errors, count=3593 SQL hash=0x8b6dc8e6
PARSE ERROR: ospid=17974, error=1795 for statement:
2021-02-08T07:37:28.836962-06:00
select * from ( select cust.pk as pk1_431_, cust.name_id as name_i2_431_, cust.cust_id as name_u3_431_, cust.cust_id_type as name_u4_431_ from customer cust where (cust.name_id like :1 ) and (cust.name_id not in  (:2  , :3  , :4  , :5  , :6  , :7  , :8  , :9  , :10  , :11  , :12  , :13  , :14  , :15  , :16  , :17  , :18  , :19  , :20  , :21  , :22  , :23  , :24  , :25  , :26  , :27  , :28  , :29  , :30  , :31  , :32  , :33  , :34  , :35  , :36  , :37  , :38  , :39  , :40  , :41  , :42  , :43  , :44  , :45  , :46  , :47  , :48  , :49  , :50  , :51  , :52  , :53  , :54  , :55  , :56  , :57  , :58  , :59  , :60  , :61  , :62  , :63  , :64  , :65  , :66  , :67  , :68  , :69  , :70  , :71  , :72  , :73  , :74  , :75  , :76  , :77  , :78  , :79  , :80  , :81  , :82  , :83  , :84  , :85  , :86  , :87  , :88  , :89  , :90  , :91  , :92  , :93  , :94  , :95  , :96  , :97  , :98  , :99  , :100  , :101  , :102  , :103  , :104  , :105  , :106  , :107  , :108  , :109  , :110  , :111  , :112  , :113  , :114  , :115  , :116  , :117  , :118  , :119  , :120  , :121  , :122  , :123  , :124  , :125  , :126  , :127  , :128  , :129  , :130  , :131  , :132  , :133  , :134  , :135  , :136  , :137  , :138  , :139  , :140  , :141  , :142  , :143  , :144  , :145  , :146  , :147  , :148  , :149  , :150  , :151  , :152  , :153  , :154  , :155  , :156  , :157  , :158  , :159  , :160  , :161  , :162  , :163  , :164  , :165  , :166  , :167  , :168  , :169  , :170  , :171  , :172  , :173  , :174  , :175  , :176  , :177  , :178  , :179  , :180  , :181  , :182  , :183  , :184  , :185  , :186  , :187  , :188  , :189  , :190  , :191  , :192  , :193  , :194  , :195  , :196  , :197  , :198  , :199  , :200  , :201  , :202  , :203  , :204  , :205  , :206  , :207  , :208  , :209  , :210  , :211  , :212  , :213  , :214  , :215  , :216  , :217  , :218  , :219  , :220  , :221  , :222  , :223  , :224  , :225  , :226  , :227  , :228  , :229  , :230  , :231  , :232  , :233  , :234  , :235  , :236  , :237  , :238
Additional information: hd=0x34cc5ce18 phd=0x34cc5e240 flg=0x28 cisid=51 sid=51 ciuid=51 uid=51 sqlid=8crran65qvk76
...Current username=USER_APP

The first stage of SQL processing is parsing. During the parse call, the database performs the following checks:

Syntax Check
Semantic Check
Shared Pool Check for hard parse or soft parse

The log message explained itself — parse error. To know what kind of parse error, check the error code in my case above “error=1795“.

[oracle@joeprod1 trace]$ oerr ORA 01795
01795, 00000, "maximum number of expressions in a list is 1000"
// *Cause:    Number of expressions in the query exceeded than 1000.
//            Note that unused column/expressions are also counted
//            Maximum number of expressions that are allowed are 1000.
// *Action:   Reduce the number of expressions in the list and resubmit.

The query in my particular case failed at the first stage — Syntax Check. It’s clear the code in the application needs a rewrite. This is a nice feature (outputing parse error in the alert log) from Oracle 12.2. DBA can catch application side issues quickly and easily and report them back to the application development team.

The behaviour is controlled by a hidden parameter _kks_parse_error_warning which has a default value 100.

select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) like upper('%_kks_parse_error_warning%') order by name; 

NAME                           VALUE                DESCRIPTION
------------------------------ -------------------- -------------------------
_kks_parse_error_warning       100                  Parse error warning

References:

ORA-2065 Is Caused When Configuring a parameter _kks_parse_error_warning (Doc ID 2695498.1)
Bug 16945190 – Diagnostic enhancement to dump parse failure information automatically (Doc ID 16945190.8)

Oracle

2 thoughts on “ORA-01795 — PARSE ERROR: ospid=17974, error=1795 for statement”

While I agree it is nice to have this error, it is seriously filling up the ALERT log, is there any way to turn it off/on?

LikeLike

Joe Li says:

April 26, 2022 at 10:47 am

Hi Edwin,

There is a hidden parameter _kks_parse_error_warning to control the behaviour. The default value is 100 which means the warning will show up in the alert log when the number of parse error reaches 100 for the same statement.

You can turn it off by changing the value to 0, but it’s not recommended to do so since normally a parse error means something is wrong on the application side which needs to be addressed within the application.

If you really want to do so, you can use

alter system set “_kks_parse_error_warning”=0 scope=both;

Or you can set the value to a higher number to reduce the frequency.

To check the current value:

select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV (‘Instance’)
and b.inst_id = USERENV (‘Instance’)
and a.indx = b.indx
and upper(a.ksppinm) like upper(‘%_kks_parse_error_warning%’) order by name;

Thanks,
Joe

LikeLike

Reply