How can entries in the SQL cache be invalidated or reparsed manually?

For some reasons it can be useful to invalidate or reparse entries in the SQL cache, e.g.:

• After having changed SQL plan related parameters in order to make sure that the new setting takes effect (e.g. late materialization parameters)
• In order to understand better if a high memory consumption is linked to a memory leak or to the available SQL statements (e.g. Pool/itab analysis)

Command

ALTER SYSTEM CLEAR SQL PLAN CACHE : All SQL statements in the SQL cache are invalidated / evicted. When the statements are executed the next time, a new parsing is required. This can have a temporary impact on resource consumption and response time.

ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY '<plan_id>' : Invalidation of a specific SQL plan with plan ID <plan_id> (M_SQL_PLAN_CACHE.PLAN_ID), so that it is recompiled from scratch (with a new PLAN_ID) when the SQL plan is required again