“A picture is the easiest word, and a word can be the most difficult picture.”  This also applies to the database performance statistics analysis as well.  Especially, in case of comparing multiple databases, long-term analysis on a particular database, and analysis on the correlation among Instance/ Schema/ Program/ SQL, if the Data Visualization technique does not apply, the corresponding tasks can be very stressful and time-consuming.  Therefore, MaxGauge offers 3 types Data Visualization techniques in order to provide convenience to the users in regards to the task of analysis. 

The Data Visualization techniques which have been applied to MaxGauge PA are as follows.

Table 3-1. Data Visualization Techniques Applied Screen Summary

The Datapath View provides a function for performance comparisons of multiple databases (or multiple dates of a single database) and analysis of two-way correlation of Instance/ Schema/ Program/ SQL.

The Datapath can satisfy the following demands made by users.

•  I want to be able to easily compare the workload between the RAC nodes.
•  I want to be able to easily compare the daily workloads of a particular database. (For example, I want to be able to intuitively compare the daily workloads of before and after tuning, or compare a month’s workload by each day.)
•  I want to be able to easily check the amount of workload of each node or a node in which a particular program has been executed in a RAC environment.
•  I want to be able to easily check the node in which a particular SQL was executed in a RAC environment, and the program which executed the corresponding SQL.

To start the performance analysis by using the Datapath View, use the Navigation Bar located on the left side of the screen. The Navigation Bar provides the following search function and items.

Find Instance

The Find Instance provides the search window, equipped with “Autocomplete” function for a convenient search of the target database.

Image 3-1.Find Instance

Note. Reference the “Target Database Search Using the Autocomplete” section in Getting Started (PA) regarding the autocomplete function.

Instance List

The Instance List provides a list of the target databases. The user can directly choose the desired database for analysis. Click on the checkbox next to Group for a group selection, and click on the checkbox next to the database for a single selection.

Image 3-2. Instance List Initial Screen

Image 3-3. After Clicking on the Group Checkbox

Calendar

The Calendar is used to set the analysis period. It is possible to analyze a particular date of multiple databases, and also, multiple dates of a particular database with the Datapath View as well. Therefore, UI changes according to the number of databases selected from the Instance List. In other words, if only a single database is selected, then the Calendar UI in which you can choose the From~TO is provided, if two or more databases or selected, then the Calendar UI in which you can select a particular date is provided.

Image 3-4. Calendar UI when only 1 database is selected

Image 3-4. Calendar UI when 2 or more database are selected

Setting the Axis of Analysis

The Datapath View provides a maximum or 4 axes for the analysis  of mutual correlation. The analysis standards are selected through the 1st axis of analysis, and the following analysis standards are provided.

•  CPU Time
•  Elapsed Time
•  Logical Reads
•  Physical Reads

The second analysis standards are selected through the 2nd axis of analysis. When Schema is selected, 4 axes are provided (3rd axis is Program/ 4th axis is SQL), and when Program/Module is selected, then 3 axes are provided. (3rd axis is SQL)

Image 3-5. When Schema is selected as the 2nd standard

Image 3-6. When Program is selected as the 2nd standard

Let us explain the Datapath View Main screen through a practice exercise for analyzing a particular date of RAC 2 node.

Image 3-7. Setting the Search Conditions for the Practice Exercise

 Initial Screen

When you click on the “Retrieve” button, the correlations among the Schema, Program, and SQL according to the node with the higher SQL CPU Time of the two nodes, are provided.

Image3-8. Initial ScreenNote. The length of bar is proportional to the workload amount.

Connection Analysis according to Schema Standards

When you click on the Schema axis, two-way analysis is possible according to the Schema standards.  You can check the node in which the corresponding Schema was executed, the Program which was executed in the corresponding Schema, and the correlation between the SQLs, and its degree of significance.

Image 3-9. After Clicking on the Schema Axis

Connection Analysis according to Program Standards

When you click on the Program axis, two-way analysis is possible according to the Program standards.  You can check the Schema and the Node which executed the corresponding Program, and the SQLs executed in the corresponding Program.

Image 3-10. After Clicking on the Program Axis

Connection Analysis According to the SQL Standards

