****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* db2exfmt, with grapher (-g) option, takes a long time *
* tocomplete ( over 5 hours) after running a query that *
* loadedthelarge amount of data into explain tables. *
****************************************************************
* RECOMMENDATION: *
* Work around:1. First Create the following three *
* indexes:CREATE INDEX OPR_I2 on *
* EXPLAIN_OPERATOR(EXPLAIN_TIME,EXPLAIN_LEVEL, STMTNO, SECTNO, *
* explain_requester,source_name,source_schema, source_version, *
* OPERATOR_ID, operator_type,total_cost, io_cost)CREATE INDEX *
* STRM_I2 on EXPLAIN_STREAM(EXPLAIN_TIME,EXPLAIN_LEVEL, *
* STMTNO, SECTNO, explain_requester,source_name,source_schema, *
* source_version, target_id, source_id,stream_count)CREATE *
* INDEX ARG_I2 on EXPLAIN_ARGUMENT(EXPLAIN_TIME,EXPLAIN_LEVEL, *
* STMTNO, SECTNO, OPERATOR_ID, argument_type)2. Then, for the *
* at least those 3 explain tables, run'runstatson table *
* xxx.explain_yyy and detailed indexes all' rightafterrunning *
* query that loads large amount of data into thoseexplain *
* tables. *
**************************************************************** |
Work around:
1. First Create the following three indexes:
CREATE INDEX OPR_I2 on EXPLAIN_OPERATOR(EXPLAIN_TIME,
EXPLAIN_LEVEL, STMTNO, SECTNO, explain_requester, source_name,
source_schema, source_version, OPERATOR_ID, operator_type,
total_cost, io_cost)
CREATE INDEX STRM_I2 on EXPLAIN_STREAM(EXPLAIN_TIME,
EXPLAIN_LEVEL, STMTNO, SECTNO, explain_requester, source_name,
source_schema, source_version, target_id, source_id,
stream_count)
CREATE INDEX ARG_I2 on EXPLAIN_ARGUMENT(EXPLAIN_TIME,
EXPLAIN_LEVEL, STMTNO, SECTNO, OPERATOR_ID, argument_type)
2. Then, for the at least those 3 explain tables, run 'runstats
on table xxx.explain_yyy and detailed indexes all' right after
running query that loads large amount of data into those
explain tables. |
