Enforcing a re-parse of a cursor in Autonomous Database. Using a hammer

While setting up a demo environment in my Autonomous Database I found that one of my tuning techniques – evicting a cursor from the shared pool – isn’t quite as straight forward as it could be. In fact, at the time of writing it wasn’t possible to force a cursor reparse in Autonomous Database (to the best of my knowledge).

The problem

Executing my flush.sql script failed, and from what I can tell, dbms_shared_pool is not exposed to users in Autonomous Transaction Processing database at the moment:

SQL> show user
USER is "ADMIN"
SQL> desc sys.dbms_shared_pool
ERROR:
ORA-04043: object sys.dbms_shared_pool does not exist

If dbms_shared_pool isn’t accessible, it makes it pretty hard to purge a cursor with the surgical precision this package offers.

A potential workaround exists

There is a workaround, however it affects all cursor referencing a table. It’s neither granular, nor very elegant and has the potential to do more harm than good due to the potentially high number of cursor invalidations!. I’d say the workaround sits somewhere between dbms_shared_pool.purge and flushing the shared pool altogether.

Due to its broad scope, be very careful applying this workaround, and use all industry best known methods prior to its use, refrain from using it unless you are absolutely sure about what you are about to do. Consider yourself warned.

Setting the scene

As always, I’m using Swingbench for this example. I created the SOE schema in the Autonomous Transaction database, it’s quite straight forward to do and documented on Dominic Giles’ blog. I had to massage the data model a little after creating a rather large number of rows to reproduce my classic bind variable issue.

Consider the ORDERS table, and more specifically, ORDER_MODE_EXT:

SOE> select count(*), order_mode_ext from orders group by order_mode_ext;

  COUNT(*) ORDER_MOD
---------- ---------
  12625080 online
    113920 undefined
    231820 direct

3 rows selected. 

I created an index and frequency histogram on order_mode_ext, and I’m triggering an index-based access path by using a SQL statement and a bind variable. You will notice that I’m running SQL scripts, these are of course not mine, they are Tanel Poder’s.

SQL> var om varchar2(20)
SQL> exec :om := 'undefined' 

PL/SQL procedure successfully completed.

SQL> select /* atp-test */ /*+ gather_plan_statistics */ count(*) from orders where order_mode_ext = :om;

  COUNT(*)
----------
    113920

1 row selected.

SQL_ID: 1cw6t95y0jcsm 

Modern SQL*Plus can print a SQL ID if you set “feedback on SQL_ID” by the way, as you can see. Let’s have a look at the execution plan:

SQL> select plan_hash_value, child_number, invalidations, executions, is_bind_aware, is_bind_sensitive 
 2 from v$sql where sql_id = '1cw6t95y0jcsm';

PLAN_HASH_VALUE CHILD_NUMBER INVALIDATIONS EXECUTIONS I I
--------------- ------------ ------------- ---------- - -
      545586833            0             0          1 N Y

SQL> @xi 1cw6t95y0jcsm %
eXplain the execution plan for sqlid 1cw6t95y0jcsm child %...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  1cw6t95y0jcsm, child number 0
-------------------------------------
select /* atp-test */ /*+ gather_plan_statistics */ count(*) from
orders where order_mode_ext = :om

Plan hash value: 545586833

-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |   311 (100)|      1 |00:00:00.01 |     358 |
|   1 |  SORT AGGREGATE   |                |      1 |      1 |            |      1 |00:00:00.01 |     358 |
|*  2 |   INDEX RANGE SCAN| I_ORDERS_OMEXT |      1 |    113K|   311   (1)|    113K|00:00:00.01 |     358 |
-----------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'undefined'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_MODE_EXT"=:OM)


25 rows selected. 

Tanel’s scripts prove the expected index-based access path was chosen for my bind value. Indexed based access is ok for this particular bind value (‘undefined’) because it’s a small subset of the table. But what about the other extreme, ‘online’ orders? Let’s try passing a different bind variable:

SQL> exec :om := 'online'

PL/SQL procedure successfully completed.

SQL> select /* atp-test */ /*+ gather_plan_statistics */ count(*) from orders where order_mode_ext = :om;

  COUNT(*)
----------
  12625080

1 row selected. 
 
SQL_ID: 1cw6t95y0jcsm

Again, let’s have a look at the shared pool:

SQL>@xi 1cw6t95y0jcsm %
eXplain the execution plan for sqlid 1cw6t95y0jcsm child %...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  1cw6t95y0jcsm, child number 0
-------------------------------------
select /* atp-test */ /*+ gather_plan_statistics */ count(*) from
orders where order_mode_ext = :om

Plan hash value: 545586833

-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |   311 (100)|      1 |00:00:01.01 |   33760 |
|   1 |  SORT AGGREGATE   |                |      1 |      1 |            |      1 |00:00:01.01 |   33760 |
|*  2 |   INDEX RANGE SCAN| I_ORDERS_OMEXT |      1 |    113K|   311   (1)|     12M|00:00:01.36 |   33760 |
-----------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'undefined'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_MODE_EXT"=:OM)


