Doug's Oracle Blog (Entries tagged as Fun)

How to generate more Logical Reads

dougburns@yahoo.com (Doug Burns) — Wed, 22 Apr 2009 13:30:00 +0100

(With my tongue starting to feel a little tired ...)

Mmmm, there’s a table that the application is accessing extremely frequently, called R_DUMMY. I wonder what data it contains?

SQL> SELECT dummy_key FROM r_dummy;

DUMMY
-----
DUMMY

Mmmm, that looks familiar, doesn’t it? It’s almost like the DUAL table, isn’t it?

FAST DUAL optimisation? We don’t need no stinkin’ FAST DUAL optimisation.

Sigh.

How to induce log file sync waits

dougburns@yahoo.com (Doug Burns) — Wed, 22 Apr 2009 02:24:32 +0100

(With my tongue causing a minor gash on the inside of my cheek ....)

I'm working on an application that seems to suffer constant long waits on log file parallel write and, consequently, log file sync. I thought that the 54 ms log file parallel writes where just typical of an early 90s disk configuration, with each file type on a discrete drive, all attached to the same controller and it was timely, given a blog post I read recently.

But no, let's not just slow up the I/O, let's make sure we generate lots of it! I noticed that these two procedures were some of the most frequently executed.

PROCEDURE RL_addLock (
        lockEntity IN d000m.lock_entity_no%TYPE,
        lockEntityKey IN d000m.lock_entity_key%TYPE,
        lockProfileId IN d000m.lock_profile_id%TYPE,
        lockSessionNo IN d000m.lock_session_no%TYPE,
        lockOperatorId IN d000m.lock_operator_id%TYPE,
        lockTaskId IN d000m.lock_task_id%TYPE,
        lockDate IN d000m.lock_date%TYPE,
        lockTime IN d000m.lock_time%TYPE
        )
IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        INSERT INTO d000m
        VALUES (lockEntity,
                lockEntityKey,
                lockProfileId,
                lockSessionNo,
                lockOperatorId,
                lockTaskId,
                lockDate,
                lockTime
        );
        COMMIT;
EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        BEGIN
                ROLLBACK;
        END;
END;
/

PROCEDURE RL_delLock (
        lockEntity IN d000m.lock_entity_no%TYPE,
        lockEntityKey IN d000m.lock_entity_key%TYPE,
        lockProfileId IN d000m.lock_profile_id%TYPE,
        lockSessionNo IN d000m.lock_session_no%TYPE
        )
IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        DELETE FROM d000m
         WHERE lock_entity_no = lockEntity
           AND lock_entity_key = lockEntityKey
           AND lock_profile_id = lockProfileId
           AND lock_session_no = lockSessionNo;
        COMMIT;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
        BEGIN
                ROLLBACK;
        END;
END;
/

Excellent. Yet another developer implementing their own locking architecture, presumably to make their application database independent. Yes, it is common, but having just moaned about this kind of thing in the footnote to a previous blog post ...

"Why am I still seeing locking problems? I think it's because I often have to support Third-Party "Database-independent" (yuck!) applications, some of which insist on implementing their own locking mechanisms. Sigh."
...this one sent me over the edge.

"People like to have one number they can believe in."

dougburns@yahoo.com (Doug Burns) — Thu, 26 Feb 2009 04:41:08 +0000

I love this article that I found via Greg Plavik's blog. Not being a financial risk management expert means that I can't claim whether it's right or wrong, but I found it interesting.

Why I mention it on this blog is that I spent almost the entire time reading it thinking that the writer could have been talking about IT in general and Oracle performance in particular. That quote above is one of dozens I could have plucked out and reminded me of an old blog post.

It's a long article, but read it and see what you think.

A Miracle Masterclass ...

dougburns@yahoo.com (Doug Burns) — Wed, 24 Sep 2008 21:17:06 +0100

.... in promotion.

Not for Miracle the bland pen, or notebook or bouncy ball. Oh, no, tattoos are the future! (Thanks to Tim Hall for modelling it)

bstat/estat Fun

dougburns@yahoo.com (Doug Burns) — Sat, 05 Apr 2008 07:38:35 +0100

I've been playing around with utlbstat.sql/utlestat.sql, only briefly I should add, for a short section on the history of Oracle's performance tuning utilities and I noticed a couple of things that tickled me enough to blog about them.

First, as the scripts output the comments, I noticed that quite a few of the updates to these scripts have been made by familiar names. Here are a few examples I've snipped out.

SQL> Rem        cdialeri        01/02/01  - 891059: SQL*Plus compat, 1566460: connect /
SQL> Rem        khailey 03/15/99 -  594266: Correct per logon stats, add fstat fields
SQL> Rem        akolk   08/09/96 -  #387757: fix latch hitratios
SQL> Rem        drady   09/09/93 -  merge changes from branch 1.1.312.2

Oak Table Network members, all. So, there you go, another possible path to membership is to have worked on bstat/estat

Then I noticed a bug in the scripts as supplied with 10.2.0.4! (Not sure I have to be that specific about the version here) The fact that the 10g version of V$SESSION contains wait event information leads to the following bug.

SQL> insert into stats$begin_bck_event
  2    select event, sum(total_waits), sum(time_waited)
  3      from v$session s, v$session_event e
  4      where type = 'BACKGROUND' and s.sid = e.sid
  5      group by event;
    group by event
             *
ERROR at line 5:
ORA-00918: column ambiguously defined

Do you think I should file a bug report? I wonder what priority 10g bstat/estat bug-fixes have?

So, just to be clear, it's a bit of fun. I think you probably have better tools available these days! It's also very likely that I could lead a more varied and stimulating life if I put my mind to it