Forum Stats

  • 3,827,865 Users
  • 2,260,833 Discussions
  • 7,897,399 Comments

Discussions

PARTITION LIST ITERATOR reading too many partitions

Dom Brooks
Dom Brooks Member Posts: 5,560 Silver Crown
edited Feb 16, 2021 1:48PM in General Database Discussions

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")
 
«134

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,980 Blue Diamond

    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

  • evgenyg
    evgenyg Member Posts: 337 Bronze Badge

    Hi

    What would be the execution plan if you will use plain comparison? (I.e. remove the collection from the where clause) .

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Feb 16, 2021 2:52PM

    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?).

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown

    Just to confirm that 50839 (execs line #5 in the RTSM output) is the number of partitions in the table.

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Feb 16, 2021 5:54PM

    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 |     . |          |                 |
    =====================================================================================================================================================================================================
    
  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Feb 16, 2021 5:38PM

    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 |          |                 |
    =================================================================================================================================================================
    
  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Feb 16, 2021 5:56PM

    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))
    
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,980 Blue Diamond

    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

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Feb 16, 2021 6:09PM

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,980 Blue Diamond

    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.