Finding Slow PL/SQL: Use the Profilers!

Join Steven Feuerstein and Chris Saxon as we hear from our special guest, Shashank Barki, on his experience using PL/SQL profilers (DBMS_PROFILER and DBMS_HPROF) to identify bottlenecks in PL/SQL code.
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.
DBMS_PROFILER is 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.
DBMS_HPROF is 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).
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
asktom.oracle....
Oracle Developers portal: developer.orac...
Sign up for an Oracle Cloud trial: www.oracle.com...

[@arunothayakumara932]

Thanks Shashank Barki

[@gunjansharma3260]

I heard steven's voice after long time. I used his tuts for learning collection. Its great.

[@priyankabarki2954]

Great session Shashank 👍🏼

[@sameerahmad7592]

Hi Shashank, kindly help..I don't have profiler.sql file into that admin folder. How could I get the HTML report then..?

[@dprasannapondy]

Clear explanation 👍👍👍

[@amansinghrajpoot1465]

Geat session. I wanted to know about columns in the report like what IND% represents;

[@muttleychess]

where can I found a book about It ?

[@sabyasachimitra5719]

Presentation could have been better and more organized. The presentation lacks coherence and it's hard to understand. At the beginning the presenter says profiler can be used instead of AWR but later contradicts his own point. It also has lots of verbosity which is quite distracting. He also did not explain the perquisites to run DBMS_PROFILER. I understand it was his first presentation and hopefully it would be bettered next time.

[@filipkomaromi55]

Thanks Shashank Barki