如何找到抛出ORA-00933错误的SQL

前几天上线，凌晨3点多打车回来的路上，兄弟联系我，提了一个问题，某核心系统，上线的时候，报了很多ORA-00933的错误，明显是应用写的SQL出现了错误导致的，但是因为未将出错的SQL打印到日志中，所以不知道究竟是什么SQL出错了，由于逻辑中涉及到很多的SQL，逐个排查，非常耗时。

ORA-00933，意思是“SQL command not properly ended”，明显是SQL的语法出现错误，但是现在的问题，就是如何找到错误的SQL？

第一种考虑，能不能从数据字典视图中找到？

我们执行如下这两条SQL，第一条是错误的，提示ORA-00933，第二条是正确的，

SQL> select object_id from t whereobject_id=1;
select object_id from t whereobject_id=1
                                      *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select object_id from t where object_id=1;
no rows selected

此时通过dba_hist_sqltext检索不到任何SQL，

SQL> select sql_id, sql_text from dba_hist_sqltext where sql_text like '%select object%';
no rows selected

dba_hist_sqltext是展示在AWR中采集到的属于共享SQL游标的SQL语句，

究其原因，如果SQL语句没有达到AWR的threshold值，是不会记录到 dba_hist_sqltext中的。但是，我们在执行完SQL后手动创建快照，就可以在dba_hist_sqltext中看到，如下所示，但是，只出现执行正确的这条SQL，提示ORA-00933的SQL，并未采集到，

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text from dba_hist_sqltext where sql_text like '%select object%'
  2  and sql_text not like '%dba_hist_sqltext%';
SQL_ID          SQL_TEXT
------------- --------------------------------------------------------------------------------
366bf1pphk8vp select object_id from t where object_id=1

我们知道，一条SQL在Oralce中执行的过程，会经过下图中的几个阶段，在解析阶段，会进行SQL的语法检查、语义检查以及共享池中查找是否存在执行过的SQL，如上ORA-00933错误，应该发生在语法解析阶段，所以尚未到达执行阶段，AWR未采集这条SQL，就可以解释通了，

第二种考虑，使用Logminer能从日志中得到SQL？

我们知道Logminer是可以从在线/归档日志中解析出曾经执行过的SQL语句，其实从上面，我们已经知道，ORA-00933错误的SQL根本没到执行阶段，因此从原理上看，不可能写入日志，就无从通过Logminer得到需要的SQL。

P.S.

当时没想到其他的方法，唯一的做法就是让应用改程序，打印出错的SQL到日志中，这种方法其实很直接，但是毕竟涉及到了改动，还是带来了不便。

后来看了惜分飞老师的文章，有了另外的考虑，SQL语句执行解析失败最大的可能性就是SQL语句语法/权限错误，对于这类问题可以通过设置event 10035进行跟踪，

打开10035事件，执行SQL，

SQL> alter system set events '10035 trace name context forever, level 1';
System altered.

SQL> select object_id from t whereobject_id=1;
select object_id from t whereobject_id=1
                                      *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter system set events '10035 trace name context off';
System altered.

就可以在alert日志中，看到如下信息，error=933，就是ORA-00933，第二行的SQL，就是要找的，

PARSE ERROR: ospid=18911, error=933 for statement:
select object_id from t whereobject_id=1^@

但是这种方法，需要注意，因为一旦打开10035，任何解析错误的SQL都会输出到alert日志中，如果对并发量很高的系统，碰巧解析错误的会执行多次，可能瞬时导致磁盘空间压力，因此Oracle中任何的event，我们在使用前，一定要知道他的副作用，避免带来影响。

现在我们在做的一项工作，就是制定《软件运维能力成熟度模型》，旨在通过设定一些通用的运维需求，采用分级的形式，从定量和定性，两个维度，评估软件的可运维能力，并给出提升指导。其中一项，就是应用执行错误的SQL语句需要打印到应用日志中，包括SQL原文、报错信息、参数等，一方面为监控报警提供数据，另一方面便于问题的排查。

一般的开发人员只会关注系统的功能实现，对其他层面，例如性能、运维等，考虑的很有限，这就对系统运行和问题排查，会产生些影响，或者造成不便，此时，我们就可能需要其他的方法来解决，像上面介绍的10035，但归根结底，一款软件如果要运营的持久，除了系统架构要满足功能，还需要为系统的可扩展性、可维护性等买单，当然，有人说过，“好的系统，不是设计出来的，而是演进来的”，不是说所有的问题都能在设计之初考虑到，但是当出现问题的时候，及时吸取教训，在设计上完善，就算是一种改进，值得做。