Top SQL

The Top SQL screen provides a list of Top SQL for the query period. This is SQL statistics self-calculated by WhaTap based on SQL in Active Sessions collected by the agent, including metrics such as execution count, execution time, and wait time.

• Queries can be made in the range of minimum 1 minute to maximum 30 days, making it suitable for long-term analysis.

• Trend charts allow you to visually identify SQL occurrence patterns in a specific time period.

• Statistics are calculated based on SQL excluding literal values.

Tip

Differences from SQL Statistics (DB)

Category	Top SQL	SQL Statistics (DB)
Data Source	Active Sessions collected by the agent	pg_stat_statements view in DB
Statistics Calculation	Self-calculated by WhaTap	Collects statistics provided directly by DB
Separate DB Configuration	Not required	pg_stat_statements extension installation required
Key Metrics	Execution count, execution time (avg/max/sum), wait time	Execution count, execution time, row processing, block I/O, etc.
Query Range	1 minute to 30 days	Hourly/daily units
Trend Chart	Provided	Not provided

Prerequisites

Agent configuration is required to use the Top SQL analysis feature.

Agent configuration

5-minute interval statistics are provided by default without any additional configuration. For more precise 1-minute interval analysis, add the following setting to whatap.conf.

sqlstat_send_interval=1

Option	Type	Default	Description
sqlstat_send_interval	int	5	Interval (minutes) for generating and sending SQL statistics data. Can be set to 1 or 5.

Analyzing Top SQL

The Top SQL screen is configured to analyze SQL performance from various perspectives by selecting an analysis criteria tab and sort order. Users can explore step by step from group analysis from an application perspective to individual SQL performance analysis using drill-down/drill-up.

Top SQL analysis proceeds in the following order:

1. Select the analysis criteria in the analysis criteria tab.

   • Analysis criteria types: ALL, DB, APPLICATION, USER, HOST

2. Select the top selection metric in sort order.

   • Top selection metric types: Elapsed Time, Wait Elapsed Time, etc.

3. Identify groups with high load in the chart.

4. Select a specific group and check individual Top SQL in the SQL List.

5. If further analysis is needed, navigate to the SQL detail analysis screen.

Basic options

In the top filter area, you can set the time, target instance, filters, and more.

• Time: Select the query time range (1 minute to 30 days)

• Instance: Select the target DB instance

• Filter: Condition-based search by field (default: exclude 'WhaTap' from SQL Text)

• Sort order: Select the criterion metric (Elapsed Time, Wait Elapsed Time, Avg Elapsed Time, Max Elapsed Time, Execute Count) for Top SQL selection, sorted in descending order by the selected metric

• Query count: Set the number of items to display in the table

• Top N: Set the number of top items to display in the trend chart

• Column selection: Add/remove columns to display and change order by drag

• Download: Export in CSV format

Field panel

In the field panel on the left side of the screen, you can easily add filter conditions. Click a field name to enter a condition, and that condition is added to the filter.

Field	Type	Description
SQL Text	String	SQL statement
Elapsed Time	Number	Total execution time (seconds)
Wait Elapsed Time	Number	Total wait time (seconds)
Avg Elapsed Time	Number	Average execution time (seconds)
Max Elapsed Time	Number	Maximum execution time (seconds)
Execute Count	Number	Execution count
WSQL Hash	String	SQL hash value
User	String	Executing user
Datname	String	Database name
Application Name	String	Application name
Client Hostname	String	Client hostname

Analysis criteria tab

In the analysis criteria tab, select how to analyze Top SQL. Various analysis criteria such as ALL, DB, APPLICATION, USER, and HOST are provided. Depending on the selected criteria, SQL execution information can be analyzed in group units or SQL units.

ALL

Selecting ALL allows you to directly view Top SQL by SQL unit. The chart is also displayed by SQL unit, and is used to directly check SQL with high performance impact across the entire system.

DB/APPLICATION/USER/HOST

DB / APPLICATION / USER / HOST are frequently used analysis criteria from an application perspective. Selecting these tabs aggregates SQL execution metrics by the corresponding criteria and displays them in the chart.

Tab	Description
DB	Analyze SQL execution load by database unit.
APPLICATION	Analyze SQL load by application name.
USER	Analyze SQL load by user account.
HOST	Analyze SQL load by client host.

