by Donald K
Burleson
Over the past few releases Oracle has been automating and improving the internal administration of tables and indexes
It has gradually recognized the benefits of bitmap data structures in all areas of the database
Recently
Oracle has introduced two new tablespace parameters that automate storage management functions:
LMT (Locally Managed Tablespaces) — The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to the tablespace definition
LMT tablespaces automate extent management and remove the ability to specify the NEXT storage parameter
The only exception is when NEXT is used with MINEXTENTS at table creation time
ASSM (Automatic Segment Space Management) — The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition
ASSM tablespaces automate FREELIST management and remove the ability to specify PCTUSED
FREELISTS
and FREELIST GROUPS storage parameters
You cannot use ASSM unless you also use LMTs on a tablespace
It is important to note that LMT and ASSM are optional and are used in the same instance with
traditional
tablespaces
Remember
LMT and ASSM are implemented at the tablespace level and each instance can have LMT
LMT and ASSM tablespaces
or traditional tablespaces
Before we discuss the differences between bitmap FREELISTS and traditional FREELIST management
let
s examine how bitmap FREELISTS are implemented
We begin by creating a tablespace with the segment space management auto parameter
Note that ASSM is only valid for locally
managed tablespaces with extent management local syntax
create tablespace
asm_test
datafile
c:\oracle\oradata\diogenes\asm_test
dbf
size
m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;
Once a table or index is allocated in this tablespace
the values for PCTUSED for individual objects will be ignored
and Oracle
i will automatically manage the FREELISTS for the tables and indexes inside the tablespace
For objects created in this tablespace
the NEXT extent clause is now obsolete because of the locally
managed tablespace (except when a table is created with MINEXTENTS and NEXT)
The INITIAL parameter is still required because Oracle cannot know in advance the size of the initial table load
When using Automatic Space Management
the minimum value for INITIAL is three blocks
There is some debate about whether a one
size
fits
all approach is best for Oracle
In large databases
individual object settings can make a huge difference in both performance and storage
As we may know
the setting for PCTUSED governs FREELIST re
linking
If we want high disk space usage
we set PCTUSED to a value xzignthly greater than avg_row_len
Conversely
if we want fast INSERT performance
we set PCTUSED to a low value
ensuring that all free blocks are nearly empty
providing lots of block space for INSERT operations
The Issue of PCTFREE
As a quick review
the PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion
If PCTFREE is set improperly
SQL update statements can cause a huge amount of row fragmentation and chaining
The setting for PCTFREE is especially important where a row is initially stored small and expanded at a later time
In such systems
it is not uncommon to set PCTFREE equal to
telling Oracle to reserve
percent of the data block space for subsequent row expansion
Fortunately
Oracle
i does not allow you to specify the value for PCTFREE if you are using Automatic Space Management
Row chaining is a serious problem for the DBA
and it appears that Automatic Space Management is still appropriate for tables for which you need to reserve space for large row expansions with PCTFREE
The Issue of PCTUSED
As we know
improper settings for PCTUSED can cause huge degradation in the performance of SQL inserts
If a data block is not largely empty
excessive I/O will happen during SQL inserts because the re
used Oracle data blocks will become full quickly
Taken to the extreme
improper settings for PCTUSED can create a situation in which the free space on the data block is smaller than the average row length for the table
In these cases
Oracle will try five times to fetch a block from the FREELIST chain
After five attempts
Oracle will raise the high
water mark for the table and grab five fresh data block for the insert
In Oracle
i with Automatic Segment Management
the PCTUSED parameter no longer governs the re
link threshold for a table data block
and we must rely on the judgment of Oracle to determine when a block is empty enough to be placed onto the FREELIST
Unlike PCTFREE
in which Oracle cannot tell in advance how much row expansion will occur
Oracle
i does have information about the right time to re
link a data block
Because Oracle knows the average row length for the table rows (dba_tables
avg_row_Len)
Oracle should be able to adjust PCTUSED to ensure that the re
linked data block will have room for new rows
An Oracle Inconsistency
While Oracle
i ignores the PCTUSED
FREELISTS
and FREELIST GROUPS parameters with LMT and ASSM tablespaces
Oracle does not give an error message when these
ignored
parameters are used in a table definition
SQL> create table
test_table
(c
number)
tablespace
asm_test
pctfree
pctused
storage
( freelists
next
m ) ;
Table created
Most Oracle DBAs would assume that invalid parameters would be treated as they have been treated since Oracle
and reported as an error
SQL> create index
test_type_idx
on
book(book_type)
PCTUSED
;
PCTUSED
*
ERROR at line
:
ORA
: invalid CREATE INDEX option
This could lead to confusion when a DBA believes that they are changing these values when in reality
tablespaces with LMT or SAM ignore any specified values for PCTUSED
NEXT
and FREELISTS
No More Buffer Busy Waits
One huge benefit of Automatic Segment Management is the bitmap FREELISTS that are guaranteed to reduce buffer busy waits
Let
s take a close look at this feature
Prior to Oracle
i
buffer busy waits were a major issue
As a review
a buffer busy wait occurs when a data block is inside the data buffer cache
but it is unavailable because it is locked by another DML transaction
A block was unavailable because another SQL insert statement needed to get a block on which to place its row
Without multiple FREELISTS
every Oracle table and index had a single data block at the head of the table to manage the free block for the object
Whenever any SQL insert ran
it had to go to this block and get a data block on which to place its row
Obviously
single FREELISTS cause a backup
When multiple tasks wanted to insert into the same table
they were forced to wait while Oracle assigned free blocks
one at a time
Oracle
s Automatic Segment Space Management feature claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used
simultaneously eliminating serialization for free space lookups
According to Oracle benchmarks
using bitmap FREELISTS removes all segment header contention and allows for super
fast concurrent insert operations (refer to figure
)
From:http://tw.wingwit.com/Article/program/Oracle/201311/17040.html