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:
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.
This is Part 2 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find Part 1 here. In this post,...