Shulou(Shulou.com)06/01 Report--

One:

12c Migrate 19c project, compare the performance of timed jobs related to big data, and find that some job connections 19c have Socket read timed out problems, which can be repeated, and there is no abnormality in this part of job connections 12c.

The preliminary investigation is as follows:

1)jdbc throws Error: java.io.IOException: SQLException in nextKeyValue when executing some sql. The cause is Caused by: java.sql.SQLRecoverableException: IO Error: Socket read timed out

2) jdbc stack info no ora error, looks like a client-side unilateral error

3) Check the error log for several times in succession, and find that each error is reported in the 11th minute after SQL is initiated. Ask the customer if there is a timeout related test. The customer feedback is that no parameter is set in the connection string.

4) Perform a replay test on this scenario, and find that the client reports Socket read timed out, and the database session is still executing until the end of sql execution.

5) About socket read timed out It is preliminarily known that when the client initiates a query to the database, waiting for data to return exceeds a certain time limit, resulting in timeout.

6) Understand that jdbc driver version is 11.2.0.1.0

Two:

1)jdbc driver version 11.2.0.1 is incompatible with 19c, it is recommended that customers upgrade test first

2) Need to further check whether the network has timeout settings

3) Narrow the scope of investigation through the above steps

2. The discussions progressed as follows:

1) The customer denies that there is a problem with the network, but I think it is possible to continue the packet capture and troubleshooting.

2) The customer indicates that the driver package problem can be upgraded to drive the test to confirm whether the driver package problem

3) It is proposed that each error report starts at the 11th minute. The customer suggests that the application platform may have a timeout setting configured for 10 minutes. The development troubleshooting code finds that there is a timeout setting of 10 minutes. Later, the timeout time is increased and the test is conducted. It is found that the error is no longer reported.

4)Customer wonders why the same configuration does not work at 12c but works at 19c

5) Database sqlnet parameters were checked at the same time, and there was no difference between 12c and 19c.

Three:

1) Ask your middleware colleagues, indicating that there may be different timeout settings for different database versions. No relevant information can be found on this point. Ask sr, gcs indicates that there are no different settings.

2) Middleware colleagues and sr also recommend checking the operating system layer. Currently, only the database OS can be seen. The network-related parameters currently seen are not confirmed.

3)The customer tested in the test environment using version 11.2.0.4 jdbc and found that there was no timeout at 12c and a timeout at 19c

4) In order to rule out the reason why socket timeout does not take effect due to different data versions, I use a java code to specify different versions of jdbc driver to connect 12c and 19c respectively for a query operation, and set socket timeout time to 10s. It is found that jdbc driver connection 12c and 19c of 11.2.0.1 and 11.2.0.4 can timeout after querying for more than 10s. It can further explain whether timeout takes effect is independent of database version. At the same time, it has nothing to do with jdbc version, so the problem still appears in the application side configuration, and this situation is fed back to the customer.

Four:

1)Adjust java test code readtimeout from 10 seconds to 10 minutes to see if the customer problem can be reproduced.

2) Adjust to 10 minutes for test, the result is 19c normal 10 minutes timeout, 12c 10 minutes timeout.

3) Discuss with gcs jdbc engineer, suspect that 12c timeout does not take effect because client socket api keeps receiving data packets during monitoring, resulting in timeout time being continuously extended.

4) Coordinate the system engineer to perform network packet capture tests on connections 12c and 19c. It is found that when connection 12c is connected, db will send a 10 bytes tns packet to client every 2 minutes, and then client will return an ack packet. On connection 19c, db sends a keep-alive packet to client every 2 minutes. From the db side, sqlnet.expire_time=2 is currently configured, which may be related to this.

5) Discuss with gcs engineers, 12c why timeout does not take effect should be the impact of timed tns packets, and 19c timeout takes effect because keep-alive packets are relatively low-level data packets, and the client operating system directly filters them out.

Five:

1) Query the Oracle Net 12c: Changes to the Functionality of Dead Connection Detection (DCD) (Doc ID 1591874.1) and find that after 12c, the DB terminal detects dead connections by changing from sending 10 bytes tns packets every n minutes to tcp keep-alive packets, which can explain the keep-alive packets seen every 2 minutes in 19c network packet capture.

2) However, at present, connection 12c uses the traditional tns package, while 19c uses the new keep-alive package. From document 1591874.1, the new mode is used by default after 12.1. However, if the operating system does not support or think that the setting can be changed to the traditional mode of sending tns packages. At present, 12c is the solaris 11.3 operating system, which supports keep-alive. Theoretically, it should support the new mode. No more information can be found at present. It's still a mystery.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.