Category Archives: Oracle

Dec 01 0 comments
Oracle, SQL

Stats are Made for Recomputing

Pop quiz: How fast should this Oracle query be?

select id from foo where rowid = :rid;

a) super fast. We are grabbing a row by its rowid, dummy.
b) really fast. This is a rowid, right?
c) ridiculously slow on a large table if the db statistics are out of date. It’ll do a full table scan and make you want to pull off your toenails with pliars.

You can probably guess the correct answer: C. Why else would I blog about this?

The rest of this is a boring war story. I include an interesting picture to entertain you before you skip over the rest.

fun times

At Jobster, we use Oracle Text for fulltext indexing. It is really powerful, allowing rich querying and a lot of flexibility in deciding what to index. After major changes to the database schema we need to recreate our fulltext indexes, and this takes a while. A while usually means about an hour for the whole database. A while doesn’t mean 40 hours, which is what we were seeing this week in our sandbox environment.

40 hours? While I would be delighted to pop some trucker pills and wait for this to complete during a maintanence outage, I would rather spend that time doing normal, human things like playing ping pong and waxing my back.

Let me quickly put you to sleep with how we use Oracle Text. We write a PL/SQL procedure that computes what to index for a given row in the table we want to index. When we notice a row’s data has changed, we poke Oracle which calls this procedure to get an up to date index. And when we tell Oracle to rebuild an index, it marches along and calls this procedure for each row in the corresponding table. Our procedures look something like this:

CREATE OR REPLACE PROCEDURE SP_INDEXFOO(rid IN ROWID, tlob IN OUT CLOB) AS
BEGIN
SELECT F_ROLLUPFOO(foo_id) INTO tlob FROM foo WHERE ROWID = rid;
END;
/

CREATE OR REPLACE FUNCTION F_ROLLUPFOO(p_foo_id IN INT)
AS
result CLOB;
BEGIN
-- a lot of queries that grab all the data we care about
-- (often from other tables) and stuff it into result
RETURN result;
END;
/

Anyway, f_rollupfoo does a lot of work. We tuned it and made it considerably faster (about a factor of four) and recreating the indexes again. No improvement.

We grabbed a trace of database activity, but nothing jumped out at us as horribly expensive. We removed the most expensive part of f_rollupfoo and tried again. Little improvement. Huh?

As a datapoint, we decided to make f_rollupfoo do no work and see how long index creation takes with that change.


CREATE OR REPLACE FUNCTION F_ROLLUPFOO(p_foo_id IN INT)
AS
result CLOB;
BEGIN
-- do nothing
select ' ' into result from dual;
RETURN result;
END;
/

Same speed!

We got a trace of that run and nothing jumped out at us. All of the time was spent in f_rollupfoo. And there wasn’t any lock contention or other wait issues. We were CPU bound. The trace showed a full table scan of foo, but we assumed that was just from Oracle Text indexing each row in the table. Our friends at Blue Gecko suggested that maybe the full scan was happening for each row we were indexing (i.e. each call to p_rollupfoo). Why would this happen? We are just grabbing a row by its rowid? Could it possibly be due to stale or just evil statistics? To test this, we recalculated foo’s stats and tried the run again. It was fast with no table scans.

It turns out the database statistics were bad, so Oracle decided to do a full table scan on an 800k row table to find a row by its rowid. Brilliant. After recomputing stats, the time to create the index descreased from 40 hours to 15 minutes and I was home in time to watch Johnny Deutch interview Donald Trump.

Who’s laughing now?

Apr 03 0 comments
Oracle

Oracle has no autoincrement

Why doesn’t Oracle have an autoincrementing column? This is extremely useful for implementing a unique id column.

MySQL:

create table foo (
    id int not null auto_increment,
    primary key (id)
);

Oracle:

create table foo (
    id number not null,
    constraint pk_foo primary key (id) validate
);
create sequence foo_seq
    start with 1
    increment by 1
    nomaxvalue;

create trigger foo_trigger
    before insert on foo
    for each row
    begin
        select foo_seq.nextval into :new.id from dual;
    end;
/

The alternative to having a trigger in Oracle is requiring the caller to get the sequence’s next value

insert into foo values(foo_seq.nextval, 'hi');

Is there a good reason Oracle doesn’t provide the equivolent to autoincrement? I doubt it’s hard for them to implement.

Feb 18 0 comments
Oracle

Annoying SQL error

We are seeing an intermittent SQL exception on our production database when performing a simple query:

select body
from foo
where id = :id

The helpful error message:
ORA-17110 execution completed with warning

foo.id is a CLOB. This doesn’t repro in SQL*Plus but does when using our JDBC driver. And we’ve been using the same driver and similar queries on CLOBs for a long time. Now this hits us. Blah.

Jan 12 0 comments
Oracle

Oracle considered life-shortening

Oracle is very powerful database but can be frustrating.

A couple of our stored procedures at Jobster started behaving strangely. They would work fine for a while then all of a sudden give errors with the same input. Like a carcass in a gutter, they began to rot. The offending portion was something like


FOR f IN (SELECT * FROM foo f WHERE bar >= 0) LOOP
-- do something
END LOOP;

Hmmm. Maybe it is confused that an alias is declared but not used for foo. We tried the following change which made the procedure work again.

FOR f IN (SELECT * FROM foo WHERE bar >= 0) LOOP
-- do something
END LOOP;

Well, this worked for a while. Then after a half hour we started getting the same error. After some hair pulling and a couple of coffees we tried using an alias (again) for foo that wasn’t the same as the loop variable

FOR f IN (SELECT * FROM foo ff WHERE ff.bar >= 0) LOOP
-- do something
END LOOP;

This works, or at least hasn’t broken over the past 24 hours.

I don’t see why the first two didn’t work. What I really don’t see is why they would only work intermittently with the same input. There are some things grandpa doesn’t warn you about.