Re: ODBC SQLColums is very slow
Date: Tue, 20 Jan 2009 09:34:11 -0800 (PST)
Message-ID: <a63701d7-e334-4920-ba0a-72c587f70d3c_at_l33g2000pri.googlegroups.com>
On Jan 20, 10:05 am, Wolfram Roesler <w..._at_spam.la> wrote:
> Hello,
>
> I'm using the Oracle ODBC driver to connect to my 10g database, and
> found the SQLColumns function (that returns a table description
> similiar to sqlplus's "describe" command) to be terribly slow: It
> takes about 1.3 seconds per table, even when executed repeatedly
> for the same table. In sqlplus, "describe" for the same tables
> returns instantly.
>
> I ran SQLColumns in SQL_TRACE mode and found that it submits the
> following two queries (GRP_ALLKONF is my table name, a simple
> table with seven columns):
>
> SELECT /*+ RULE */ COUNT(*)
> FROM
> ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND UPPER(SYNONYM_NAME)=
> UPPER('GRP_ALLKONF')
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 10.17 15.38 0 212858 0
> 1
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 10.17 15.38 0 212858 0
> 1
>
> SELECT /*+ RULE */ '',owner,table_name,column_name,0,data_type,
> data_precision, decode(data_type, 'DATE',16,'FLOAT',8,'LONG
> RAW',2147483647,
>
> 'LONG',2147483647,'CLOB',2147483647,'NCLOB',2147483647,'BLOB',2147483647,
> 'BFILE',2147483647,'CHAR',char_length,'NCHAR',char_length,'VARCHAR2',
> char_length,'NVARCHAR2',char_length,'NUMBER',NVL(data_precision+2,40),
> data_length), data_scale, 0, decode(nullable, 'Y', 1, 'N', 0), '' , '',
> 0,
> 0,decode
> (data_type,'CHAR',data_length,'VARCHAR2',data_length,'NVARCHAR2',
> data_length,'NCHAR',data_length, 0),column_id, decode(nullable, 'Y',
> 'YES',
> 'N', 'NO')
> FROM
> all_tab_columns WHERE UPPER(TABLE_NAME)=UPPER('GRP_ALLKONF') UNION
> 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('GRP_ALLKONF') ORDER BY 2,3,17
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 6.67 7.48 0 213578 0
> 7
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 6.67 7.48 0 213578 0
> 7
>
> This time, with tracing turned on, it even took 23 seconds. The normal
> time for a single SQLColumns call is about 1.3 seconds however.
>
> The database has plenty of RAM and nothing much to do. Everything else is
> fast and fine.
>
> My database version is 10.2.0.2, and my ODBC driver version is 9.2.0.5.4,
> but I found SQLColumns to be slow with other combinations of database and
> driver too.
>
> Any idea what I can do to speed up my calls to SQLColumns?
>
> Thanks for any help
> W. Roesler
You might try taking a look at the DBMS_XPLANs for those simple looking queries, using ALLSTATS LAST as a parameter for DBMS_XPLAN. Those simple looking SQL statements have quite a complicated plan.
See if the following query returns any rows:
SELECT
*
FROM
SYS.TAB_STATS$;
If no rows are returned by the above, that is an indication that fixed
object statistics were never collected for the database. If
necessary, collect fixed object statistics with the following command
in SQL*PLUS:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL)
Also, you might consider switching from an ODBC connection string to
an OLEDB connection string. While tracing a couple applications here,
we found a couple SQL statements in trace files which were not
submitted by the application code, yet were taking considerable
execution time (mostly on the CPU), for instance:
PARSING IN CURSOR #7 len=289 dep=0 uid=31 oct=3 lid=31 tim=4568301442
hv=1545094011 ad='53a90f48'
SELECT '', b.owner, b.table_name, b.column_name, b.position,
b.constraint_name FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE
(a.constraint_name = b.constraint_name AND a.constraint_type = 'P'
AND b.table_name='MY_TABLE' AND b.owner='MY_OWNER' ) ORDER BY b.owner,
b.table_name, b.position
END OF STMT
PARSE
#7:c=203125,e=202117,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=4568301435
BINDS #7:
EXEC #7:c=0,e=302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4568302326
WAIT #7: nam='SQL*Net message to client' ela= 5 driver id=1413697536
#bytes=1 p3=0 obj#=13451 tim=4568302483
FETCH
#7:c=78125,e=81673,p=0,cr=9733,cu=0,mis=0,r=1,dep=0,og=1,tim=4568384281
In several cases, switching the connection string from ODBC to OLEDB dropped the login time for those applications from 2+ seconds to nearly instantaneous, as SQL statements like the above were not submitted to the database with the OLEDB connection.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jan 20 2009 - 11:34:11 CST