一、 现象
1. 查看统计信息
346 parse count (total) 64 8835573075 63887964
347 parse count (hard) 64 1294780517 143509059
348 parse count (failures) 64 1240605275 1118776443
错误解析占比14%。
2. AWR报告
|
Buffer Nowait %: |
100.00 |
Redo NoWait %: |
100.00 |
|
Buffer Hit %: |
99.74 |
In-memory Sort %: |
100.00 |
|
Library Hit %: |
86.77 |
Soft Parse %: |
86.54 |
|
Execute to Parse %: |
-271.54 |
Latch Hit %: |
99.28 |
|
Parse CPU to Parse Elapsd %: |
2.10 |
% Non-Parse CPU: |
18.08 |
|
Statistic Name |
Time (s) |
% of DB Time |
|
parse time elapsed |
1,724,083.84 |
76.90 |
|
failed parse elapsed time |
1,400,647.59 |
62.48 |
|
sql execute elapsed time |
355,256.49 |
15.85 |
|
DB CPU |
45,483.09 |
2.03 |
|
hard parse elapsed time |
10,795.04 |
0.48 |
|
connection management call elapsed time |
9,239.38 |
0.41 |
二、 原因
1. 程序语句的写法
例如：(Delphi)
Close;
Connection := FAdoConn;
SQL.Add(‘Select F.FunCode,F.FunName,F.FunType,F.IsAuto,F.DllName,F.DllFun ‘)；
SQL.Add(‘From txtFunction F’)；
Open;
SQLTRACE跟踪的结果
Error encountered: ORA-00923
--------------------------------------------------------------------------------
Select F.FunCode,F.FunName,F.FunType,F.IsAuto,F.DllName,F.DllFun
2. ADO游标问题
ADO游标类型使用不当，SQL中会自动增加ROWID，如果是访问的视图，并且视图中带有Group By 、Distinct、Union等操作，就会导致解析错误。
举例如下：
The following statements encountered a error during parse:
SELECT BGNDATE , BGNTIME , BILLNO , CXTYPE , DATATYPE , ENDDATE , ENDTIME , ISENABLED , ISHYZSZ , LMTCOUNT , ORGCODE , PLUCODE , PLUID , POSCOMUPTYPE , PRI , UPTDATE , YHPRICE , YHRATE , YHTYPE , VPROCXPTPLU."ROWID" FROM VPROCXPTPLU WHERE ORGCODE = '101C' AND UPTDATE>=TO_DATE('2014-08-25 23:25:48','YYYY-MM-DD HH24:MI:SS')
==============
Error encountered: ORA-01446
--------------------------------------------------------------------------------
SELECT OPTIONTYPE , OPTIONVALUE , ORGCODE , REMARK , SYSOPTION , SYSOPTIONNAME , VSYSCVSOPTION."ROWID" FROM VSYSCVSOPTION WHERE ORGCODE='101C' OR ORGCODE='*'
==============
Error encountered: ORA-01445
VPROCXPTPLU、VSYSCVSOPTION视图中带group by 或 distinct 或 union
三、 解决方法
1. 程序语句的写法的改变
Close;
Connection := FAdoConn;
sSql := ‘Select F.FunCode,F.FunName,F.FunType,F.IsAuto,F.DllName,F.DllFun ‘
+ ‘From txtFunction F’;
SQL.Add(sSql)；
Open;
2. 游标类型的改变
adOpenStatic (SQL_CURSOR_STATIC)
改用
adOpenForwardOnly (SQL_CURSOR_FORWARD_ONLY, default).
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7320672/viewspace-1390471/ ，如需转载，请注明出处，否则将追究法律责任。
