CLEAR_PLAN_CACHE procedure

The CLEAR_PLAN_CACHE procedure either clears all plans Start of change or removes the specified plans End of change from the SQL plan cache without requiring a system IPL.

Authorization: The CLEAR_PLAN_CACHE procedure requires that the authorization ID associated with the statement has *JOBCTL special authority or QIBM_DB_SQLADM function usage.

The schema is QSYS2.

qro-hash: A character or graphic string expression that identifies a set of plans for a specific SQE query. If qro-hash is not specified, the null value is used.
plan-identifier: A numeric value which uniquely identifies a plan within the plan cache. If plan-identifier is not specified, the null value is used.

Start of change If qro-hash is specified and plan-identifier is not specified, the procedure will clear all the plans from the SQL plan cache with that qro-hash. If a qro-hash and a plan-identifier are specified, that specific plan will be removed. If no matching plan is found, the procedure returns without error. End of change

Start of change If no parameters are specified, or a null value is specified for both qro-hash and plan-identifier, the procedure will clear all plans in the SQL plan cache that exist at the time the procedure is run. End of change

Start of change Besides clearing the plan information, any Maintained Temporary Indexes (MTIs) not currently in use by a query will be deleted as part of the clear operation. Queries run while the CLEAR_PLAN_CACHE procedure is running may have their plans removed, but the queries themselves will not incur a failure related to plan removal. After the clear is complete, as queries are re-optimized, they will be inserted into the plan cache. End of change

Removal of all plans is intended for use primarily in performance test and quality assurance environments. It provides database performance analysts with a way to create a consistent environment from which to evaluate potential database performance changes.

Start of change Removal of a specific plan is intended for limited use, such as when directed by IBM service or when needing to force the optimizer to re-optimize a specific query. End of change

The time the CLEAR_PLAN_CACHE procedure takes to run will vary depending on the plan cache size. To avoid tying up an interactive job, it is recommended that the procedure should be submitted in a batch job using a combination of the Submit Job (SBMJOB) and Run SQL (RUNSQL) CL commands.

Notes

The QRO hash is an internally generated identifier for an SQE query. In general, this identifier will be unique for each SQE query and uses implicit schema qualification among other data to generate the QRO hash. If the SQE optimizer generates multiple plans for the same query, then multiple plans will have the same QRO hash. However, every plan will have a unique plan identifier. The QRO hash for a statement may change on release boundaries or after loading PTFs. The QRO hash is externalized:

In a Visual Explain
From Show Statements exploration of the SQL Plan Cache and SQL Plan Cache Snapshots
In the QQC83 column of the 3014 record of a database monitor or plan cache snapshot file
As information passed to a Query Supervisor exit program.
As returned from the QSYS2.ACTIVE_JOB_INFO table function

The plan identifier is a unique number that is generated when the plan is optimized. The plan identifier is externalized:

The statement number of a Visual Explain of a plan cache snapshot or a Visual Explain from the Show Statements exploration of the SQL Plan Cache
From Show Statements exploration of the SQL Plan Cache and SQL Plan Cache Snapshots
In the QQUCNT column of the 1000 record of a plan cache snapshot file.
As information passed to a Query Supervisor exit program.

Example

Submit a job to clear the plan cache.

SBMJOB CMD(RUNSQL SQL('CALL QSYS2.CLEAR_PLAN_CACHE()') COMMIT(*NONE) NAMING(*SQL))

Clear all plans that have a QRO hash of 'D0C257FD'.

CALL QSYS2.CLEAR_PLAN_CACHE(QRO_HASH => 'D0C257FD');

Clear one specific plan.

CALL QSYS2.CLEAR_PLAN_CACHE(QRO_HASH => '1239A9F0', 
                            PLAN_IDENTIFIER => 1305582);