TUNING SQL*NET - Oracle FAQ

loyalsockvillemobNetworking and Communications

Oct 27, 2013 (3 years and 7 months ago)

62 views



TUNING SQL*NET

Data communication between Client and Server is done 5 layer as shown Figure 1.

7 Layer OSI(Open System Interconnection) which is defined by ISO was held as 5 layer in this issue.




CLIENT








SERVER


Application




Application

SQL*Net




SQL*Net

ULP




ULP

LLP




LLP

Physical Layer




Physical Layer




Figure 1 :


Physical Layer



: The lowest level of machine makes processing such as volt level.

LLP (Lower Layer Protocol)

: A layer That defines the last size

of data to physical layer.It’s Buffer


size is Ethernet,TokenRing etc. buffer size.

ULP(Upper Layer Protocol)

: A layer on which network protocol is loaded.It’s Buffer size is


TCP/IP,IPX/SPX etc. buffer Size.

SQL*Net Layer



: A layer that transmit d
ata between Application Layer and
ULP


layer.It’s Buffer size is value of SDU in Tnsnames.ora and


Listener.ora. If These are different, minimum is selected.

Application Katmanı


: A layer that holds absolute data.


While Application Layer transmits absolute data, others transmit data and header.

Each Layer has buffer size.If Buffer size is exceeded fragmentation occurs.

A table and Algorithm about Data Tranmis
sion were given below :



LAYER

FUNCTION

Oracle7 Server Application

Data :
=ne_aktar(Data,Buffer
App.,
0)

SQL*Net

Data :
=ne_aktar(Data,Buffer
SQL*Net
,Header
SQL*Net
)

ULP

Data :=ne_aktar(Data,Buffer
ULP
,
Header
ULP
)

LLP

Data :=ne_aktar(Data,Buffer
LLP
,
Head
er

LLP
)



Figure 2 :









Procedure Bul;

Begin

Data := 1436;

Data = ne_aktar(Data,1000,0);

/* Data Application Layer to SQL*Net Layer */

Data = ne_aktar(Data,1000,10);

/* Data SQL*Net Layer to ULP Layer*/

Data = ne_aktar(Data,1500,40);

/* Data ULP lay
er to LLP Layer*/

Data = ne_aktar(Data,1500,14);

/* Data LLP layer to Phsysical Layer*/

end;


Function ne_aktar(Data,Buffer,Header) is integer

/* ne_aktar returns how much data will be transmitted.

/* Data_remainder occurs when fragmentation occurs*/

Begin


if Buffer >= (Data+Header)


return(Data+Header)


else

Begin



Data_remainder:=Data
-
(Buffer
-
Header);



ne_aktar(Data_remainder,Buffer,Header);

return(Buffer
);



end;

end;


Example 1 :


-
Let Server send 1436 byte to Application Layer

-
Let Buffer
APP

= 1000 byte

-
Let Buffer
SQL*NET

= SDU = 1000 byte


Header
SDU

= Header
SQL*Net

= 10 byte

-
Let Buffer
ULP

= Buffer
TCP/IP

= 1500 by
te


Header
ULP

= Header
TCP/IP

= 40 byte

-
Let Buffer
LLP

= Buffer
Ethernet

= 1500 byte


Header
LLP

= Header
Ethernet

= 14 byte


This example makes a tree as below :


Layer 1:




ne_aktar(1436,1000,0)












1000



436











Layer 2:


ne_aktar(1
000,1000,10)


ne_aktar(436,1000,10)





1000



20




446














Layer 3:

ne_aktar(1000,1500,40)

ne_aktar(20,1500,40)


ne_aktar(446,1500,40)














1040




60




486














Layer 4:

ne_aktar(1040,1500,14)


ne_aktar(60,1500,14)

ne_aktar(486,1500,14)


Result :


1054



74




500

Free Space :



446



1426




1000


As shown our example above, 1436 byte was separated three packages(1054+74+500=1628)

But Each package can contain more data.There is free sapace ea
ch package.


We can tune this example as below :


-
Let Server send 1436 byte to Application Layer

-
Let Buffer
App

= 1500 byte

-
Let Buffer
SQL*Net

= SDU = 2000 byte

-
Let Buffer
ULP

= Buffer
TCP/IP

= 1500 byte

-
Let Buffer
LLP

= Buffer
Ethernet

= 1500 byte

-
H
eaders are not changing.


This eaxample makes a list as below :


Layer 1:





ne_aktar(1436,1500,0)








1436



Layer 2:





ne_aktar(1436,2000,10)








1446



Layer 3:





ne_aktar(1446,1500,40)








1486



Layer 4:





ne_aktar(1486,1500,14)


Tr
ansmitted

:





1500

Free space

:






0


As seen our example,same data was trasmitted as one package.

Application Developer should calculate data size to Application Layer well.

Also,



Make Sql_Trace False



Tnsnames.ora should be small



If There is no ne
ed, Encyryption/decryption should be off

Note : Default SDU is 2K.


For SQL*Net 2.3.x,Maksimum SDU is 32K.


SQL*Net 2.3 and below, Maximum and default value is 2K.











For Listener.ora :






For Tnsnames.ora :


SID_LIST_LISTENER=





Oracle.world=


(SID_LIST=







(DESCRIPTION=


(SID_DESC=







(SDU=4096)


(SDU=4096)








(ADDRESS=



(SID_NAME=ORCL)







(PROTOCOL=TCP)




(ORACLE_HOME=.........)





(HOST=LINKPLUS)


)









(
PORT=1526)


)










)











(CONNECT_DATA=












(SID=ORCL)











)








)











































Danisment Gazi Unal








Database Analyst








unal@linkplus.com.tr