High Performance Tuning Tools

Obligatory Melodrama

No matter how much time goes by I still remember it. The day my database was crippled beyond reckoning. That moment when I saw my hopes for a bright and shining future with my database spill through my fingers like so many cracker crumbs falling on a clean and well pressed pair of slacks.

It was the day I was told that we had to ditch AWR/ASH for a downgrade to Standard Edition.

There Will Won’t Be Blood

You know, it’s funny. I got started on Oracle 7.3 (I know, I’m still a whippersnapper to many of you), back when men were real men, women were real women, and sqlplus / as sysdba was connect internal. We didn’t have no Statspack, we didn’t NEED no Statspack. We ran UTLBSTAT and we liked it. And when we didn’t like it, we ran UTLESTAT.

No, really, it sucked. But at the time, it wasn’t that bad. We seemed to find what we needed (most of the time) in the tools we had, and if we couldn’t find what we needed to do we’d drop back ten and punt (meaning we’d rewrite queries until our fingers bled).

Then Oracle 8 came and things still sucked. But then something magical happened. Oracle added an i. In case you don’t know, that ‘i’ stands for cloud internet. In version 8.1.6 we got Statspack. And suddenly understanding our databases got a little cooler. We could get the big picture, a real top level view of what was going on in the instance as a whole. It gave us hourly snaps that we could use to see how our database progressed throughout the day/week/month/year/ORA-01653. The wait interface was lacking, the details sketchy or missing, but it still worked. It made a lot of things easier to understand.

With Oracle 9i it got a little better. But then Oracle worked their magic again and added a g. In case you don’t know, ‘g’ stands for cloud grid. With Oracle 10g we got AWR and ASH. Whether we paid for it or not. And we got hooked. It wasn’t just a snapshot tool, it’s built into the architecture. It grows, it evolves, it slices, it dices! On sale today, and mind that you don’t disable it because that feature’s protected by license (I kid, I kid, they fixed that). And ASH, don’t even get me started. Rolling session-based performance snapshots? Near realtime performance tracking? Be still my beating heart.

So naturally when you’re losing it, it feels like you’ll never make it in this cruel, cruel world. It’s hard to have something so convenient taken away. But there’s still plenty of options out there for you.

Take in the Views

First of all, Oracle’s own DBA_, V$, and X$ views are becoming more and more detailed and easier to query by the version. Show of hands, how many of you still query v$session_event instead of v$session just to get an event name? There’s a ton of good information to be gleaned from Oracle’s stock views and packages. From DBMS_XPLAN (which gets better every version) to time model statistics and beyond, the metrics are getting better.

Just make sure they’re the metrics you’re looking for.

Oracle’s views are just a small piece of the performance tuning puzzle, however. Don’t forget that there is a world of events, tracing, and OS level analysis you can do. Just because we have these neat high level tools doesn’t mean you should forget the low level treasure troves. Sometimes the holistic tools don’t fully expose the details.

Snap It Up

Seriously guys, Snapper 4 is the coolest thing since Snapper 3. Using Snapper, you can get ASH-like session runtime details with manually controlled snapshots of V$ views by running a single script. Tanel Poder can work a SQL prompt like no other, and Snapper is his magnum opus.

You can download Snapper by visiting his page (as he says, download it, don’t paste it) and run it immediately. With or without ASH, it’s well worth running for in depth details on your instance runtime as a whole or targeted to a single SID/user/whatever.

Sashay Into the Room

Another option for more long-term ASHishness is S-ASH by Kyle Hailey and the ASH Masters crew. This pack of scripts actually lets you create a repository database for your statistics and gather runtime data just like the real thing. You’ll be able to get all your session details along with time model statistics. It works without a Diagnostics Pack license and can even work against Oracle 9i.

And if you’re really missing those stacked area charts where you can visualize that poor sweet CPU horsepower being crushed mercilessly by the evil bad User I/O and Concurrency, you can even use ASHMON (another ASH Masters product) to get your graphs back.

Ignite Your Performance – Free!

Confio Ignite is a highly popular monitoring software with response time analysis to help locate tricksy bottlenecks. But if your company pursestrings are a little tight (after all, cheapskates won’t even buy you Diagnostics Pack) Confio is gracious enough to offer Ignite Free.

While it doesn’t have the full drill-down capabilities of the full version, it IS free and therefore a bargain. It also monitors SQL Server, DB2, Sybase, and has a version for databases on VMWare.

The Web

There is a plethora of advice out there for nearly any version of Oracle you may be using and a decent portion of the problems you may encounter. And if the problems you face can’t be found anywhere on that lovable series of tubes we call the Internet, there are forums and discussion groups and social networks full of people who love to help. And that’s just aces. (ba dum bum bum)

But…

Remember that actions have consequences (both good and bad). Be sure you test before you tune, and never ever blindly make changes to a production environment. Why, who knows where the advice has been. It might be dirty.

Good luck, and happy tuning!