Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> DBLINK GIGABIT - 100MBIT NETWORK TESTS

DBLINK GIGABIT - 100MBIT NETWORK TESTS

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 14 Mar 2007 05:46:03 -0700
Message-ID: <1173876363.067196.5750@b75g2000hsg.googlegroups.com> 





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




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




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




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




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




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
Received on Wed Mar 14 2007 - 07:46:03 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US