Database Tables - Expert Oracle Database Architecture - page 362

You'll find the buffer busy waits, CPU time, and elapsed time to have decreased for this case as well, similar to
when we configured the perfect number of FREELIST s for a segment using MSSM—without having to figure out the
optimum number of required FREELIST s:

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 199 27-Jan-14 12:16:30 33 1.0
End Snap: 200 27-Jan-14 12:16:37 33 1.0
Elapsed: 0.12 (mins) Av Act Sess: 5.3
DB time: 0.62 (mins) DB CPU: 0.25 (mins)
...
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle 341 562 1647 92.9
CPU time 15 2.4
log file parallel write 341 10 29 1.6
heartbeat redo informer 8 8 1000 1.3
lreg timer 2 6 3000 1.0
--------------------------------------------------------------------------------

This is one of ASSM's main purposes: to remove the need to manually determine the correct settings for many
key storage parameters. ASSM uses additional space when compared to MSSM in some cases as it attempts to
spread inserts out over many blocks, but in most all cases, the nominal extra storage utilized is far outweighed by the
decrease in concurrency issues. An environment where storage utilization is crucial and concurrency is not (a data
warehouse pops into mind) would not necessarily benefit from ASSM managed storage for that reason.

PCTFREE and PCTUSED
In general, the PCTFREE parameter tells Oracle how much space should be reserved on a block for future updates. By
default, this is 10 percent. If there is a higher percentage of free space than the value specified in PCTFREE , then the
block is considered to be free . PCTUSED tells Oracle the percentage of free space that needs to be present on a block that
is not currently free in order for it to become free again. The default value is 40 percent.
As noted earlier, when used with a table (but not an IOT, as we'll see), PCTFREE tells Oracle how much space
should be reserved on a block for future updates. This means if we use an 8KB block size, as soon as the addition of a
new row onto a block causes the free space on the block to drop below about 800 bytes, Oracle will use another block
from the FREELIST instead of the existing block. This 10 percent of the data space on the block is set aside for updates
to the rows on that block.

■ PCTFREE and PCTUSED are implemented differently for different table types. some table types employ both,
whereas others only use PCTFREE , and even then only when the object is created. IOts use PCTFREE upon creation to set
aside space in the table for future updates, but do not use PCTFREE to decide when to stop inserting rows into a given
block, for example.

Note