16.10 - SHOWBLOCKS - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-26
dita:mapPath
zll1480972831047.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval

Purpose

The SHOWBLOCKS command displays statistics about data block size, number of rows per data block, and information about the compression status of data blocks and tables for all the tables defined by the SCOPE command. SHOWBLOCKS can also display WAL log statistics.

The CreateFsysInfoTable and PopulateFsysInfoTable macros provide a way to generate SHOWBLOCKS-like file system information to an SQL table. For more information, see SQL Functions, Operators, Expressions, and Predicates.

Syntax



The online help lists the display options (/S, /M, /L) as /dispopt.
/S
For each primary data subtable, display the following:
  • A histogram of block sizes
  • The minimum, average, and maximum block size per subtable
  • Block compression information (status, estimated compression ratio, estimated uncompressed)
This is the default display.
/M
Same as the /S option, but display the table name, map that the table uses for data distribution, and statistics for all subtables. For each subtable, display the BLOCKCOMPRESSION value set when the table was created or altered.
/L
For each subtable, for each block size, display the following:
  • Table name
  • Map that the table uses for data distribution
  • Number of blocks
  • The minimum, average, and maximum number of rows per data block size
  • Block compression information (status, estimated compression ratio, estimated percent uncompressed)
Displays the statistics for all subtables.
COMPRESSTLA=, AUTOTEMP, DEFAULT, MANUAL, ALWAYS, or NEVER
SHOWBLOCKS output should be filtered to show information only for tables that have their BLOCKCOMPRESSION set to the specified value, AUTOTEMP, DEFAULT, MANUAL, ALWAYS, or NEVER. The BLOCKCOMPRESSION value can be set when the table is created or altered.

The Ferret ABORT command can be used to halt this operation during command execution.

Usage Notes

The output of the long display is basically one line for every size data block from every subtable of every table in the scope.

The output can be quite lengthy; therefore, consider using the OUTPUT command to redirect the output to a file.

This command can be used with the DATABLOCKSIZE option of the CREATE TABLE and ALTER TABLE SQL statements to determine the best data block size for tables based on performance requirements.

Teradata Database can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows ("region") in a data or index subtable. In these cases, Teradata Database marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart. If SHOWBLOCKS encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

Although disk space allocated for TJ and WAL records is charged against table 0 26, no actual TJ or WAL records are found in the subtables of this table. Instead, these records are in the WAL log. The only row that exists in any subtable of table 0, 26 is the table header in subtable 0.

Example: SHOWBLOCKS short display

The following is a portion of the output of the showblocks command using the /s short display option:

+--------+------+----------+------------+--------------------------------------------------------------------------+---------------+------+---------+
|        |Compr-|Estimated |Estimated % |                    Distribution of data block sizes                      |  Data block   |Total | Total   |
|Table ID|ession| Compres- | of Blocks  |                     (by range of number of sectors)                      |size statistics|Number| Number  |
|        |Status|  sion    |   Un-      +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+   (sectors)   |  of  |  of     |
|        |      |  Ratio   | compressed | 1- | 2- | 4- | 8- | 16-| 32-| 48-| 64-| 80-| 96-|112-|128-|160-|192-|224-|-----+----+----+ Data |Cylinders|
|        |      |          |            | 1  | 3  | 7  |15  | 31 | 47 | 63 | 79 | 95 |111 |127 |159 |191 |223 |255 | Min | Avg| Max|Blocks|         |
+--------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+-----+----+----+------+---------+
| 0   256|   N  |          |            |    |    |    | 88%| 12%|    |    |    |    |    |    |    |    |    |    |   9 |  13|  19|     8|       8*|
| 0   265|   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |    |    |    |   1 |   1|   1|     3|       3*|
| 0   266|   N  |          |            |    |100%|    |    |    |    |    |    |    |    |    |    |    |    |    |   2 |   2|   3|     5|       5*|
| 0  1804|   C  |   50%    |    20%     |    | 64%|  2%| 34%|    |    |    |    |    |    |    |    |    |    |    |   2 |   6|  15|  4974|      12*|
| 0  1805|  PC  |   88.14% |    81.69%  |    |    |    | 18%|    |    |    |    |    | 26%| 56%|    |    |    |    |   4 |  94| 121|  4008|      20*|
| 0  2087|   U  |          |            |    |    |  1%| 99%|    |    |    |    |    |    |    |    |    |    |    |   1 |  13|  15|   689|       8*|
| WAL LOG|   N  |          |            |  7%| 83%|  3%|  1%|  5%|  1%|    |    |    |    |    |    |    |    |    |   1 |   4| 255|  2475|      25 |
+--------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+-----+----+----+------+---------+
* Reported in units of Large Cylinders
(A Large Cylinder is 6 times the size of a small cylinder)
"Compression Status" :  C = Fully Compressed
                       PC = Partially Compressed
                        U = Fully Uncompressed
                        N = Not Compressible

Example: SHOWBLOCKS medium display

