I have noticed a weird behaviour with Update queries on json data.
I have a table named test created using CREATE TABLE test (DATA NCLOB NOT NULL);
I am using it to store JSON data. I also have an index created using
CREATE UNIQUE INDEX testidx ON test(json_value(data, '$.ID.string()' ERROR ON ERROR));
Now by default I set CURSOR_SHARING to FORCE for each session.
If I try to run my Update queries which look like:
UPDATE test SET data = JSON_TRANSFORM( data, SET '$."abc"' = '1') WHERE (JSON_VALUE(data, '$.ID') = 'id');
In each query, I just change the id.
Here, upto 23.5
the execution plan looked like
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 1 | UPDATE | TEST | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 |
|* 3 | INDEX UNIQUE SCAN | TESTIDX | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TEST"."SYS_NC00002$"=:SYS_B_3)
and hence there was a single cursor created for all my queries.
Since 23.6
the execution plan is almost the same, but the ID literal is hardcoded instead:
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TEST"."SYS_NC00002$"='000001')
and therefore, a new cursor is created for every query.
Each cursor has the same SQL text:
UPDATE test SET data = JSON_TRANSFORM( data, SET :"SYS_B_0" = TO_NCLOB(:"SYS_B_1") FORMAT JSON) WHERE JSON_VALUE(data, :"SYS_B_2") = :"SYS_B_3"
When i inspect v$sql_shared_cursor, I notice that everything in the cursors match other than HASH_MATCH_FAILED
----------------
Due to this, I also notice significant performance issues as well.
Edit: I tried passing the ID using OJDBC Prepared Statements and the cursor is shared as expected. The problem is only for raw sql queries with cursor_sharing.