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).

PGA allocations 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.

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