Strange SQL

Giganews Newsgroups
Subject: Strange SQL
Posted by:  andersleffl…@hotmail.com
Date: 8 Nov 2005

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.

Replies