Re: ODBC SQLColums is very slow

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 22 Jan 2009 04:17:05 -0800 (PST)
Message-ID: <5f84af51-f214-4617-aef4-3af34b82d332_at_r36g2000prf.googlegroups.com>



On Jan 22, 4:44 am, Shakespeare <what..._at_xs4all.nl> wrote:
> 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

Shakespeare,

That is a very detailed investigation, thanks.

One caution is that ALL_SYNONYMS shows those synonym references which are _accessible by the user_, while DBA_SYNONYMS shows all synonym references which exist in the database, and that the DBA_ prefixed views/tables are not accessible by all user accounts. I would not feel too comfortable creating a synonym named ALL_SYNONYMS which points to DBA_SYNONYMS - it seems like doing so could cause confusion during a future troubleshooting exercise.

If the OP is able to offer a suggestion to the programmers, a trick that I have used in the past so that I have had no need for SQLColumns is to select into a recordset:
SELECT
  *
FROM
  MYTABLE
WHERE
  ROWNUM=1;
(or possible ROWNUM<1)

Once the above is retrieved in the recordset, it should be possible to check the data types of the retrieved columns quite easily.

For fun, I temporarily changed the program (using ADO) which I mentioned earlier back to using ODBC (10.2.0.1 driver) instead of OleDB. A 10046 trace file shows that the program accesses (dep=0 calls, indicating that the calls are coming from the application): ALL_CONSTRAINTS, ALL_CONS_COLUMNS, ALL_TABLES, ALL_INDEXES, and ALL_IND_COLUMNS - even though the application code does not request data from those tables. Also, when a query with a high precision WHERE clause is submitted, the trace file shows a SQL statement like the following being parsed, but never executed:   SELECT * FROM MYTABLE If the OP is lucky, he may not be dealing with the performance problems of unrequested accesses to ALL_CONSTRAINTS, ALL_CONS_COLUMNS,

ALL_TABLES, ALL_INDEXES, and ALL_IND_COLUMNS.  As you found with
ALL_SYNONYMS, accesses to the DBA_ versions are much faster than the
ALL_ versions of these views - a DBMS Xplan shows why.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jan 22 2009 - 06:17:05 CST

Original text of this message