Who's online

There are currently 0 users and 25 guests online.

Recent comments

Oakies Blog Aggregator

Who Stole gettimeofday() System Calls From Oracle strace() sessions?

I've been meaning to write this blog post for a while now but never seemed to find the time. Hopefully this posting will be useful information for anyone that spends a lot of time tracing processes in Oracle from the Linux Operating System. I'm one of those people, so it was good to get to […]

Storage Indexes vs Database Indexes Part I MIN/MAX (Maxwell’s Silver Hammer)

It’s often stated that in Exadata, you don’t need conventional database indexes anymore as everything runs so damn fast that indexes are simply a waste of time and space. Simply drop all database indexes and things will run just as fast. Well, not quite … There are many many scenarios where database indexes are still […]

Why is it easier to get things to work at home?

I’ve been grabbing a few minutes here and there over the last couple of work days trying to get something working with no joy. I this case it is a DAD entry in the “dads.conf” file on the OHS running as part of 11gR2 Forms & Reports Services on WebLogic 11g (10.3.6). I started to think that maybe this functionality is disabled on the installation or something like that, but couldn’t find any reason for that to be the case.

In a fit of desperation I came home tonight, fired up a DB and a F&R installation, defined a DAD and it worked first time. Aaaarrrggghhhh!!!!!

On the plus side, I know there is nothing fundamentally weird about the OHS installation that comes with 11gR2 F&R Services. On the down side, I still have no flippin’ clue why it is not working on the installation at work. Think I need to get in early tomorrow and focus on it for a little while. No doubt it will be something stupid I’ve missed while trying to do a bunch of different things at once.

File this one under:



Update: Fixed it. It was the bloody firewall. :) #DontForgetToCheckTheFirewall

Why is it easier to get things to work at home? was first posted on December 18, 2012 at 12:06 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Dbvisit Reporting…

I try to stick close to core Oracle technologies and steer clear of product endorsements, but over the years I’ve bumped into the ladies and gents from Dbvisit a number of times and they have been consistently cool and consistently on the money with their products. At Oracle OpenWorld 2012 I bumped into Arjen Visser who told me about their new product called Dbvisit Reporting, so I asked him to keep nagging me until I tried it out. When we met at UKOUG 2012 I still hadn’t tried it, but he had been too polite to bug me about it. This weekend it made it to the top of my list. :)

Dbvisit Reporting is essentially a cut-down version of Dbvisit Replicate, targeted specifically at creating real-time offload reporting databases. I’ve been having a play with it today, which resulted in this.

You can see my previous articles on their other products here.

Like all their products, Dbvisit Reporting delivers exactly what is says it will. The install is quick, so you don’t need to be a rocket scientist to get it working.

Keep up the good work ladies and gents! :)



PS. You can now test-drive Dbvisit Standby in the Cloud.

Dbvisit Reporting… was first posted on December 15, 2012 at 10:41 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Throttling IO with Linux


I guess the first question which comes to mind when reading this title is ‘Why’? For a database, but I guess for any IO depended application, we want IO’s to be faster, not throttle them, alias make them slower. Well, the ‘why’ is: if you want to investigate IO’s, you sometimes want them to slow down, so it’s easier to see them. Also, (not so) recent improvements in the Oracle database made great progress in being able to use the available bandwidth by doing IO in parallel, which could strip away much of the ability to see them in Oracle’s popular SQL trace.


I use VMWare Fusion on my MacBook and use Linux in the VM’s to run the Oracle datatabase. Desktop virtualisation, like VMWare Fusion (and Virtualbox and VMWare Workstation, I think all desktop virtualisation products) use the operating system IO subsystem. This introduces a funny effect: if you stress the IO subsystem in the VM, and measure throughput, it looks like the disk or disks are getting faster and faster every run. The reason for this effect is the blocks in the file, which is the disk from the perspective of the VM, are getting touched (read and/or written) more and more, thus are increasingly better candidates for caching from the perspective of the underlying operating system.

