Running gather table statistics multiple times inside a package causes Performance Issues

Question

We have a big package that ALWAYS encounters performance issues. We get an average of 6-10 tickets raised for this issue in a month. Sometimes the program would run successfully for minutes, sometimes it would run for days just to Error out with an unexplained error.

I started to look deeper into this and found there are a number of possible causes of the performance issues, such as numerous un-tuned SQLs and bad coding practice, etc.

One thing that struck me today is in the code, it's calling Gather Table Statistics multiple times, in multiple places before doing some big operation (such as a huge Select Statement and a lot of DML statements).

This program is run on a daily, weekly and monthly basis, depending on the organization's practices.

Unfortunately, I am unable to replicate the performance issue to know more about this, but I am guessing running Gather Table statistics to multiple tables, multiple times, can cause major performance issues in the program. I am unable to find any resources to back this idea up. Can someone confirm?

Answer 1

Yes, can confirm, have seen code that spends 80% of the runtime gathering stats. Given your constraints, I'd try, in the following order:

1. I'd have a look at the DELETE statements to check if they can be replaced by TRUNCATE TABLE.
2. Gather stats once the tables are filled, lock their stats and comment out any other gather_table_stats calls. The assumption is that the data will not differ widely enough from day to day or week to week to cause different query plans.
3. If that doesn't work, I'd try to have a look at DBA_TAB_MODIFICATIONS to at least check if the tables have been changed enough since the last stats gathering.