nz_frag -h
Usage: nz_frag <database> <table/mview>
Purpose: Dump out extent/page allocations in order to visualize table fragmentation.
Storage is allocated an extent at a time (3MB). Within the extent, it is
then filled up with records a page at a time (128KB). The pages are filled
front-to-back. Once all of the available space in the extent is used up, a
new extent is allocated.
Usually, all of the 24 pages within an extent are in-use. But there are
exceptions.
o The very last extent for a table (on any given dataslice) will probably
only be partially filled. So any remaining pages will be unused (empty).
Unused pages are not scanned.
o If you have done a "GROOM TABLE <tablename> PAGES ALL;" then any pages
that contain 100% deleted/groomable rows will be marked as being empty.
They will no longer be used/scanned, though they still exist within the
extent. If all 24 pages in the extent are empty, the extent will be
removed from the table and added back to the global storage pool.
o Clustered Base Tables (those created with an ORANIZE ON clause) may only
partially fill any given cluster/extent.
Inputs: The database and table/mview names are required.
-dsid <nn> By default, data slice 1 (the 1st amongst all data slices)
will be reported upon. You can choose to look at the
information for a different data slice if you wish.
Storage allocation/usage is specific to each dataslice.
Rather than showing all info for all dataslices (which
could be voluminous), this script will concentrate on
just one dataslice, which should provide a good
representative sample.
However, if you want to see ALL information for ALL
dataslices ALL at once, then specify: -dsid all
Outputs: A dump of the extent+page storage information for this table/mview (for
a single dataslice).
The "Extent ID" that is displayed is the system assigned extent number.
The values for contiguous extents will vary by 32.
The "#" column is a simple, one-up number ... to make things easier to
read.
The "gap" column is used to indicate whether the extents are contiguous
on disk. If the extents are contiguous (if the gap is 0) then a blank
will be displayed. Otherwise, this number will represent the number of
other extents (not belonging to this table) between this extent and the
prior extent.
"Used/Unused Pages (./0)" is used to represent which of the 24 pages
within each extent are (or are not) in use. A "." indicates the page
is in use. A "0" indicates the page is not being used.
Example follows:
$ nz_frag SYSTEM TEST_TABLE
Database: SYSTEM
Object Name: TEST_TABLE
Object Type: TABLE
Object ID : 10578974
Data Slice: 1
Extent ID | DataSlice | # | gap | Used/Unused Pages (./0)
-----------+-----------+----+-----+--------------------------
38886368 | 1 | 1 | | 0.......................
38886432 | 1 | 2 | 1 | .0......................
38886560 | 1 | 3 | 3 | ..0.....................
Usage: nz_frag <database> <table/mview>
Purpose: Dump out extent/page allocations in order to visualize table fragmentation.
Storage is allocated an extent at a time (3MB). Within the extent, it is
then filled up with records a page at a time (128KB). The pages are filled
front-to-back. Once all of the available space in the extent is used up, a
new extent is allocated.
Usually, all of the 24 pages within an extent are in-use. But there are
exceptions.
o The very last extent for a table (on any given dataslice) will probably
only be partially filled. So any remaining pages will be unused (empty).
Unused pages are not scanned.
o If you have done a "GROOM TABLE <tablename> PAGES ALL;" then any pages
that contain 100% deleted/groomable rows will be marked as being empty.
They will no longer be used/scanned, though they still exist within the
extent. If all 24 pages in the extent are empty, the extent will be
removed from the table and added back to the global storage pool.
o Clustered Base Tables (those created with an ORANIZE ON clause) may only
partially fill any given cluster/extent.
Inputs: The database and table/mview names are required.
-dsid <nn> By default, data slice 1 (the 1st amongst all data slices)
will be reported upon. You can choose to look at the
information for a different data slice if you wish.
Storage allocation/usage is specific to each dataslice.
Rather than showing all info for all dataslices (which
could be voluminous), this script will concentrate on
just one dataslice, which should provide a good
representative sample.
However, if you want to see ALL information for ALL
dataslices ALL at once, then specify: -dsid all
Outputs: A dump of the extent+page storage information for this table/mview (for
a single dataslice).
The "Extent ID" that is displayed is the system assigned extent number.
The values for contiguous extents will vary by 32.
The "#" column is a simple, one-up number ... to make things easier to
read.
The "gap" column is used to indicate whether the extents are contiguous
on disk. If the extents are contiguous (if the gap is 0) then a blank
will be displayed. Otherwise, this number will represent the number of
other extents (not belonging to this table) between this extent and the
prior extent.
"Used/Unused Pages (./0)" is used to represent which of the 24 pages
within each extent are (or are not) in use. A "." indicates the page
is in use. A "0" indicates the page is not being used.
Example follows:
$ nz_frag SYSTEM TEST_TABLE
Database: SYSTEM
Object Name: TEST_TABLE
Object Type: TABLE
Object ID : 10578974
Data Slice: 1
Extent ID | DataSlice | # | gap | Used/Unused Pages (./0)
-----------+-----------+----+-----+--------------------------
38886368 | 1 | 1 | | 0.......................
38886432 | 1 | 2 | 1 | .0......................
38886560 | 1 | 3 | 3 | ..0.....................
No comments:
Post a Comment