一、 现象
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博客 ” ,链接:http://blog.itpub.net/7320672/viewspace-1390471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7320672/viewspace-1390471/