Re: ODBC SQLColums is very slow
- From: Charles Hooper <hooperc2000@xxxxxxxxx>
- Date: Tue, 20 Jan 2009 09:34:11 -0800 (PST)
On Jan 20, 10:05 am, Wolfram Roesler <w...@xxxxxxx> 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.
.
- Follow-Ups:
- Re: ODBC SQLColums is very slow
- From: Shakespeare
- Re: ODBC SQLColums is very slow
- From: Wolfram Roesler
- Re: ODBC SQLColums is very slow
- From: Gerard H. Pille
- Re: ODBC SQLColums is very slow
- References:
- ODBC SQLColums is very slow
- From: Wolfram Roesler
- ODBC SQLColums is very slow
- Prev by Date: Re: Oracle 11g Virtual Columns?
- Next by Date: Re: ODBC SQLColums is very slow
- Previous by thread: ODBC SQLColums is very slow
- Next by thread: Re: ODBC SQLColums is very slow
- Index(es):
Relevant Pages
|
Loading
