Re: (long and boring) Any way to reduce waits over the link? (SQL*Net message from dblink)

Home -> Community -> Usenet -> c.d.o.server -> Re: (long and boring) Any way to reduce waits over the link? (SQL*Net message from dblink)

From: Tanel Poder <change_to_my_first_name_at_integrid.info>
Date: Wed, 10 Sep 2003 22:42:36 +0300
Message-ID: <3f5f7eaf_1@news.estpak.ee>

Here's a little proof to my post (that selecting from a view in remote database which is doing join of some tables, causes fewer sqlnet roundtrips than doing the join directly, without a view):

C:\Work\Oracle>sqlplus "admin/admin"

SQL*Plus: Release 9.2.0.4.0 - Production on K Sep 10 22:15:25 2003

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create table t1 (id number, name varchar(10));

Table created.

SQL> create table t2 (id number, name varchar(10));

Table created.

I create a database link which actually references to my own schema, but it will do the job.

SQL> create database link l connect to admin identified by admin using 'orcl';

Database link created.

SQL> select n.name, s.value from v$sesstat s, v$statname n where n.statistic# = s.statistic# 2 and s.sid = (select sid from v$mystat where rownum < 2) 3 and n.name like 'SQL*Net roundtrips to/from dblink';

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                         0

SQL> select t1.name name1, t2.name name2 from t1_at_l, t2_at_l where t1.id = t2.id;

no rows selected

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        12

First select to these 2 tables causes 12 sqlnet roundtrips

SQL> select t1.name name1, t2.name name2 from t1_at_l, t2_at_l where t1.id = t2.id;

no rows selected

SQL>
SQL> select n.name, s.value from v$sesstat s, v$statname n where n.statistic# = s.statistic# 2 and s.sid = (select sid from v$mystat where rownum < 2) 3 and n.name like 'SQL*Net roundtrips to/from dblink';

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        17

Second one only 5

SQL> select t1.name name1, t2.name name2 from t1_at_l, t2_at_l where t1.id = t2.id;

no rows selected

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        22

Third and any subsequent steps take also 5 roundtrips - so the cost for this kind of operation is about 5 RT per query. Now let's create a view to remote server which does exactly the same join (since I made a loopback dblink to my own server then I am both local and remote server, though using different sessions)

SQL> create view v as select t1.name name1, t2.name name2 from t1, t2 where t1.id = t2.id;

View created.

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        23

Note that after creating the view, SQL*Net roundtrips increased by one for some reason? This is because when you select anything over database link, a distributed transaction is started. But since "create view" is a DDL command, it issues a implicit commit, thus causing an acknowledgement to be sent to remote server. You can verify this from v$transaction when you've run a select over dblink.

SQL> select name1, name2 from v_at_l;

no rows selected

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        27

When selecting exactly the same "data" from a view, we get only 3 additional roundtrips

SQL> select name1, name2 from v_at_l;

no rows selected

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        30

SQL> select name1, name2 from v_at_l;

no rows selected

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

SQL*Net roundtrips to/from dblink                                        33

SQL> select name1, name2 from v_at_l;

no rows selected

And 3 it remains. So the number of sqlnet roundtrips (thus waits) went down 40% for this simple set of tables.

Cheers,
Tanel. Received on Wed Sep 10 2003 - 14:42:36 CDT