I’ve said it before and I’ll say it a million more times. Summers in Chicago are awesome!!! Caught an amazing concert over the weekend with Over The Rhine. They’re incredible musicians who I try to catch anytime they’re in Chicago.

Then of course we went to the Taste on Sunday… there’s a good reason that this is one of the biggest festivals in the city of Chicago!

Sometimes, office equipment makes me laugh. I just noticed this on Friday and it seemed worth a quick pic. Just in case you were wondering, this is not an appropriate way to dispose of ties.

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

Sometimes Chicago traffic seriously drives me crazy… last night I needed to be home for a 7pm meeting at church so I left extra early. Picked up Kathryn to give her a lift back into town and we were driving back into Chicago by 5pm. But wouldn’t you know it… apparently a bunch of traffic signals were down yesterday. The Kennedy and the Eisenhower are always a mess at rush hour so I usually take non-expressway alternatives if I’m driving around that time. But thanks to the signals being out - which I didn’t know about at the time - traffic was horrendous. After three hours I had missed my meeting and we were still out by O’Hare. UGH.

So we just parked the truck and walked over to a random thai/sushi place for dinner. And after some sushi everything seemed much better. Got home eventually… really late… but hey, life goes on. :)

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

Couldn’t resist at least one short tourist moment and had to snap a pic (not a great one). It’s pretty cool that my hotel is a short walk away.

I’ve been in DC all week working on PeopleSoft HR and Financials database systems for a major world-wide publisher. Oh yeah - and of course swing dancing. :) Went to The Jam Cellar Tuesday night and had a blast! They had a great turnout and there were lots of cool people around.

Also, just picked up this short video from another blog I read pretty regularly and I think it’s worth reposting. What do you think of it?

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

I’m still in Phoenix (or actually Scottsdale) and someone recommended that I check out this awesome coffee shop tonight; I was looking for a place to hang out for a little while before I go swing dancing. Miscalculated a bit… they close in 4 minutes — but this place is amazing. I feel like I’m in the coffee version of a microbrewery. Yep, that’s a roaster in the pic - they roast everything right here in the shop. I like it. :) Too bad I can’t hang out for awhile!

Chicago Blues Festival and CUBE were AWESOME!!! Holy smokes what a great weekend! Was a little crazy but worth every minute.

I got home from Phoenix at about 2am Saturday morning, slept for a few hours, played a softball game, then hung out with everyone staying out at my place for CUBE. We made breakfast then went down to the blues festival for the rest of the day. Then came home, cleaned up, and went the late-night party which started at midnight. Didn’t stay long… came home and went to bed around 2 so I could get up for church this morning… then had lunch at church… now frantically getting laundry done and packing before small group and the flight back to Arizona. Yup. Crazy.