Hi! I've searched various online resources and have been unable to find any that addressed this particular problem, so I'm hoping someone here may be able to offer some advice.
I have a stored procedure that is about 300 lines long, takes no parameters, and defines one record type and a few table types internally. When I run it on a particular data set, it takes about 50 seconds to finish in my environment. Unfortunately, it will have to be executed as an anonymous PL/SQL block rather than a stored procedure, due to policies that exist regarding the environment to which I'm deploying. However, when I converted it to an anonymous PL/SQL block, making no other code changes whatsoever and running it from the same schema that owned the stored procedure, it takes about 6.5 minutes (nearly 8 times as long) to complete. Aside from the parsing and validation overhead, which could add a fraction of a second, can anyone please suggest some potential reasons for the large performance difference, and offer advice on what I may be able to do about it? In case it matters, the procedure uses bulk binds (e.g., bulk collect, forall) where it seems to make sense, and does not call any other PL/SQL objects. Thanks in advance.