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.
Category: Uncategorized
Slow insert values with array binding after JDBC upgrade to 19c
A very brief note to alert the community of a nasty JDBC bug affecting INSERT performance. It was noticed by our Java developers after upgrading their JDBC driver 11.2.0.3.0 to version 19.3.0.0.190416DBRU. They were inserting data in batches of 5,000 rows at a time, 250,000 total, and the time to process the entire batch went up from 16 to 102 minutes.
Continue reading “Slow insert values with array binding after JDBC upgrade to 19c”
Network performance in pictures
A few illustrations of patterns to look for when using Wireshark to understand poor network performance. I’ve already touched upon this topic in the past, but this time I just want to share a couple of screenshots with a few comments.
Offloading regex parsing using external table preprocessors
Loading data from flat files into an Oracle database is a very common task. Oracle’s implementation of external tables is fantastic, and in many cases it simplifies the job to such a degree that the developer is left with very little to do: just write a “create table” statement with a few additional details about the file’s structure and that’s pretty much it. Even if the information in the file is not in a simple format (like comma-separated or tab-delimited), this doesn’t make things much more complicated, as you can e.g. load the raw text and then use regex functions to process it.
So I’ve been using this feature in a broad variety of situations (some of them I covered in this blog, e.g. here), and one problem that I occasionally incur is that performance isn’t always great. For example, here is the DDL of what I use to parse listener log files:
Continue reading “Offloading regex parsing using external table preprocessors”
Where did my RAM go?
In my previous posts (e.g. here and here) I showed how to use ps output (e.g. from ExaWatcher) visualization to spot performance problems in Linux. Here I’d like to show that this approach can be taken a little bit further, namely, to find the source of increase in memory usage.
The R code for this is quite straightforward. I also think it shouldn’t be much of a problem to do the same in Python, although I haven’t gotten around to try it myself. In the code below read_ps is the function that reads in a ps output file from ExaWatcher without unzipping it, sum_and_tidy does the aggregation, and visualize_rss does the plotting. There is also an auxillary function keep_top_n which is needed to keep the number of color bands to something reasonable.
Memory fragmentation: the silent performance killer
In my last post, where I analyzed the problems caused by memory fragmentation on a Linux server, I said very little about memory fragmentation itself. I wanted to tell a story, so I had to dial down the technical stuff. But now that the story is told, I figured I should make a more technical post on the subject. I think it might be useful for many Linux DBAs or SAs, since there is not enough awareness of this problem within the Linux community. So if your system is experiencing cryptic performance and stability issues, keep reading, your problem may well be stemming from memory fragmentation.
Continue reading “Memory fragmentation: the silent performance killer”
How to hang a server with a single ping, and other fun things we learned in a 18c upgrade
Every upgrade is a bit of a lottery. But for a long running, well established system with a lot of legacy code, it can be more of a Russian roulette. You are unlikely to gain much — your system has been running fine, thanks to various tweaks the apps team applied here and there over the years. All these nice and shiny features that arrive with the upgrade — well, sure, there can be a few that you might like and have big plans for, but most of them are not going to be very useful, at least not out of the box. While there’s a good chance that some of them would backfire in a really, really bad way.
Swapping (paging) for DBAs
Classic symptoms of memory pressure (free physical memory running low + swapping) are often more difficult to interpret than they seem, especially on modern enterprise grade servers. In this article, I attempt to bring some clarity to the issue. It is based on Linux, although many observations can be generalized to other Unix-like operating systems.
A picture paints a thousand words
When I first learned about Active Session History, it was a real game changer for me. It’s (kinda) like tracing which is always on, for every single session… well, active session, sure, but who cares about idle ones? For a while I got so obsessed with it that I almost stopped using other tools — fortunately, that was a only short while, because as great as ASH is, you still need other tools. But to the day, ASH still remains one of my favorites. However, to fully exploit its potential, you need to properly visualize its results, otherwise they can be misleading, as I intend to show in the rest of this post.
ASH for OS processes
There is a lot of different tools for analyzing OS process states which can be helpful in resolving non-trivial performance issues. One of the limitations of such tools is that they are mostly active ones — i.e. you have to do some extra work to collect the desired diagnostic information. This is inconvenient when the problem you’re facing is intermittent and manifests itself on an irregular and unpredictable schedule.
Oracle Diagnostician 