performance

Quick and dirty query timing script...

A script to check the query execution time as well as the time to retrieve all records via a FOR cur IN construct:

Golden Gate Lag Graph + Alerting scripts on the way...

I posted my GoldenGate / Gnuplot lag time alerting shell scripts to a new GitHub repository: http://github.com/dmann99/ggtools

The scripts worked on v8 and I don't think the INFO ALL screen has changed much since then so hoping it will work on v11 with a minimum of changes. I will post a more comprehensive walkthrough after I verify the v11 compatibility...

I also have an Alerting perl script that I will be tidying up and including in the ggtools repository ... but that won't be available until later this month.

Parallel Processing Shell Script for SQLPLUS...

Just a quick note to mention I created a notebook page on my site covering the Parallel Processing Shell Script and how to use it to run multiple SQLPlus sessions in parallel.

The page is available here.

-Dave

Fixed Object Statistics

If you have a database where querying against v$ Performance views is painful, consider gathering Fixed Object Stats.

Fixed objects are the 700+ X$ tables that feed a lot of these system and performance views.

Fixed Object Stats are _not_ gathered by the default stats gathering procedures. They are not gathered at all unless you run the proc to gather them. In this case Oracle relies heavily on Dynamic Sampling… but that isn’t really a substitute for really gathering stats.

Why does my query have a MERGE JOIN CARTESIAN?

Yes, the mention of Cartesian Joins usually makes a DBA's heart skip a beat. The cartesian joins caused by missing join conditions are definitely a pain to deal with - these are the types of joins that can "blow up" temp space and cause all types of alarms to go off.

I didn't find anything in Oracle's official 11g documentation on this particular join method but I did find plenty of articles about issues with it in their support DB. I have chased down a few of these in the past couple of weeks and here is what I found.

Diagnosing slow client processing of result sets...

Here are two runs of the same SQL from different programs. The top is an automated process, the bottom is Toad. (SQL is the same, but SQL_ID is different because of white space issues):

So why is one finished in 7.2 minutes and one still at 4.7 hours and still counting up?

Speed up SQL Developer Startup

If you are using SQL Developer 2.x you can reduce your startup time by about 20% by disabling any unneeded extensions. I was able to reduce my startup time from 11 seconds to 8 seconds by turning off all extensions (none of which I really needed anyway).

Finding Query Block names for a SQL that you can't modify...

I recently had an issue where a query being submitted to the database had a large change in performance. I could attribute the change to a recent change in object statistics, but the query wasn't behaving because of a combination of a view, subquery, UNION, and LIKE operators. After spending too much time on this statement I decided to hint it to make it perform like it did before the most recent stats change.

Orion IO Test Tool

I ran across Orion in a Kevin Closson blog post. From the OTN site:

"ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful for understanding the performance capabilities of a storage system, either to uncover issues that would impact the performance of an Oracle database or to size a new database installation. Since ORION is a standalone tool, the user is not required to create and run an Oracle database. "

HammerOra Quick Start...

Its time to test some IO.

I have used OraBM/OraStress in the past to throw a load at an Oracle server.

This time I wanted to see what else was out there. I had seen mention of HammerOra on forums and listserves so I figured I'de give it a shot. Here's what it took to get it running on a Windows server.

1) Download and Install HammerOra

Their home page is here: http://hammerora.sourceforge.net/

Pages

Subscribe to performance