ASHviz: Visualizing ASH dumps with Jupyter Notebooks
This article begins what I hope will be an interesting series focusing on some data visualization research I have been engaging using dumps of in-memory ASH (Active Session History) for data and R Jupyter notebooks for processing and plotting.
My ASH dataviz obsession
I've spent a considerable amount of time over the last decade thinking about how to visualize ASH data in ways that facilitate and promote better understanding of what is going on inside the vastly complex software machine called an operational Oracle database. When I say "considerable" I really do mean a nontrivial percentage of my free attention spent engaged in thinking specifically about various possible ASH visualizations.
Oracle v10 introduced a layer of features intended to assist visibility into and management of database performance in particular. A crown jewel among these new "Database Manageability" features was the Active Session History (ASH) mechanism, which constantly and regularly samples and persists detailed state information about all sessions active in the database.
My career at Oracle began in 2002 as part of a group of experienced Oracle technologists with product backgrounds recruited to design and oversee externalization of the Oracle 10g RDBMS Manageability features through the Enterprise Manager user interface, which itself was having a major design overhaul. As part of this effort we had to make sense of ASH, and figure out ways to expose its wealth of data in meaningful, informative, and even actionable ways.
I think we did pretty well actually.
Perf Page and Top Activity
The earliest usage of ASH data directly in Enterprise Manager was not the Top Activity page as most knowledgeable folks may presume. Rather, it was the set of detail wait class level pages navigated to by clicking-through a colored wait class band of DB Time on the main Session Activity chart of the main Performance Page. On these detail pages ASH data was aggregated into a Session Activity chart and Top Sessions/SQL lists, all filtered by wait class. It was only later and largely via the inspired vision of Kyle Hailey that these wait class detail pages were "UNIONed up" into the Top Activity page, arguably the most important visualization for Oracle database performance, ever...period.
The screen above looks pretty clunky by todays slick standards, but it incorporates many advanced and successful usage affordances for diagnosing, isolating and zooming in on database performance issues, with links to corrective workflows. It has been widely copied in the tools market and represents a huge collective success for everyone who worked on the original design and implementation.
DB Time Treemaps
The success of the Top Activity page showed just how valuable ASH data could be for seeing, analyzing and drilling down into Oracle database performance. The high dimensionality of ASH is a key design point: any given visualization has to slice out a subset of dimensions to quantify and visually render somehow. The standard technique is to aggregate ASH samples over one or more dimensions, which estimates the distribution of DB Time spent in those dimensions over the aggregation interval (using "ASH Math".) So aggregation is the operation, but it is the dimensional breakdown that matters, and there are thousands of possible combinations of just three or fewer dimensions.
I became interested in Treemap visualizations primarily because of their ability to render many-dimensional hierarchical breakdowns of a quantity, and making full and efficient use of 2D visual space. This mapped exactly to what I wanted to do with ASH: slice and dice DB Time across various ASH dimension combinations, sizing the Treemap cells relative to the amount of DB time (to see where the time goes, as it were.) Treemaps were invented to visualize hierarchies and there are natural hierarchies within ASH dimensions (e.g. wait class > wait events.) More abstractly though, ASH Treemaps of seemingly unrelated dimension combinations show relative DB Time spent at the intersections of dimension property values, providing multi-dimensional insight into chunks of time active in the database. The visualization aligns perfectly with time-based method for analyzing performance: pay attention the big Treemap cells, for these are big chunks of time.
Below is one of my first Treemap visualizations of ASH data. It was created using a Java applet that had lots of useful knobs for controlling visual features like colors, labels, and filters.
The story of getting Treemaps into EM is outside the scope for this series. Perhaps a future article. Below is a conceptual mockup for successor page to Top Activity that replaces the Top Lists with a Treemap:
This mockup is basically the ASH Analytics Loadmap design prototype.
ASH Dumps
I'm not going to discuss how to generate ASH dumps, because frankly I've never done it before. Dumping ASH is a DBA responsibility, and my time as an operational DBA far preceded my time at Oracle and the existence of ASH technology. And dumping ASH data from play databases just doesn't interest me so much.
Luckily, I have friends who are operational DBAs and a precious few of them have managed to provide me some real ASH dump files from operational systems. I don't have many, but a little bit of high quality data is far better for research than large quantity of poor quality data.
Basically, ASH dumps are CSV trace files of in-memory ASH data over some time period within time scope of the ASH circular buffer. Note that dump columns are not identical to V$ACTIVE_SESSION_HISTORY, which includes some columns (e.g. EVENT) that are joined into the view but not present in the dump. An ASH dump on RAC seems to include all instances, perhaps this is controllable.
The recommended investigative workflow seems to be to take ASH dumps and load them into an Oracle database for analysis. Seems reasonable enough, and if I still worked at Oracle no doubt that is what I would do.
But I had other ideas.
R and ggplot
R is a pretty strange language for a guy who has mostly done SQL and PL/SQL for 30-odd years, sprinkled with scripting in AWK, bash and such. It takes some getting used to, that's for sure.
However, I had occasion to learn a bit of R while at Teradata for purposes of generating high-quality plots of performance benchmarking data, using the R package ggplot. If R is strange, then ggplot is stranger still. It's kind of a mechanism for quickly building layered visualizations of raw data and/or statistical derivatives. Again, it takes some getting used to, but it is designed specifically for high-quality rendering and plotting of data analyses and is a good fit for rapid data visualization experimentation.
Experimental visualization scripts
Being familiar enough with R and especially ggplot and having some unexamined ASH dumps, I decided to engage some ASH data visualization investigations. The data I started with (and still use) was from a set of 4 files in CSV format representing a full dump of in-memory ASH data from a 4-node Oracle 10.2 RAC cluster.
The first script was called Setup.R and in it the ASH dump files were read into a data frame that itself is extended with some additional variables (columns) and made nice in other ways. All the subsequent data visualization scripts would `source` Setup.R to start and insure all begin with the same data frame. It didn't take long, and once the data frame was created in the working environment it needn't be re-created.
And of course it is always necessary to have the bootstrap code going back to the raw data, especially when uncertain transforms might need correction.
The most interesting and potentially useful thing learned in Setup.R was getting the ASH sample times recognized as POSIXct timestamps with microsecond accuracy. This enabled some very interesting visual analysis of inter-sample timing in a script called msecDiffs.R.
So these scripts usually just start with some visualization idea or intended analysis path and move in a meandering progression through changes and refinements until something is either interesting and useful or it looks like a fruitless path. There's plenty of bad stuff, maybe mostly. It's not about only having pretty pictures, figuring out why something doesn't work by looking at it closely can tease out what is better.
The first few scripts formed the basis for a presentation given at the NoCOUG fall meeting in 2017. The presentation wasn't very satisfying, I hadn't done some of the better experiments yet. Also, moving code and plots from RStudio to Keynote was way too clunky and static, but how to otherwise combine code and data images with presentation-style content was problematic.
There was too much impedance between the data visualization experimentation context and the content presentation context.
Jupyter Notebooks
I had heard about iPython notebooks some time ago and thought they sounded pretty cool. My Python is about like my R: pretty hacky but I can get things working. I learned it at the same time and for same reason: benchmarking ingest performance of big data platforms. However, since I had used R and not Python for data presentation and ggplot was a nontrivial learning investment I didn't jump into Python plotting libraries just to try out notebooks.
More recently I learned about Jupyter notebooks supporting R kernels and that was partly the impetus for this effort. Jupyter notebooks seemed capable of significantly reducing the impedance between code, visualization and explanation by bundling these together as a common document. So converting my raw and geeky R scripts into Jupyter notebooks seemed great for both sharing the code and discussing the output. Notebooks even offer some presentation mode feature that I have not investigated yet.
R package ashdat
Unfortunately, there was a problem and it had to do with the common sourcing of Setup.R by all my scripts. The code in Setup.R references the raw ASH dump data files and these are simply not OK to expose either explicitly as data files or even through references visible in code.
What I really needed was a way to share the nice ASH data frame that Setup.R created. The solution was to wrap it in an R package such that the Jupyter notebooks could load it like any other such package.
It turns out that creating an R package is like everything else in R: high barrier of entry but long tailed utility for what you learn. RStudio and Hadley Wickham's book R Packages along with many helpful blogs made it possible. Mercifully, it took less than a week.
So the first version of package ashdat is deployed in the notebook directory for now. In future exposing other ASH dump data frames, perhaps even a library of ASH data, might be desirable. But that is not this project.
github Repository
I created an ASHviz repository under my github account with a subdirectory for Jupyter notebooks:
As the original data visualization scripts become notebooks worth sharing, they will be added to this repository. Notebooks may evolve with better explanations or even new experiments, and there are many more ideas to pursue. So I hope this will be an interesting place to visit once in awhile.
If you clone this directory and have a Jupyter notebook environment you should be able to execute them yourself. You can just look them over in the browser though, as plot output is included in the published notebook files (making them rather large.)
notebooks:
- Setup.ipynb - Motivation and more details about converting the Setup.R script to a packaged ASH data frame for sharing by notebooks.
- stdViz.ipynb - First set of experiments, mostly showing Active Sessions (AS) and Average Active Sessions (AAS) using time as x-axis.
LinkedIn articles
For those less interested in code (the how) and more in the why and what of effective visualizations, my plan is to have a series of LinkedIn articles discussing selected images from the notebooks and why I think they are interesting or important. That's the plan anyway.
articles:
- (coming soon: Accidentally good)
- (coming soon: Issues at the x-axis)