RE: Multiple schema and Query performance issue
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Mon, 16 Oct 2006 10:34:05 -0400
We are using the Oracle driver and I found the following article that
mentioned that oracle already considered this as bug and issued a patch.
http://blog.nominet.org.uk/tech/Database/2006/06/19/Oracle-ODBC-Performa
nce-Problems-in-Oracle-10-2.html?page=comments
________________________________
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Monday, October 16, 2006 1:26 AM
To: Harvinder Singh
Cc: Bobak, Mark; oracle-l
Subject: Re: Multiple schema and Query performance issue
That sounds a little bit like the way the Microsoft ODBC driver used to
behave. If I'm right you might well gain benefits from trying a current
Oracle driver (9.2 or 10.2 depending) - you might however also run into
support issues with the vendor if this is thrid party (and of course
deploying a new Oracle client/odbc driver isn't a simple matter for
desktop support people).
On 10/13/06, oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx > wrote:
Mark,
You might be correct, these statement may be generated by odbc and they
are taking bulk of the time. Is there any option in odbc that we can
disable running this kind of queries or this is normal possible behavior
of odbc?
Thanks
--Harvinder
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Thursday, October 12, 2006 6:35 PM
To: Harvinder Singh; oracle-l
Subject: RE: Multiple schema and Query performance issue
You see this recursive SQL on every parse of the SQL, not just the
initial (hard) parse?
What language is the application implemented in? Perl/DBI? Java?
Pro*C? Something else? Sometimes, client layers can turn on extra
levels of checks that cause (sometimes excessive) recursive SQL to
occur.
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
There is nothing so useless as doing efficiently that which shouldn't be
done at all. -Peter F. Drucker, 1909-2005
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto: oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of Harvinder Singh
Sent: Thursday, October 12, 2006 6:30 PM
To: Harvinder Singh; oracle-l
Subject: RE: Multiple schema and Query performance issue
Update to the original post
-------------------------------
I can also see the same behavior in tkprof output even running the query
using table of same schema but using schema name with object name. For
example:
Select col1 from schema1.tab1
But I am not able to reproduce the issue when run the same query from
sqlplus and this only happens when running the query from application.
-----Original Message-----
From: Harvinder Singh
Sent: Thursday, October 12, 2006 6:14 PM
To: oracle-l
Subject: Multiple schema and Query performance issue
Hi,
In our application we have 1 main schema (schema1) and 3 other
schema(schema2,schema3,schema4) in nologging mode for performance
reasons. Now when I try to run the following query connected as schema1
that should take less than few ms:
SELECT col1,col2
FROM
Schema2.tmp_tab1
Optimizer run the following recursive query and that takes 3-4 sec:
SELECT /*+ RULE */ COUNT(*)
FROM
ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND SYNONYM_NAME=
'TMP_tab1' AND OWNER='SCHEMA2'
Is this a normal behavior when running the query that uses table of
another schema as 3-4 sec looks very high time and explain plan showing
lot of access to internal tables.
What are the possible solutions to make sure that optimizer don't need
to run the recursive query?
Thanks
--Harvinder
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
