Skip to main content

I was seeing some Oracle waits along the lines of Sql.*net more data from client.  It seemed to be particularly bad when attaching large documents.  I ran across a tunable Oracle parameter called session data unit. It controls the packet size between the database server and the client. The default is 8192 bytes.  You can go to 65535 bytes maximum.   Both the client and server have to agree on the size or it will use the default.   

On the oracle server side I added 

SQLNET.AUTHENTICATION_SERVICES = (NTS)
DEFAULT_SDU_SIZE=32767
to sqlnet.ora

I then changed the connection string on the middle tier server to 

data: jdbc:oracle:thin:@(DESCRIPTION=(SDU=32767)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX,XXX,XXX,XXX)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=XXXX)))

 

I am seeing less errors and have anecdotal reports that uploading documents is working better as well as improved performance in general so thought I would share

 

Thanks for reporting your findings and good that the change works for you. It's interesting why others don't need to do this, any guess?

Thanks for reporting your findings and good that the change works for you. It's interesting why others don't need to do this, any guess?

We only noticed it because we scanned a bunch of documents at one time to catch up after the upgrade and our Solarwinds Database Perfomance Analyzer software picked up on the wait time.  

I then received feedback from users that attaching documents worked better after the change.  

I don’t know that anyone “needs” to do this but I believe it may be helpful for everyone. If your Oracle server and Middleware are on the same network I don’t see how it could hurt.

Realistically a  DBA that knows far more about oracle communications than I do could run some experiments and define improvements, ideal SDU size etc but I don’t have the resources to investigate it that deeply. 

Thanks!

And for others watching, doing this change will only have an impact on document transfers if you keep the documents in Oracle. If they are kept in an FTP or network share, there should be no impact.

 

Thanks!

And for others watching, doing this change will only have an impact on document transfers if you keep the documents in Oracle. If they are kept in an FTP or network share, there should be no impact.

 

Correct however, and this is completely anecdotal , my impression is that it has increased performance across the board.  Screens a bit snappier etc 

@alex

> Correct however, and this is completely anecdotal , my impression is that it has increased performance across the board.  Screens a bit snappier etc 

Sounds great! And it's only the perceived performance that counts 🙂 Jokes aside, if Oracle can move data in larger chunks it should mean fewer requests and that might make things feel snappier.

Reply


Terms & ConditionsCookie settings