Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts.There are tons of third party products to analyze SQL statements. They are not very good on PL/SQL. Oracle provides a very powerful built in utilities.
1. DBMS_PROFILER: This built-in package allows you to turn on execution profiling in a session. When you run your code, the Oracle database uses tables to keep track of detailed information about how long each line in your code took to execute. You can then run queries on these tables to present the data in a clear graphical fashion.
2. DBMS_HPROF : Oracle database 11g features a new hierarchical profiler that makes it easier to roll performance results up through the execution call stack.
DBMS_PROFILER provides flat data about performance which makes it difficult to answer questions like "How much time altogether is spent in the ADD_ORDER procedure. The hierarchical profiler makes it easy to answer such questions.
NEXT : How to setup and run DBMS_PROFILER and DBMS_HPROF ?