1

Topic: SQL*Net message/data to/from client

Good afternoon all guru.
There was here such question. There are files which use PLSExtproc for loading of files on a disk. There are two bases, one test, other main. Servers identical, the difference only in number of the RAM, on the test is less. And so on the test circuit programmers twisted requests and loading of 200 files began to become quickly, approximately seconds 10. Such manipulations on the main circuit did not crown success. 200 files are loaded very slowly, and there are here such waitings SQL*Net message/data to/from client. In what side to look? Tried to create a file protocol.ora in which registered parameter tcp.nodelay = YES. Told that this subjective judgement began to work more slowly, but.

2

Re: SQL*Net message/data to/from client

Network loading what, what delay between servers of loading and file servers
And yes

wrote:

...
There are files which use PLSExtproc for loading of files on a disk
...

Translate smile

3

Re: SQL*Net message/data to/from client

If FROM CLIENT then the waiter (SQL*NET) waits while the client digs in the menu (or  that the waiter brought) to transfer to the cook (basis) the order (the following order) the client. A here TO CLIENT it when the waiter (SQL*NET) very long carries dish already prepared by the cook (basis) to the client.
SY.

4

Re: SQL*Net message/data to/from client

Vadim Lejnin;
Exterior procedure is used.

5

Re: SQL*Net message/data to/from client

Vadim Lejnin wrote:

network Loading what, what delay between servers of loading and file servers

And how this delay to measure? Looked netstat-i passes of packets is not present. In network technologies it is not very strong...

6

Re: SQL*Net message/data to/from client

Andrey Lunev;
That that exterior procedure I is used understood, a question where, as well as files whence are loaded.
Judging by your message, you exterior procedure load files from file system in a DB?
It so?
If yes, the Question following:
Where physically there are files for loading? A case not on a network drive?
Well also the file system with which you is how much loaded load files.
System loading ?
Than differ for the given subsystem the bench and ?
Waitings can arise as because of disk time delays (extproc already transferred on TNS a portion in basis, and the next portion yet was not considered, and from for network time delays - reading is conducted from a network drive, a time delay with transmission on TNS. Such time delay can arise at considerable ping delay and the big packets. Read that such BDP
For example here:
Oracle Net Services 12c Best Practices for Database Performance and Scalability - section Network Acceleration
For transmission of the big packets at big delay ping, (the same RMAN, STANDBY transport), normally lift separate listener with the increased size of the buffer for transmission.
p.s. It is accepted to describe a problem in more details:
That for OS, the exact version of the DBMS, what adjustments TNS

7

Re: SQL*Net message/data to/from client

Andrey Lunev;
ping remote_server

8

Re: SQL*Net message/data to/from client

http://blog.tanelpoder.com/2008/02/07/s … it-gotcha/

9

Re: SQL*Net message/data to/from client

Andrey Lunev;
Show crude

10

Re: SQL*Net message/data to/from client

The problem is solved. Added adjustments in sqlnet.ora on server side and the client.

11

Re: SQL*Net message/data to/from client

Thanks all for the help in the task decision.

12

Re: SQL*Net message/data to/from client

Lunev wrote:

the Problem is solved. Added adjustments in sqlnet.ora on server side and the client.

Andrey, in a forum it is accepted to uncover a solution of a problem that, those who catch a similar problem could solve it independently

13

Re: SQL*Net message/data to/from client

Vadim Lejnin wrote:

it is passed...
Andrey, in a forum it is accepted to uncover a solution of a problem that, those who catch a similar problem could solve independently it

Without problems. Simply, often I see here that write "thanks for the help, I solved all", and decisions is not written. Here and I on same so wrote. And so it is not a pity to me, use, can to someone it is valid helps.
On server side and the client in sqlnet.ora added here these lines:

DEFAULT_SDU_SIZE=32767
RECV_BUF_SIZE=65536
SEND_BUF_SIZE=65536

Plus esteemed here these things:
https://docs.oracle.com/cd/B19306_01/ne … rmance.htm
https://sites.google.com/site/embtdbo/w … ts#TOC-SDU
In listener.ora and tnsnames.ora did not begin to add, because it should to restart  that it would not be desirable. But it was possible and so to make, therefore it was restricted to the first variant.
Once again all thanks for the help and a direction in the necessary channel...

14

Re: SQL*Net message/data to/from client

Lunev wrote:

it is passed...
Without problems. Simply, often I see here that write "thanks for the help, I solved all", and decisions is not written. Here and I on same so wrote. And so it is not a pity to me, use, can to someone it is valid helps.
On server side and the client in sqlnet.ora added here these lines:

DEFAULT_SDU_SIZE=32767
RECV_BUF_SIZE=65536
SEND_BUF_SIZE=65536

...
In listener.ora and tnsnames.ora did not begin to add, because it should to restart  that it would not be desirable. But it was possible and so to make, therefore it was restricted to the first variant.
...

Andrey, nevertheless to twist such parameters for all clients, not the right decision,  it can cause time delays for tasks which periodically exchange only small, short packets, for example Forms. Especially, if they work on slow lines.
Plus, by transmission of the big data from the server on the client, arises an overload of buffers TCP of the client at which are not adjusted big  that leads to network waitings at such operations.
The raising selected listener on other port and adjustment special TNS Alias, only for those connections which demand it would be more adequate decision, , nevertheless.
It is unique, you will need to think over, whether dynamic registration of services on it is necessary. Normally, it it is not required, for the auxiliary purposes as a rule are used static registration, but basically it too can be adjusted.
listener, thus it is possible not to touch the main at all, and operation with it remains same, as before.
In addition, two listener, allow to restrict connections from clients, for example at creation standby if to stop the main listener.
Minus in that it is necessary to supervise

15

Re: SQL*Net message/data to/from client

Vadim Lejnin wrote:

it is passed...
Andrey, nevertheless to twist such parameters for all clients, not the right decision,  it can cause time delays for tasks which periodically exchange only small, short packets, for example Forms. Especially, if they work on slow lines.
Plus, by transmission of the big data from the server on the client, arises an overload of buffers TCP of the client at which are not adjusted big  that leads to network waitings at such operations.
The raising selected listener on other port and adjustment special TNS Alias, only for those connections which demand it would be more adequate decision, , nevertheless.
It is unique, you will need to think over, whether dynamic registration of services on it is necessary. Normally, it it is not required, for the auxiliary purposes as a rule are used static registration, but basically it too can be adjusted.
listener, thus it is possible not to touch the main at all, and operation with it remains same, as before.
In addition, two listener, allow to restrict connections from clients, for example at creation standby if to stop the main listener.
Minus in that it is necessary to supervise

Thanks, I will be  a situation if there will be such necessity I will do, as you told.