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	9wahc0hhfhjmx	dllhost.exe	select OptionValue, IsSuitOrg...
400,052	0	2.59	796mhq46ffwug	dllhost.exe	select OptionValue, IsSuitOrg...
400,014	0	2.59	fuqpzv4j0fk4j	dllhost.exe	select OptionValue, IsSuitOrg...
337,192	0	2.19	56k1g6asqvqaf	dllhost.exe	select OptionValue from tSysO...
314,193	0	2.04	5t799gqjf0fy6	dllhost.exe	select OptionValue from tSysO...
314,189	0	2.04	cps04gq5rqmqw	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).

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7320672/viewspace-1390474/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7320672/viewspace-1390474/