Re: ODBC SQLColums is very slow
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