What a day! Yesterday I spent about seven hours downtown taking two exams: the RAC beta exam in the morning and the Oracle on Linux beta exam in the afternoon. The Linux exam was pretty easy for me but the RAC one had a lot of questions that made me think… and there wasn’t much time for that with so many questions being stuffed into the allowed time. I was planning on finishing this post last night but a friend called me just as I was starting… and I ended up helping move some furniture instead. But now I’m finally done and so I’ll take a stab at finishing up that case study.

We started with an overview of the environment and configuration. Then we framed the problem and started the investigation with a cursory look at some very course, high-level statistics from an AWR report on a 12-hour period. A few things that jumped out were a very poor parse cpu/elapsed ratio and high average wait times for shared pool and library cache latches.

A Closer Look at the AWR Report

Before I go too much deeper into this I would like to make a disclaimer: I’m pretty sure that I did unearth at least one thing that was affecting performance pretty dramatically… however by no means do I believe that I’ve found it all. There might be some additional tuning opportunities that I didn’t spot while gathering this data.

Now back to the story… as I pointed out yesterday, I don’t think you can really get to the bottom of this from just the AWR report. In fact it’s probably not even worth spending more than about 10 minutes on a report this broad. But I would like to point out three quick things that caught my eye before moving on. First off, it’s always worth having a look at the top SQL.
› Read more

I thought it might be interesting to write about a situation I ran into last week and a bit of the methodology I used to tackle it.

The whole thing started as a health check for a PeopleSoft database. To give a little background, the whole environment lives on a 32-processor IBM p690 partitioned into 10 LPARs. There are local mirrored disks for operating systems but an EMC Symmetrix does the heavy lifting for PeopleSoft and Oracle. The sym has a uniform configuration that mainly consists of 9G hypers and 100G metas.

I started by logging into the development system. It has 16G of memory, three CPUs and two dedicated fiber paths to the sym. It uses 9 metas but mangles them through AIX’s LVM. Two are in the first volume group and provide 15 mount points for various software binaries. The other seven are in a second volume group and provide 87 mount points for datafiles. JFS2 is used on all of these logical volumes. There are 16 non-production databases on this server however only 9 or 10 were running when I logged in.

I opened up SQLPlus and I remember running some really basic query (probably against something like v$database or dual) – and waiting for about 5 seconds for it to return. So then I tried an explain plan on the previously attempted simple SQL – and waited another 5-10 seconds for this to return.

That doesn’t seem like normal performance to me. :)

Starting a Performance Investigation

Despite a bit of bad press that statspack has received lately, I still find it (and AWR reports – the optional and expensive 10g counterpart) to be a good starting point for these investigations. And anyhow, many shortcomings – like the fact that prior to 10gR2 it doesn’t include stats for in-progress long-running SQL – generally have a bigger impact on DW environments.
› Read more

So I’ve really been digging Kevin Closson’s blog lately. Back at the beginning of this month he had another post that caught my attention about running Oracle on Opteron in which he made the point that these boxes should always be run in NUMA mode (not SUMA). This grabbed my eye because I’ve been delving a bit deeper than usual into CPU issues recently. In particular, on both of my past two tuning engagements, we’ve looked pretty closely at CPU utilizations. At the first we wanted to see if Oracle was effectively utilizing Hyper-Threading. At the second we were investigation high CPU wait events from the database. (Which turned out not to be CPU-related!) I worked up some quick scripts to help analyze the CPU patterns in both of these situation. But before I get into that – let me go on a quick tangent about what originally got me interested in this. :)
› Read more

Wow – the last three weeks have been crazy! During the last week of May I was wrapping up the services paper and a few submissions for the UKOUG. And for the first two weeks of June I’ve been working on some performance problems for one of our clients in the Phoenix area. Nice weather but lots of work!

Turns out that the system I’m working on is running Oracle on NFS – one of Kevin Closson’s favorite causes. In fact he recently wrote a blog post on the topic of monitoring tools for this exact environment. He must have listed more than 50 tools and yet said “if you are using Oracle over NFS, there are a few network monitoring tools out there – I don’t like any of them.”

After last week I couldn’t agree more. Perhaps one of the most useful tools on linux for working with I/O is the iostat OS utility – but it’s entirely useless for NFS devices. However I really wanted to see exactly what the I/O patterns looked like – from the physical device perspective.

To make things more complicated, this server only has a single NIC which is being shared for everything. While I sympathize with Kevin’s argument that NFS keeps things simple, I’m not sure I could recommend this configuration… it can make things a little tricky to troubleshoot. Kevin has a script in his blog post that displays activity on an ethernet port similar to the way iostat monitors block devices – however in this case I need to only look at traffic to one particular IP address. So what’s a boy to do?
› Read more