Re: ODBC SQLColums is very slow

Charles Hooper schreef:
On Jan 21, 11:58 am, Shakespeare <what...@xxxxxxxxx> 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:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

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
10.2.0.1 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.


Charles,

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

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('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
.

Relevant Pages

  • Re: ODBC SQLColums is very slow
    ... I did not miss the RULE hint. ... explored one such query involving V$ACCESS: ... While expecting the suggestion to collect fixed object statistics to ... I believe that the ODBC driver was the source of the SQL statement ...
    (comp.databases.oracle.server)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... It got past looking for pub.mdb, and looks like it tried to run the query ... I also tried putting the whole thing in the connection string: ... The settings for the Merant ODBC driver for ... default connect string means a jet database. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Import MS access data
    ... The actual ODBC driver should allow you to do any valid SQL query that creates a result set. ... The queries you make using Excel and MS Query are completely independent of any queries that are in the .mdb database. ...
    (microsoft.public.mac.office.excel)
  • Re: MS SQL Server/ODBC package for Python
    ... Running your benchmark, I ran into a couple of interesting points. ... I changed the query to ... If adodbapi avoids this (which we'll also integrate into mxODBC 2.1), then this would explain the differences you see. ... ODBC driver doesn't provide proper size information - each ...
    (comp.lang.python)
  • Re: ODBC driver manager mangles delimited column names
    ... Thanks Robert, I'm beginning to think you are right. ... I've just finished running some more tests with the trace logging turned on for the ODBC driver manager. ... This is what I get in the log for the automatically generated query: ...
    (microsoft.public.data.odbc)