Re: Strange SQL

andersleffler_at_hotmail.com wrote:
> My VB application accesses the database via ADO and it seems that ADO
> (or is it the trace utility??) performs some weird SQL statements,
> which slows down the overall performance of the database. All these
> statements look like
>
> select *
> from
> (select null TABLE_CATALOG, ac.owner TABLE_SCHEMA, ac.table_name
> TABLE_NAME, acc.column_name COLUMN_NAME, null COLUMN_GUID, null
> COLUMN_PROPID, acc.position ORDINAL from all_constraints ac,
> all_cons_columns acc where ac.owner = acc.owner and ac.table_name =
> acc.table_name and ac.constraint_type = 'P' and ac.constraint_name =
> acc.constraint_name) DBSCHEMA_PRIMARY_KEYS WHERE TABLE_SCHEMA = <>
> schema_name AND TABLE_NAME = <table_name> order by 3, 4
>
> There are several occurances of these in the trace files and they come
> in different flavors (different order by figures etc). All tables have
> primary keys and different indexes.
> I wonder why these statements are fired, I know they aren't fired by
> the application, and I assume that it is Oracles data provider that
> perform these tricks. I wonder why, and I also wonder why they are
> running against the ALL_ meta tables and not against USER_ meta tables.
> I have searched the internet and Metalink for weeks, many ask the
> question but noone seems to have the answer.

This looks like a metadata query.
Your VB application or your driver or some other level issues these statements, obviously to obtain information about the primary key columns on a table.

These queries are harmless. To find out who issues them, debug the different layers of your application and hope that they are verbose enough.

Yours,
Laurenz Albe Received on Tue Nov 08 2005 - 04:50:19 CST