Re: ODBC SQLColums is very slow
Date: Wed, 21 Jan 2009 13:28:16 -0800 (PST)
Message-ID: <16f1b193-ab86-422c-895e-451795ebf95e_at_v5g2000pre.googlegroups.com>
On Jan 21, 11:58 am, Shakespeare <what..._at_xs4all.nl> 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.
Received on Wed Jan 21 2009 - 15:28:16 CST