OLEDB internal queries - last try

["Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>]

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

• Follow-Ups:
  • Re: OLEDB internal queries - last try
    • From: Dennis Williams

Other related posts:

OLEDB internal queries - last try

Re: OLEDB internal queries - last try

RE: OLEDB internal queries - last try

Re: OLEDB internal queries - last try

RE: OLEDB internal queries - last try