14.8 Extent-Level Storage Parameters

---

Extent-level storage parameters apply to groups of blocks created in an extent when a segment in a tablespace is increased in size. A segment is increased in size when more space is required by new data. There are a number of parameters using the following syntax. Note that some parameters apply to specific object types.

STORAGE({       [ INITIAL n[K|M] ] [ NEXT n[K|M] ]       [ MINEXTENTS n ] [ MAXEXTENTS { n | UNLIMITED } ]       [ PCTINCREASE n ]       [ FREELISTS n ] [ FREELIST GROUPS n ]       [ OPTIMAL [ n[K|M] | NULL ] ]       [ BUFFER_POOL { KEEP | RECYCLE | DEFAULT } ]});

Let's look at the details.

14.8.1 Setting Extent Sizes

Initial and next extent sizes can be set using the INITIAL and NEXT parameters.

Tip 

Use of locally managed as opposed to dictionary-managed tablespaces avoids the overhead and complexity of extent management.

As far as physical tuning is concerned here are a number of pointers:

• Use a minimal number of extent sizes between different table-spaces and database objects such as tables and indexes. For some databases all extent sizes can be consistent throughout.

• If using different extent sizes standardize different objects according to a rule such as functionality or growth rates.

• Do not use Oracle Database defaults. They are small and will ultimately give you a lot of problems if your database grows at a reasonable rate.

INITIAL sets the size of the first extent. NEXT sets the size for all but the first extent unless PCTINCREASE is used; PCTINCREASE is best avoided. Generally you can use kilobyte ranges for small tables but use nothing less than 1M for anything larger than that. If you do want to use sizes matching each table then do not use too many different sizes, especially in the same tablespace. Deleted extents are not reused unless new extent requirements can fit into the old extent. Defragmentation on large tables is a much more serious problem than fitting a 1 block static table into a 1M extent; space is wasted physically but access time is not increased. Oracle Database reads in blocks not extents, reading one block multiplied by the multi-block read count parameter value; 1M will not be read. If you must use multiple extent sizes perhaps separate different objects into different tablespaces based on differing extent sizes and perhaps even multiple block size tablespaces. These things will help to alleviate fragmentation in the future.

14.8.2 Minimum and Maximum Extents

The MINEXTENTS value specifies that any new extent added to a datafile must be created at a minimum value, perhaps helping to alleviate fragmentation.

MAXEXTENTS should be set to UNLIMITED for everything but objects you know are uncontrollable and you are not concerned about causing a problem when MAXEXTENTS is reached. Do not set MAXEXTENTS UNLIMITED for rollback segments if there is any development or ad hoc SQL activity. Runaway transactions do occur often and can cause rollback to resize a datafile upwards so far as to fill all disk space and potentially crash your database.

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated and replaced by automatic undo (automatic rollback).

14.8.3 Variable Extent Sizes

PCTINCREASE can be used to gradually increase the size of extents. Do not use it. In a past version of Oracle Database PCTINCREASE had to be set to at least 1 in order to help automate tablespace-level coalescence. The default for PCTINCREASE was 50% in past versions of Oracle Database. Manual coalescence never worked very well so I do not see why automated coalescence should either. Also the defragmentation problem will crop up once again.

PCTINCREASE increases each new extent as a percentage increase of the previous extent. Any deleted extents will likely never be used again. The PCTINCREASE parameter was the culprit of many Oracle databases in the past being much larger and therefore much slower than they should have been. Fortunately locally managed tablespaces ignore the PCTINCREASE parameter.

14.8.4 Managing Concurrency

The FREELIST GROUPS and FREELIST parameters are generally only meaningful in very highly concurrent environments such as Oracle RAC (Parallel Server) environments. These parameters are beyond the scope of this book.

14.8.5 Minimizing Rollback Resizing

The OPTIMAL parameter determines the number of extents to which a rollback segment will shrink after having extended and completed a transaction. If OPTIMAL is not set then no rollback segment shrinkage will occur. Shrinking rollback segments will result in those rollback segments potentially extending once again when reused. Continual extending and shrinking is a waste of time. Increase OPTIMAL until it matches the size of most transactions. This can be assessed by minimizing the SHRINKS column in the V$ROLLSTAT dynamic performance view.

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated and replaced by automatic undo (automatic rollback).

Setting this parameter is not required for automatic undo.

14.8.6 Different Cache Recently Used Lists

The BUFFER_POOL storage setting allows for objects such as tables to be placed into different parts of the database buffer cache or, more specifically, to have them placed into either the MRU or LRU lists. The most recently used (MRU) list allows faster access by retaining blocks in the database buffer cache. The least recently used (LRU) list forces blocks out of the database buffer cache faster.

Various other options exist in Oracle9i Database for the purposes of caching specific tables in the database buffer cache. One is the CACHE option in the CREATE TABLE and ALTER TABLE commands. Another is using multiple block sizes for tablespaces and their related multiple-sized database buffer caches. The parameters DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE are to be favored over the BUFFER_POOL storage setting.

This chapter covered the low-level aspects of physical tuning encompassing block and extent structures. The next chapter will cover hardware resource usage tuning in the areas of CPU, memory, and I/O usage.

---