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.
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
- The problem of [connection lost contact] after C # code connecting Oracle database for a period of time
- To solve the problem of C # calling excel interface error, prompt: the COM object of Microsoft. Office. Interop. Excel. Applicationclass is forcibly converted to the interface type “Microsoft. Offi”
- C language — to solve the problem of program flashback when programming (in VS)
- Failed to create Oracle Oracle home user solution appears in oracle12c installation
- Solve the problem of VC 6.0 fatal error C1033: cannot open program database’.\debug\vc60.pdb’
- How to solve the problem that the output results of C + + program written in VS2010 flash by
- When installing oracle12c, the problem of “unable to check whether the specified location is on CFS” appears
- Systemctl command doesn’t exist. How to solve the problem that the service can’t be started
- Solve the problem that idea’s. Gitignore doesn’t work sometimes
- Oracle12c client32 bit installation error: [ins-20802] Oracle net configuration assistant failed
- seaborn.load_ Data set error urlerror: < urlopen error [winerror 10060] the connection attempt failed because the connecting party did not reply correctly after a period of time or the connected host did not respond
- This program cannot be started because vcruntime140 is missing from your computer_ 1.dll。 Try to install the program again to solve the problem.
- Solve the problem of “wireless network activation failure” in Ubuntu 18, and repeatedly pop up the password input interface
- The problem of window flash after C + + program is compiled and run
- Oracle 12C installation process related errors and Solutions
- Solve the problem of error: cannot pass objects of non trivially copyable type ‘STD:: String’ in C / C + +
- Solve the problem of error running xxxapplication command line is too long when compiling and running IntelliJ idea
- Error: unrecognized command line option “-std=c++11”, to solve the problem that ubuntu does not support c++11
- Solve the problem of “A TimThumb error has occured” in the WordPress program
- How to Solve mybatis returns null when querying Oracle database with char type field