Although Db2 adds rows to
EXPLAIN tables automatically. However, it does not automatically delete any rows from the
tables.
About this task
As with other user
tables, you can issue DELETE or TRUNCATE statements to remove data from PLAN_TABLE and the various
related EXPLAIN tables.
You can use the
QUERYNO, GROUP_MEMBER, and EXPLAIN_TIME columns to identify the corresponding rows in the various
EXPLAIN tables. Other columns, such as APPLNAME and PROGNAME can also be used for this purpose.
When you consider your strategy for retaining EXPLAIN table data, remember that you might not
need to keep EXPLAIN records for static SQL statements because you can issue EXPLAIN PACKAGE
statements to recapture the EXPLAIN data at any time.
Important: Certain optimization tools depend on instances of the
various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are
created for these tools.
Procedure
To remove obsolete EXPLAIN table rows, you might use any of the
following approaches:
-
Use DELETE statements to remove rows from EXPLAIN tables based on the age of the rows.
This approach is most appropriate for use in development environments.
For example, you might use the following statement to delete all rows that are more than
one month old from a particular EXPLAIN
table:
DELETE FROM table-name WHERE EXPLAIN_TIME < CURRENT TIMESTAMP - 1 MONTH;
-
Establish a practice for selectively deleting obsolete or unneeded PLAN_TABLE rows, and then
use DELETE statements to selectively remove rows from the related EXPLAIN tables, based on the
PLAN_TABLE rows that remain.
For example, the following statement deletes rows from DSN_DETCOST_TABLE that do not have
corresponding rows in
PLAN_TABLE:
DELETE FROM DSN_DETCOST_TABLE DT
WHERE NOT EXISTS
(SELECT 1
FROM PLAN_TABLE PT
WHERE PT.QUERYNO = DT.QUERYNO
AND PT.APPLNAME = DT.APPLNAME
AND PT.PROGNAME = DT.PROGNAME
AND PT.GROUP_MEMBER = DT.GROUP_MEMBER
AND PT.EXPLAIN_TIME = DT.EXPLAN_TIME);
In
this example, the APPLNAME and PROGNAME columns are included to improve the performance of the
subquery if many duplicate QUERYNO values exist.
With this approach, take the following
actions to avoid performance problems such as lock contention:
- Ensure that an index
exists on the following PLAN_TABLE columns: QUERYNO, APPLNAME, PROGNAME, EXPLAIN_TIME, GROUP_MEMBER.
The PLAN_TABLE_HINT_IX index is suitable for this purpose. For more information about creating the
PLAN_TABLE_HINT_IX index, see Preparing to influence access paths.
- Lock each EXPLAIN table before issuing the DELETE statement for that table.
- Issue a COMMIT statement after the DELETE statement for each table.
-
Issue DROP statements for the EXPLAIN tables and create a new set of EXPLAIN tables.
You can also call the
ADMIN_EXPLAIN_MAINT stored procedure and specify the DROP_AND_CREATE action to replace the set of
EXPLAIN tables.
If you use this approach, no existing EXPLAIN table data is retained.
