Procedures:

WinForm program (user management system) written by C #, the back-end WCF service provides data for it, and Oracle related operations are completed in WCF server.

Problems encountered: Recently, a user reported an exception, describing that a certain search interface of WinForm (to call WCF service to find data in Oracle) has been waiting. Someone in the DBA also reported that some Oracle sessions that call the stored procedures related to the user management system have been running for three hours, which has affected the Oracle database, and these processes need to be terminated manually.

Finally, the reasons are found

A large amount of data is suddenly inserted into the table of Oracle database, which makes the query slow. Then the user clicks the search button when searching in the WinForm interface, and the WCF service will be called to retrieve the database. However, the retrieval is very slow. After waiting for 3 minutes, the user doesn’t want to wait any longer, so he closes the interface directly. Notice that in the process,

Although WinForm forced the client to shut down during the connection with WCF, the server code of WCF was running half way and waiting for the result returned by Oracle. The WCF was not shut down and was still running. The connection with oracle was always connected.

Let’s talk about Oracle first. If C # calls one of its stored procedures, a connection or session will be established. Oracle has a mechanism to automatically clean up inactive sessions. Although it is not real-time, it has this mechanism. If the session between c#and Oracle is continuous, the session will not be cleaned up. If C # calls a time-consuming stored procedure, but C # exits in the middle of the way, the session will become inactive and will be cleaned up by Oracle at the right time.

Now the problem is that the user exits in the WinForm interface, but it is still executing in WCF, so the connection between WCF server and Oracle is always active session, so the stored procedure has been executing for three hours. At this time, the user opens a new WinForm interface and tries to query again. WCF will open a new session with Oracle, Until the available connection pool is used up (or the maximum number of connections that the Oracle server can accept at the same time), the WCF server and the Oracle session will be in the state of waiting for available connections, and then the WinForm interface will naturally be in the state of waiting for no response.

Solution:

Add the timeout attribute to WCF’s code to connect to Oracle. When the code is executed for more than 5 minutes, the connection will be automatically disconnected. After that, the inactive session in Oracle will be cleared, and the connection pool of C # WCF server will be enough, and there will be no waiting state.

Reasons for slow stored procedures and queries in databases:

This table has a large amount of data, and a large amount of data has just come in. Oracle sometimes encounters this problem. The same query statement was executed very slowly last time, and it may be completed very soon next time (I don’t know much about this DBA category). If a statement occupies the table for three hours, then other people’s session is also very slow to execute the query statement (maybe other people’s session is lucky and should have been right soon). Now use timeout to end this session, and other sessions will execute query statements, which may be faster.

https://stackoverflow.com/questions/12660636/oraclecommand-timeout?utm_ medium=organic&utm_ source=google_ rich_ qa&utm_ campaign=google_ rich_ qa  comand tiem out

https://forums.devart.com/viewtopic.php?t=25872