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.

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.

This is a spinoff of Obsidian colour scheme for Oracle SQL Developer. It is based on Obsidian Eclipse colour scheme by Morinar.

Unfortunately Oracle doesn’t make it easy to import a new colour scheme into SQL Developer, thus a little bit of hacking is required.

• Close SQL Developer. This is important. If you modify the scheme file while SQL Developer is open, your changes won’t be saved.

• Locate file dtcache.xml in the SQL Developer’s settings directory. On my system it is located in directory C:\Users\sergey\AppData\Roaming\SQL Developer\system4.0.3.16.84\o.ide.12.1.3.2.41.140908.1359

• Locate <schemeMap> tag inside dtcache.xml file. Insert the content of ozbsidian-scheme.xml file inside <schemeMap> alongside the other colour schemes. Be careful not to break the XML.

• Launch SQL Developer. Navigate to menu Tools->Preferences, then select item Code Editor -> PL/SQL Syntax Colours in the left pane.

• Select “OzBsidian” in the “Scheme” drop down list on the top.

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!

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.

For that you need to download and install Tobias Erichsen’s loopMIDI device.

Once installed, click on loopMIDI icon in the system tray to bring up its window. Click “+” to create a MIDI device.

Open FL Studio, go to Options → MIDI Setting

Find the loopMIDI you created both in “Input” and “Output” sections. Set the loopMIDI Input MIDI port to 1, and Output to 2. (You may choose other port numbers, just make sure that output port is different from input).

Insert an instrument you want to transmit the MIDI data from into one instrument channel, and a receiving instrument into another channel. In my example I use BlueArp arppegiator into a track containing Synth1 synthesizer. BluArp itself requires a MIDI input, so I drew a simple 3-note pattern in the piano roll of its instrument channel.

Now we neet to set up the MIDI routing.

Open BlueArp and set its MIDI Output Port to 1 (the number we used for our Output loopMIDI port). Click on the “Gear” icon to open BlueArp’s Fruity Wrapper and set Output Port also to 1.

Now open the Synth 1’s Fruity Wrapper and set its MIDI Input Port to 2 (the number we used for our Input loopMIDI port).

That’s all the setup you need to do.

Now press “Record” button and arm recording of Automation & Score.

Press Play to kick of the recording, and you should see the output of the arpeggiator recording into the Synth1’s piano roll.

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.

The following columns are returned:

• Owner schema.
• Object name and type (INDEX, TABLE, etc.).
• Name of the table this object is associated with. E.g. indexes are associated with their parent tables.
• Database space occupied by the object in megabytes.
• Tablespace this object is in.
• Number of extents allocated for the object.
• Size of the initial extent in bytes.
• Total database size occupied by the parent table. E.g. for indexes it will be the size of the parent * table plus sizes of all the indexes on that table.

This script is based on the Stackoverflow.com discussion.