Re: ODBC SQLColums is very slow

From: Shakespeare <>
Date: Thu, 22 Jan 2009 10:44:42 +0100
Message-ID: <4978400c$0$193$>

Charles Hooper schreef:
> On Jan 21, 11:58 am, Shakespeare <> wrote:
>> Looks like you missed the 'RULE' hints in the query...

> Honestly, I did not miss the RULE hint. One of the problems with 10g,
> or maybe a feature, is that without fixed object statistics, queries
> against certain data dictionary objects, such as V$ACCESS, produce
> either suboptimal plans, or crash the session which is executing the
> query - there was a thread in this group several months ago which
> explored one such query involving V$ACCESS:
> While expecting the suggestion to collect fixed object statistics to
> provide limited benefit in this case (due to the RULE hint), my hope
> was that it might provide some relief, or at least indicate that there
> might be other hidden problems lurking in the database.
>> 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

> I believe that the ODBC driver was the source of the SQL statement
> that the OP provided (as it was for the query that I posted which was
> attempting to select from ALL_CONS_COLUMNS), and not the OP's third
> party program code - but I could be wrong. If the ODBC driver is the
> source, switching to a different ODBC driver, or OLEDB (requiring a
> source code change) might prevent that specific SQL statement from
> being submitted to the database, as had happened in the case I
> reported. Sybrand reported that the problem is related to a bug in
> the Oracle ODBC driver - most of my clients computers are running the
> version of the ODBC driver, so I am wondering which version
> has the bug fix.
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.


I was under the impression that the SQLColumns function produced the statements, but we may be talking about the same issue here, because it is an ODBC specific function. Anyway, a better implementation of the SQLColumns function is the only way to fix the problem.

I ran the fixed object statistics, but that did not change execution time of the described query. So regardless of which process produces the query, on my database, even from SQLPLUS, it is a slow one even without the /*+ RULE */ hints.
The bad part of the query is

   /*+ 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('DUAL') ORDER BY 2,3,17 It can be speed up by a factor of 6 by using DBA_TAB_COLUMNS and DBA_SYNONYMS by the way. Further investigation shows that ALL_SYNONYMS is the cause of the problem. By using DBA_SYNONYMS in stead, the gain in performance is already at factor 6 to 7. Looking at the definitions of ALL_ and DBA_ synonyms one will see why..... and conclude that DBA_SYNONYMS will do just as well. It can have more rows than ALL_SYNONYMS, but since it is joined with ALL_TABLES this effect will disappear.

Shakespeare Received on Thu Jan 22 2009 - 03:44:42 CST

Original text of this message