PL/SQL Profilers Shashank Barki / @shankbarki 

• Senior principal data engineer in Manhattan associates. • 13 years of industry experience as developer, cloud architect and data analyst. • PL/SQL evangelist. • Life is easy (or tough) when you support Arsenal FC, read PG Wodehouse and eat Mangoes (!). Shashank Barki / @shankbarki 

Agenda 1. PL/SQL performance 2. PL/SQL Profilers 3. PL/SQL Hierarchical Profilers 

• Code flow • Iterations • Number of executions • Sub program calls • Bulk operations ? • Nested calls PL/SQL Performance 

• Collects data for performance improvement • Determining code coverage for PL/SQL applications. • This information includes • The total number of times each line executed • The total amount of time spent executing that line • Minimum and maximum times spent on execution of that line PL/SQL Profiler 

• The profiling information is stored in database tables. • Can build customizable reports (summary reports etc.,) • And you can analyze the data. PL/SQL Profiler 

PL/SQL Hierarchical Profiler • Identify bottlenecks and performance-tuning opportunities • Organized by function calls • Reports SQL and PL/SQL execution times separately • No special source or compile-time preparation • Any PL/SQL program can be profiled 

PL/SQL Hierarchical Profiler • Custom report generation by tools (such as SQL Developer and command line) • Provides subprogram-level execution summary information, such as: • Number of calls to the subprogram • Time spent in the subprogram itself (function time or self time) • Time spent in the subprogram itself and in its descendent subprograms • Detailed parent-children information, for example: • All callers of a given subprogram (parents) • All subprograms that a given subprogram called (children) • How much time was spent in subprogram x when called from y • How many calls to subprogram x were from y 

DBMS_HPROF package has two components: • Data Collection - - The data collection component is an intrinsic part of the PL/SQL Virtual Machine. - The DBMS_HPROF package provides APIs to turn hierarchical profiling on and off and write the raw profiler output to a file or raw profiler data table. • Analyzer - - Processes the raw profiler output and produce analyzed results. - Convert into HTML CLOB report for analysis. 

No content

No content

Resources • https://docs.oracle.com/en/database/oracle/oracle- database/19/adfns/hierarchical-profiler.html#GUID-B2E3A739-08C6- 4648-A65F-1D093A0DADDE • MetaLink Note: 243755.1 

THANK YOU