This feature made its debut in Oracle Database 12c Release 2 and enhancements in Oracle Database 18c allow it to be used in a broader range of scenarios. SQLMaria mentioned it in a blog post so I realized that I am overdue in producing a blog post for this cool feature.
What is Fine-Grained Cursor Invalidation?
You might be familiar with the concept of rolling or deferred cursor invalidation because DBMS_STATS uses it if you gather statistics with NO_INVALIDATE=>AUTO_INVALIDATE (this is the default). Fine-grained cursor invalidation is similar except that it is relevant when DDL commands are used to change schema objects; adding indexes or changing partitions and so on.
Consider a SALES table that has a number of SQL statements in the shared pool that access it. These statements are referred to as dependent cursors. If we make any changes to SALES (such as adding an index or refreshing statistics) then we should invalidate and re-parse dependent cursors to take these changes into account.
If SALES is a 'hot' application table and it has a large number of dependent cursors then our DDL might cause a sudden surge in hard parses. An increase in hard parse rate on a busy system can have a detrimental effect on database performance, but if we spread re-parse activity over a period of time then performance is much more likely to remain steady.
Prior to Oracle Database 12c Release 2, DDL invalidated dependent cursors immediately, but with fine-grained cursor invalidation the database can often defer invalidation and spread the cost of re-parsing dependent cursors.
What is Rolling Invalidation?
A cursor marked for rolling invalidation will become eligible for re-parse after a random amount of time (up to a maximum). A newly generated random time is assigned to each dependent cursor. Randomization therefore spreads the overhead of re-parsing dependent cursors over time.
How does Fine-Grained Cursor Invalidation Work?
During cursor compilation the database annotates cursors with fine-grained dependency information (from Oracle Database 12c Release 2 onwards). For example, "this cursor reads index SALES_IDX" or "this cursor does static partition pruning and only accesses partitions in the range [P1, P10]" or "this cursor does a partition related optimization that depends on the partition scheme of the tables involved, e.g. partition-wise join".
Commonly-used DDLs define a set of rules that consult each cursor's fine-grained dependencies to decide how the cursor should be processed.
There are four actions that we can take for a cursor:
- Cursor remains valid -- this is used when we know that the cursor is safe to execute and that the cursor's plan is still optimal. For example, modifying a table partition to read only.
- Cursor is marked for rolling invalidation -- this is used when we know that the cursor is safe to execute but its plan may be sub-optimal. For example, if we create a visible index, then the cursor is safe to execute but we want to recompile it sometime in the future so the new index can be considered for the plan.
- Cursor is marked for rolling invalidation with metadata refresh -- this is used when the cursor is safe to execute after refreshing some of its metadata at runtime. For example, if we rebuild an index that is used by the cursor, then the cursor can be executed if we refresh the metadata for the index so the cursor uses the new index segment.
- Cursor is invalidated -- this is used if the cursor is no longer safe to execute. For example, if we drop an index that is used by the cursor, we must invalidate the cursor so it is recompiled to get a new plan that does not use the index.
Note that actions 1, 2, and 3 correspond to DDL_NO_INVALIDATE, IS_ROLLING_INVALID, and IS_ROLLING_REFRESH_INVALID columns in V$SQL. Action 4 is simply the existing invalidation action that was used prior to the fine-grained invalidation feature.
Action 1 is enabled by default; i.e. we will use action 1 when applicable without any syntax or parameter required.
Actions 2 and 3 are only used if DEFERRED INVALIDATION syntax is used, or if CURSOR_INVALIDATION parameter is set to DEFERRED (see below).
Fine-grained cursor invalidation reduces the risk of performance degradation associated with DDL operations on objects in you application schema.
As mentioned above, the feature is controlled using DDL statements that have an additional DEFERRED VALIDATION clause (for example, ALTER TABLE). There is also a CURSOR_INVALIDATION initialization parameter.
More detail is available in the SQL Tuning Guide. Note that we have not documented the multitude of factors that affect where this feature will or will not kick in. This is where the differences between Oracle Database 12c and Oracle Database 18c are, so the changes between these two releases will not be obvious from the documentation. Nevertheless, if you want to experiment, I have linked to some scripts below that will give you a good place to start. You will see that DML cursors are treated differently to queries because queries are generally less sensitive to change than DML. A SQL statement's eligibility for deferred invalidation is also dependent on access method (e.g. whether its SQL execution plan uses a modified index) and also whether partitions are statically pruned or accessed via KEY.
Big thanks to George Eadon for putting together the 'how does it work?' section.
If you want to try this feature out, I've uploaded examples to GitHub. The tests are in a very raw state, but I explain that in the README file.