performance issue for inserting into subpartiotioned tables
Hi All,
My DB is 11.1
Some SQLs , which look like 'insert into table1 select * from view_name' ran well for tables of which have 10 partitions but no subpartitions.
But yesterday we re-orged these tables to subpartitioned tables for a project. And now they have 10 partitions that in turn have 40 subpartitions respectively.
I noticed that these SQL suddenly ran very slowly today and the following events become predominat waits.
I think it may have something to do with too many subpartitions. I just guess whether it will cause more contention on sement headers to allocate spaces for every subpartition?
My DB is 11.1
Some SQLs , which look like 'insert into table1 select * from view_name' ran well for tables of which have 10 partitions but no subpartitions.
But yesterday we re-orged these tables to subpartitioned tables for a project. And now they have 10 partitions that in turn have 40 subpartitions respectively.
I noticed that these SQL suddenly ran very slowly today and the following events become predominat waits.
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time enq: HW - contention 35,148 3 141,014 4012 0.73 15.18 latch: cache buffers chains 313,714 0 105,737 337 6.54 11.39
I think it may have something to do with too many subpartitions. I just guess whether it will cause more contention on sement headers to allocate spaces for every subpartition?
0