Press "Enter" to skip to content

Troubleshooting an Occasionally Poorly Performing Query in Oracle

Jura Bratina looks at the numbers:

A client asked us to verify why an INSERT AS SELECT statement, which was part of a scheduled ETL job executing on a single instance V11.2 database running as a data warehouse, suddenly started to take longer than usual. The database doesn’t have the Diagnostic and Tuning license, so Statspack and an external 3rd party application are being used to monitor the performance.

Okay, so I like making fun of Oracle licensing as much as (well, more than) the next guy, but seriously, a Diagnostic and Tuning license? Anyhow, the post isn’t about Oracle’s quest to find a more efficient way to vacuum money out of your bank account, but instead a performance troubleshooting approach when performance is intermittently bad. Spoilers: noisy neighbors. It’s usually noisy neighbors.