Post 19c upgrade, SQL seems to be reading too many partitions.
This is 2nd example of 2 very similar problems.
Any thoughts?
CFL table is list:list partitioned by SNAPSHOT_ID & SNAPSHOT_VERSION.
Index is LOCAL, unprefixed, on CASH_FLOW_ID
Driving collection holds three attributes of note - SNAPSHOT_ID & SNAPSHOT_VERSION + CASH_FLOW_ID
Proc tends to be called, as can be see below, with 1 element in the collection.
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Execution Started : 02/16/2021 13:07:22
First Refresh Time : 02/16/2021 13:07:26
Last Refresh Time : 02/16/2021 13:08:01
Duration : 39s
Program : JDBC Thin Client
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B2 | 2 | VARCHAR2(128) | LATEST |
========================================================================================================================
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 40 | 11 | 28 | 0.84 | 1 | 70728 | 8497 | 66MB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=3833018128)
=====================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +39 | 1 | 1 | | | | |
| 1 | NESTED LOOPS | | 1 | 38 | 1 | +39 | 1 | 1 | | | | |
| 2 | NESTED LOOPS | | 1 | 36 | 36 | +4 | 1 | 1 | | | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 29 | 35 | +4 | 1 | 1 | | | | |
| 4 | PARTITION LIST ITERATOR | | 1 | 7 | 1 | +39 | 1 | 1 | | | | |
| 5 | PARTITION LIST ITERATOR | | 1 | 7 | 31 | +9 | 50839 | 1 | | | 2.56 | Cpu (1) |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CFL | 1 | 7 | 38 | +2 | 59731 | 1 | 5828 | 46MB | 56.41 | Cpu (11) |
| | | | | | | | | | | | | cell single block physical read (11) |
| 7 | INDEX RANGE SCAN | IDX_CFL_ID | 17 | 3 | 36 | +4 | 43527 | 534 | 2669 | 21MB | 38.46 | cell single block physical read (15) |
| 8 | TABLE ACCESS BY INDEX ROWID | SNAP | 1 | 2 | 1 | +39 | 1 | 1 | | | | |
| 9 | INDEX UNIQUE SCAN | PK_SNAP | 1 | 1 | 1 | +39 | 1 | 1 | | | | |
=====================================================================================================================================================================================================
Plan hash value: 3833018128
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| | | |
| 1 | NESTED LOOPS | | 1 | 190 | 38 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 157 | 36 (0)| 00:00:01 | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 2 | 29 (0)| 00:00:01 | | |
| 4 | PARTITION LIST ITERATOR | | 1 | 155 | 7 (0)| 00:00:01 | KEY | KEY |
| 5 | PARTITION LIST ITERATOR | | 1 | 155 | 7 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CFL | 1 | 155 | 7 (0)| 00:00:01 | KEY | KEY |
|* 7 | INDEX RANGE SCAN | IDX_CFL_ID | 17 | | 3 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY INDEX ROWID | SNAP | 1 | 33 | 2 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | PK_SNAP | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("CFL"."STATUS"=:B2 AND "CFL"."SNAPSHOT_ID"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
"CFL"."SNAPSHOT_VERSION"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2)))
7 - access("CFL"."CASH_FLOW_ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
9 - access("CFL"."SNAPSHOT_ID"="ST"."SNAPSHOT_ID" AND "CFL"."SNAPSHOT_VERSION"="ST"."VERSION")