Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Multiple schema and Query performance issue

RE: Multiple schema and Query performance issue

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 13 Oct 2006 18:52:51 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51801DBB123@ex2003.metratech.com>


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_at_il.proquest.com] 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 13 2006 - 17:52:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US