Re: Locally Managed Tablespace and ASSM

On Jan 22, 8:33 pm, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
> In my 10gR2:
>
> SQL> select extent_management, segment_space_management,
> tablespace_name from dba_tablespaces;
> LOCAL MANUAL SYSTEM
> LOCAL MANUAL UNDOTBS1
> LOCAL AUTO SYSAUX
> LOCAL MANUAL TEMP
> LOCAL AUTO USERS
> LOCAL AUTO EXAMPLE
> LOCAL AUTO ART_TS
>
> In spite of all the big stories and advantages of ASSM, why is Oracle
> 10gR2 still using MANUAL for segment_space_management?

If you create the database manually, you could (theoretically as far as I can tell) use ASSM for the system tablespace, although it is not the default for the system tablespace. The default for 10g R2 is to create a locally managed system tablespace that does not use ASSM - of course a locally managed system tablespace means that dictionary managed tablespaces cannot be created in the database, and that transportable dictionary managed tablespaces can be mounted only in read-only mode.

ASSM cannot be used for temporary tablespaces - one must use uniform extents in a locally managed temporary tablespaces. If inserts into tables are not performed from a single session, ASSM will very likely cause the clusting factor for indexes on the tables to increase - this makes the index access paths on tables appear to be more expensive. Paraphrased and/or quoted from "Cost-Based Oracle Fundamentals": "With ASSM, when a process needs to insert a row, it selects a space map block that is dictated by its process ID, and then picks from the space map a block that is dictated by the process ID. The net effect is that concurrent processes will tend to pick a different block to insert their rows. This has a tendency to increase the clustering factor."

For the DBAs who have selected to use a 16KB or larger block size (maybe for a couple tablespaces or the entire database), there is another potential surprise with ASSM, especially for small objects stored in the tablespace. Page 2-9 of the 10g R2 Concepts manual: "Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with 'segment space management auto', and if the database block size is 16K or higher, then Oracle manages segment size by creating extents with a minimum size of 1M."

Paraphrased from "Expert Oracle Database 10g Administration": "With AUTOALLOCATE, the extent size starts at 64KB and increase to 64MB as additional extents are added. The default for tablespace extent management is AUTOALLOCATE. Oracle recommends that unless all objects in a tablespace are the same size (grouped into small, medium, and large buckets), AUTOALLOCATE should be used.

> Why is PCTUSED, NEXT, FREELISTS is not needed in LMT? The main
> feature of LMT is bitmap managed freelists, vs the dictionary managed
> freelists in prior version. But I cannot understand why the PCTUSED,
> NEXT, FREELISTS feature can be taken away with the availability of a
> new feature, as they can surely co-exists. If it is taken away for
> simplification of management - then it is fine.
>
> I had to agree that all these parameters are really very
> complicated. Now that it is removed, good - it has been replaced by
> some internal formula. So how are these PCTUSED, NEXT, FREELISTS etc
> formula implemented? Can it be customized or modified by us - in
> future?

Page 2-10 of the 10g R2 Concepts manual: "The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be specified at the tablespace level for locally managed tablespaces. They can, however, be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and MINEXTENTS are used together to compute the initial size of the segment. After the segment size is computed, internal algorithms determine the size of each extent."

I am sure there is much more detailed information out on the Internet or tucked inside a book, but maybe the above will help.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jan 23 2008 - 14:23:41 CST