Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PARTITION LIST ITERATOR reading too many partitions

Dom BrooksFeb 16 2021 — edited Feb 16 2021

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

Comments

Pierre Forstmann
You can specify a PARTITION clause in SELECT statement but this works only for a single partition:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table x purge;

Table dropped.

SQL> drop table y purge;

Table dropped.

SQL>
SQL> create table x
  2  (
  3  c1  number(5),
  4  c2   varchar2(30),
  5  c3   date
  6  )
  7  partition by list (c1)
  8  (
  9   partition c1p1 values(1),
 10   partition c1p2 values(2),
 11   partition c1p3 values(3)
 12  );

Table created.

SQL>
SQL>
SQL> alter table x add c4 number not null;

Table altered.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from x;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL> create table y as select * from x partition (c1p1);

Table created.

SQL> select * from y;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1

SQL>
It could be easier to create the new table empty and to use partitions exchanging to copy partitions to the new table:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2762



Edited by: P. Forstmann on 20 nov. 2009 09:48
Anurag Tibrewal
Hi,

I do not think so you can create partition table with CTAS.

You can try
1) Inserting with APPEND hint
2) Creating individual tables for each partition and then doing exchange partition.
3) expdp/impdb to the empty paritition table. (Original table should be renamed or should not exist in destination schema)

Regards
Anurag
Pierre Forstmann
Here is an example with partitions exchanging that requires new table is created empty with same partitioning structure:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table x purge;

Table dropped.

SQL> drop table y purge;

Table dropped.

SQL> drop table tmp purge;

Table dropped.

SQL>
SQL> whenever sqlerror exit failure;
SQL>
SQL> create table x
  2  (
  3  c1  number(5),
  4  c2   varchar2(30),
  5  c3   date,
  6  c4   number
  7  )
  8  partition by list (c1)
  9  (
 10   partition c1p1 values(1),
 11   partition c1p2 values(2),
 12   partition c1p3 values(3)
 13  );

Table created.

SQL>
SQL>
SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from x;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL>
SQL> create table y
  2  (
  3  c1  number(5),
  4  c2  varchar2(30),
  5  c3  date,
  6  c4  number
  7  )
  8  partition by list (c1)
  9  (
 10   partition c1p1 values(1),
 11   partition c1p2 values(2),
 12   partition c1p3 values(3)
 13  );

Table created.

SQL>
SQL> create table tmp as select * from y where 1=0;

Table created.

SQL>
SQL> alter table x exchange partition c1p1 with table tmp;

Table altered.

SQL> alter table y exchange partition c1p1 with table tmp;

Table altered.

SQL> alter table x exchange partition c1p2 with table tmp;

Table altered.

SQL> alter table y exchange partition c1p2 with table tmp;

Table altered.

SQL>
SQL> select * from x;

no rows selected

SQL> select * from y;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL>
733226
Now i'm trying so...i have created the table without partitions with ctas and i'm inserting the data into an empty partitioned table..but it's taking very much time...
733226
But one my two tables is without partitons beacuse has been created with the "CREATE TABLE AS SELECT" statement...so i can't use this solution :(
Rob van Wijk
user12195888 wrote:
This table is very very big, so i can't use the INSERT statement(it take 16 hours when create table as takes 30 minutes)
This is a strange statement. Here are my findings:
SQL> set timing on
SQL> create table very_very_big (id,name)
  2  as
  3   select level
  4        , 'Name' || to_char(level)
  5     from dual
  6  connect by level <= 10000000
  7  /

Tabel is aangemaakt.

Verstreken: 00:00:29.05
SQL> drop table very_very_big purge
  2  /

Tabel is verwijderd.

Verstreken: 00:00:00.06
SQL> create table very_very_big
  2  ( id   number(8)
  3  , name varchar2(12)
  4  )
  5  partition by range (id)
  6  ( partition p1  values less than (1000000)
  7  , partition p2  values less than (2000000)
  8  , partition p3  values less than (3000000)
  9  , partition p4  values less than (4000000)
 10  , partition p5  values less than (5000000)
 11  , partition p6  values less than (6000000)
 12  , partition p7  values less than (7000000)
 13  , partition p8  values less than (8000000)
 14  , partition p9  values less than (9000000)
 15  , partition p10 values less than (maxvalue)
 16  )
 17  /

Tabel is aangemaakt.

Verstreken: 00:00:00.00
SQL> insert /*+ append */ into very_very_big
  2   select level
  3        , 'Name' || to_char(level)
  4     from dual
  5  connect by level <= 10000000
  6  /

10000000 rijen zijn aangemaakt.

Verstreken: 00:00:36.03
Not that much slower ...
Did you use the APPEND hint?

Regards,
Rob.
Hoek
Hi,

Maybe this will give you ideas:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5532451667350#tom5544884963551
1 - 7

Post Details

Added on Feb 16 2021
#general-database-discussions
38 comments
1,250 views
2 mentions