Deferred Invalidation

I was going to write an article on the way 12.2 has introduced the option for “deferred invalidation” for a number of DDL operations but I did a quick google search before I started writing and found that both Franck Pachot and Richard Foote (yes, rebuild index is one of the operations) had got there long ago, so here are a couple of links – as much for my own benefit as anything else:

Richard Foote:

• Deferred invalidation relating to indexes.

Franck Pachot:

• General DDL deferred invalidation.
• Partitioned tables and deferred invalidation

Franck’s 2^nd example could be particularly relevant to some clients of mine who were having problems with SQL queries that were crashing (slowly and randomly) instead of running very efficiently because they were running against one subpartition of a table while another subpartition of the same table was subject to exchange. With a little bad luck in the timing an exchange that took place between a parse and an execute would cause a query to have its cursor invalidated and re-parsed in a way that failed to do (sub-)partition elimination because the local indexes were temporarily in an indeterminate state.

Update Aug 2020

I’ve just been reminded of about deferred invalidation, and got very excited because I’d forgotten that I already knew about it – but again I did a couple of searches and found my own reference note plus a few more references, so here’s a little update and expansion.

Entries in Reference manuals for 12.2

• Alter index: rebuild / unusable
• Alter table (partitioning clause)
• Parameter cursor_invalidation 

Nigel Bayliss has an article with echoes back to dbms_stats and its rolling invalidation, and comments on the  new states and supporting columns in v$sql and has a link to the SQL Tuning Guide (12.2).

• Blog note on fine-grained cursor invalidation

Maria Colgan has an article about deferring invalidation for partition operations, highlighting the fact that it won’t work for dropping (sub)partitions, but you can more-or-less work around the limitation by using truncate instead of drop. The issue relates to the fact that “drop (sub-)partition” results in renumbering of partitions (in the views if nowhere else). She also points out that “The fix in 12cR2 that will not invalidate cursors for EXCHANGE PARTITION, TRUNCATE PARTITION or MOVE PARTITION is fully backportable to 11.2.0.4 or 12c R1. All you need to do is request a back port for Bug ID 24817834.”

• Blog note on avoiding re-parsing on partition maintenance (pt.2)

Footnote

There is a documentation error in SQL Tuning reference page that Nigel Bayliss links to – it gives an example of the syntax as “immediate validation” when it should be “immediate INvalidation”