Fine Grained Cursor Invalidation on 18c

In the previous post we discussed about parallel query cursor invalidation and ORA 12842. In 11.2.0.4 we had a hard time dealing with cursor invalidation error and serial downgrade. We had to choose between one of the two evils.

In Oracle Database 12.2, we have a new feature called fine grained cursor invalidation. This is controlled by a new parameter cursor_invalidation. Prior to 12.2, cursors were invalidated immediately after a DDL was issued. This DDL could be any of the partition maintenance operations such as move partition, truncate partition or a partition exchange. But with 12.2 Oracle does not invalidate them immediately. Rather, we now have a set of rules based on individual cursor dependencies which decides whether a cursor should be invalidated immediately or deferred.

Here is a small test case to demonstrate the problem on 11.2

/*PREPARE the test table*/

--DROP TABLE TMP_TN2;

CREATE TABLE TMP_TN2

(

    ID    NUMBER

)

PARTITION BY LIST (ID)(PARTITION P_1 VALUES (1), PARTITION P_2 VALUES (2));

BEGIN
  INSERT INTO tmp_tn2
  SELECT /*+parallel(8)*/
                 1
          FROM dba_objects t1,
               dba_objects t2,
               dba_objects t3
          WHERE ROWNUM < 100000000;
  COMMIT;
END;
/
/* START QUERY IN SESSION 1 */

DECLARE
  lv_c NUMBER;
BEGIN
  dbms_output.put_line('START');

  SELECT SUM(c)
  INTO lv_c
  FROM
  (
    SELECT /*+ PARALLEL(4)*/
           COUNT(1) c
    FROM tmp_tn2 partition(P_1) t1,
         tmp_tn2 partition(P_1) t2,
         tmp_tn2 partition(P_1) t3
    UNION ALL
    SELECT /*+ PARALLEL(4)*/
           COUNT(1) c
    FROM tmp_tn2 partition(P_1) t1,
         tmp_tn2 partition(P_1) t2,
         tmp_tn2 partition(P_1) t3
    WHERE ROWNUM < 1000000
  );
    
END;
/

/* INVALIDATE CURSOR IN SESSION 2. Let this run until session 1 fails with ORA 12842  */


BEGIN
  WHILE(TRUE)
  LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE TMP_TN2 TRUNCATE PARTITION P_2';
      dbms_lock.sleep(5);
   END LOOP;
END;
/

In 11.2, first session fails with ORA 12842. But on my 18c database my first session never fails.
Now the optimizer is much more intelligent, it realizes that the current cursor is accessing only one partition P_1. And truncate of P_2 from second session will not affect the existing cursor in anyway. Therefore that is not invalidated. Hence avoiding ORA 12842 or serial downgrade.

Note that add and drop partition would still invalidate the cursors because it changes the partition number. Instead of a drop, it is better to exchange or truncate the partition.