Thanks for the question, Sree.
Asked: March 14, 2024 - 2:17 am UTC
Last updated: March 18, 2024 - 3:10 am UTC
Version: 19c
Viewed 100+ times
You Asked
hi
I have a Partitioned(List) table by a VERSION_ID, which has around 15 million per partition.
We have daily partitioned ID created bulk insert for 15 Million rows with 500 columns and then have 10 updates(MERGE UPDATE) for multiple columns from multiple other tables.
is it good to gather stats after insert once and then after multiple update once. What is good practice for performance in gather stats for these partitioned table scenarios's
second question, when i use merge on partition table from other partioned table, i am seeing the below in explain plan when i use Parallel DML hint.
PDML disabled because single fragment or non partitioned table used
and Connor said...
We have daily partitioned ID created bulk insert for 15 Million rows with 500 columns and then have 10 updates(MERGE UPDATE) for multiple columns from multiple other tables.For a start, look at consolidating this into a single insert if possible. Updates are expensive.
is it good to gather stats after insert once and then after multiple update once. What is good practice for performance in gather stats for these partitioned table scenarios'sStats are never the "goal", performance is the goal. So only gather stats as a mean of avoiding a performance issue. This *might* mean after each insert, it might mean after every 5 inserts etc. And don't forget the benefit of *setting* stats. If you *know* you just inserted 15million rows, then perhaps look at simply setting the changed stats rather than re-calculating them.
Also check out incremental statistics for partitioned tables.
https://blogs.oracle.com/optimizer/post/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1 i am seeing the below in explain planWe can't see your explain plan unless you post it in the question :-)