When you click on the SQL axis, two-way analysis is possible according to the SQL standards.  You can check the Program, Schema, and the Node in which the corresponding SQL was executed. 

Image 3-11. After Clicking the SQL Axis

The Datapath View provides a macroscopic viewpoint of the View.  Therefore, in case you want a more detailed analysis, then you do not need to use the PA screen.  The Datapath View provides the following connection functions.

PA Summary Trend Screen Connection

When you click on the bar below the axis, a screen which provides additional information will appear on the right or left side of the screen.  If you click on the [P] button within the corresponding screen, you will be connected to the corresponding date’s summary trend screen.

Image3-12. PA Connection Button

Image 3-13. Summary Trend Connection Screen

SQL Detail Screen Connection

When you click on the bar below the axis, the screen which provides additional information will appear on the right or left side of the screen.  If you click on the [SQL] button within the corresponding screen, you will be connected to the corresponding date’s summary trend screen.

Image 3-14. SQL Detail Connection Option

Image 3-15. SQL Detail Connection Screen

The Hotspot View provides a function through which you can easily and quickly find the Hot-Spot according to OS CPU (%) regarding multiple databases (or multiple dates for a single database). 

The Hotspot View can satisfy the following demands made by the users.

•   Every morning, when I come to work, I want to check yesterday’s hourly CPU usage status of the  tens of databases  that I manage.
•   I want to check the hourly CPU usage status over a month period, regarding a particular database.

To start the performance analysis  using the Hotspot View, use the Navigation Bar located on the left side of the screen.  Reference the Datapath View section regarding the Navigation Bar functions.  

Let us explain the Hotspot View Main Screen with an example of analyzing a particular date of multiple databases.  After setting the search conditions in the navigation bar area, if you click on the “Retrieve” button, the following screen will appear.

Image 3-16. Hotspot View Screen

Hotspot View X/Y Axis

The Hotspot-View ’s Y axis displays the database name (or the date), and the X axis displays the time for 0~23 hour.

Definition of Cell Colors

The blue becomes darker as the O/S CPU usage rate decreases, and the red turns darker as the O/S CPU usage rate increases.

Color Change Standard Scroll Bar

The standard for the red color is 70 in the Hotspot View .  If you wish to change this value, use the scroll bar at the top to increase or decrease the standard value.  

Image 3-17. Color Change Standard Scroll Bar

Because the Hotspot View only provides the Heat-Map which is based on CPU, you must use the PA screen for the performance analysis of the Hot-spot period.  For this, the Hotspot View provides the following connecting function.

PA Summary Trend Screen Connection

You can connect to the Summary Trend screen by using your mouse to drag the corresponding area if the Hot-spotperiod is wide, or clicking on the individual cells.

The Treemap VIew not only allows you to compare the hourly SQL Elapsed Time, but it also provides the relationship among the Schema/ Program/ SQL executed at every hour in a hierarchical structure.

The Treemap View can satisfy the following demands made by users.

•  I want to be able to easily verify the significance of the hourly SQL Elapsed Time of a particular date.
•  I want to be able to identify at a glance, all the Schema/ Program/ SQL’s which have been executed during the problem time period.

To start the performance analysis by using the Hotspot View , use the Navigation Bar located on the left side of the screen.  The Navigation Bar provides the following search functions and items. 

Instance

Selects the target database for analysis.   

Image3-18. Instance

Hour

When you select the target database, the Hour displays the significance of the hourly SQL Elapsed Time of the set date on the calendar.  The higher the significance of the SQL Elapsed Time, the bigger the size of the area.  When you click on the hour which you want to analyze, the results will be displayed on the Main screen. 

Image3-19. Hour

The Main screen displays the relationship among the Schema/ Program/ SQL which were executed during the corresponding hour, in a hierarchical tree structure.  Through this, you can easily grasp the significance, simplicity, or the complexity of the Scehma/ Program/ SQL executed within the corresponding time period.  

Image3-20. Example of Multiple SQLs Executed within a Single Program 

Image 3-21. Example of a Long-SQL Executed within a Single Program

When you click on the Treemap-View’s SQL area, you will be connected to the SQL Detail screen.