Forum Stats

  • 3,824,915 Users
  • 2,260,439 Discussions
  • 7,896,345 Comments

Discussions

Real Time SQL Monitor: could Metrics curves be smoothed/averaged?

user9540031
user9540031 Member Posts: 188 Silver Badge

Hi,

A while ago I wrote about Metrics curves in the Real Time SQL Monitor screen being wrong for parallel queries—please see this post.

Regardless, there's another comment to be made: apparently the ASH metrics are not aggregated over a limited number of time buckets. This results in curves that are not smoothed/averaged over time, even for long-running queries. Therefore, variations in measures are shown in their full glory, and ultimately, not only the charts may appear slow to render, but they may also become barely readable.

Here's one example (from a busy test system):

And the same query, as seen from the corresponding Active report:

While it's interesting to know that the I/O throughput is a lot more jittery than the smoother curves could let imagine, at some point SQL Developer's metrics curves become too messy to be of practical use.

Therefore, adopting an approach similar to what is done in DBMS_SQLTUNE Active reports—aggregating over a limited number of time buckets—could be a welcome enhancement.

A popup showing the metrics value when hovering over the curves would be nice, too. (But again, that would only make sense after smoothing the curves; it might be terrible if done with the curves as they stand now.)

Regards,

Answers

  • user9540031
    user9540031 Member Posts: 188 Silver Badge

    Looking further into how SQL Developer handles ASH metrics, I have just realized that it shows only read I/O throughput and read IOPS: write I/O throughput and write IOPS are not shown, nor is temp space usage. 😥

    And sometimes that really leaves one big half of the story out of the picture—here's the other half, from the Active Report for the same query:

    Of course, drawing multiple messy-jittery curves on the same chart would not be always pretty. Again, that may only be done if drawing smoothed/averaged curves.

    Regards,