OBIEE Query Performance degrades when using synonyms

Sravan Gereddy

Hello All,

I have 2 schemas(say Schema1 and Schema2) where, Schema1 has the actual table and Schema2 has a synonym for the table created in Schema1. For example, I have a table Schema1.Tbale1, then it has a synonym in Schema2 as Schema2.Table1.

When trying to generate a direct database request from analyitics using the synonyms, the report is taking too long to return results(typically 168-266 secs), whereas the same database request using the actual table( Schema1.Tbale1), the report returns results in a couple of seconds(~1.2-1.5 secs).

But, executing the 2 physical queries in SQL Developer is returning results in a couple of seconds(~1.2-1.5 secs) irrespective of the schema being used.

I'd like to know where exactly could be the issue which is causing performance degradation. Would be glad to hearing a response.

Thanks,

SG