Home » RDBMS Server » Performance Tuning » Veiw synonym performs differently in diferent users
Veiw synonym performs differently in diferent users [message #215124] Fri, 19 January 2007 07:05 Go to next message
omkar@tcs
Messages: 16
Registered: September 2006
Junior Member

HI all,

I have a view and its public synonym. If the public synonym is queried from other user than its owner, the performance is degraded.

If I query the view from the owner, it takes less thime to execute.

Can any body explain me why this might be happening?

Regards.

Omkar
Re: Veiw synonym performs differently in diferent users [message #215129 is a reply to message #215124] Fri, 19 January 2007 07:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quite a known issue with public synonyms.
Public synonyms employ a mechanism called Negative Dependency Check, which is quite complex and takes more resource to resolve dependencies in DBA_OBJECTS. This check will make sure that no other object is having the same name (since synonyms will mask the original name).
Re: Veiw synonym performs differently in diferent users [message #215273 is a reply to message #215129] Sat, 20 January 2007 06:48 Go to previous messageGo to next message
omkar@tcs
Messages: 16
Registered: September 2006
Junior Member
Dear Mahesh,

Thanks a lot for your response.
I will try to collect more information on this regard.

Thank a lot,
Regards.

Omkar
Re: Veiw synonym performs differently in diferent users [message #215287 is a reply to message #215273] Sat, 20 January 2007 09:31 Go to previous messageGo to next message
omkar@tcs
Messages: 16
Registered: September 2006
Junior Member
Dear Mahesh,

I got the following inforrmation use of Public Synonyms.

1)For Public Synonym to be accessed,
the synonym must be loaded into the library cache,
and a dependency structure must be established.

2)Each library cache lookup establishes a lock structure,
for which memory must be allocated.
Each library cache object access establishes a pin structure,
which also needs memory.

3)For a statement that refers to a single table only,
the CPU usage during the parse phase is approximately
1:2:4 for a fully-qualified reference, a private synonym and a public
synonym respectively.For complex SQL statements, the cost
of synonym usage is even greater.

4) In an instance with high parse rates, the use of
synonyms increases CPU usage dramatically, and often
causes contention on the latches that control access to the library
cache, dictionary cache and shared pool.

Shall I safely assume that due to contention mentioned in the point number 4 is
causing performance delay in my case?

Please guide me.

Regards.
Omkar.
Re: Veiw synonym performs differently in diferent users [message #215484 is a reply to message #215287] Mon, 22 January 2007 08:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Shall I safely assume that due to contention mentioned in the point number 4 is causing performance delay in my case?

You might Smile.
Without a detailed analysis, i cannot exactly say what is happening. If you are still worried about performace and want to prove your theory, set up a test case with ALTER SESSION SET CURRENT_SCHEMA.
compare the results.
Re: Veiw synonym performs differently in diferent users [message #215522 is a reply to message #215484] Mon, 22 January 2007 12:45 Go to previous message
omkar@tcs
Messages: 16
Registered: September 2006
Junior Member
Dear Mahesh,

Can u plz elaborate on test cases?

My query is being called from Oracle applications form.

Regards.

Omkar
Previous Topic: SQL performance tuning
Next Topic: Materialized view refresh methods
Goto Forum:
  


Current Time: Tue Dec 06 02:55:58 CST 2016

Total time taken to generate the page: 0.09902 seconds