Re: Strange SQL

On 8 Nov 2005 02:15:10 -0800, 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.

recursive sql to query the data dictionary, for table and column info that isn't cached in the shared pool. Can be a sign of unnecessary redundant statement parsing in your app.

--
Sybrand Bakker, Senior Oracle DBA