Plsql_ Performance Optimization series 12_oracle Connection Management

2014-09-25 Baoxinjian

I. Summary

In the Official document "Oracle Performance Tuning Guide", mention connecting to the database was an expensive operation, which is highly unscalable.

Database connection operations are expensive and difficult to scale (large concurrency support).

Feel the last database login operation is an instant thing, how expensive is it?

Simply put, the listener receives a remote connection request and forwards it to the server process;

For each session database, a server process is generated to allocate memory for the session, authentication, auditing

In addition, in one session, cursors can be reused in a number of ways,

such as the Plsql program,

All static SQL is cache, and repeated calls do not soft parse;

Sometimes you can set the Session_cached_cursor parameter to reduce soft parse.

Therefore, if a program uses short connections, frequent logon/logoff are not available for these features. This is another disadvantage of short connections.

While this is a very straightforward question, designers should have the concept of ' connection management ' to avoid the dangers of short connections. On a production system, we can still see short connections:

grep ' <txt>22-oct-2012 14:47 ' LOG.XML|WC

102 1220 18141

With more than 100 connections per minute, just processing the connection request may consume half the CPU.

Second, connection management

So how to manage the connection in the application design?

(1) Simple application, or the application of C/s structure, the connection is established when the application is started (interface Open), the connection is terminated when the application is closed. This normally allows an endpoint to initiate 1-2 connections in 1 days.

(2) Java programs can use connection pooling, which can be shared among multiple threads.

(3) Most systems currently employ middleware (WebSphere, WebLogic), using a connection pool (connection pool), each time a request is initiated, the connection handle is obtained from the connection pool, and no new connections need to be established continuously.

Reference: Baidu Library information

Plsql_ Performance Optimization series 12_oracle Connection Management