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_at_freelists.org <oracle-l-bounce_at_freelists.org> 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_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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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