The PL/SQL engine offers two profiler utilities to help identify performance bottlenecks in your application:
1. DBMS_PROFILER
That's the name of the package that provides an API to the profiler that computes the time that your PL/SQL program spends at each line, in each subprogram. Saves runtime statistics in database tables, which you can then query.
2. DBMS_HPROF
The hierarchical profiler; this utility reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. It accounts for SQL and PL/SQL execution times separately. Requiring no special source or compile-time preparation, it generates reports in HTML. You can also store profiler data and results in relational format in database tables for custom report generation (such as third-party tools offer).
You can find lots more information about these two profilers in the documentation.
In our June 2nd 2020 Office Hours session, I am very pleased to have Shashank Barki, an experienced database developer who has made extensive use of DBMS_PROFILER, present his experiences and lessons learned on these key utilities.
Shashank is a Senior Principal Data Engineer at Manhattan Associates. He has 13 years experience as a developer, cloud architect and data analyst. In each of his jobs, he has promoted the use of PL/SQL to improve the performance, security and maintainability of their applications.
We hope you can join us on June 2nd, 11 AM Eastern, to learn more about PL/SQL profilers.
1. DBMS_PROFILER
That's the name of the package that provides an API to the profiler that computes the time that your PL/SQL program spends at each line, in each subprogram. Saves runtime statistics in database tables, which you can then query.
2. DBMS_HPROF
The hierarchical profiler; this utility reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. It accounts for SQL and PL/SQL execution times separately. Requiring no special source or compile-time preparation, it generates reports in HTML. You can also store profiler data and results in relational format in database tables for custom report generation (such as third-party tools offer).
You can find lots more information about these two profilers in the documentation.
Shashank is a Senior Principal Data Engineer at Manhattan Associates. He has 13 years experience as a developer, cloud architect and data analyst. In each of his jobs, he has promoted the use of PL/SQL to improve the performance, security and maintainability of their applications.
We hope you can join us on June 2nd, 11 AM Eastern, to learn more about PL/SQL profilers.
Comments
Post a Comment