ODBC SQLColums is very slow
Wolfram Roesler
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
Charles Hooper
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.
Gerard H. Pille
Hallo Charles,
we're plagued by third party applications sending those kind of queries time and again, while
the authors deny any involvement. My conclusion was also that ODBC had to be the culprit.
Every two seconds they query what indexes are defined on the same table.
So, where can we switch from ODBC to OLEDB? Or do we depend on the third party for that change?
Thanks,
Gerard
Charles Hooper
> Hallo Charles,
>
> we're plagued by third party applications sending those kind of queries time and again, while
> the authors deny any involvement. My conclusion was also that ODBC had to be the culprit.
> Every two seconds they query what indexes are defined on the same table.
>
> So, where can we switch from ODBC to OLEDB? Or do we depend on the third party for that change?
>
> Thanks,
>
> Gerard
Gerald,
Changing from ODBC to OLEDB will require a change to the program code,
although switching from the Microsoft ODBC driver to the Oracle
provided ODBC driver might change, for better or worse, what SQL
statements are automatically submitted by the ODBC connector.
An ODBC connection string will look something like this, where
"MyConn" is the name of the ODBC connection:
Data Source=MyConn;User ID=UserNameHere;Password=PasswordHere;
An OleDB connection string will look something like this, where MySID
is specified in tnsnames.ora:
Provider=OraOLEDB.Oracle;Data Source=MySID;User
ID=UserNameHere;Password=PasswordHere;
There is a small chance that making the above change will result in
errors within the program where bind variables are defined - some bind
variable type constants which were understood by ODBC may not be
understood by OleDB. Such a change will also likely make the
application slightly less database independent, if that is a concern.
sybr...@hccnet.nl
wrote:
>Charles Hooper schreef:
Gerard,
Oledb is a completely different beast and your problem is related to a
bug in the Oracle ODBC driver. You need to upgrade it. You are not on
the terminal release of 9iR2 anyway.
I had the same problem at a customer.
--
Sybrand Bakker
Senior Oracle DBA
Gerard H. Pille
>
>
> Gerard,
>
> Oledb is a completely different beast and your problem is related to a
> bug in the Oracle ODBC driver. You need to upgrade it. You are not on
> the terminal release of 9iR2 anyway.
> I had the same problem at a customer.
>
Bedankt Sybrand.
They turned down an earlier request to upgrade the ODBC driver, which currently might very well
be a release 9. Do you have some more input on that bug? It might add some weight to my
request ;-)
Gerard
Wolfram Roesler
news:a63701d7-e334-4920...@l33g2000pri.googlegroups.com:
> See if the following query returns any rows:
> SELECT
> *
> FROM
> SYS.TAB_STATS$;
It doesn't.
> 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)
That ran fine, and now above query does return rows, but SQLColumns
still is slow.
Unfortunately, switching from ODBC to OLEDB is not an option.
Any further ideas, apart from upgrading the ODBC driver?
Thanks for your help
Wolfram Roesler
Shakespeare
Looks like you missed the 'RULE' hints in the query...
<snap>
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
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
Gerard H. Pille
>
>
> 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
There is another solution that I found on the web.
In the schema of the user running the query, create tables all_synonyms and all_tab_columns, ...
Fill them with data and add the necessary indexes.
Barbatruc.
Shakespeare
Materialized views would be the option then....
shakespeare
Charles Hooper
> 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.
Shakespeare
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
Or create a synonym ALL_SYNONYMS for DBA_SYNONYMS. (Sounds like a
wonderful META operation). And pray ALL_SYNONYMS isn't used anywhere
else... DBA_SYNONYMS contains all the rows of ALL_SYNONYMS plus some
more (so ALL is not ALL at all).
It's ALL_SYNONYMS that is bugging the query. See one of my other posts
in this discussion. Filling tables is not such a good idea, because
you would have to update them every time a synonym or table is created....
Shakespeare
Charles Hooper
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.
Wolfram Roesler
> 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.
You were right, all I did was call the ODBC function SQLColumns
which, through the Oracle ODBC driver, generated the SQL I posted.
Thanks and best regards
Wolfram Roesler
Wolfram Roesler
aef4-3af...@r36g2000prf.googlegroups.com:
> 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)
That looks like a nice alternative to SQLColumns. I think it would not
be necessary to select anything (i. e. one could use a where clause that
matches nothing). Perhaps WHERE 0=1 would even be more efficient than using
ROWNUM? Would a FIRST_ROWS hint help? We are dealing with some big tables
here, as well as with some views that point to DB links, so I'd like to
use a query that accesses the data dictionary only without touching the
actual table data.