Oracle Scratchpad

March 13, 2018

Deferred Invalidation

Filed under: 12c,CBO,Infrastructure,Oracle,Troubleshooting,Upgrades — Jonathan Lewis @ 6:30 pm GMT Mar 13,2018

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:

Franck Pachot:

Franck’s 2nd 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

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).

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.”

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”

 

2 Comments »

  1. I’ve been working through a ORA-12842 cursor invalidation issue.
    It’s still an application specific test case so not suitable for public broadcast yet but so far I’ve got:
    – Serial (possibly unintentional) CTAS statement including one UNION ALL and with two PARALLEL SELECTs
    When that mixes with concurrent partition additions on the underlying tables (partitions not relevant to the selected partitions above), I get ORA-012842

    Under these conditions, the setting of CURSOR_INVALIDATION and/or any explicit invalidation clauses on the partition operations seem to be irrelevant.

    Unless I’m getting lucky/unlucky with the timing, what makes a difference is any of:
    – Remove UNION ALL
    – Parallelise CTAS
    – Separate CTAS of empty table plus INSERT (parallel or non-parallel).

    Comment by Dom Brooks — January 5, 2021 @ 2:51 pm GMT Jan 5,2021 | Reply

  2. Dom,

    Is this one of those where you have to be very careful with timing to make the problem happen?
    I look forward to hearing your conclusions when you solved or pinpointed the problem – there’s a spot in the list above waiting for a new URL.

    Maybe irrelevant, but there are times when “rowsets” seem to be a critical issue, and I’ve seen a recent anomaly with a combination of UNION ALL and parallelism that revolved around rowsets.

    Regards
    Jonathan Lewis

    Comment by Jonathan Lewis — January 24, 2021 @ 11:34 am GMT Jan 24,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: