Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> OLEDB internal queries - last try
Hello,
This OLEDB/ADO question was asked several times, and I try it once more time.
The Oracle ODBC/OLEDB Provider, on certain situations, sends out to the
RDBMS dictionary querys on its own (aka "select * from (select NULL
......from DBSCHEMA_INDEXES ..., etc), when in VB code we use
"CursorLocation = adUseClient". These querys return primary key and
index info from the dictionary at a low level, to populate the schema
rowsets in ADO, but (since we use synonyms which differs from the real
tables's names in the app), they aren't returning rows (ie: the app uses
"TABLE", so the driver at low level query for "TABLE" -- but the real
table is "A_TABLE"). So it's useless (and the ADO layer just discards
the result with no harm in the app)
These querys are really parsed by the RDBMS. They cost a bit, and, in some situations which we found, their accumulated time worths for more than 90% of waits of the application (SQL net from client) , though their individual performance is ok). I'm not talking about problems with these queries's performance at an individual level, but instead their relevance on an aggregate level, across many executions. Also, we see they are ruining the global parse to execute ratio (albeit being soft-parsered).
This happens since ODBC/OLEDB drivers from the series 9.2 client, and also from 10.2.0.2 .
In Metalink there are some bugs documenting this behavior:
3249559 ANSI JOIN SYNTAX AND ADO CLIENT CURSORS CAUSES COSTLY QUERIES,
which correspond to base bug:
3866119 MOST JOINS NOT UPDATEABLE WHEN USING SQL 1999 SYNTAX
Bug 3249559 most precisely describes the situation, but it's "base bug"
3866119 doesn't really fixed all the cases. A very simple ADO Recordset
test from vbscript using 9.2.0.7.0 OLEDB Provider, and not using ansi
joins at all, reproduces the problem.
What I would like to know is if any of you have to deal with this before, and would like to share if there are any viable workarounds. Changing the VB code is not an option.
regards,
alvaro
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 03 2007 - 13:07:42 CDT