Re: ODBC SQLColums is very slow
Date: Tue, 20 Jan 2009 20:44:33 +0100
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
>> 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
>> 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
>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?
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 DBAReceived on Tue Jan 20 2009 - 13:44:33 CST