15.00 - SHOWBLOCKS - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

SHOWBLOCKS

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.

Note: 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  

Note: The online help lists the display options (/S, /M, /L) as /dispopt.

 

Syntax element …

Specifies...

/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 statistics for all subtables.

    /L

    For each subtable, for each block size, display the following:

  • 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
    NEVER

    SHOWBLOCKS output should be filtered to show information only for tables that have their BLOCKCOMPRESSION set to the specified value, AUTOTEMP, DEFAULT, MANUAL, or NEVER. The BLOCKCOMPRESSION value can be set when the table is created.

    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 range of rows (“region”) in a data or index subtable. In these cases, Teradata Database marks only the affected subtable or region down. This allows transactions that do not require access to the down subtable or rows to proceed, without causing a database crash or requiring a database restart. If SHOWBLOCKS encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

    Note: 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  

    The following is a portion of the output of the showblocks command:

    +--------+------+----------+------------+--------------------------------------------------------------------------+---------------+------+---------+
    |        |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  

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

    +------------+-------+-----------+--------------+-----------+--------------+------------+-----------------------+-----------+
    |            | Compr-| Comp Ratio| Estimated %  |           |              |            |    Statistics of      |   Total   |
    |            |ession | in        |  of Blocks   |   Data    |  Number of   | Cumulative |    Number of Rows     |   Number  |
    |            |Status | indicated |Un-compressed |   Block   | Data Blocks  | Percent of |    Per Data Block     |     of    |
    |  Table ID  |       | blk Sizes |              |   size    | of indicated |   Total    +-------+-------+-------+           |
    |            |       |           |              | (sectors) |    size      |Data Blocks |  Min  |  Avg  |  Max  | Cylinders |
    +------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |    0  1804 | DB.t1                                                                                                        |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |          0 |   N   |           |              |      2    |          8   |  100.00%   |   1   |    1  |    1  |         8*|
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1024 |   C   |    95%    |      10%     |      2    |       3047   |   65.50%   |  244  |  253  |  266  |        12*|
    |            |   PC  |    85%    |              |      3    |       1078   |   80.00%   |  275  |  275  |  275  |           |
    |            |   U   |           |              |      4    |       2016   |   85.00%   |  275  |  275  |  275  |           |
    |            |   C   |    96%    |       0%     |      5    |        512   |   95.00%   |  275  |  275  |  275  |           |
    |            |   PC  |    99%    |              |      6    |        321   |  100.00%   |  275  |  275  |  275  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1028 |   N   |           |              |           |              |            |       |       |       |         9*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1032 |   N   |           |              |           |              |            |       |       |       |         9*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2048 |   C   |    75%    |       0%     |      2    |       2047   |   65.50%   |  244  |  253  |  266  |        15*|
    |            |   C   |    80%    |      10%     |      3    |       2018   |   80.00%   |  275  |  275  |  275  |           |
    |            |   U   |           |              |      4    |       1016   |   85.00%   |  275  |  275  |  275  |           |
    |            |   PC  |    85%    |              |      5    |        512   |   95.00%   |  275  |  275  |  275  |           |
    |            |   C   |    53%    |       5%     |      6    |        400   |   98.60%   |  275  |  275  |  275  |           |
    |            |   U   |           |              |     15    |         29   |  100.00%   |  275  |  275  |  275  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2052 |   N   |           |              |           |              |            |       |       |       |         8*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          1   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2056 |   N   |           |              |           |              |            |       |       |       |         9*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    +------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |    0  1805 | DB.t2                                                                                                        |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |         0  |   N   |           |              |      2    |          8   |  100.00%   |   1   |    1  |    1  |         8*|
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1024 |   C   |    55%    |       5%     |      3    |          7   |    0.48%   |   21  |   22  |   22  |        10*|
    |            |   PC  |    65%    |              |      4    |          1   |    0.55%   |   35  |   35  |   35  |           |
    |            |   PC  |    40%    |              |      6    |          1   |    0.62%   |   74  |   74  |   74  |           |
    |            |   C   |    95%    |       2%     |      7    |          6   |    1.03%   |   71  |   79  |   87  |           |
    |            |   PC  |    53%    |              |      8    |          1   |    1.10%   |   99  |   99  |   99  |           |
    |            |   C   |    85%    |       9%     |      9    |          3   |    1.30%   |  107  |  109  |  111  |           |
    |            |   C   |    65%    |       2%     |     11    |          1   |    1.37%   |  130  |  130  |  130  |           |
    |            |   PC  |    75%    |              |     12    |          2   |    1.51%   |  151  |  152  |  152  |           |
    |            |   C   |    80%    |       2%     |     13    |          4   |    1.79%   |  154  |  161  |  172  |           |
    |            |   U   |    65%    |              |     14    |          1   |    1.85%   |  175  |  175  |  175  |           |
    |            |   PC  |    70%    |              |     16    |         14   |    2.82%   |  206  |  210  |  218  |           |
    |            |   C   |    80%    |       2%     |     17    |        192   |   16.00%   |  210  |  221  |  233  |           |
    |            |   PC  |    90%    |              |     18    |        769   |   68.82%   |  220  |  232  |  243  |           |
    |            |   PC  |    85%    |              |     19    |        453   |   99.93%   |  232  |  241  |  245  |           |
    |            |   C   |    65%    |       1%     |     20    |          1   |  100.00%   |  245  |  245  |  245  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1028 |   N   |           |              |           |              |            |       |       |       |         8*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1032 |   N   |           |              |           |              |            |       |       |       |         9*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2048 |   C   |    85%    |       5%     |      3    |          7   |    0.48%   |   20  |   22  |   27  |         8*|
    |            |   C   |    65%    |       5%     |      4    |          1   |    0.55%   |   35  |   35  |   35  |           |
    |            |   U   |           |              |      6    |          1   |    0.62%   |   74  |   74  |   74  |           |
    |            |   PC  |    55%    |              |      7    |          6   |    1.03%   |   71  |   79  |   87  |           |
    |            |   PC  |    83%    |              |      8    |          1   |    1.10%   |   99  |   99  |   99  |           |
    |            |   PC  |    95%    |              |      9    |          3   |    1.30%   |  107  |  109  |  111  |           |
    |            |   C   |    65%    |       3%     |     11    |          1   |    1.37%   |  130  |  130  |  130  |           |
    |            |   C   |    55%    |       6%     |     12    |          2   |    1.51%   |  151  |  152  |  152  |           |
    |            |   PC  |    75%    |              |     13    |          4   |    1.79%   |  154  |  161  |  172  |           |
    |            |   C   |    65%    |       9%     |     14    |          1   |    1.85%   |  175  |  175  |  175  |           |
    |            |   C   |    85%    |       4%     |     16    |         92   |    2.82%   |  206  |  210  |  218  |           |
    |            |   PC  |    45%    |              |     17    |        114   |   16.00%   |  210  |  221  |  233  |           |
    |            |   PC  |    65%    |              |     18    |        563   |   68.82%   |  220  |  232  |  243  |           |
    |            |   C   |    95%    |       5%     |     19    |        253   |   99.93%   |  232  |  241  |  245  |           |
    |            |   U   |           |              |     20    |          1   |  100.00%   |  245  |  245  |  245  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2052 |   N   |           |              |           |              |            |       |       |       |         9*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          2   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          2   |  100.00%   |  258  |  258  |  258  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2056 |   N   |           |              |           |              |            |       |       |       |        12*|
    |            |       |           |              |     16    |          1   |   12.50%   |  216  |  216  |  216  |           |
    |            |       |           |              |     17    |          4   |   62.50%   |  226  |  230  |  235  |           |
    |            |       |           |              |     18    |          4   |   87.50%   |  235  |  238  |  240  |           |
    |            |       |           |              |     19    |          3   |  100.00%   |  258  |  258  |  258  |           |
    +------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |    0  2087 | DBN.t256k 					              	                                                   |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |          0 |   N   |           |              |      2    |          8   |  100.00%   |   1   |    1  |    1  |         8*|
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       1024 |   U   |           |              |           |              |            |       |       |       |        24*|
    |            |       |           |              |      3    |          7   |    0.48%   |   21  |   22  |   22  |           |
    |            |       |           |              |      4    |          1   |    0.55%   |   35  |   35  |   35  |           |
    |            |       |           |              |      6    |          1   |    0.62%   |   74  |   74  |   74  |           |
    |            |       |           |              |      7    |          6   |    1.03%   |   71  |   79  |   87  |           |
    |            |       |           |              |      8    |          1   |    1.10%   |   99  |   99  |   99  |           |
    |            |       |           |              |      9    |          3   |    1.30%   |  107  |  109  |  111  |           |
    |            |       |           |              |     11    |          1   |    1.37%   |  130  |  130  |  130  |           |
    |            |       |           |              |     12    |          2   |    1.51%   |  151  |  152  |  152  |           |
    |            |       |           |              |     13    |          4   |    1.79%   |  154  |  161  |  172  |           |
    |            |       |           |              |     14    |          1   |    1.85%   |  175  |  175  |  175  |           |
    |            |       |           |              |     16    |         14   |    2.82%   |  206  |  210  |  218  |           |
    |            |       |           |              |     17    |        192   |   16.00%   |  210  |  221  |  233  |           |
    |            |       |           |              |     18    |       2000   |   68.82%   |  220  |  232  |  243  |           |
    |            |       |           |              |     19    |       3455   |   99.93%   |  232  |  241  |  245  |           |
    |            |       |           |              |     20    |          1   |  100.00%   |  245  |  245  |  245  |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |       2048 |   U   |           |              |           |              |            |       |       |       |        24*|
    |            |       |           |              |      3    |          7   |    0.48%   |   21  |   22  |   22  |           |
    |            |       |           |              |      4    |          1   |    0.55%   |   35  |   35  |   35  |           |
    |            |       |           |              |      6    |          1   |    0.62%   |   74  |   74  |   74  |           |
    |            |       |           |              |      7    |          6   |    1.03%   |   71  |   79  |   87  |           |
    |            |       |           |              |      8    |          1   |    1.10%   |   99  |   99  |   99  |           |
    |            |       |           |              |      9    |          3   |    1.30%   |  107  |  109  |  111  |           |
    |            |       |           |              |     11    |          1   |    1.37%   |  130  |  130  |  130  |           |
    |            |       |           |              |     12    |          2   |    1.51%   |  151  |  152  |  152  |           |
    |            |       |           |              |     13    |          4   |    1.79%   |  154  |  161  |  172  |           |
    |            |       |           |              |     14    |          1   |    1.85%   |  175  |  175  |  175  |           |
    |            |       |           |              |     16    |         14   |    2.82%   |  206  |  210  |  218  |           |
    |            |       |           |              |     17    |        192   |   16.00%   |  210  |  221  |  233  |           |
    |            |       |           |              |     18    |       2000   |   68.82%   |  220  |  232  |  243  |           |
    |            |       |           |              |     19    |       3455   |   99.93%   |  232  |  241  |  245  |           |
    |            |       |           |              |     20    |          1   |  100.00%   |  245  |  245  |  245  |           |
    +------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |    WAL LOG | WAL LOG             .WAL LOG                                                                     |           |
    |            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    |    WAL Log |   N   |           |              |      1    |         48   |  100.00%   |    0  |    2  |    3  |        14 |
    +------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
    * 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