Database Reference
In-Depth Information
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle 346 279 806 85.9
CPU time 15 4.5
log file parallel write 344 9 27 2.9
lreg timer 3 9 3000 2.8
heartbeat redo informer 8 8 1000 2.5
--------------------------------------------------------------------------------
What you want to do for a table is try to determine the maximum number of concurrent (truly concurrent) inserts
or updates that will require more space. What I mean by
truly concurrent
is how often you expect two people at exactly
the same instant to request a free block for that table. This is not a measure of overlapping transactions; it is a measure
of how many sessions are doing inserts at the same time, regardless of transaction boundaries. You want to have about
as many
FREELIST
s as concurrent inserts into the table to increase concurrency.
You should just set
FREELIST
s really high and then not worry about it, right? Wrong—of course, that would
be too easy. When you use multiple
FREELIST
s, there is a master
FREELIST
and there are process
FREELIST
s. If a
segment has a single
FREELIST
, then the master and process
FREELIST
s are one and the same thing. If you have two
FREELIST
s, you'll really have one master
FREELIST
and two process
FREELIST
s. A given session will be assigned to
a single process
FREELIST
based on a hash of its session ID. Now, each process
FREELIST
will have very few blocks
on it—the remaining free blocks are on the master
FREELIST
. As a process
FREELIST
is used, it will pull a few blocks
from the master
FREELIST
as needed. If the master
FREELIST
cannot satisfy the space requirement, then Oracle will
advance the HWM and add empty blocks to the master
FREELIST
. So, over time, the master
FREELIST
will fan out its
storage over the many process
FREELIST
s (again, each of which has only a few blocks on it). So, each process will use
a single process
FREELIST
. It will not go from process
FREELIST
to process
FREELIST
to find space. This means that
if you have ten process
FREELIST
s on a table and the one your process is using exhausts the free buffers on its list, it
will not go to another process
FREELIST
for space—so even if the other nine process
FREELIST
s have five blocks each
(45 blocks in total), it will go to the master
FREELIST
. Assuming the master
FREELIST
cannot satisfy the request for
a free block, it would cause the table to advance the HWM or, if the table's HWM cannot be advanced (all the space
is used), to extend (to get another extent). It will then continue to use the space on its
FREELIST
only (which is no
longer empty). There is a tradeoff to be made with multiple
FREELIST
s. On one hand, use of multiple
FREELIST
s is a
huge performance booster. On the other hand, it will probably cause the table to use slightly more disk space than
absolutely necessary. You will have to decide which is less bothersome in your environment.
Do not underestimate the usefulness of the
FREELISTS
parameter, especially since you can alter it up and down at
will with Oracle 8.1.6 and later. What you might do is alter it to a large number to perform some load of data in parallel
with the conventional path mode of SQL*Loader. You will achieve a high degree of concurrency for the load with
minimum waits. After the load, you can reduce the value to some more reasonable day-to-day number. The blocks on
the many existing
FREELIST
s will be merged into the one master
FREELIST
when you alter the space down.
Another way to solve the previously mentioned issue of buffer busy waits is to use an ASSM managed tablespace.
Take the preceding example and create the table
T
in an ASSM managed tablespace, as follows:
EODA@ORA12CR1> create tablespace assm
2 datafile size 1m autoextend on next 1m
3 segment space management auto;
Tablespace created.
EODA@ORA12CR1> create table t ( x int, y char(50) ) tablespace ASSM;
Table created.
