Skip to Main Content
  • Questions

  • Gather STATS on Partitioned Table and Parallel for Partitioned Table

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Sree.

Asked: March 14, 2024 - 2:17 am UTC

Last updated: July 30, 2024 - 5:29 am UTC

Version: 19c

Viewed 1000+ 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's

Stats 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 plan

We can't see your explain plan unless you post it in the question :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Plan is getting changed

sree, July 27, 2024 - 5:28 am UTC

I have multiple Merge statements for updating the partitions tables from other tables.
the process is running fire for fee time, all of sudden one simple insert/merge will get stuck with plan change which was running in few seconds will get stuck for 5 to 7 hrs. and only way is to kill

how can we avoid such scenarios.
i have 1000 of insert and update, not sure which one and when it will be stuck.
my complete process takes 2 hrs to completed, after 1:30 mins it gets stuck in some insert/update and have to kill and rerun.

any easy way to control such often plan changes foe this kind of situations
Connor McDonald
July 30, 2024 - 5:29 am UTC

SQL Plan management is your friend here.

If you know the plan you'd like to enforce, simply make sure that it the only plan allowed.

https://blogs.oracle.com/optimizer/post/how-to-use-sql-plan-management
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.