Oracle 错误解析

最新推荐文章于 2025-03-07 10:21:49 发布

转载最新推荐文章于 2025-03-07 10:21:49 发布 · 265 阅读

标签

#数据库

一、现象

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)