Automatic indexing - indexing JSON - Part III

In the previous blogpost on how JSON expression can now be automatically indexed, there was an outstanding issue with the associated CBO immediately after the creation of automatic index.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  9154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  9154   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
If we look at the statistics of the execution plan, we notice both the numbers consistent gets (478790) and physical reads (478784), remain very high and identical to the pervious full table scan.
 
The first issue is with the estimated cardinality in the execution plan. The plan got a cost of 9154 but most importantly an estimated cardinality of 100K rows, but actuals were only one row returned. The estimates are way off and so therefore are the associated cost. This could potentially result in an inefficient plan and with index not being used by the CBO.
 
Basically, although autotrace suggests the newly created automatic index is being used, in fact the previous Full Table Scan plan is still being invoked.  (Note: this of course is one of the dangers of the autotrace plan, in that it might not display the actual plan being invoked).
 
 
So what’s going on there? It was due to one of the new feature introduced in Oracle 12c Release 2 – fine grained cursor invalidations – the ability to defer the invalidation of depended SQL cursor when an index is created or modified.
 
When an AI was created in Oracle 21c, the current SQL cursors are not invalidated (to reduce the overhead of having to potentially reparse of large number of current SQL cursors). However, this means the current inefficient sql statement will keep their existing sub-optimal execution plan post the creation of the newly created automatic indexes, until the existing sql cursors are aged out.
 
At which point, the new CBO plan using the AI will actually be invoked.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 t11 where json_value(t11.my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   339 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |     1 |   339 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
So just be aware that in Oracle database 21c, that your beautifully created AI may not actually get used a desired period of time.