Synonyms for remote synonyms

In my last post i have mentioned about the sql*net message from client waits in Oracle Forms. I was working on the same problem and found out that the problem was related to the synonym the form accesses.

I have a synonym that points to a remote synonym in another database which points to a table in another schema in the same remote database.

In the remote database, i have a table on user_b which i want to to select from user_a. So, i create a synonym for it at user_a.

User_A: create synonym tab1 for user_b.tab1; --(assuming select privilege on user_b.tab1 has been granted to user_a)

In the local database (which the form connects):

Create database link remotedb.world
Connect to user_a identified by user_a
Using ‘remotedb’;

Create synonym tab1 for tab1@remotedb.world;

Then i create a simple form which connects to the user at the local database and uses tab1 as its base table. The form queries one row from that table and updates that row. The sql trace output for this form shows (the sql trace contains a select for update statement for one row and an update statement for that row):

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     167        0.00          0.00
  SQL*Net message from client                   167        0.07          0.26
  SQL*Net message to dblink                      59        0.00          0.00
  SQL*Net message from dblink                    59        0.09          0.23
  latch free                                      1        0.00          0.00
  log file sync                                   1        0.12          0.12

I see that there are 167 waits for the sql*net message from client event and 59 waits for the sql*net message from db link event . The time waited here is much much worse in remote clients where the bandwidth is not much.

In the local database, if i recreate the synonym by specifying the table owner explicitly, there is a boost in performance:

Create or replace synonym tab1 for user_B.tab1@remotedb.world;

When i run the form again:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      14        0.00          0.00
  SQL*Net message from client                    14        0.31          0.48
  SQL*Net message to dblink                       8        0.00          0.00
  SQL*Net message from dblink                     8        0.02          0.06

Look at the number of waits, they are much much fewer than before.

So, if you are using Oracle Forms and use a synonym to a remote synonym as your base table, check if that synonym is created with the table owner specified or not. We had a huge performance improvement by recreating the synonym by specifying the table owner as you see.

(Both the local and remote databases are 9.2.0.5 in this test).