The following is a portion of the output of the showblocks command using the /m medium display option:

Showblocks has been started on all AMP vprocs in the SCOPE.
Type 'ABORT' to stop the command before completion
+------------+------+----------+------------+-----------------------------------------------------------------------------+-----------------+------------+-----------+
|            |Compr-|Estimated |Estimated % |                      Distribution of data block sizes                       |   Data block    |   Total    |   Total   |
|            |ession| Compres- | of Blocks  |                       (by range of number of sectors)                       | size statistics |   Number   |   Number  |
|  Table ID  |Status|  sion    |   Un-      +----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+    (sectors)    |     of     |     of    |
|            |      |  Ratio   | compressed |  1-|  9-| 25-| 65-|121-|169-|217-|257-|361-|457-|513-|761-|1025-|1305-|1633-|-----+-----+-----+    Data    | Cylinders |
|            |      |          |            |  8 | 24 | 64 |120 |168 |216 |256 |360 |456 |512 |760 |1024|1304 |1632 |2048 | Min | Avg | Max |   Blocks   |           |
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0     0 | DBC.FIRSTPERMTABLE (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                   |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0     7 | DBC.RCConfiguration (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                  |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    25 | DBC.Global (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         1  |         1*|
|       2048 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         1  |         1*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    26 | DBC.TransientJournal (DEFAULT)(Map: TD_GLOBALMAP)                                                                                         |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    27 | DBC.Owners (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   2 |         3  |         3*|
|       2048 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   2 |         3  |         3*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    30 | DBC.ErrorMsgs (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                        |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |    |    |    |    |    |    |100%|    |    |    |    |    |     |     |     | 239 | 244 | 248 |         4  |         4*|
|       2048 |   N  |          |            |    |    |    |    |    |    |100%|    |    |    |    |    |     |     |     | 239 | 244 | 248 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    32 | DBC.ChangedRowJournal (DEFAULT)(Map: TD_GLOBALMAP)                                                                                        |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    36 | DBC.SavedTransactionStatusTable (DEFAULT)(Map: TD_GLOBALMAP)                                                                              |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    WAL LOG | WAL LOG.WAL LOG                                                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    WAL LOG |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   3 |        43  |         7*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)

 "Compression Status" :  C = Fully Compressed
                         U = Fully Uncompressed
                         N = Not Compressible

Example: SHOWBLOCKS long display

The following is a portion of the output of the showblocks command using the /l long output option.

+------------+-------+-----------+--------------+-----------+--------------+------------+-----------------------+-----------+
|            | Compr-| Estimated | Estimated %  |           |              |            |    Statistics of      |   Total   |
|            |ession | Compres-  |  of Blocks   |   Data    |  Number of   | Cumulative |    Number of Rows     |   Number  |
|            |Status |  sion     |Un-compressed |   Block   | Data Blocks  | Percent of |    Per Data Block     |     of    |
|  Table ID  |       |  Ratio    |              |   size    | of indicated |   Total    +-------+-------+-------+           |
|            |       |           |              | (sectors) |    size      |Data Blocks |  Min  |  Avg  |  Max  | Cylinders |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0     0 | DBC.FIRSTPERMTABLE(Map: TD_DATADICTIONARYMAP)                                                    |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       1   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0     7 | DBC.RCConfiguration(Map: TD_DATADICTIONARYMAP)                                                   |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    25 | DBC.Global(Map: TD_DATADICTIONARYMAP)                                                            |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       1024 |   N   |           |              |       1   |          1   |  100.00%   |     1 |     1 |     1 |         1*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       2048 |   N   |           |              |       1   |          1   |  100.00%   |     1 |     1 |     1 |         1*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    26 | DBC.TransientJournal(Map: TD_GLOBALMAP)                                                          |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    27 | DBC.Owners(Map: TD_DATADICTIONARYMAP)                                                            |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       1024 |   N   |           |              |       1   |          2   |   66.67%   |     1 |     2 |     2 |         3*|
|            |   N   |           |              |       2   |          1   |  100.00%   |    34 |    34 |    34 |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       2048 |   N   |           |              |       1   |          2   |   66.67%   |     1 |     2 |     2 |         3*|
|            |   N   |           |              |       2   |          1   |  100.00%   |    34 |    34 |    34 |           |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|49152     0 | DBC.FIRSTSPOOLTABLE(Map: TD_MAP1)                                                                |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       1   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    WAL LOG | WAL LOG.WAL LOG                                                                                  |           |
|            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    WAL LOG |   N   |           |              |       1   |         48   |   85.71%   |     0 |     2 |     4 |         8*|
|            |   N   |           |              |       2   |          1   |   87.50%   |     0 |     6 |     6 |           |
|            |   N   |           |              |       3   |          6   |   98.21%   |     0 |     7 |     9 |           |
|            |   N   |           |              |       5   |          1   |  100.00%   |     0 |     1 |     1 |           |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)

 "Compression Status" :  C = Fully Compressed
                         U = Fully Uncompressed
                         N = Not Compressible