EDUCAÇÃO E TECNOLOGIA

SQL Analyzer now available in Business Application Studio Part 2

In my previous blog, I have explained how to enable the new SQL Analyzer in Business Application Studio and upload a PlanViz file for analysis.

We will now look into the details of each feature available with the new SQL Analyzer. Multiple PLV files can be opened and all the open ones will be displayed as a list in the PLAN GRAPHS of the left pane. Only one PLV can be the active which will be shown and others will be hidden and only shown as a tab with the file name on top.

First, lets start looking into the details of the left pane besides the PLAN GRAPHS

  • PLAN GRAPHS – list of all open PLV files
  • Top 5 Dominant Operators – The top 5 operators that took the most implicit execution time. Clicking on the operator will display the selected operator in Plan Graph
  • Top 10 Critical Path Operators – The top 10 operators that took the most implicit execution time of the critical path. The critical path is shown in the Plan Graph as a thick red arrows showing the most time consumed execution path of operators.
  • Last 10 Visited Operators – When clicking on a operator in Plan Graph, the operator is listed here so that it can be easily revisited when needed.
  • Bookmarks – A bookmark can be created for a specific operator so that it can be used during communication with others by referencing the stored bookmarks.

In the right side, the pane is split in 2 and there are 2 menu areas.

The Main Menu on top which have the following sections.

  • Overview – Basic information regarding the plan
  • Statement Statistics – Shows executed SQL statements within a stored procedure.
  • Plan Graph – detailed plan shown in graph
  • SQL – information regarding the SQL statement of the plan

The Sub Menu in the middle

  • Operators – list of operators within the current plan
  • Timeline – shows the timeline of operators
  • Table Access – list of tables accessed by the current plan
  • Properties – shows properties of operator selected in the main Plan Graph
  • Recommendation – Provides detailed recommendation to improve execution performance
  • Compilation Summary – shows information around compilation
  • Performance Trace – list of operators which have performance trace details
  • Function profiler – list of operators which have function profiler in detail

Let’s now look into the details of the main menu areas starting with the Overview. It shows the compilation time and, total execution time, execution start time and peak memory during execution.

The next available information in the Overview section is the dominant plan operators with the relative percentage of the overall time consumed by the operator. Detailed information of the information available can be referenced from the blue question mark icon that will display a popup window with description of the what information is provided.

Next information is Data Usage which was previously Data Flow in HANA Studio which only displayed record count and accessed table count. Now, the number of virtual tables and cross db objects are also listed with the number as a link to display the full list of tables accessed.

Another useful information from the overview section is the context which summarizes the SQL statement, db user, schema with session variables and parameter values so the plan can be re-executed with all the necessary information as session variables affect how the plan is compiled and executed.

The detail of these information can be found in the SQL section of the main menu with the full SQL statement and all session variables that is part of the SQL Plan Cache key or defines a Workload Class as part of Workload Management. Also, parameter values are captured for procedures or parameterized views which was not previously available.

Another new feature available in the overview section is the new SQL Performance Recommendations information and by clicking on the number will show the details of the recommendation  in the lower sub menu pane.

For the next blog, we will look into the details of the Plan Graph and corresponding sub menu items.