Presenting ASH viewer

Over last few years, I’ve put together a few utilities in R to visualise database performance data. One that was particularly useful for me is my own version of ASH viewer. I think it could be useful for many other DBAs and developers who deal with performance optimisation topics frequently enough, so I finally published it on github.

The repository contains a README file with some basic instructions for installation and usage, so I don’t need to duplicate everything here. Instead, I’ll just briefly explain what it is, how it’s similar to/different from its analogs and if you are a database performance geek but not yet into R, why you may want to try it out.

What it is

It’s a R shiny app that allows you to visualise ASH data without having to write any SQL or export data from a SQL client to a visualisation tool. It allows you to aggregate it on any dimension and add arbitrary filters. In addition to average active sessions, it allows you to access some other metrics as well, such as I/O requests (or bytes) or PGA or TEMP usage.

What other tools is it similar to

The closest analog I’m familiar with is ASH analytics to which I only had some brief exposure in a OCI environment. It’s a great tool but it suffers a lot (in my mind) from trying to do everything via a GUI. Some people may like it, some people are more into command-line tools, I myself like to try and find an optimum balance between clicking and typing. In addition to the question of convenience, how would one implement filters using functions and expressions, e.g.

WHERE module like 'ABC%'

?

Another analog is a Java program by Alexander Kardapolov which looks quite interesting, but I haven’t explored it in detail. But based on screenshots, it also doesn’t offer much in the way of custom filtering and aggregation.

How it is different from its analogs

My main goal was flexibility:

  • filter data by any SQL expression
  • transform the dimension variable by a function/expression if necessary
  • Another feature that I haven’t found elsewhere was the ability to plot things other than activity (the number of average active sessions), using ASH columns such as DELTA_READ_IO_REQUESTS, DELTA_READ_IO_BYTES, PGA_ALLOCATED etc.

    Why R shiny

    To be honest, my main goal is not to promote this particular utility, as useful as I may find it in my own work. I’m hoping to get more people interested in R (and in particular R shiny) as a great analytics platform, perfect for performance data analysis and visualisation, whether it’s ASH, AWR, alert logs, listener logs, trace files etc.

    R is great because it’s

  • free
  • has amazing libraries for visualisations, time series analysis/forecasting, machine learning and pretty much everything
  • easily connects to practically any database
  • easy to learn since to can start using it as an interactive environment, building your way to proper programming
  • allows you to deploy lightweight web apps with very little coding.
  • Screenshots

    The default OEM-like view (breakdown of activity by wait class).

    ashviewer_wait_class

    PGA allocations by module.

    ashviewer_pga_by_module

    TEMP tablespace usage by user. Since ASH only contains a user id and not the actual user name, a correlated subquery

    (select username from dba_users where user_id = ash.user_id)
    

    is used in the “transform breakdown column” field. Note the ash alias used for joining the subquery with the main query block.

    ashviewer_temp_usage_by_viewer

    Read I/O requests per second by various PL/SQL objects. Once again, a correlated subquery is used to transform the dimension variable.

    ashviewer_read_iops_by_plsql_object

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s