Re: DBLINK GIGABIT - 100MBIT NETWORK TESTS

On Mar 14, 1:46 pm, "hopehope_123" <hopehope_..._at_yahoo.com> wrote:
> Hi ,
>
> This is a long mail , including some test results and question about
> gigabit network performance with dblink .
> Sorry and please dont read it if you don't interest , i don't want to
> disturb the group or anyone.
>
> I want to transfer some of the tables between two systems by using
> dblink over gigabit network. The source system is a 2 node rac redhat
> linux system , the destination is a single sun solaris . My problem
> is there is no performance difference between the gigabit network and
> normal 100Mbit network. Iwant to understand whether gigabit network
> is really in use , if in use why does not work fast. Let me describe
> you what i have done .
>
> First of all , in order to be sure whether the gigabit really works ,
> i made a ftp transfer test .
>
> The source system ( redhat linux)
> 100Mbit ip: AA
> Gigabit ip: BB
>
> The destination system ( sun solaris)
> 100Mbit ip: CC
> gigabit ip: DD
>
> 1. FTP TEST
>
> I made an ftp connection from the sun box to the linux box , and
> transferred a large file , during the transfer i use both servers
> local disks , storage system (emc ) is not included.
>
> on sun box:
>
> ftp BB
> Connected to BB
> 220 tanidw1 FTP server (Version wu-2.6.1-21) ready.
> Name (BB:oracle): oracle
> 331 Password required for oracle.
> Password:
> 230 User oracle logged in.
> Remote system type is UNIX.
> Using binary mode to transfer files.
> ftp> cd /home/tolga/
> 250 CWD command successful.
> ftp> mget test.txt
> mget test.txt? y
> 200 PORT command successful.
> 150 Opening BINARY mode data connection for test.txt (230877887
> bytes).
> 226 Transfer complete.
> local: test.txt remote: test.txt
> 230877887 bytes received in 9.5 seconds (23808.85 Kbytes/s)
>
> So , 23MB per second is the transfer rate. And it takes 9.5 seconds to
> transfer 230MB. file.
>
> the same transfer takes more than 20 secs if i use 100mbit interface,
> i dont add the details in order to keep the mail short.
>
> 2. LOCAL CTAS TEST ON SOURCE DB
>
> now , i want to create a table by using ctas in sun server with
> gigabit interface. First of all , on source db which is linux , i
> tested how long does it take to create the copy of the table : ( The
> table is about 80MBytes)
>
> y.sql is the ctas script:
>
> y.sql :
> alter session set events '10046 trace name context forever , level
> 12';
> create table gecici.urunydk nologging tablespace data noparallel as
> select /*+full(u) noparallel(u)*/ * from tanidwh.urun u;
> exit 0;
>
> In order to eliminate the disk io , here i use noparallel , also the
> 80Mb. file is cached inside the sga according to the:
>
> SELECT inst_id, count(*) FROM GV$CACHE WHERE NAME ='URUN' group by
> inst_id
>
> INST_ID COUNT(*)
> 1 4859
> 2 16
> total: 4875
>
> (4875 * 16k blocks ) in bytes
>
> So the table is in sga .
>
> time sqlplus tanidwh/dwh1 @y.sql
>
> SQL*Plus: Release 9.2.0.5.0 - Production on Wed Mar 14 11:18:47 2007
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> With the Partitioning, Real Application Clusters, OLAP and Oracle Data
> Mining options
> JServer Release 9.2.0.5.0 - Production
> Session altered.
> Table created.
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 -
> 64bit Production
> With the Partitioning, Real Application Clusters, OLAP and Oracle Data
> Mining options
> JServer Release 9.2.0.5.0 - Production
>
> real 0m4.489s
> user 0m0.019s
> sys 0m0.004s
>
> and this is the trace output:
>
> create table gecici.urunydk nologging tablespace data noparallel as
> select /*+full(u) noparallel(u)*/ * from tanidwh.urun u
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 1 2.00 3.97 0 5105
> 2117 728311
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 2.00 3.97 0 5105
> 2117 728311
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 46
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> library cache pin 1
> 0.00 0.00
> row cache lock 156
> 0.00 0.00
> control file sequential read 15
> 0.01 0.02
> enqueue 119
> 0.00 0.01
> global cache open x 37
> 0.00 0.00
> KJC: Wait for msg sends to complete 65
> 0.00 0.01
> global cache cr request 3
> 0.00 0.00
> global cache s to x 7
> 0.00 0.00
> direct path write 2
> 0.00 0.00
> control file parallel write 5
> 0.00 0.00
> rdbms ipc reply 1
> 0.00 0.00
> SQL*Net message to client 1
> 0.00 0.00
> SQL*Net message from client 1
> 0.00 0.00
> ***************************************************************************­*****
>
> No disk io , only reading the sga , 80 mb. table ctas takes 4.4 sec.
>
> 3. REMOTE CTAS TEST ON TARGET DB BY USING 100MBIT AND GIGABIT NETWORK
>
> in solaris box , in order to use gigabit interface i changed the
> tnsnames.ora :
>
> this is gigabit network:
>
> TANIDWHBCK =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = BB)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = TDWH)
> )
> )
>
> this is the 100Mb network:
>
> TANIDWH =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = AA)(PORT = 1521))
> (LOAD_BALANCE = yes)
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = TDWH)
> )
> )
>
> and i have created two dblinks:
>
> create public database link DWH9I
> connect to zzzzz
> identified by "<pwd>"
> using 'tanidwh';
>
> create public database link DWH9IBCK
> connect to zzzzz
> identified by "<pwd>"
> using 'tanidwhbck';
>
> now it really does not matter which link i use, both have the same
> results:
>
> with 100MBit :
>
> create table merkez.testurun tablespace crm_tfs_genel nologging
> noparallel as
> select /*+full(u) noparallel(u)*/ * from tanidwh.urun_at_dwh9i u
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.02 0.06 0 4
> 1 0
> Execute 1 12.21 12.58 0 981
> 6181 728311
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 12.23 12.65 0 985
> 6182 728311
>
> Misses in library cache during parse: 1
> Optimizer mode: CHOOSE
> Parsing user id: 136
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> control file sequential read 13
> 0.00 0.01
> SQL*Net message to dblink 25
> 0.00 0.00
> SQL*Net message from dblink 25
> 0.06 0.07
> SQL*Net more data from dblink 22634
> 0.07 0.93
> direct path write 26
> 0.00 0.00
> control file parallel write 3
> 0.00 0.00
> control file single write 1
> 0.00 0.00
> log file sync 2
> 0.00 0.00
> SQL*Net message to client 1
> 0.00 0.00
> SQL*Net message from client 1
> 0.00 0.00
> ***************************************************************************­*****
>
> with gigabit :
>
> create table merkez.testurun tablespace crm_tfs_genel nologging
> noparallel as
> select /*+full(u) noparallel(u)*/ * from tanidwh.urun_at_dwh9ibck u
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.03 0.25 0 4
> 1 0
> Execute 1 12.52 13.01 0 981
> 6181 728311
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 12.55 13.27 0 985
> 6182 728311
>
> Misses in library cache during parse: 1
> Optimizer mode: CHOOSE
> Parsing user id: 136
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> control file sequential read 13
> 0.01 0.01
> SQL*Net message to dblink 25
> 0.00 0.00
> SQL*Net message from dblink 25
> 0.43 0.45
> SQL*Net more data from dblink 22646
> 0.07 0.79
> direct path write 5
> 0.00 0.00
> control file parallel write 3
> 0.00 0.00
> control file single write 1
> 0.00 0.00
> log file sync 2
> 0.00 0.00
> SQL*Net message to client 1
> 0.00 0.00
> SQL*Net message from client 1
> 0.00 0.00
> ***************************************************************************­*****
>
> Both traces show more or less same numbers. It takes 12-13 secs. to
> create the table . Local table creation takes 4 sec. Why does not
> gigabit improve the performance?
>
> 3. LOCAL CTAS TEST ON TARGET DB
>
> In order to understand , whether gigabit / 100mbit network matters
> or disk write , i simply created another table from this table on sun
> box.
>
> merkez.testurun table is in sga
>
> SELECT COUNT(*) FROM V$BH
> WHERE OBJD=948238
>
> 4832
>
> create table merkez.testurun1 tablespace crm_tfs_genel nologging
> noparallel as
> select /*+full(u) noparallel(u)*/ * from merkez.testurun u
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.00 0 1
> 0 0
> Execute 1 2.04 2.01 0 4918
> 5747 728311
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 2.05 2.01 0 4919
> 5747 728311
>
> Misses in library cache during parse: 1
> Optimizer mode: CHOOSE
> Parsing user id: 136
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> control file sequential read 13
> 0.01 0.02
> direct path write 54
> 0.00 0.00
> control file parallel write 3
> 0.00 0.00
> control file single write 1
> 0.00 0.00
> log file sync 1
> 0.00 0.00
> SQL*Net message to client 1
> 0.00 0.00
> SQL*Net message from client 1
> 0.00 0.00
> ***************************************************************************­*****
>
> So it takes 2 secs.
>
> In summary ,
>
> In source db , creating a new local copy of the table takes 4sec.
> in target db , creating a new local copy of the table takes 2sec.
> but creating a new remote copy of the source table in destinaton by
> using dblink , takes 12-13 sec. both for gigabit and 100mbit
> network.
>
> I think i have successfully eliminated the disk io , i mean table is
> completely in sga .
>
> Why does the dblink operation take 12 sec? Where does 12-4=10 sec
> spend? In traces :
>
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
>
> SQL*Net more data from dblink 22646 0.07
> 0.79
>
> is same for both gigabit and 100mbit. Is there any other parameters
> that must be tuned for better network access ?
> Does the dblink really use gigabit network?
>
> Kind Regards,
> hope

Sqlnet is sqlnet is sqlnet. It communicates by default in units of 2048 bytes. Your 'sqlnet more data to dblink' waits show one record doesn't fit in one unit. So you have latency for every record. If your network card's MTU is lower than 2048, your network layer will fragment the 2048 bytes Oracle is sending. This will again result in latency. This is *exactly* the cause why your local CTAS is so much faster.
You can upgrade your connection to any speed: if you don't tune sql*net properly, nothing will happen. Most likely to the actual speed did increase the latency will remain the cpu, as the latency is dependent on cpu-speed.
So you need to make sure your SDU (set in tnsnames.ora and listener.ora) is a multiple of the MTU of the network card.

And guess what: this is all documented.
But you are one of the many people here that rathers dumps their perceived problem right away in this forum, assuming you are the first one who hit this, instead of reading the docs.

--
Sybrand Bakker
Senior Oracle DBA