Multiple schema and Query performance issue
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Oct 2006 18:14:12 -0400
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
Other related posts:
