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)));
Watch video How to identify bottlenecks quickly and easily in PL/SQL packages and procedures online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 10 March 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 50 once and liked it 1 people.