Over-the-Top Tales from the Trenches. Motto: Bringing order to the chaos of every day DBA life. Dear Diary, Are we there yet? How much farther? Ever heard this during a recovery of a downed database? The poor thing has either a minor scratch, requires immediate attention, or is dead on arrival (DOA). You could cut the tension in the air with [...]

Welcome to the thirtieth edition of Log Buffer, the weekly review of database blogs. We start with an item on O’Reilly Radar by Tim O’Reilly: Data is the Intel Inside. Tim shows some evidence in favour of the the notion that, in the post-Web-2.0 world (”Web 2.0″ being a coinage of his own), where data and [...]

Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of every day DBA life.

This diary has made you conversant in the world of RMAN standbys, duplicates and corruptions… exposed you to the world the hidden nature of shmmax. You have seen the nastiness of Oracle MONTHS_BETWEEN. What more worlds and mysteries are left for us to conquer? Do we weep like Alexander, or board a boat and discover America?

Actually, my aim in any posting is to make your job as a DBA in Oracle, SQLserver and …

One of our clients found a bug with dbms_scheduler: it logs a job as successful when a ORA-01403 is thrown.

SQL> create or replace procedure x_no_data_found is v_dummy varchar2(1); begin select dummy into v_dummy from dual [...]

At the end of my last MySQL post I mentioned strange behavior with GROUP BY and DISTINCT. In Oracle such constructions are not valid and produce an error. Here is the test table and statements:

create table t1 (c1 int, c2 int);

insert into t1 values (1,10); insert into t1 values (2,20); insert into t1 values (3,30); insert into t1 values [...]

Continuing MySQL saga… This is actually a follow up on my previous post. Developers tried to rewrite all statements and even overdid it. As we say in Russia - “teach fool how to pray and he will break his forehead”. Note, I had to rename columns/tables as I write so sorry for possible typos Query converted by [...]

Goal:

Getting rid of filesorts and temporary tables by tuning MySQL queries.

Background:

Filesorts and temp tables are a necessary evil in MySQL, used when MySQL must sort the data before returning the output to the user. They are the most common issue with slow queries in MySQL, the main reason being that if the output is too large, you can kiss goodbye in-memory performance, and say hello to disk access.

Common User and Manager symptoms:

1. Sore throats due to excessive swearing at poor database performance.
2. Sore hip pockets due to lack of scalability requiring continuous hardware purchases.
3. Sore users who are sitting on high speed bandwidth and have to wait more than 1.5 secs for a response from any web page. Google has raised the bar, time to ditch the straddle technique and go for the Fosbury Flop.

Appropriate Medicine:

The quickest way to …

Today is the first time I had to look at MySQL performance. Tiny database as web application back-end was having significant performance issues with spikes of CPU workload. After identifying problematic queries, I found a pile of statements using IN subqueries. Typical example is: SELECT * FROM t1 WHERE c1 IN (SELECT c1 [...]