progress/status of ongoing GROOM operations can be monitored via the
system view _V_GROOM_STATUS
nz_groom [dbname [tablename <...>]] [ optional args ]
Purpose: A wrapper around the 'groom' command to provide additional functionality.
nz_reclaim is automatically invoked when running NPS 4.x and 5.x
nz_groom is automatically invoked when running NPS 6.x +
Inputs: The database name is optional. If not specified then all databases will be
processed.
The tablename is optional. If not specified then all tables in the database
will be processed. Or, you can specify one (or many) tablenames to be
processed.
-scan The default option. This will report on the amount of space
that would be reclaimed IF you were to do a
"groom table <name> records all;"
Gathering this information simply involves a full table scan.
-pages Run a page based groom against the entire table.
Any empty pages (128KB) will be removed from the scan list (although
they will still exist in the table). Any empty extents (3MB) will
be removed the from table.
Where "empty" means that the page/extent only contains deleted rows,
and those rows are currently reclaimable -- i.e. not of interest.
"groom table <name> pages all;"
The goal here is to remove as many rows as possible ... using the
fastest method possible.
-records Run the actual "groom table <name> records all;" against the
table. This option will, by far, take the longest to run as
it basically involves a read+rewrite of the entire table.
Therefore, when you choose this option, the script will
automatically do the following:
a) First perform the "-pages" operation, since it is faster
to throw away pages/extents of data, than individual records.
b) Then perform the "-scan" operation to see if there are
any rows that actually need reclaiming. If not, we'll
be able to skip the actual record level grooming as that
would not be of any benefit.
c) IF, and only IF, a record level groom is warranted, it
will then be run against the table. (The table must
have reclaimable rows, and must meet/exceed any of the
following thresholds that you are allowed to specify).
The following switches are optional, and apply to the
-scan/-records options. They can be used in any combination
to limit the output report to just those tables that meet/
exceed the specified thresholds. If multiple conditions are
specified, they will be OR'ed together.
If a table does not meet at least one of the thresholds it
will not be included in the report (the "-scan" option) nor
will the script do a record level groom against the table
(the "-records" option).
-rows <nnn> # Example: -rows 1000000
# Only show tables with >= 1M reclaimable rows
-percent <nnn> # Example: -percent 50
# Only show tables where >= 50% of the rows
# are reclaimable
-size <nnn> # Example: -size 1000000000
# Only show tables with >= 1 GB of reclaimable
# space
If no thresholds are specified, then:
o All tables will be displayed
(for purposes of the "-scan" option).
o Only tables with >= 1 reclaimable row will be reclaimed
("-records" option)
-records <all|ready>
There may be times when you want to FORCE a record level groom
to be performed (even if there are no logically deleted rows in
the table to be reclaimed). For example:
o) To rewrite a table, after you have enabled compression,
so that it will now be compressed
o) To "organize" the rows within a clustered base table.
This option allows you to FORCE the record level groom (of your
choosing) to be performed. Note that this script will skip the
initial page level groom, and subsequent scan, of each table ...
as that just adds additional overhead, and it won't make any
difference (since the script is always going to end up doing the
record level groom that you requested anyway).
-mview If a table has one (or more) active materialized views associated
with it, the table cannot be groom'ed until any such MView has been
suspended. So, by default, this script will skip over those tables.
Add this switch to the command line if you want the script to
o Automatically suspend any such materialized views
o Perform the GROOM operation
o Refresh the materialized views upon completion
-version If you have a versioned table (which is the result of doing an
'ALTER TABLE <table> [ADD|DROP] COLUMN ...' operation against
it), then the ONLY groom operation that is allowed against the
table is a GROOM VERSIONS. By default, this script will skip
over any such table.
Add this switch to the command line if you want the script to
automatically do a GROOM VERSIONS against such tables.
-backupset <[backupsetid | NONE]>
This switch will allow you to override the default backupset. If used, this
information is simply passed along to the "groom" command for it to handle.
The system synchronizes your groom request with the most recent backup set to
avoid reclaiming rows not yet captured by incremental backups. In other words,
groom will not remove any unbackedup data that has been deleted or updated.
In addition, groom will not remove any data that an old/outstanding (yet
still active) transaction might still have an interest in.
So ... there may very well be logically deleted rows in a table ... that
groom is not willing to physically delete at this point in time (for the
above mentioned reasons). The scripts 'nz_invisible' and 'nz_transactions'
may be useful in such a situation.
For additional information, refer to the "System Administrator's Guide"
For this script to function, you must have SELECT access to the following
objects:
_T_BACKUP_HISTORY
_VT_HOSTTXMGR
Notes: If an unexpected error is encountered at any point in time, this script
will echo the error message and then exit. It will not continue processing
other tables if any problem is encountered.
The progress/status of ongoing GROOM operations can be monitored via the
system view _V_GROOM_STATUS
Outputs: For the default "-scan" option, a report such as this will be produced.
The "Size"s are estimates (based on each table's average row size).
The "Remaining Rows/Remaining Size" represents what would remain in the
table once the actual "groom table <name> records all;" is processed.
This includes visible rows -- as well as deleted rows that cannot yet
be groom'ed (for whatever reason).
The "NON-Groomable Rows" column represents the number of deleted rows that
are NOT elgible for grooming at this point in time, which may be due to
o an outstanding transaction that might have an interest in them
o the rows have not yet been captured in the most recent backup set
Name Remaining Rows Remaining Size Reclaimable Rows Reclaimable Size NON-Groomable Rows
-------- -------------- -------------- ---------------- ---------------- ------------------
system view _V_GROOM_STATUS
nz_groom [dbname [tablename <...>]] [ optional args ]
Purpose: A wrapper around the 'groom' command to provide additional functionality.
nz_reclaim is automatically invoked when running NPS 4.x and 5.x
nz_groom is automatically invoked when running NPS 6.x +
Inputs: The database name is optional. If not specified then all databases will be
processed.
The tablename is optional. If not specified then all tables in the database
will be processed. Or, you can specify one (or many) tablenames to be
processed.
-scan The default option. This will report on the amount of space
that would be reclaimed IF you were to do a
"groom table <name> records all;"
Gathering this information simply involves a full table scan.
-pages Run a page based groom against the entire table.
Any empty pages (128KB) will be removed from the scan list (although
they will still exist in the table). Any empty extents (3MB) will
be removed the from table.
Where "empty" means that the page/extent only contains deleted rows,
and those rows are currently reclaimable -- i.e. not of interest.
"groom table <name> pages all;"
The goal here is to remove as many rows as possible ... using the
fastest method possible.
-records Run the actual "groom table <name> records all;" against the
table. This option will, by far, take the longest to run as
it basically involves a read+rewrite of the entire table.
Therefore, when you choose this option, the script will
automatically do the following:
a) First perform the "-pages" operation, since it is faster
to throw away pages/extents of data, than individual records.
b) Then perform the "-scan" operation to see if there are
any rows that actually need reclaiming. If not, we'll
be able to skip the actual record level grooming as that
would not be of any benefit.
c) IF, and only IF, a record level groom is warranted, it
will then be run against the table. (The table must
have reclaimable rows, and must meet/exceed any of the
following thresholds that you are allowed to specify).
The following switches are optional, and apply to the
-scan/-records options. They can be used in any combination
to limit the output report to just those tables that meet/
exceed the specified thresholds. If multiple conditions are
specified, they will be OR'ed together.
If a table does not meet at least one of the thresholds it
will not be included in the report (the "-scan" option) nor
will the script do a record level groom against the table
(the "-records" option).
-rows <nnn> # Example: -rows 1000000
# Only show tables with >= 1M reclaimable rows
-percent <nnn> # Example: -percent 50
# Only show tables where >= 50% of the rows
# are reclaimable
-size <nnn> # Example: -size 1000000000
# Only show tables with >= 1 GB of reclaimable
# space
If no thresholds are specified, then:
o All tables will be displayed
(for purposes of the "-scan" option).
o Only tables with >= 1 reclaimable row will be reclaimed
("-records" option)
-records <all|ready>
There may be times when you want to FORCE a record level groom
to be performed (even if there are no logically deleted rows in
the table to be reclaimed). For example:
o) To rewrite a table, after you have enabled compression,
so that it will now be compressed
o) To "organize" the rows within a clustered base table.
This option allows you to FORCE the record level groom (of your
choosing) to be performed. Note that this script will skip the
initial page level groom, and subsequent scan, of each table ...
as that just adds additional overhead, and it won't make any
difference (since the script is always going to end up doing the
record level groom that you requested anyway).
-mview If a table has one (or more) active materialized views associated
with it, the table cannot be groom'ed until any such MView has been
suspended. So, by default, this script will skip over those tables.
Add this switch to the command line if you want the script to
o Automatically suspend any such materialized views
o Perform the GROOM operation
o Refresh the materialized views upon completion
-version If you have a versioned table (which is the result of doing an
'ALTER TABLE <table> [ADD|DROP] COLUMN ...' operation against
it), then the ONLY groom operation that is allowed against the
table is a GROOM VERSIONS. By default, this script will skip
over any such table.
Add this switch to the command line if you want the script to
automatically do a GROOM VERSIONS against such tables.
-backupset <[backupsetid | NONE]>
This switch will allow you to override the default backupset. If used, this
information is simply passed along to the "groom" command for it to handle.
The system synchronizes your groom request with the most recent backup set to
avoid reclaiming rows not yet captured by incremental backups. In other words,
groom will not remove any unbackedup data that has been deleted or updated.
In addition, groom will not remove any data that an old/outstanding (yet
still active) transaction might still have an interest in.
So ... there may very well be logically deleted rows in a table ... that
groom is not willing to physically delete at this point in time (for the
above mentioned reasons). The scripts 'nz_invisible' and 'nz_transactions'
may be useful in such a situation.
For additional information, refer to the "System Administrator's Guide"
For this script to function, you must have SELECT access to the following
objects:
_T_BACKUP_HISTORY
_VT_HOSTTXMGR
Notes: If an unexpected error is encountered at any point in time, this script
will echo the error message and then exit. It will not continue processing
other tables if any problem is encountered.
The progress/status of ongoing GROOM operations can be monitored via the
system view _V_GROOM_STATUS
Outputs: For the default "-scan" option, a report such as this will be produced.
The "Size"s are estimates (based on each table's average row size).
The "Remaining Rows/Remaining Size" represents what would remain in the
table once the actual "groom table <name> records all;" is processed.
This includes visible rows -- as well as deleted rows that cannot yet
be groom'ed (for whatever reason).
The "NON-Groomable Rows" column represents the number of deleted rows that
are NOT elgible for grooming at this point in time, which may be due to
o an outstanding transaction that might have an interest in them
o the rows have not yet been captured in the most recent backup set
Name Remaining Rows Remaining Size Reclaimable Rows Reclaimable Size NON-Groomable Rows
-------- -------------- -------------- ---------------- ---------------- ------------------
Great information
ReplyDelete