Database Reference
In-Depth Information
have the same update contention issues, the fact that there are more rows per block is not a concern but a benefit.
Moreover, the transactional users get their own buffer cache in effect; they don't have to worry about the reporting
queries overrunning their cache.
But in general, the default, keep, and recycle pools should be sufficient for fine-tuning the block buffer cache, and
multiple block sizes would be used primarily for transporting data from database to database and perhaps for a hybrid
reporting/transactional system.
Shared Pool
The shared pool is one of the most critical pieces of memory in the SGA, especially with regard to performance and
scalability. A shared pool that is too small can kill performance to the point that the system appears to hang. A shared
pool that is too large can have the same effect. A shared pool that is used incorrectly will be a disaster as well.
What exactly is the shared pool? The shared pool is where Oracle caches many bits of “program” data. When we
parse a query, the parsed representation is cached there. Before we go through the job of parsing an entire query,
Oracle searches the shared pool to see if the work has already been done. PL/SQL code that you run is cached in the
shared pool, so the next time you run it, Oracle doesn't have to read it in from disk again. PL/SQL code is not only
cached here, it is shared here as well. If you have 1,000 sessions all executing the same code, only one copy of the code
is loaded and shared among all sessions. Oracle stores the system parameters in the shared pool. The data dictionary
cache (cached information about database objects) is stored here. In short, everything but the kitchen sink is stored in
the shared pool.
The shared pool is characterized by lots of small (generally 4KB or less) chunks of memory. Bear in mind that
4KB is not a hard limit. There will be allocations that exceed that size, but in general the goal is to use small chunks of
memory to prevent the fragmentation that would occur if memory chunks were allocated in radically different sizes,
from very small to very large. The memory in the shared pool is managed on an LRU basis. It is similar to the buffer
cache in that respect—if you don't use it, you'll lose it. A supplied package called
DBMS_SHARED_POOL
may be used to
change this behavior—to forcibly pin objects in the shared pool. You can use this procedure to load up your frequently
used procedures and packages at database startup time, and make it so they are not subject to aging out. Normally,
though, if over time a piece of memory in the shared pool is not reused, it will become subject to aging out. Even
PL/SQL code, which can be rather large, is managed in a paging mechanism so that when you execute code in a very
large package, only the code that is needed is loaded into the shared pool in small chunks. If you don't use it for an
extended period of time, it will be aged out if the shared pool fills up and space is needed for other objects.
The easiest way to break Oracle's shared pool is to not use bind variables. As you saw in Chapter 1, not using bind
variables can bring a system to its knees for two reasons:
•
The system spends an exorbitant amount of CPU time parsing queries.
•
The system uses large amounts of resources managing the objects in the shared pool as a
result of never reusing queries.
If every query submitted to Oracle is a unique query (because of unique values hard-coded in), the concept of
the shared pool is substantially defeated. The shared pool was designed so that query plans would be used over and
over again. If every query is a brand-new, never-before-seen query, then caching only adds overhead. The shared pool
becomes something that inhibits performance. A common but misguided technique that many use to try to solve this
issue is adding more space to the shared pool, which typically only makes things worse than before. As the shared
pool inevitably fills up once again, it gets to be even more of an overhead than the smaller shared pool, for the simple
reason that managing a big, full shared pool takes more work than managing a smaller, full shared pool.
The only true solution to this problem is to use shared SQL to reuse queries. Earlier, in Chapter 1, we briefly
looked at the parameter
CURSOR_SHARING
, which can work as a short-term crutch in this area. The only real way to
solve this issue, however, is to use reusable SQL in the first place. Even on the largest of large systems, I find that
there are typically at most 10,000 to 20,000 unique SQL statements. Most systems execute only a few hundred
unique queries.
