I work on a complex system. We have huge amount of PL/SQL code running at night to do all sorts of tasks. One such task is performing number crunching and storing results in meta data tables. The applications consult these tables during the day to get information quickly.
Our database is a very large one. And the data keeps growing. So the number crunching job kept getting longer and longer. I asked for help from the performance engineering team at our company. Their first idea was a divide and conquer method. So I wrote some korn shell scripts that run 10 jobs in parallel, crunching portions of the data at the same time. There was not a 10 for 1 speed improvement. But it was close. We had a very happy system administration staff working at night because now our jobs finished quickly.
When we started up the system the following year, the number crunching routine slowed back down to old levels. Our chief DBA assumed that we somehow deployed the old code. I had him check the Production korn shell and PL/SQL packages. The correct code was there. So I called in the performance engineering team. They monitored the job the next night. The job finished in record time. Performance engineering said perhaps the gathering of statistics first was the key. Whatever the reason, I am glad we are back to quick speeds.
Our database is a very large one. And the data keeps growing. So the number crunching job kept getting longer and longer. I asked for help from the performance engineering team at our company. Their first idea was a divide and conquer method. So I wrote some korn shell scripts that run 10 jobs in parallel, crunching portions of the data at the same time. There was not a 10 for 1 speed improvement. But it was close. We had a very happy system administration staff working at night because now our jobs finished quickly.
When we started up the system the following year, the number crunching routine slowed back down to old levels. Our chief DBA assumed that we somehow deployed the old code. I had him check the Production korn shell and PL/SQL packages. The correct code was there. So I called in the performance engineering team. They monitored the job the next night. The job finished in record time. Performance engineering said perhaps the gathering of statistics first was the key. Whatever the reason, I am glad we are back to quick speeds.
