Identify clustered base tables that require grooming

The /nz/kit/bin/adm/tools/cbts_needing_groom script identifies CBTs in one, several, or all databases that require grooming. CBTs that have many ungroomed or empty pages can slow the performance of queries that use those tables.

The script lists any CBTs in the specified databases that have 960 or more ungroomed or empty pages in any one data slice. The script outputs the SQL commands that identify the databases and the GROOM TABLE commands for any CBTs that meet the groom threshold.

You can run these commands from a command line, or output the command to a file that you can use as an input script to the nzsql command. You can use script options to specify the databases to search and the threshold for the number of ungroomed pages in a data slice.

The script has the following syntax:

/nz/kit/bin/adm/tools/cbts_needing_groom [-h] {-alldbs | -db db_name 
[db_name ...]} [-th threshold]

Table 1. The cbts_needing_groom input options
Option	Description
-h	Displays the usage for the script.
-alldbs	Checks all the databases in the system for CBTs that require grooming.
-db `db_name` [`db_name`...]	Checks the specified database in the system for CBTs that require grooming. You can specify one or more database names to check only those databases.
-th `threshold`	Specifies the minimum number of empty or deleted pages in at least one data slice for CBTs to be designated as needing groom. The default is 960. Although the valid values are 0 - 2147483647, the practical maximum is about 24,000 pages. Keep in mind that a value that is too high causes the command to ignore CBTs that would benefit from a groom operation, while a number that is too low can report CBTs for which a groom is not required.

A sample script follows:

[nz@nzhost tools]$ ./cbts_needing_groom -alldbs -th 400
\c "my_db"
groom table "lineitem2"; -- 505 / 4037

The script can take a few minutes to run, depending on the number of databases and tables that it checks. If the command finds no CBTs that meet the groom threshold criteria, the command prompt displays with no command output. As the sample shows, one CBT meets the user-supplied threshold criteria.

If the script reports any CBTs that would benefit from a groom, you can connect to each database and run the GROOM TABLE command manually for the specified table. Or, you can also direct the output of the command to a file that you can use as an input script to the nzsql command. For example:

[nz@nzhost tools]$ ./cbts_needing_groom -alldbs -th 400 >/export/home/nz/testgrm.sql
[nz@nzhost tools]$ nzsql -f /export/home/nz/testgrm.sql
You are now connected to database "my_db".
nzsql:/export/home/nz/testgrm.sql:2: NOTICE:  Groom processed 4037 
pages; purged 0 records; scan size shrunk by 1 pages; table size shrunk 
by 1 extents.
GROOM ORGANIZE READY