Hello Tom,
Could you please advise why I'm getting so huge difference in db block gets and redo for insert between range and reference partitioned table?
Db block gets are like 100x more for reference partitioned table and insert is 2-3 times slower.
DB01> create table t1 (id number(19) primary key, ts date)
2 partition by range (ts) interval (numtodsinterval(1, 'DAY')) (partition P0001 values less than (to_date('2024-01-01' ,'YYYY-MM-DD')));
Table created.
DB01>
DB01> insert into t1 (id, ts) values (1, sysdate);
1 row created.
DB01>
DB01>
DB01> -- range interval
DB01> create table t2 (id number(19), t1_id number(19) not null, constraint t2_fk foreign key (t1_id) references t1 (id))
2 partition by range (t1_id) interval (1) (partition values less than (1));
Table created.
DB01> set autotrace trace exp stat
DB01> insert into t2 (id, t1_id) select level, 1 from dual connect by level <= 2000000;
2000000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T2 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LEVEL<=2000000)
Statistics
----------------------------------------------------------
105 recursive calls
51252 db block gets
7237 consistent gets
0 physical reads
147628492 redo size
123 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2000000 rows processed
DB01> set autotrace off
DB01> commit;
Commit complete.
DB01>
DB01>
DB01> -- reference
DB01> create table t3 (id number(19), t1_id number(19) not null, constraint t3_fk foreign key (t1_id) references t1 (id))
2 partition by reference (t3_fk);
Table created.
DB01> set autotrace trace exp stat
DB01> insert into t3 (id, t1_id) select level, 1 from dual connect by level <= 2000000;
2000000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T3 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LEVEL<=2000000)
Statistics
----------------------------------------------------------
144 recursive calls
6078974 db block gets
9930 consistent gets
0 physical reads
696626512 redo size
123 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2000000 rows processed
DB01> set autotrace off
DB01> commit;
Commit complete.
There is an existing (unpublished) enhancement request to improve the performance of inserting into reference partitioned tables. Please contact support to let them know this is affecting you - this helps raise the priority of changes.
In the meantime, check the setting for the DB_BLOCK_CHECKING parameter. If this is FULL or TRUE, you may get some speed gains by setting it to FALSE, OFF, or LOW. Changing this on my 19.20 instance made inserting into the referenced partitioned table faster. Though it still used the same number of block gets and was slower than inserting into a regular partitioned table.