Package Analysis

We got this big PL/SQL package that contains code which runs every night. The thing does a lot of stuff such as probem detection/correction, business rule implementation, and metadata regeneration. The code has been growing over the years. This past year the job start running too long. We tried some hacks but did not get very far. We are still attempting some hacks to get the darn thing to complete on time.

Luckily I got assigned the task to analyze this monster. I started by documenting what each part of the massive job does. It was a reverse requirements analysis task. Then I started to catalog which tables each of the procedures used, and which tables got modified by the procedures. I am starting to come up with an understanding of what procedures depend on what other ones. Isn't there some software that can do this automatically for me? If not, might be time to write some.