Databases Reference
In-Depth Information
As illustrated in this recipe, this approach is completely wrong, as we don't use connection
pooling and waste more time in establishing a connection to the database and then
terminating it, than in accessing the data. If connection pooling is in place, instead, we can
use this pattern successfully.
There are two things to consider in web applications. First, the application is
disconnected
, so
that we can treat each page as a single request disconnected from the previous. Second, with
many users, scalability is a goal to keep in mind starting at the first phase of application design.
To meet these requirements, we need to use connection pooling when connecting to the
database in our web application. When this is not possible—due to limitations in the language
used—we can implement a multi-tier system. Move the data access layer inside web services,
which could then use a dedicated connection, shared among many user requests.
This way we have pool connections at the application layer, which then uses a dedicated
connection to the database.
Client-server Online Transaction Processing
Client server Online Transaction Processing (OLTP) is the most common database application.
In this kind of an environment, a dedicated server connection to the database is an easy
choice. Estimate the RAM requirement to serve the requests, by multiplying the maximum
number of concurrent users with the amount of memory used per Oracle server process.
Due to the nature of transactions involved in this environment—they last for a few
milliseconds—a shared server approach could also be used.
For a multi-tier multi-layer application, use connection pooling on the data access layer—as
in the Web Application example discussed in the previous section—to save some memory
resources on the database server.
Batch processing
Batch processing is a typical activity paired with OLTP applications—often CPU-intensive
tasks are scheduled to work in off-peak time, optimizing the resource utilization.
For batch processing, a dedicated server connection is a must, because they often use
large queries and updates, which may last for minutes, if not hours on large systems.
See also
F
Tuning the Program Global Area and the User Global Area
in
Chapter 9
,
Tuning Memory
