NYOUG 2018 Summer General Meeting: Unduly Forgotten Performance Tuning Hero: PL/SQL Hierarchical Profiler

Unduly Forgotten Performance Tuning Hero: PL/SQL Hierarchical Profiler

The PL/SQL Hierarchical Profiler became available in Oracle 11g Release 1 and replaced the old DBMS_PROFILER package. The goal was the same: to profile runtime behavior of PL/SQL code, i.e. to register and timestamp every operation (including SQL statements) that occurs during the monitoring window. However, the changes were startling:

• Profiler output is now a file that can be generated in one environment and analyzed elsewhere. This makes production debugging significantly easier because performance-tuning specialists don’t need to touch PROD as long as they have access to the same code base.
• In addition to loading profiler output to the database and running queries against it, the command line utility PLSHPROF creates HTML-based reports (human readable!). These reports contain various data aggregations to speed up the review process. This presentation covers a number of real use-cases when HProf significantly shortened response time to production performance problems. You will see how easy it is to figure out the actual source of the slowdown, namely:
• Hundreds of thousands of calls to a pretty light user-defined function –> Check execution plans of corresponding queries
• Sluggish SQL statement –> Don’t blame PL/SQL and start SQL tracing
• Strange third-party calls in wrapped packages –> Collect hard evidence and start complaining Overall, if you write PL/SQL, you must utilize the PL/SQL Hierarchical Profiler. Otherwise, you will be guessing at your code behavior instead of knowing it, which could lead to unpredictable performance. Unpredictable performance often means production calls at 3 am on Sunday. This presentation will help you sleep longer!

Michael Rosenblum
Michael Rosenblum is a Software Architect/Development DBA at Dulcian, Inc. where he is responsible for system tuning and application architecture. Michael supports Dulcian developers by writing complex PL/SQL routines and researching new features. He is the co-author of PL/SQL for Dummies (Wiley Press, 2006), PL/SQL Performance Tuning Tips & Techniques (Oracle Press, 2014), contributing author of Expert PL/SQL Practices (A Press, 2011), and author of several database related articles (IOUG Select Journal, ODTUG Tech Journal) and conference papers. Michael is an Oracle ACE, a frequent presenter at various Oracle user group conferences (Oracle OpenWorld, ODTUG, IOUG Collaborate, RMOUG, NYOUG, etc.), and winner of the ODTUG Kaleidoscope 2009 Best Speaker Award. In his native Ukraine, he received the scholarship of the president of Ukraine, a Master of Science degree in information systems, and a diploma with honors from the Kiev National University of Economics

Demo Files: Hprof_Demo