Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
PARTITION LIST ITERATOR reading too many partitions
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")
Answers
-
Does every partition have the same number of subpartitions ?
Does each segment hold exactly one (snapshot_id, snapshot_version)?
How many partitions and subpartitions?
Is there any AUTOMATIC partitioning ?
One very odd detail: how do you get 534 rowids from an index, and then have to visit the table 59,731 times?
What happens if you try to set up a materialized WITH subquery from the collection that casts the collection type into the correct column types and join to that ?
Regards
Jonathan Lewis
-
Hi
What would be the execution plan if you will use plain comparison? (I.e. remove the collection from the where clause) .
-
No, every partition does not have the same number of subpartitions.
A snapshot is a particular feed for a particular day - SNAPSHOT_ID.
If a particular feed for a particular day needs to be reloaded then that is a new version - SNAPSHOT_VERSION - new subpartition.
Most partitions will tend to have 1 subpartition.
Not automatic.
I have done an emergency fix to the code by rewriting it - if there is only snapshot_id/version in the collection then set two variables as such and bind it in directly to the where clause rather than just joining to the collection - otherwise use the existing code.
So, I can fix it for what seems to be most/all of the immediate or problematic application use cases. And there are other things I could have done as part of that fix.
The issue is apparent post upgrade. There was a slight plan changed from BATCHED to not, but switching the plan back does not solve the "problem".
ASH sampling suggests that sometimes even for the one row in collection use case many, many partitions are being read (CURRENT_OBJ should be reliable here, right?).
-
Just to confirm that 50839 (execs line #5 in the RTSM output) is the number of partitions in the table.
-
What happens if you try to set up a materialized WITH subquery from the collection that casts the collection type into the correct column types and join to that ?
Actually it's incredibly difficult for me to get access to these schema objects directly :(
This is what happens eventually (UAT environment compared to the prod where I captured the problem information above - same verison 19.6, same patches):
------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 10 | 1 | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | 1 |00:00:00.01 | 10 | 1 | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6743_6D15FB92 | 1 | | | | 0 |00:00:00.01 | 1 | 0 | | 3 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 1 | | | 1 |00:00:00.01 | 0 | 0 | | 4 | NESTED LOOPS | | 1 | 1 | | | 1 |00:00:00.01 | 8 | 1 | | 5 | NESTED LOOPS | | 1 | 1 | | | 1 |00:00:00.01 | 7 | 0 | | 6 | NESTED LOOPS | | 1 | 1 | | | 1 |00:00:00.01 | 4 | 0 | | 7 | VIEW | | 1 | 1 | | | 1 |00:00:00.01 | 0 | 0 | | 8 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D6743_6D15FB92 | 1 | 1 | | | 1 |00:00:00.01 | 0 | 0 | | 9 | PARTITION LIST ITERATOR | | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 4 | 0 | | 10 | PARTITION LIST ITERATOR | | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 4 | 0 | |* 11 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CFL | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 4 | 0 | |* 12 | INDEX RANGE SCAN | IDX_CFL_ID | 1 | 17 | KEY | KEY | 1 |00:00:00.01 | 3 | 0 | |* 13 | INDEX UNIQUE SCAN | PK_SNAP | 1 | 1 | | | 1 |00:00:00.01 | 3 | 0 | | 14 | TABLE ACCESS BY INDEX ROWID | SNAP | 1 | 1 | | | 1 |00:00:00.01 | 1 | 1 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 11 - filter(("CFL"."SNAPSHOT_ID"="GTRF"."SNAPSHOT_ID" AND "CFL"."SNAPSHOT_VERSION"="GTRF"."SNAPSHOT_VERSION" AND "CFL"."STATUS"=:B2)) 12 - access("CFL"."CASH_FLOW_ID"="GTRF"."CASH_FLOW_ID") 13 - access("CFL"."SNAPSHOT_ID"="ST"."SNAPSHOT_ID" AND "CFL"."SNAPSHOT_VERSION"="ST"."VERSION") SQL Plan Monitoring Details (Plan Hash Value=1041549111) ===================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ===================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | . | | | | 1 | TEMP TABLE TRANSFORMATION | | | | 1 | +0 | 1 | 1 | | | . | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6743_6D15FB92 | | | 1 | +0 | 1 | 1 | | | 1024 | | | | 3 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 29 | 1 | +0 | 1 | 1 | | | . | | | | 4 | NESTED LOOPS | | 1 | 11 | 1 | +0 | 1 | 1 | | | . | | | | 5 | NESTED LOOPS | | 1 | 11 | 1 | +0 | 1 | 1 | | | . | | | | 6 | NESTED LOOPS | | 1 | 9 | 1 | +0 | 1 | 1 | | | . | | | | 7 | VIEW | | 1 | 2 | 1 | +0 | 1 | 1 | | | . | | | | 8 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D6743_6D15FB92 | 1 | 2 | 1 | +0 | 1 | 1 | | | . | | | | 9 | PARTITION LIST ITERATOR | | 1 | 7 | 1 | +0 | 1 | 1 | | | . | | | | 10 | PARTITION LIST ITERATOR | | 1 | 7 | 1 | +0 | 1 | 1 | | | . | | | | 11 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CFL | 1 | 7 | 1 | +0 | 1 | 1 | | | . | | | | 12 | INDEX RANGE SCAN | IDX_CFL_ID | 17 | 3 | 1 | +0 | 1 | 1 | | | . | | | | 13 | INDEX UNIQUE SCAN | PK_SNAP | 1 | 1 | 1 | +0 | 1 | 1 | | | . | | | | 14 | TABLE ACCESS BY INDEX ROWID | SNAP | 1 | 2 | 1 | +0 | 1 | 1 | 1 | 8192 | . | | | ===================================================================================================================================================================================================== -
Possibly UAT is of limited benefit here as the original SQL when I try it gives me the following without the problematic access in PARTITION LIST ITERATOR
SQL Plan Monitoring Details (Plan Hash Value=3833018128) ================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | NESTED LOOPS | | 1 | 38 | 1 | +0 | 1 | 1 | | | | 2 | NESTED LOOPS | | 1 | 36 | 1 | +0 | 1 | 1 | | | | 3 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 29 | 1 | +0 | 1 | 1 | | | | 4 | PARTITION LIST ITERATOR | | 1 | 7 | 1 | +0 | 1 | 1 | | | | 5 | PARTITION LIST ITERATOR | | 1 | 7 | 1 | +0 | 1 | 1 | | | | 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CFL | 1 | 7 | 1 | +0 | 1 | 1 | | | | 7 | INDEX RANGE SCAN | IDX_CFL_ID | 17 | 3 | 1 | +0 | 1 | 1 | | | | 8 | TABLE ACCESS BY INDEX ROWID | SNAP | 1 | 2 | 1 | +0 | 1 | 1 | | | | 9 | INDEX UNIQUE SCAN | PK_SNAP | 1 | 1 | 1 | +0 | 1 | 1 | | | =================================================================================================================================================================
-
What would be the execution plan if you will use plain comparison? (I.e. remove the collection from the where clause) .
This doesn't exactly answer your question but in my fix, I count distinct partition/subpartition keys from the in collection. If there is one then a parameterise and bind in. I also for simplicity get the SNAP column separately and end up with:
------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 4 | | 1 | NESTED LOOPS | | 1 | 1 | | | 1 |00:00:00.01 | 4 | | 2 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 1 | | | 1 |00:00:00.01 | 0 | | 3 | PARTITION LIST SINGLE | | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 4 | | 4 | PARTITION LIST SINGLE | | 1 | 1 | | | 1 |00:00:00.01 | 4 | |* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CFL | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 4 | |* 6 | INDEX RANGE SCAN | IDX_CFL_ID | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("CFL"."STATUS"='LATEST' AND "CFL"."SNAPSHOT_VERSION"=:B2)) 6 - access("CFL"."CASH_FLOW_ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)) -
It's slightly interesting that the filter predicate ordering changes when you switch from the collection join to the materialized view join - but that's may be irrelevant (simple costing on "cpu cost of function" rather than some semantic effect).
Is this written as a three table join, or is there an unnested subquery somewhere? I've realised that could join the collection to SNAP then join SNAP to CFL - and that might make a difference to the way Oracle treats the partition key elimination.
One exotic idea that crossed my mind - is it possible that there's a cache mechanism that is used to do partition elimination, and if so is it possible that the cache has overflowed and the fallback position is to check everything.
You said 50,839 partitions - did you mean data segments or did you really mean partitions of which some may have multiple subpartitions to give a larger number of data segments.
Is the snap_version consistent across all partitions - i.e. does every snap_id have a snap_version '01' (say), then a few also have '02', then a handful with '03'. Could that affect the way Oracle tries to eliminate - can we tell if it's iterating subpartitions first or partitions first, and might this change if the predicates were applied in a different order.
Regards
Jonathan Lewis
-
With a bit of tidying, It is/was written as
OPEN p_refcursor FOR SELECT st.business_datetime business_date , cfl.cash_flow_start_date cash_flow_date , cfl.* FROM TABLE ( cast (:cf AS TYP_CF)) gtrf INNER JOIN cfl ON cfl.cash_flow_id = gtrf.cash_flow_id AND cfl.snapshot_id = gtrf.snapshot_id AND cfl.snapshot_version = gtrf.snapshot_version AND cfl.status = gc_status INNER JOIN snap st ON cfl.snapshot_id = st.snapshot_id AND cfl.snapshot_version = st.version;COUNT(*) CFL Partitions - 50839
COUNT(*) CFL Subpartitions - 59732
Yes, every partition has a version 1 subpartition - pre-created because every feed will at least try and load once. Most feeds will not have version 2, etc subpartitions but these are created on the fly as feeds are reloaded. Very occasionally one partition might go up to double digits of subpartitions.
-
Follow-up to exotics, I've just found the parameter
_ptn_cache_threshold -> flags and threshold to control partition metadata caching
Regards
Jonathan Lewis
In answer to your question about current_obj# - I'd trust it.