I think that if you combine the ‘disk getting faster’ effect with the need to investigate IO’s, you understand that it can be beneficial to throttle IO’s in certain cases.


The mechanism which can be used to control and throttle resources is ‘cgroups’. Cgroups is a Linux kernel feature, which is an abbreviation of ‘control groups’, and has the function to limit, account and isolate resource usage (see this wikipedia article. Cgroups are a Linux kernel feature since kernel version 2.6.24. This means there is no cgroups in Redhat and Oracle linux version 5, but there is version 6.

The idea behind cgroups is to have control over resources in a system, which becomes more and more important with today’s systems getting bigger. Cgroups have been created to function from single processes to complete (virtualised) systems.

Simple setup and usage

(please mind all commands are either executed as root (indicated by ‘#’), or as a regular user (oracle in my case, indicated by ‘$’))

First, we need to make sure the ‘blkio’ controller is available:

# grep blkio /proc/mounts || mkdir -p /cgroup/blkio ; mount -t cgroup -o blkio none /cgroup/blkio

Next, we create a cgroup called ‘iothrottle’:

# cgcreate -g blkio:/iothrottle

In order to throttle IO on the device, we need to find the major and minor number of the block device. If you use ASM, you can list the PATH field in the V$ASM_DISK view, and generate a long listing of it on linux:

$ ls -ls /dev/oracleasm/disk1 
0 brw-rw----. 1 oracle dba 8, 16 Dec 15 13:22 /dev/oracleasm/disk1

This shows that the major and minor number of the block device are: 8 16.

The next step is to use the ‘read_iops_device’ configuration option of the blkio controller to apply throttling to the ‘iothrottle’ cgroup. The ‘read_ops_device’ configuration option uses the following format: major_number:minor_number nr_IO_per_second (major:minor, space, maximum number of read IO’s per second)

cgset -r blkio.throttle.read_iops_device="8:16 10" iothrottle

Okay, we now have a cgroup called ‘iothrottle’ setup, and used the ‘read_iops_device’ option of the ‘blkio’ controller. Please mind there are no processes assigned to the cgroup yet. The next steps are to use an IO generation and measurement tool to first measure uncapped IO performance, then assign the process to the ‘iothrottle’ cgroup, and rerun performance measurement.

For the IO tests I use ‘fio’. This tool gives you the opportunity to investigate your system’s IO subsystem and IO devices performance. This is my file:

$ cat 


Now run it! Please mind I’ve snipped a large part of the output, because fio gives a great deal of output, which is extremely interesting, but not really relevant to this blog:

$ fio --section=simple 
simple: (g=0): rw=read, bs=8K-8K/8K-8K, ioengine=libaio, iodepth=1
simple: (g=0): rw=read, bs=8K-8K/8K-8K, ioengine=libaio, iodepth=1
fio 1.57
Starting 4 processes
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
Jobs: 2 (f=2): [_R_R] [100.0% done] [123.3M/0K /s] [15.4K/0  iops] [eta 00m:00s]

So, we did an average of 15.4K read IOPS. Now lets put the process which runs fio in the ‘iothrottle’ cgroup!
Get PID of the process we just used with ‘fio’:

$ echo $$

And assign the ‘iothrottle’ cgroup to it:

# echo 5994 > /cgroup/blkio/iothrottle/tasks

You can see if your process is assigned a cgroup by reading the ‘cgroup’ file in ‘proc’:

$ cat /proc/self/cgroup 

Okay, we are assigned the ‘iothrottle’ cgroup! Now rerun the ‘simple’ fio benchmark:

$ fio --section=simple 
simple: (g=0): rw=read, bs=8K-8K/8K-8K, ioengine=libaio, iodepth=1
simple: (g=0): rw=read, bs=8K-8K/8K-8K, ioengine=libaio, iodepth=1
fio 1.57
Starting 4 processes
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
fio: only root may flush block devices. Cache flush bypassed!
Jobs: 4 (f=4): [RRRR] [0.3% done] [81K/0K /s] [9 /0  iops] [eta 14h:37m:42s]

To be honest, I cancelled this fio run after a little while, because the time it takes to run is very long (approximately 14 hours and 30 minutes, as can seen above).
I think this example shows the cgroup ‘iothrottle’ in action very clear!

Cgroup usage in reality

I can’t imagine anybody want to echo all the process ID’s in the cgroup’s ‘tasks’ file in order to get these processes in a certain cgroup. With my DBA background, I would love to have control over an entire (all processes belonging to a) database. Setting up cgroups like done above (manually) means you have to set it up every time the server reboots. Luckily, there is a way to automate cgroup creation and assigning!

cgconfig service
In order to create cgroups, there is a service called ‘cgconfig’, which reads the file /etc/cgconfig.conf. In order to get the ‘iothrottle’ cgroup, and the disk throttling configuration created automatically, use this configuration:

mount {
	blkio = /cgroup/blkio;

group iothrottle {
	blkio {
		blkio.throttle.read_iops_device="8:16 10";

In order to use this configuration restart the cgconfig service using ‘service cgconfig restart’. Optionally, you can enable automatic starting of this service on startup using ‘chkconfig –level 2345 cgconfig on’ (optionally check when this service is started with ‘chkconfig –list cgconfig’). Now the cgroup is created. But now do we assign processes to it?

cgred service
This is where the cgred service is for. This daemon uses a simple configuration file: /etc/cgrules.conf. Once configured and active, this service assigns cgroups to users, groups or processes. For the purpose of limiting IO from an Oracle database, I created this simple line:

oracle			blkio			/iothrottle

Now the cgred service can be started using ‘service cgred restart’. Optionally, you can enable automatic starting of this service using ‘chkconfig –level 2345 cgred on’.


The purpose of this blogpost was to introduce cgroups, and let you understand why I choose the IO throttling functionality. Next it showed how to setup cgroups manually, and a simple test to prove it works, with enough information to let you repeat the test for yourself. The last part showed how to automate cgroup creation and assignment.

A word of caution is on it’s place. It’s a fairly new feature at the time of writing, which means it could break or does not work as expected. So use at your own risk! In my limited tests it worked like a charm.

Tom Kyte in Moscow – 2

On December 11th Tom Kyte performed “AskTom in Moscow” for the second time (first time was almost three years ago in February 2010). I was there and enjoyed presentations, tweeted a lot about the content and in the end I’ve won a signed copy of the Expert Oracle Database Architecture (2nd edition, in Russian)!

The start

A glass for questions. It was full in the end of the day

Tom started with a bit of history of how Oracle developed its database. Old stuff, C compilers, 37kg disk drives :-) followed by the modern, upcoming features in 12c. Pluggable Databases mostly – the biggest change in the Oracle architecture since version 6, he asserted.

Also mentioned: automatic data tiering, aka “data heat maps”, data redaction, adaptive query optimization (scary 12c feature of), parallel execution and partitioning improvements.
One thing about adaptive query optimization that is not mentioned is you can’t change any given execution in run time. I think it’s only possible if the client (or some buffered row source before giving data back to the client) didn’t fetch any row. Otherwise I believe it’s not possible to guarantee the same row order output.

Next in agenda was Security. Key points: everybody afraid to delete data (not only for historic reason, inserting data could be much faster – see Tim Gorman’s article in the latest NoCOUG journal), it’s hard to detect data theft, SQL Injection is dangerous; Database Firewall could help in protecting the database (for example, no DDL in production). Audit trails in the 12c will be someway different, “unified”. Advanced Security will be free in the next release. Different ways to encrypt: tablespace, column, backup… Interesting way of getting a part (like 10%) of the database masked and copied to QA/dev environment. New feature to detect which privileges are actually needed to application, to be able to easily turn off unnecessary grants. More separation of duties: new SYSBACKUP, SYSDG, SYSKM roles.

New features in the development was after a coffee break. A lot of new features expected in 12c: transaction guard, application continuity (very interesting client-side analog of TAF). With SQL Translator Oracle will be able to run code created for different databases (but will it be correct?).
APEX can do a lot of work even on a low-end machine. With 12c there’s option to install it either on CDB or PDB level.
IDENTITY columns – finally!
SQL Developer is evolving towards developer and DBA toolkit. I need to start using it.
The number of new features in 12c is astonishing. A lot of new stuff I don’t remember already! Good thing I was able to record the whole event, so I can hear Tom again when I would like to.
You can do this too, here are 99MB (5h30min) of Tom Kyte speaking. Enjoy!

What I do remember clearly: Tom answered my questions and gave me a signed copy of his book. I made sure that he will get the questions: I first asked Tom in twitter and then repeated questions on paper:
Questions to Tom
As for the first question, the lack of statistical data in V$SQL for redo/undo, Tom said he understands why somebody would want this information be available; to actually get this feature in Oracle means the need to open an enhancement request. I’ll see if I’m allowed to submit ER and if I’m allowed I’ll definitely submit it.
The second question, about flashback cursors, had a mistake. Tom has built a test case to show that flashback query builds new child cursor on every parse call, not execution as I thought it was. Still Tom thinks that this behavior looks like a bug and he promised to file a bug on this matter. Great! My questions deserved a book :) which I’ll try to read next year.
Expert Oracle Database Architecture

It was again a very well-organized, day of Oracle presentations of excellent quality by one of the best presenters in the world. Big thanks to Oracle CIS and Tom for the event!

Filed under: Oracle Tagged: 12c, presentations, Tom Kyte

Small mishap with this website…

Sorry, for those who where missing, or needed, some of the info here yesterday. Due to a small mishap regarding an incorrect URL update, some annoying redirects occurred. :S

AMIS vat Oracle OpenWorld 2012 samen in Speciale Whitepaper

Als sluitstuk van de jaarlijkse Oracle OpenWorld conferentie brengt AMIS een whitepaper uit. Een handzaam document waarin het volledige verhaal van Oracle OpenWorld 2012 is gebundeld. Een team van AMIS was tijdens de conferentie in oktober nadrukkelijk aanwezig; als sponsor, deelnemer, netwerker en spreker – en als aandachtig luisteraar en analist. Zeven Oracle-specialisten van AMIS …

Continue reading »

WordPress 3.5…

WordPress 3.5 has landed. You can download it from the usual place.

The auto update went fine on all five blogs I manage (they are not all mine :) ).

Happy upgrading!



WordPress 3.5… was first posted on December 11, 2012 at 8:16 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Quiet Release MySQL Plugin — Bug Fixes

This is just a small bug-fix release of the plugin. It has actually been quietly released for a while now. If you have downloaded the plugin recently, you have the latest version. To be sure, check the version in the Console, or you can see it in the file name.

There are two bugs fixed:
1. Deployment on an OMS hosted on Solaris didn’t work. (And I suspect it could be the same for Agents on Solaris.)
2. Changing thresholds on the metrics caused the error “Modification of Target Monitoring Settings has Failed”. Also, applying monitoring template was failing for the same reason.

If you didn’t know that you could setup threshold and customized the thresholds that are set out of the box with the plugin, then you are not fully using Enterprise Manager. You can do that in the MySQL target menu — Monitoring -> Metric and Collection Settings. When alert or warning conditions are met, Oracle EM can raise incidents and send notification according to your settings. As I stated above, had a bug that prevented users from changing the thresholds.

Same known issues exist as for release.
Useful links:

Follow the same deployment procedure for upgrade — (1) import into EM12c using EMCLI, (2) upgrade on OMS by deploying on OMS, (3) upgrade on Agents byt deploying on agents.

Happy monitoring!