Aggregated Top SQL Queries

Note

AppMon 2018 April feature

This view is available in AppMon 2018 April only.

The Top Queries web view lists queries with longest execution time, aggregated over all invocations. The goal is to characterize the general SQL query distribution in the current timeframe. Fast queries have a chance to appear here, if in aggregate they generate large load, approximated by total execution time.

Top slow queries - Oracle
Top slow queries - Oracle

The view expands the Database health web view functionality by presenting historical data. It lists only the top 100 queries vs all available in the query cache in the Database health web view.

The table is initially sorted by total elapsed time, aggregated for all invocations. You can sort the data using any of the visible columns, by clicking on its header. A specific query may be found by filter in the upper right. Selecting a query displays details including time spent breakdown and generated read/write load (see below for detailed descriptions).

By default the queries are sorted by total elapsed time, summed over all executions. The sorting order can be changed by clicking a column header or adding an additional column in the left bar.

You can filter queries by the content of any column. This includes the SQL text.

Oracle only Additional columns

Click the item from the left hand panel to add it to the table. You can only add one column from each section. The table will be automatically sorted by the new column, and the queries according to database resource usage type. You can still sort table by any available column.

The Disk utilization section sorts queries by:

  • Average direct reads per execution.
  • Average direct writes per execution
  • Average processed row count per execution.

Reads and writes are synchronous read/write data block request counts. Row count refers to read rows (not returned rows). Row count abstracts away row data size and layouts vs read/write request counts.

The CPU utilization is non disk processing time. High CPU time with low disk utilization suggests that the data is buffered efficiently, but for example a large amount of data is processed in RAM. Buffer gets is a good optimization metric. It represents data block requests both to the cache and HDD.

The Wait time analysis allows you to analyze non-disk and non-CPU times.

Data scope

Data is collected every 5 minutes for last 5 minutes period. Historical data older than 24 hours is aggregated and available with lower granularity. Historical data is stored for 2 months at most, or shorter if storage runs out of space.

Limitations

This feature is using internal database statistics view— the data may not be accurate if the database is under heavy disk or memory load.

Only statistic for the completed queries is displayed.