Oracle 5.0, Reference Manual

Page: 1313 / 3232

InnoDB
Performance Tuning and Troubleshooting
1293
Segment information includes these values:
id
: The segment ID.
space
,
page
: The tablespace number and page within the tablespace where the segment “inode” is
located. A tablespace number of 0 indicates the shared tablespace.
InnoDB
uses inodes to keep track
of segments in the tablespace. The other fields displayed for a segment (
id
,
res
, and so forth) are
derived from information in the inode.
res
: The number of pages allocated (reserved) for the segment.
used
: The number of allocated pages in use by the segment.
full ext
: The number of extents allocated for the segment that are completely used.
fragm pages
: The number of initial pages that have been allocated to the segment.
free extents
: The number of extents allocated for the segment that are completely unused.
not full extents
: The number of extents allocated for the segment that are partially used.
pages
: The number of pages used within the not-full extents.
When a segment grows, it starts as a single page, and
InnoDB
allocates the first pages for it
individually, up to 32 pages (this is the
fragm pages
value). After that,
InnoDB
allocates complete
64-page extents.
InnoDB
can add up to 4 extents at a time to a large segment to ensure good
sequentiality of data.
For the example segment shown earlier, it has 32 fragment pages, plus 2 full extents (64 pages each),
for a total of 160 pages used out of 160 pages allocated. The following segment has 32 fragment
pages and one partially full extent using 14 pages for a total of 46 pages used out of 96 pages
allocated:
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14
It is possible for a segment that has extents allocated to it to have a
fragm pages
value less than 32
if some of the individual pages have been deallocated subsequent to extent allocation.
14.2.12.2.3.
InnoDB
Table Monitor Output
The
InnoDB
Table Monitor prints the contents of the
InnoDB
internal data dictionary.
The output contains one section per table. The
SYS_FOREIGN
and
SYS_FOREIGN_COLS
sections are
for internal data dictionary tables that maintain information about foreign keys. There are also sections
for the Table Monitor table and each user-created
InnoDB
table. Suppose that the following two tables
have been created in the
test
database:
CREATE TABLE parent
(
par_id INT NOT NULL,
fname CHAR(20),
lname CHAR(20),
PRIMARY KEY (par_id),
UNIQUE INDEX (lname, fname)
) ENGINE = INNODB;
CREATE TABLE child
(
par_id INT NOT NULL,
child_id INT NOT NULL,
name VARCHAR(40),
birth DATE,
weight DECIMAL(10,2),
misc_info VARCHAR(255),
last_update TIMESTAMP,
PRIMARY KEY (par_id, child_id),