Скачать или смотреть How to identify bottlenecks quickly and easily in PL/SQL packages and procedures

I want to share a recent experience of helping a developer with slow running package.
The main procedure was calling other procedures which were calling other procedures and visual inspection could not identify the source of slowness due to length and complexity.

In this session I cover how to profile a simple package/procedure using Oracle SQL Developer.
The profile shows call stack and execution time of each procedure.

In the real world, packages are more complex and if profile did not help identify root cause,
you need a more generalized method that can be used with any package/procedure.

The generalized method is to sprinkle timers and using PRINT statements.

DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP || ' - BEGIN ' || utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1)));
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP || ' - END ' || utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1)));