Database Reference
In-Depth Information
•
Migrate the database from a Windows-based OS to some other OS where multiple processes
are used, effectively allowing the database to utilize all installed RAM. On a 32-bit Windows
platform, you are limited to about 2GB of RAM for the combined PGA/SGA regions (2GB for
both, together) since they are allocated by a single process. Using a multiprocess platform that
was also 32-bit would limit you to about 2GB for the SGA and 2GB per process for the PGA,
going much further than the 32-bit Windows platform.
As you can see, none of these are “OK, we'll do that this afternoon” sort of solutions. Each is a complex solution
to a problem that could have most easily been corrected during the database port phase, while you were in the
code poking around and changing things in the first place. Furthermore, a simple test to scale before rolling out to
production would have caught such issues prior to the end users feeling the pain.
Use Bind Variables
If I were to write a book about how to build
nonscalable
Oracle applications, “Don't Use Bind Variables” would be
the first and last chapter. Not using bind variables is a major cause of performance issues and a major inhibitor of
scalability—not to mention a security risk of huge proportions. The way the Oracle shared pool (a very important
shared-memory data structure) operates is predicated on developers using bind variables in most cases. If you want
to make a transactional Oracle implementation run slowly, even grind to a total halt, just refuse to use them.
A bind variable is a placeholder in a query. For example, to retrieve the record for employee
123
, I can query:
select * from emp where empno = 123;
Alternatively, I can query:
select * from emp where empno = :empno;
In a typical system, you would query up employee
123
maybe once or twice and then never again for a long
period of time. Later, you would query up employee
456
, then
789
, and so on. Or, foregoing
SELECT
statements, if you
do not use bind variables in your insert statements, your primary key values will be hard-coded in them, and I know
for a fact that these insert statements can't ever be reused later!!! If you use literals (constants) in the query, then every
query is a brand-new query, never before seen by the database. It will have to be parsed, qualified (names resolved),
security-checked, optimized, and so on. In short, each and every unique statement you execute will have to be
compiled every time it is executed.
The second query uses a bind variable,
:empno
, the value of which is supplied at query execution time. This query
is compiled once and then the query plan is stored in a shared pool (the library cache), from which it can be retrieved
and reused. The difference between the two in terms of performance and scalability is huge, dramatic even.
From the preceding description, it should be fairly obvious that parsing unique statements with hard-coded
variables (called a
hard
parse) will take longer and consume many more resources than reusing an already parsed
query plan (called a
soft
parse). What may not be so obvious is the extent to which the former will reduce the number of
users your system can support. Obviously, this is due in part to the increased resource consumption, but an even more
significant factor arises due to the latching mechanisms for the library cache. When you hard-parse a query, the database
will spend more time holding certain low-level serialization devices called
latches
(see the chapter
Locking and Latching
for more details). These latches protect the data structures in Oracle's shared memory from concurrent modifications
by two sessions (otherwise Oracle would end up with corrupt data structures) and from someone reading a data
structure while it is being modified. The longer and more frequently you have to latch these data structures, the longer
the queue to get these latches will become. You will start to monopolize scarce resources. Your machine may appear to
be underutilized at times, and yet everything in the database is running very slowly. The likelihood is that someone is
holding one of these serialization mechanisms and a line is forming—you are not able to run at top speed. It only takes
one ill-behaved application in your database to dramatically affect the performance of every other application. A single,
small application that does not use bind variables will cause the relevant SQL of other well-tuned applications to get
discarded from the shared pool over time. You only need one bad apple to spoil the entire barrel.
