Top SQL
Provides a list of Top SQL during the query period. These are SQL statistics for which WhaTap independently calculates metrics such as execution count and execution time, based on the SQL within active sessions collected by the agent. It helps you find high-load SQL and set tuning priorities.
The trend chart lets you visually grasp the SQL occurrence pattern for a specific time period, and statistics are calculated based on SQL with literal values excluded.
Basic Options
Set query conditions in the top filter area.
| Item | Description |
|---|---|
| Time | Select the query time range |
| Cluster | Select the target cluster to query |
| Sort Order | Select the metric used as the basis for selecting Top SQL |
| Number of Results | Set the number of items to display in the table |
| Field / Filter | Narrow down the list by applying conditions on fields such as SQL Text |
One point that can be confusing here is the top selector. MySQL Top SQL selects a target instance, but SingleStore Top SQL selects a target cluster. Since SingleStore processes SQL in a distributed manner at the cluster level, statistics are aggregated on a cluster basis.
Analysis Criteria Tabs
In the analysis criteria tabs, select the basis on which to group Top SQL.
| Tab | Description |
|---|---|
| ALL | Check directly at the SQL level |
| DB | Analyze SQL load at the database level |
| USER | Analyze SQL load by user account |
| NODE | Analyze SQL load by node (node_id) |
Top SQL Trends Chart
Displays the trend of the top SQL based on the selected metric as a time-series chart. You can see at a glance which SQL has a high load at which time period. Clicking the legend or the chart highlights the corresponding SQL line.
SQL List Table
Displays the list of Top SQL during the query period as a table.
| Column | Description |
|---|---|
| SQL Text | The full SQL text (with literal values excluded) |
| SQL Hash Value | A hash value that groups identical SQL |
| User | Executing user |
| Execute Count | Execution count |
| Elapsed Time | Cumulative execution time |
| Avg Elapsed Time | Average execution time |
| Max Elapsed Time | Maximum execution time |
Per-Query Charts
Click the expand button on a table item to view the execution count and average execution time charts for an individual query. Clicking a bar in the chart moves to the Instance Monitoring screen for that time period, where you can check the node status and active sessions at that point together.
SQL Details View
Clicking a SQL opens a detail popout. The popout consists of the following areas.
| Area | Description |
|---|---|
| Query | The full SQL text |
| SQL Statistics | Statistics such as execution count and execution time |
| Elapsed Time Trends | Execution time trend |
| Plan | Execution plan |
Plan (Execution Plan)
The Plan area shows the SQL's execution plan as a tree table. You can check the number of rows processed (Rows), node cost (Node Cost), and execution plans (Plans) at each stage step by step.
Simply put, an execution plan is the work plan the database lays out for the order in which it will process the SQL. By finding the high-cost stages, you can gauge where it takes a long time.
Data Interpretation Guide
| Metric | Meaning | Interpretation |
|---|---|---|
| Execute Count | SQL call count | The higher it is, the more frequently it is called, so optimization has a greater effect |
| Elapsed Time | Cumulative execution time | The higher it is, the greater the overall load of the SQL |
| Avg Elapsed Time | Average execution time per call | The higher it is, the slower each execution, making it a priority tuning target |
| Max Elapsed Time | Maximum execution time | A large gap from the average suggests intermittent performance degradation |