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

And this is part of what I was referring to when I said that one needs to tune the query. In a distributed query, there is the part that gets executed locally, and the part the gets executed remotely. One needs to tune the local and the remote ends of the query. In your case, the join was quicker to be done remotely then to bring back the data and join it locally.

Cheers,
Brian

> Tanel Poder wrote:
>
> 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
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> 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
>
> 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 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
>
> 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 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.
>
> 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 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
>
> 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 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
>
> 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 30
>
> SQL> select name1, name2 from v_at_l;
>
> 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 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.
>

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"