oracle sql不能执行,Oracle SQL解析但不执行问题

一、现象

1.

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

Parse Calls

Executions

% Total Parses

SQL Id

SQL Module

SQL Text

400,134

0

2.59

dllhost.exe

select OptionValue, IsSuitOrg...

400,052

0

2.59

dllhost.exe

select OptionValue, IsSuitOrg...

400,014

0

2.59

dllhost.exe

select OptionValue, IsSuitOrg...

337,192

0

2.19

dllhost.exe

select OptionValue from tSysO...

314,193

0

2.04

dllhost.exe

select OptionValue from tSysO...

314,189

0

2.04

dllhost.exe

select OptionValue from tSysO

2.

2、统计语句

SUBSTR(SQL_TEXT,1,50)

PARSE_CALLS        EXECUTIONS    VERSION_COUNT

SELECT * FROM tPlcPrjCard

56264659              0                1         0

Select * from tWebComField where    16663944                0                2         0

SELECT * FROM tPlcProject

16482250              0                2         0

SELECT * FROM tBasCardType

14672100              0                2         0

二、原因

1.

ADO游标问题

The related user program was connecting to Oracle via ADO

driver.

ADO Connection Object Initialization properties was set to 'Read/Write

(adModeReadWrite) 3' while the default value is 'Read-only (adModeRead) 1'

ADO driver will automatically rewrite the SQL to another one, in which a ROWID

column is added, e.g.

parse_calls

executions sql_text

1                0     SELECT A FROM T_TEST WHERE NO = 300

1                1     SELECT A, T_TEST.ROWID FROM T_TEST WHERE NO

= 300

For the original SQL this means, it will be parsed but not executed.

This is caused by that cursor type

(SQL_ATTR_CURSOR_TYPE) is downgraded from adOpenStatic(SQL_CURSOR_STATIC) to

adOpenForwardOnly (SQL_CURSOR_FORWARD_ONLY, default).

adOpenStatic (SQL_CURSOR_STATIC) admit a scrollable cursor, which requires a

ROWID in the SQL, if the SQL will not admit a ROWID, then the cursor is downgraded

to Forward Only.

=> this is what happens here : SELECT on tables works fine, but not on views

The ODBC Driver get a PARSE error (ORA-01446) which indicate a downgrade of

the cursor to SQL without ROWID, this is what going on beneath the Driver and

the errorcode should be restrained,however in this case the error is populated

since SQLCancel is issued by the SQL_ATTR_QUERY_TIMEOUT is set.

2.

其他原因

如远程访问等。

三、解决方法

1.

游标类型的改变

adOpenStatic

(SQL_CURSOR_STATIC)

改用

adOpenForwardOnly

(SQL_CURSOR_FORWARD_ONLY, default).