PLSQL Script Performance Optimization -- 2

I have several production PL SQL scripts that are starting to show their age: execution times have crept up and they are consuming more CPU and memory than I am comfortable with. The primary focus of this project is therefore PLSQL itself, not application-level logic or UI work.

What I need from you is a careful optimisation of the existing codebase. The goal is two-fold—raise overall performance and cut back on resource usage—without changing the functional output in any way. You will be working with code that is already in use, so a light-touch, incremental approach that can be verified against current results is essential.

Deliverables I expect
• Revised PL SQL scripts ready for drop-in replacement
• A short before/after performance summary (timings and resource figures)
• Brief notes on the techniques applied so future maintenance remains straightforward

I will provide the current scripts, execution plans and any relevant schema information as soon as we start. If you are comfortable profiling with native Oracle tools (TKPROF, SQL*Plus AUTOTRACE, SQL Monitor) and rewriting with techniques like bulk processing, analytic functions or index hints, you will be right at home.

Please let me know your typical turnaround time for an initial pass and any similar optimisation work you have recently completed.