Table space scans
The table space scan information includes the type of table space (simple, segmented, or partitioned), the number of tables in the space, and the fully qualified table name. The size of the table in pages, its record length, number of records, and columns are also provided. Other pertinent information, such as lock size and table space lock mode, close option, segment size, and percent compressed, completes the description of the table space.
This descriptive data is presented for each table accessed, detailing the size, scope, and contents of the tables being accessed by the query. The Db2 SQL Performance Analyzer Cost Summary Report is presented along with the Explain information so all the information is found in a single place. This run contains the parameter SHOWALT YES, so alternative indexes available to access this table are also presented.
When the optimizer for Db2® chooses a table space scan as the access path, it is ignoring the use of indexes, if any exist on the table, as a means of accessing the data. There are many reasons why you cannot choose an index, and the optimizer has made a valid choice in access path selection.
Table space scans are valuable when columns do not have predicates that participate in these indexes. There is no benefit in using the indexes because they would not eliminate any rows from the search.
Occasionally, the indexed columns might be involved with predicates that are stage 2, or contain subtle incompatibilities, such as being compared to columns or data of the wrong length or type.
Db2 SQL Performance Analyzer explains the table space scan in the report segment shown in the following example. Notice that the query states “FROM L1000,” but SQL PA adds the high-level qualifier TDT690 to the table name.
EXPLAIN PLAN SET QUERYNO = 100000001 FOR
SELECT * FROM L1000
QUERYNO: 100000001 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
+------------------------------------------------------------------+
|ANL7003I *** GUIDELINE: |
|Close Yes was specified for the Tablespace and/or the index... |
|if these are little used this is OK. If high volume access then |
|consider Close No. Extremely relevant pages can be "page fixed" |
|in memory by highly referencing, using Hiperpools (Castout Y/N), |
|putting into a dedicated buffer pool large enough to hold all |
|pages, deploying data sharing with Group Buffer Pool Cache All |
|option, etc. each with associated costs. Close No also increases |
|chances that DBD will remain in EDM Pool for next execution. |
+------------------------------------------------------------------+
+------------------------------------------------------------------+
|ANL7006I *** |
|This statement contains a select of all columns in the table. |
|Typically a select of all columns results in significantly |
|increased processing time to fetch and process each column of each|
|row. Use select all only when you want to select all columns in a |
|view definition. Improve performance by specifying the name of |
|each column for the SQL SELECT statement. |
+------------------------------------------------------------------+
+------------------------------------------------------------------+
|ANL5008W *** WARNING: |
|This SQL statement contains no predicates and uses no Built in |
|Functions, so Db2 is selecting the Tablespace Scan access method. |
|In general, this means poor performance, unless the table is very |
|small: verify the accuracy of catalog statistics (NPAGES, NACTIVE,|
|CARD), and re-evaluate whether this access path is appropriate. |
+------------------------------------------------------------------+
TABLESPACE SCAN
---------------
CREATOR: TDT690
TABNAME: L1000
TABLESPACE SCAN WAS CHOSEN ON A 4K SEGMENTED TSPACE WITH: 3 TABLES
VERSION: 0 TABLE CONTAINS A TOTAL OF: 31680 4K PAGES
TABLE ROWS: 100000 COLUMNS: 50 REC LENGTH: 1008 BYTES
DSSIZE: 0 GB NUMBER OF MQTS: 0 LOG: Y AVG ROW LENGTH: 1006 BYTES
TYPE: T LOCK SIZE: A TS LOCK MODE: IS LOCK PART: N CLOSE TABLE: Y
PAGES WITH ROWS: 99% PCT COMPRESSED: 0% MAX ROWS: 255 BPOOL: BP11
ENCODE: E CCSIDS ARE SBCS: 833 DBCS: 834 MIXED: 933 VOLATILE: N
SEQUENTIAL PREFETCH WILL BE EMPLOYED TO ACCESS THIS TABLE
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX NAME: L1000R1N
VERS: 0 KEY LEN: 6 PADDED: - C-ED: N C-ING: N CLURATIO: 11.6627
FULLKEY CARD: 5954 FIRSTKEY CARD: 5954
TYPE: 2 NLEAF PAGES: 155 NLEVELS: 2 UNIQUE: D KEY COLS: 1
KEY ORDER COLCARD COLUMN NAME
--------------------------------------------------
1 A 5954 RIKEY1
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX NAME: L1000R2N
VERS: 0 KEY LEN: 6 PADDED: - C-ED: N C-ING: N CLURATIO: 70.0010
FULLKEY CARD: 100000 FIRSTKEY CARD: 100000
TYPE: 2 NLEAF PAGES: 359 NLEVELS: 3 UNIQUE: U KEY COLS: 1
KEY ORDER COLCARD COLUMN NAME
--------------------------------------------------
1 A 100000 RIKEY2
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000001 WILL REQUIRE 95.28095 SECONDS OF ELAPSED TIME *
* DURING WHICH 26.08726 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 6967 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 199 ESTIMATED PROCESSING COST $ 14.0652 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
ANL5025W *** WARNING:
ESTIMATE OF 26.087 EXCEEDS "CPU TIME" LIMIT OF 10 CPU SECONDS!