Re: ODBC SQLColums is very slow

From: <sybrandb_at_hccnet.nl>
Date: Tue, 20 Jan 2009 20:44:33 +0100
Message-ID: <g9acn4tmv966odk82q7n7egieqegc7equ8_at_4ax.com>

On Tue, 20 Jan 2009 19:15:50 +0100, "Gerard H. Pille" <ghp_at_skynet.be> wrote:

>Charles Hooper schreef:
>> Also, you might consider switching from an ODBC connection string to
>> an OLEDB connection string. While tracing a couple applications here,
>> we found a couple SQL statements in trace files which were not
>> submitted by the application code, yet were taking considerable
>> execution time (mostly on the CPU), for instance:
>> PARSING IN CURSOR #7 len=289 dep=0 uid=31 oct=3 lid=31 tim=4568301442
>> hv=1545094011 ad='53a90f48'
>> SELECT '', b.owner, b.table_name, b.column_name, b.position,
>> b.constraint_name FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE
>> (a.constraint_name = b.constraint_name AND a.constraint_type = 'P'
>> AND b.table_name='MY_TABLE' AND b.owner='MY_OWNER' ) ORDER BY b.owner,
>> b.table_name, b.position
>> END OF STMT
>> PARSE
>> #7:c=203125,e=202117,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=4568301435
>> BINDS #7:
>> EXEC #7:c=0,e=302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4568302326
>> WAIT #7: nam='SQL*Net message to client' ela= 5 driver id=1413697536
>> #bytes=1 p3=0 obj#=13451 tim=4568302483
>> FETCH
>> #7:c=78125,e=81673,p=0,cr=9733,cu=0,mis=0,r=1,dep=0,og=1,tim=4568384281
>>
>> In several cases, switching the connection string from ODBC to OLEDB
>> dropped the login time for those applications from 2+ seconds to
>> nearly instantaneous, as SQL statements like the above were not
>> submitted to the database with the OLEDB connection.
>>
>> Charles Hooper
>
>Hallo Charles,
>
>we're plagued by third party applications sending those kind of queries time and again, while
>the authors deny any involvement. My conclusion was also that ODBC had to be the culprit.
>Every two seconds they query what indexes are defined on the same table.
>
>
>So, where can we switch from ODBC to OLEDB? Or do we depend on the third party for that change?
>
>Thanks,
>
>Gerard

Gerard,

Oledb is a completely different beast and your problem is related to a bug in the Oracle ODBC driver. You need to upgrade it. You are not on the terminal release of 9iR2 anyway.
I had the same problem at a customer.

-- 


Sybrand Bakker
Senior Oracle DBA

Received on Tue Jan 20 2009 - 13:44:33 CST