Strange SQL
in [Oracle]
|
Prev: RAC internals - GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED
Next: FRM-40508: Oracle error: unable to insert record
From: andersleffler on 8 Nov 2005 05:15 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.
From: Laurenz Albe on 8 Nov 2005 05:50 andersleffler(a)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
From: Sybrand Bakker on 8 Nov 2005 17:20 On 8 Nov 2005 02:15:10 -0800, andersleffler(a)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
From: Connor McDonald on 9 Nov 2005 07:51 Sybrand Bakker wrote: > > On 8 Nov 2005 02:15:10 -0800, andersleffler(a)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(a)yahoo.com "Semper in excremento, sole profundum qui variat." ------------------------------------------------------------
From: Connor McDonald on 9 Nov 2005 07:52
andersleffler(a)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. Most probably your ODBC driver or equivalent. 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(a)yahoo.com "Semper in excremento, sole profundum qui variat." ------------------------------------------------------------ |