How to identify Oracle sessions consuming CPU time

Identifying sessions consuming CPU time is a common task in Oracle performance tuning. However, as simple as it sounds, it is not that straightforward. Oracle recommends using Enterprise Manager or Automatic Workload Repository for that. The problem is that in real-life situations Enterprise Manager is often not installed, or you may not have access to it. You may not also have necessary privileges to run AWS. Besides, running AWS reports for such a simple task sounds like overkill.

Oracle database reports each session's CPU usage in V$SYSSTAT performance view. However, it only indicates a total CPU time used since the session's log in. And because different sessions may have logged in at different times, you can't compare the reported figures as they are. After all, it is obvious that a session logged in a few days ago cumulatively may have used more CPU time than a session started just a few minutes ago.

However, there is a workaround.

I wrote a script which accurately measures CPU time consumed by Oracle sessions within the given period (30 seconds by default). It works by taking a snapshot of CPU stats at the beginning of the interval, and then another one at the end. It then calculates the CPU time used during the interval and presents the sorted list.

It prints the result into dbms_output in the following CSV format:
sid, serial#, cpu_seconds

Once you identified the top CPU consuming sessions, you can use a script like this one to find out what they are doing.

You can download cpu_usage.sql script from my GitHub page.

Show Oracle sessions

Here is a small script which shows information about running Oracle sessions. You can use commented lines to filter by an Oracle instance (in case you have a RAC), OS user, session ID, process ID or an application name.

OzBsidian dark colour scheme for Oracle SQL Developer

I recently switched to Oracle SQL Developer for my PL/SQL development needs. I have mixed feeling about SQL Developer: it's been in development for nearly a decade, and it still feels very raw. Nonetheless, it has a comprehensive set of features, a fully functional PL/SQL debugger, and you can't beat a free price tag.

However, one thing I wasn't happy about is the default colour scheme. I've grown quite fond of dark colour schemes, notably I like Obsidian by Morinar. It was originally created for Eclipse, but then became massively popular and got cloned into just about every single IDE and text editor... Except for SQL Developer, that is.

I was surprised nobody cloned it for SQL Developer, so I went ahead and did it myself.

The colours are mostly match Obsidian scheme, although not exactly. For instance, the background is a bit darker. Hence the name is OzBsidian to differentiate it from the original scheme.

You can download the colour scheme with the installation instruction from my GitHub account.
Enjoy!

MIDI routing in FL Studio via a MIDI loopback device

This tutorial explains how to record the output of one MIDI track (for example, arpeggiator’s output) into another MIDI track.

Although FL Studio allow internal MIDI routing, it is not possible to record MIDI information which is being internally routed from one MIDI track into another. I.e. you can have an arpeggiator in one MIDI track transmitting notes into another track, where a synth would play it, but you won’t be able to record the notes arppegiator transmits. Fortunately, there is a workaround that allows us to do just that.

How to get size of all tables in an Oracle database schema

If you ever wanted to know how what's taking space in an Oracle database, or how large is the table you're working on, here's a script which answers these questions. It shows the size of all the database objects large than 10 Mb in a particular database schema.

Music production for masses - iOS to the recscue!

Some time ago I wrote my thoughts on the modern state of PC music-making applications.
In the nutshell, I reckon that niche became too overpriced and overcomplicated. All the existing digital audio workstations are targeted towards professional users, with the price tags to match. At the same time, the area of casual music production remains a no-man’s land.

Since then I got an iPad and discovered that simple and cheap music apps which I missed so much are plentiful there. I am excited about the emerging phenomenon of music production on iOS. There are already all-in-one DAWs for iOS such as NanoStudio and BeatMaker2, and even multitrack recording DAWs such as Auria. Moreover, while virtual synths for PCs & Macs cost circa $100 or more on average, synths of comparable quality for iOS are about 5 times cheaper. If you doubt, take a look at AniMoog, WaveGenerator, or nLogSynth.

Since its appearance the iOS music scene is expanding rapidly. Large companies start to realize that iPad is not just a toy, and the recent inflow of software from Korg, Moog, Akai, and the most recent, Steinberg and Yamaha is the proof of that.

Indeed, I reckon we are witnessing a rise of a new music making platform. The platform which is simple and accessible enough for beginners yet has a potential to become powerful enough for professionals.

Troubleshooting eth0 in Oracle Linux

A few days ago I installed Oracle Linux in an Oracle VirtualBox VM. Once it was installed I found that eth0 interface wasn't starting upon boot.

That was unusual. I am myself a Linux enthusiast, and I regularly download and install lots of distributions, and, by and large, network works in them out of the box.

Today I finally got around to troubleshooting this problem. It took me a couple of hours digging through the scripts, setting trace points, and reading logs; and here's what I found.