25 rows selected.

SQL>select plan_hash_value, child_number, invalidations, executions, is_bind_aware, is_bind_sensitive
 2 from v$sql where sql_id = '1cw6t95y0jcsm';

PLAN_HASH_VALUE CHILD_NUMBER INVALIDATIONS EXECUTIONS I I
--------------- ------------ ------------- ---------- - -
      545586833            0             0          2 N Y  

No change after the second execution (I know, this isn’t the full story).

Triggering the re-parse

Let’s assume further that PHV 545586833 (the index-based plan) causes immense problems for the application and you need the database to reparse quickly since you know the “wrong” bind variable has been passed to the cursor as a one-off. You also realise that you forgot to implement plan stability measures to prevent this problem from occurring in the first place.

Keeping with the story, you don’t want to wait for Adaptive Cursor Sharing to help you, and need to take action. In the past, you could have used dbms_shared_pool to remedy the situation, but again, it’s not available at the time of writing.

There is another option though, but it comes with caveats.

From what I can tell the process you are about to read is the only option to force a reparse of a cursor referencing the table, but it’s a blunt and harsh method. It will force a re-parse of all cursors referencing the table. Only use it if there is no other option! You may end up fixing one problem and causing many others.

A table-DDL operation causes Oracle to re-parse all SQL statements referring to the table. After the most careful considerations about the side-effect on other SQL statements you have been given the green light by management to go ahead. The potential gain is considered worth the pain.

Before I made any changes, here’s the state of play:

SQL> select object_name, object_type, sysdate as now, to_char(last_ddl_time,'dd.mm.yyyy hh24:mi:ss')
 2 from dba_objects where object_name = 'ORDERS' and owner = 'SOE';

OBJECT_NAME                    OBJECT_TYPE             NOW                 TO_CHAR(LAST_DDL_TI
------------------------------ ----------------------- ------------------- -------------------
ORDERS                         TABLE                   2020-10-25 19:43:36 25.10.2020 19:06:34

The degree of parallelism on my table is 1 (noparallel), so let’s try a DML operation.

SQL> select degree from dba_tables where owner = 'SOE' and table_name = 'ORDERS';

DEGREE
----------------------------------------
         1

SQL> alter table soe.orders noparallel;

Table altered.

SQL> select degree from dba_tables where owner = 'SOE' and table_name = 'ORDERS';

DEGREE
----------------------------------------
         1

SQL> select object_name, object_type, sysdate as now, to_char(last_ddl_time,'dd.mm.yyyy hh24:mi:ss')
 2 from dba_objects where object_name = 'ORDERS' and owner = 'SOE';

OBJECT_NAME                    OBJECT_TYPE             NOW                 TO_CHAR(LAST_DDL_TI
------------------------------ ----------------------- ------------------- -------------------
ORDERS                         TABLE                   2020-10-25 19:44:24 25.10.2020 19:44:12

No change made to the table DOP, but the last DDL time changed. This will force ALL cursors referencing this table to become invalidated.

Now let’s run the “problem” query again, and check the execution statistics:

SQL> select /* atp-test / /+ gather_plan_statistics / count(*) from orders where order_mode_ext = :om; 

  COUNT(*)
----------
  12625080

1 row selected.

SQL_ID: 1cw6t95y0jcsm

SQL>@xi 1cw6t95y0jcsm %
eXplain the execution plan for sqlid 1cw6t95y0jcsm child %...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  1cw6t95y0jcsm, child number 0
-------------------------------------
select /* atp-test */ /*+ gather_plan_statistics */ count(*) from
orders where order_mode_ext = :om

Plan hash value: 3862103574

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |   594 (100)|      1 |00:00:01.10 |   34845 |
|   1 |  SORT AGGREGATE               |                |      1 |      1 |            |      1 |00:00:01.10 |   34845 |
|*  2 |   INDEX STORAGE FAST FULL SCAN| I_ORDERS_OMEXT |      1 |     12M|   594  (31)|     12M|00:00:01.44 |   34845 |
-----------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'online'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ORDER_MODE_EXT"=:OM)


25 rows selected.

SQL>select plan_hash_value, child_number, invalidations, executions, is_bind_aware, is_bind_sensitive
 2 from v$sql where sql_id = '1cw6t95y0jcsm';

PLAN_HASH_VALUE CHILD_NUMBER INVALIDATIONS EXECUTIONS I I
--------------- ------------ ------------- ---------- - -
     3862103574            0             1          1 N Y   

As a direct consequence of the DDL operation against the orders table a new plan has been chosen for SQL ID 1cw6t95y0jcsm (and all other statements referencing table!). As you can see there isn’t a new child cursor for SQL ID 1cw6t95y0jcsm, the old cursor has been evicted completely.

Remember that a DML operation as the one shown in this post is a blunt weapon and only to be used as a last resort.