Database Reference
In-Depth Information
Understanding Oracle Architecture
I have worked with many customers running large production applications—applications that had been “ported”
from another database (for example, SQL Server) to Oracle. I quote “ported” simply because most ports I see reflect
a “what is the least change we can make to have our SQL Server code compile and execute on Oracle” perspective.
The applications that result from that line of thought are frankly the ones I see most often, because they are the ones
that need the most help. I want to make clear, however, that I am not bashing SQL Server in this respect—the opposite
is true! Taking an Oracle application and just plopping it down on top of SQL Server with as few changes as possible
results in the same poorly performing code in reverse; the problem goes both ways.
In one particular case, however, the SQL Server architecture and how you use SQL Server really impacted
the Oracle implementation. The stated goal was to scale up, but these folks did not want to really port to another
database. They wanted to port with as little work as humanly possible, so they kept the architecture basically the same
in the client and database layers. This decision had two important ramifications:
•
The connection architecture was the same in Oracle as it had been in SQL Server.
•
The developers used literal (nonbound) SQL.
These two ramifications resulted in a system that could not support the required user load (the database server
simply ran out of available memory), and in a system that had abysmal performance.
Use a Single Connection in Oracle
Now, in SQL Server it is a very common practice to open a connection to the database for each concurrent statement
you want to execute. If you are going to do five queries, you might well see five connections in SQL Server. In Oracle,
on the other hand, if you want to do five queries or five hundred, the maximum number of connections you want to
open is one. So, a practice that is common in SQL Server is something that is not only not encouraged in Oracle, it is
actively discouraged; having multiple connections to the database is just something you don't want to do.
But do it they did. A simple web-based application would open 5, 10, 15, or more connections per web page,
meaning that their server could support only 1/5, 1/10, or 1/15 the number of concurrent users that it should have
been able to. Moreover, they were attempting to run the database on the Windows platform itself—just a plain
Windows server without access to the “data center” version of Windows. This meant that the Windows single-process
architecture limited the Oracle database server to about 1.75GB of RAM in total. Since each Oracle connection took
at least a certain fixed amount of RAM, their ability to scale up the number of users using the application was severely
limited. They had 8GB of RAM on the server, but could only use about 2GB of it.
■
there are ways to use more raM in a 32-bit Windows environment, such as with the /aWe switch, but they
required versions of the operating system that were not in use in this situation.
Note
There were three approaches to correcting this problem, and all three entailed quite a bit of work—and this was
after the “port” was complete! The options were as follows:
•
Re-architect the application to allow it to take advantage of the fact that it was running “on”
Oracle, and use a single connection to generate a page, not somewhere between 5 and 15
connections. This is the only solution that would actually solve the problem.
•
Upgrade the operating system (no small chore) and utilize the larger memory model of
the Windows Data Center version (itself not a small chore either as it involves a rather
complicated database setup with indirect data buffers and other nonstandard settings.
