Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, May 27, 2008

SEG$, FET$, UET$

Any object in the database that takes up space is a segment. A segment is made of extents and a segment exists in a tablespace. Each extent in the segment is made up of blocks and each extent must exist within a single file. In dictionary managed tablespaces, Oracle’s space management basically revolves around 5 tables:
• fet$ free extents
• uet$ used extents
• seg$ segments
• ts$ tablespaces
• file$ files
Between them fet$ and uet$ map all the space in the database. Seg$ is used to cluster used space, and ts$ is used to cluster free space, file$ does little more than list the files.

SEG$ structure in a 10.2.0.3 Database:

Name Null? Type
----------------------------------------- -------- ----
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
TYPE# NOT NULL NUMBER
TS# NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
EXTENTS NOT NULL NUMBER
INIEXTS NOT NULL NUMBER
MINEXTS NOT NULL NUMBER
MAXEXTS NOT NULL NUMBER
EXTSIZE NOT NULL NUMBER
EXTPCT NOT NULL NUMBER
USER# NOT NULL NUMBER
LISTS NUMBER
GROUPS NUMBER
BITMAPRANGES NOT NULL NUMBER
CACHEHINT NOT NULL NUMBER
SCANHINT NOT NULL NUMBER
HWMINCR NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 NUMBER


I found some good information on segments here which I am pasting below:

Segment types
There are 11 types of segments in Oracle 10g:

* table
* table partition
* index
* index partition
* cluster
* rollback
* deferred rollback
* temporary
* cache
* lobsegment
* lobindex

These types can be grouped into four segment classes:

* data segments: table and cluster
* index
* rollback
* temporary data

In order to find out the size of the respective segments, use dba_segments (or user_segments, or all_segments).
A segment can either be created so that it can have an unlimited or limited number of extents.
Oracle keeps track of free blocks for each data segment.
Segment Headers
The segment header is stored in the first block of the first extent. It contains:

* The extents table
* The free lists descriptors
* The high water mark

Temporary segments
The following SQL statements require a temporary segment if they cannot be completed in memory:

* create index.
* select order by
* select distinct
* select group by
* select union
* select intersect
* select minus by
* analyze table
* unindexed joins
* certain correlated subqueries

Actually, most of these statements require a sort.
Temporary segments can be stored in any tablespace. However, it makes more sense to store temporary segments in a temporary tablespace.
SMON frees temporary segments.

Apart from handling special events such as instance recovery, the System MONitor (smon) has two basic tasks which it cycles through during the lifetime of an instance.

Every five minutes it looks through the free extent table (sys.fet$) to see if there are any adjacent free extents that can be coalesced into a single extent, and on every 25th cycle (i.e. every two hours and five minutes) it looks at the segment table (sys.seg$) to see if there are any temporary segments that need to be eliminated

1 comment:

Anonymous said...

Excellent Description . Thanks BOSS