Selecting a specific group in the chart displays the SQL list belonging to that group (SQL List) at the bottom. This allows you to drill down from group → SQL to analyze individual Top SQL in detail.

Trend chart

Displays the trend of Top N SQL based on the selected metric tab as a time-series chart. You can see at a glance which SQL has high metric values and in which time periods. Each SQL can be checked in the legend at the bottom of the chart, and clicking the legend or chart highlights the corresponding SQL line.

The data interval of the chart is automatically adjusted according to the query time range.

Query Range	Chart Interval
3 hours or less	1 minute or 5 minutes (determined by sqlstat_send_interval value)
1 day or less	5 minutes
30 days or less	1 hour

Per-query chart

Clicking the ► button for a table item displays the execution count and average execution time charts for that individual query. The chart interval is the same as above.

• Hover the mouse to check values by time.

• Clicking the bar graph in the chart opens the Instance Monitoring screen for that time in a new window. You can check the overall instance and active sessions during the time when that SQL was executed.

Data interpretation guide

Key metrics

Column	Meaning	Interpretation Point
sql	SQL statement (excluding literal values)	Check what query it is
instance	Name of the instance where executed	Check which DB it was executed on
execute count	Execution count	The higher, the more frequently called SQL → Greater optimization effect
elapse sum (sec)	Total accumulated execution time (seconds)	The higher, the greater the overall load SQL
wait elapse sum (sec)	Total accumulated wait time (seconds)	The higher, the longer the wait SQL → Check for lock, I/O bottleneck, etc.
elapse avg (sec)	Average execution time (seconds) = elapse sum / execute count	The higher, the slower individual executions → Primary tuning target
elapse max (sec)	Maximum execution time (seconds)	Detect intermittent performance degradation → Understand worst-case scenario

Identifying SQL to tune

• SQL with high elapse sum and high execute count

  Frequently executed with high overall load → Top priority tuning target

• SQL with high elapse avg

  The individual query itself is slow → SQL tuning, index check

• SQL with low elapse avg but high execute count

  Individual is fast but called too frequently → Caching, call logic improvement

• SQL with high wait elapse sum

  Long wait time making overall execution slow → Check for lock contention, I/O bottleneck, etc.

• SQL with large difference between elapse max and elapse avg

  Usually fast but suddenly slows down under specific conditions → Check for lock contention, data skew, etc.

Practical usage scenarios

Scenario 1. Finding SQL with high overall load

1. Sort in descending order by elapse sum (sec)

2. Top SQL has the greatest impact on overall DB load

3. Check execute count and elapse avg together to make a judgment

   • If elapse avg is high: The individual query itself is slow → SQL tuning, index check

   • If elapse avg is low but execute count is high: Called too frequently → Caching, call logic improvement

Scenario 2. Finding SQL with long wait time

1. Sort in descending order by wait elapse sum (sec)

2. Identify SQL with long wait times

3. Analyze causes such as lock contention, I/O bottleneck, connection wait

Scenario 3. Finding slow SQL

1. Sort in descending order by elapse avg (sec)

2. Identify SQL with slow individual executions

3. Check the Plan (execution plan) for the SQL to determine tuning points

Scenario 4. Detecting intermittent performance degradation

1. Check SQL with a large difference between elapse max (sec) and elapse avg (sec) → SQL that is usually fast but suddenly slows down under specific conditions

2. Open the per-query trend chart with the ► button to check the time when it slowed down

3. Click the bar graph for that time to analyze the cause in Instance Monitoring

Scenario 5. Analyzing anomalies in a specific time period

1. Check abnormally high intervals in the trend chart

2. Click on that time period to check instance monitoring metrics

3. Analyze which SQL was executed intensively during that time period

Additional features

You can check SQL details and set condition-based filters.

SQL details

Click the SQL column to open the details window. Refer to the following for descriptions of the SQL details window.

Adding filters

1. Click the add filter button.

2. Select the filter key.

   • String: = (contains), != (does not contain)

   • Number: == (equals), >= (greater than or equal to), <= (less than or equal to)

3. Enter a value and apply.

4. Apply AND logic for multiple conditions.