Tracing the PL/SQL programs

Code tracing is an important technique to measure the code performance during runtime and identify the expensive areas in the code which can be worked upon to improve the performance. The tracing feature shows the code execution path followed by the server and reveals the time consumed at each step. Often developers assume tracing and debugging as one step, but both are distinctive features. Tracing is a one-time activity which analyses the complete code and prepares the platform for debugging. On the other hand, debugging is the bug identification and fixing activity where the trace report can be used to identify and work upon the problematic points.

Oracle offers multiple methods of tracing:

• DBMS_APPLICATION_INFO: The SET_MODULE and SET_ACTION subprograms can be used to register a specific action in a specific module.

• DBMS_TRACE: The Oracle built-in package allows tracing of PL/SQL subprograms, exceptions and SQL execution. The trace information is logged...