Re: ODBC SQLColums is very slow

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 21 Jan 2009 17:58:41 +0100
Message-ID: <49775441$0$198$e4fe514c_at_news.xs4all.nl>

Charles Hooper schreef:

> On Jan 20, 10:05 am, Wolfram Roesler <w..._at_spam.la> wrote:

>> Hello,
>>
>> I'm using the Oracle ODBC driver to connect to my 10g database, and
>> found the SQLColumns function (that returns a table description
>> similiar to sqlplus's "describe" command) to be terribly slow: It
>> takes about 1.3 seconds per table, even when executed repeatedly
>> for the same table. In sqlplus, "describe" for the same tables
>> returns instantly.
>>
>> I ran SQLColumns in SQL_TRACE mode and found that it submits the
>> following two queries (GRP_ALLKONF is my table name, a simple
>> table with seven columns):
>>
>> SELECT /*+ RULE */ COUNT(*)
>> FROM
>> ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND UPPER(SYNONYM_NAME)=
>> UPPER('GRP_ALLKONF')
>>
>> call count cpu elapsed disk query current
>> rows
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ----------
>> Parse 1 0.00 0.00 0 0 0
>> 0
>> Execute 1 0.00 0.00 0 0 0
>> 0
>> Fetch 1 10.17 15.38 0 212858 0
>> 1
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ----------
>> total 3 10.17 15.38 0 212858 0
>> 1
>>
>> SELECT /*+ RULE */ '',owner,table_name,column_name,0,data_type,
>> data_precision, decode(data_type, 'DATE',16,'FLOAT',8,'LONG
>> RAW',2147483647,
>>
>> 'LONG',2147483647,'CLOB',2147483647,'NCLOB',2147483647,'BLOB',2147483647,
>> 'BFILE',2147483647,'CHAR',char_length,'NCHAR',char_length,'VARCHAR2',
>> char_length,'NVARCHAR2',char_length,'NUMBER',NVL(data_precision+2,40),
>> data_length), data_scale, 0, decode(nullable, 'Y', 1, 'N', 0), '' , '',
>> 0,
>> 0,decode
>> (data_type,'CHAR',data_length,'VARCHAR2',data_length,'NVARCHAR2',
>> data_length,'NCHAR',data_length, 0),column_id, decode(nullable, 'Y',
>> 'YES',
>> 'N', 'NO')
>> FROM
>> all_tab_columns WHERE UPPER(TABLE_NAME)=UPPER('GRP_ALLKONF') UNION
>> SELECT
>> /*+ RULE */ '',b.owner,b.synonym_name,a.column_name, 0, a.data_type,
>> a.data_precision, a.data_length, a.data_scale, 0, decode(a.nullable,
>> 'Y', 1,
>> 'N', 0), '' , '', 0, 0, 0, a.column_id, decode(a.nullable, 'Y', 'YES',
>> 'N',
>> 'NO') FROM all_tab_columns a, all_synonyms b WHERE ((a.table_name =
>> b.table_name and a.owner = b.table_owner) ) AND UPPER(b.synonym_name)=
>> UPPER('GRP_ALLKONF') ORDER BY 2,3,17
>>
>> call count cpu elapsed disk query current
>> rows
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ----------
>> Parse 1 0.00 0.00 0 0 0
>> 0
>> Execute 1 0.00 0.00 0 0 0
>> 0
>> Fetch 2 6.67 7.48 0 213578 0
>> 7
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ----------
>> total 4 6.67 7.48 0 213578 0
>> 7
>>
>> This time, with tracing turned on, it even took 23 seconds. The normal
>> time for a single SQLColumns call is about 1.3 seconds however.
>>
>> The database has plenty of RAM and nothing much to do. Everything else is
>> fast and fine.
>>
>> My database version is 10.2.0.2, and my ODBC driver version is 9.2.0.5.4,
>> but I found SQLColumns to be slow with other combinations of database and
>> driver too.
>>
>> Any idea what I can do to speed up my calls to SQLColumns?
>>
>> Thanks for any help
>> W. Roesler

> 
> You might try taking a look at the DBMS_XPLANs for those simple
> looking queries, using ALLSTATS LAST as a parameter for DBMS_XPLAN.
> Those simple looking SQL statements have quite a complicated plan.
> 
> See if the following query returns any rows:
> SELECT
>   *
> FROM
>   SYS.TAB_STATS$;
> 
> If no rows are returned by the above, that is an indication that fixed
> object statistics were never collected for the database.  If
> necessary, collect fixed object statistics with the following command
> in SQL*PLUS:
> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL)
>

Looks like you missed the 'RULE' hints in the query...

<snap>

> 
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I ran the second query with the join (with actual tablenames) with sql plus on one of our production databases; takes more than a second as well. If I persist running the same query on the same table, it tunes down to 0,6 seconds. So it seems it's a slow query anyway.... so replacing ODBC with anything else won't help much. It's the second part of the union that is slow. The first part runs within 0.1 second. Joining all_tab_columns with all_synonyms seems a bad idea....

Shakespeare Received on Wed Jan 21 2009 - 10:58:41 CST