Re: Strange SQL

Sybrand Bakker wrote:
>
> 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

unlikely to be the database itself - it would more likely to be going at tab$, con$ and the like

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------