Oracle WTF

The Undocumented "/1000" currency formatting function

noreply@blogger.com (William Robertson) — Sun, 03 May 2009 04:32:55 PDT

Hi,
How can I format currency values to shorthand?
i.e. how can I display 12500 as 12.5, 2700 as 2.7, 700 as 0.7 etc?
I have tried using various masks but can't achieve the results I'm looking for.

That's a tough one. How to make 700 into 0.7? Could there be some Oracle feature to help with this?

Two quick replies later:

Thanks for the replies guys
I wasnt aware of the "/1000" feature, but it has done exactly what I need.

Oracle needs to do more to promote these display format features. What else are they hiding? That's what we want to know.

How to talk your way out of a hole

noreply@blogger.com (William Robertson) — Fri, 10 Apr 2009 00:32:34 PDT

One last shot from our favourite consultant:

Be careful what you expect from this proof of concept. We can’t prove the performance will match the requirements, and I would argue that performance isn’t a function of this architecture, it’s a function of technology.
The issue is not the architecture, it’s that [this company] doesn’t have the technology. I would even argue that the technology required might not exist yet.
We can say that indicatively if the technology did exist, then this architecture would hit that performance requirement.

Let's hear it for architecture astronauts.

Rollback segments explained

noreply@blogger.com (William Robertson) — Fri, 20 Mar 2009 01:03:11 PDT

I recently read this in a book about data warehousing:

Source System Rollback Segments
When extracting from a relational source, extracts that take a long time can be problematic. If an extract asks for all records updated in the last 24 hours, the system must locate the appropriate set of records. This means that no user can change the updated_date field while your query is being processed. As transactions flow in during your query, they are queued up in a separate place called a rollback segment, to be applied once your request is finished. If your query takes too long, this queue gets too large and runs out of space. The system then kills your job and processes the transactions that have been queued up. In general, the folks responsible for the transaction system don't like this kind of behavior.

Now to be fair, the book was published in 1998 and is not specifically about Oracle. Does anyone know whether there has ever been a commercial DBMS that worked anything like this, or did they just make it up?

The Consultant on Backups

noreply@blogger.com (William Robertson) — Sat, 14 Mar 2009 10:32:26 PDT

Our correspondent overheard The Consultant sorting out the backup requirements for the new system:

Consultant: You have a 6 hour window overnight, now as the queues get longer under heavy loading the end of day queue clearing will run into that 6 hours. Your backup window will start to get squeezed, so we need to know the minimum time to back-up this amount of data, including the time to shut down and start up the databases.

Technical guy: Why?

Consultant: Because it has to take place in that 6 hour window.

Technical guy: Why is that?

Consultant: Because then the users will come back on line and want to use the system.

Technical guy: So?

Consultant: They can't use the system if it's down to be backed up.

Technical guy: We'll use an online backup and they can do whatever they like.

Consultant: Well, if I was in auditing I'd fire you right now. You simply have to shut a database down to back it up. It's the only way you can get a consistent backup.

The Consultant

noreply@blogger.com (William Robertson) — Sun, 03 May 2009 11:02:22 PDT

The Consultant has a Ph. D, vast experience of high-performance systems architecture, a black belt in karate and a reputation as a genius. He's been brought in by senior management at vast but necessary expense for a strategic rethink of the way data is shared between systems, while implementing SOA, improving performance and finding the Higgs Boson. Needless to say, he tends towards the view that database development is overrated. He's already sorted out the data warehouse. Overheard by our correspondent:

I’ve finished the design for the data warehouse. Although I say design, it’s pretty simple. That’s why it was so quick. All data warehouses are essentially the same in that they are a dimensional model. That means that you essentially have everything that is a fact, an immutable fact [waves arms expressively], in the fact table. Just the one, big, table. That’s why they’re so attractive as reporting solutions - everything is in the same place so it’s easy to understand and the reporting is easy to automate. So in that fact table you’ve got all trades, the cashflows, positions, accounting information, accounts, exceptions, counterparties. Anything that’s a fact goes in that table [does wide googly eyes expression with dramatic pause]. Then anything derived is called a dimension, like for instance P&L calculations, whether the account is on balance sheet, or off... they go in the dimension table. Basically all we have to do is just pump messages into that fact table from the bus and then recalculate the dimensions in the dimension table periodically, and that’s the technical job. Getting the facts in there and getting the calculations done.

More Fake Performance Tips

noreply@blogger.com (William Robertson) — Mon, 03 Nov 2008 00:33:06 PST

We're not sure if this is a joke or just (more likely) the work of an idiot. Here are 15 Tips for better performance and tuning in Oracle SQL and PL/SQL:

FTS (Full Table Scans) are always bad and Index usage is always good.

Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.

Empty Space in an index that gets created due to the DML operations do not get used.

Indexes should be rebuilt at regular intervals.

Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.

Usage of cursors is always bad so avoid them like the plague.

Truncate command cannot be rolled back because it is a non-logged operation.

Table variables in SQL Server are always only memory resident.

Column order in a covered index does not matter.

In the case of SQL Server, one can separate the clustered index from the table.

Only committed data gets written to the disk.

Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.

Count(1) is better performing than count(*).

Issue frequent commits in the application to make the transaction faster and also improve concurrency.

Views are evil evil DB Objects that always slow down performance.

We like the fact that 8, 9 and 10 appear to be about SQL Server, despite the heading. Probably Nawal could only think of 12 fake Oracle tips but thought nobody would notice. Can you help him out with some more misleading tips for Oracle? (Or SQL Server. Nobody will notice.) For example,

The buffer cache hit ratio is a reliable indicator of system performance.
Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
The Inuit have fifty words for snow.

Spotted by Michel Cadot on Oracle-L.

Update: Yong Huang pointed out that the source of the article appears to be a list of common myths posted on a SQL Server blog, which makes it slightly less funny than it first seemed. Oh well.

TGI g_friday

noreply@blogger.com (William Robertson) — Fri, 01 Aug 2008 04:04:36 PDT

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
    ...end-of-week processing...
else
    ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.

Auxiliary Constructs Appeal

noreply@blogger.com (William Robertson) — Sun, 03 May 2009 11:12:54 PDT

Will somebody give this guy some auxiliary constructs? He just needs to know what's the auxiliary constructs, and examples in the auxiliary constructs. So if you have any auxiliary constructs you don't need, now's the time to dig deep. The appeal starts here.

Fine tuning

noreply@blogger.com (William Robertson) — Sat, 03 May 2008 08:41:15 PDT

Mike is doing some work on an application that started life as SQL Server. Now that it has been converted to Oracle, there are one or two bits that could still do with a little fine tuning.

One particular procedure seemed to take rather a lot of time, and several developers had tried to get better performance out of it without much success. Here it is:

CREATE OR REPLACE PROCEDURE getupdatedrunids
    ( p_customer_id  IN VARCHAR2 DEFAULT NULL
    , pc_results_out IN OUT SYS_REFCURSOR )
AS
    v_fetch_status     INTEGER := 0;
    v_sql_status       INTEGER;
    v_fetch_status1    INTEGER := 0;
    v_sql_status1      INTEGER;
    v_event_id         VARCHAR2(50);
    v_runid            NUMBER(10, 0);
    v_count_run_conns  INTEGER := 0;
    v_temp_runs        INTEGER;

BEGIN
    DELETE FROM temp_runs;

    DELETE FROM temp_run_connections;

    INSERT INTO temp_runs
         ( run_id )
    SELECT DISTINCT i.run_id
    FROM   event_status i
         , run_status    b
    WHERE  i.run_id = b.run_id
    AND    i.event_id IN
           ( SELECT DISTINCT i.event_id
             FROM   event_status i
                  , run_status   b
             WHERE  i.run_id = b.run_id
             AND    b.customer_id = p_customer_id
             GROUP  BY i.event_id
             HAVING COUNT(i.run_id) > 1)
             AND    b.customer_id = p_customer_id;

    BEGIN
        v_temp_runs := 0;
        SELECT COUNT(*)
        INTO   v_temp_runs
        FROM   dual
        WHERE  EXISTS
               ( SELECT *
                 FROM   temp_runs );
    END;

    IF v_temp_runs > 0 THEN
    
        DECLARE
            CURSOR c_runs IS
                SELECT DISTINCT run_id
                FROM   temp_runs;
        BEGIN
            OPEN c_runs;
            FETCH c_runs INTO v_runid;

            IF c_runs%NOTFOUND
            THEN
                v_sql_status1   := 2;
                v_fetch_status1 := -1;
            ELSE
                v_sql_status1   := 0;
                v_fetch_status1 := 0;
            END IF;
        
            WHILE v_fetch_status1 = 0
            LOOP
                BEGIN
                    DECLARE
                        CURSOR cust_incidents_cs IS
                        
                            SELECT DISTINCT i.event_id
                            FROM   event_status i
                            WHERE  i.run_id = v_runid
                            AND    i.rejected = 0;
                    BEGIN
                        OPEN cust_incidents_cs;
                    
                        FETCH cust_incidents_cs INTO v_event_id;

                        IF cust_incidents_cs%NOTFOUND
                        THEN
                            v_sql_status   := 2;
                            v_fetch_status := -1;
                        ELSE
                            v_sql_status   := 0;
                            v_fetch_status := 0;
                        END IF;

                        <<i_loop1>>
                        WHILE v_fetch_status = 0
                        LOOP
                            BEGIN
                                INSERT INTO temp_run_connections
                                     ( run_id
                                     , connectedids)
                                SELECT DISTINCT v_runid
                                     , i.run_id AS connectedids
                                FROM   event_status i
                                WHERE  i.run_id < v_runid
                                AND    i.event_id = v_event_id
                                AND    i.rejected = 0
                                AND    i.run_id IN
                                       ( SELECT DISTINCT run_id
                                         FROM   temp_runs );

                                <<fetchnext>>
                                FETCH cust_incidents_cs INTO v_event_id;

                                IF cust_incidents_cs%NOTFOUND
                                THEN
                                    v_sql_status   := 2;
                                    v_fetch_status := -1;
                                ELSE
                                    v_sql_status   := 0;
                                    v_fetch_status := 0;
                                END IF;
                            END;
                        END LOOP;

                        CLOSE cust_incidents_cs;
                    END;
                
                    FETCH c_runs INTO v_runid;

                    IF c_runs%NOTFOUND
                    THEN
                        v_sql_status1   := 2;
                        v_fetch_status1 := -1;
                    ELSE
                        v_sql_status1   := 0;
                        v_fetch_status1 := 0;
                    END IF;
                END;
            END LOOP;
            CLOSE c_runs;
        END;
    END IF;

    SELECT COUNT(*)
    INTO   v_count_run_conns
    FROM   temp_run_connections;

    IF v_count_run_conns > 0
    THEN
        OPEN pc_results_out FOR
            SELECT DISTINCT run_id, connectedids
            FROM   temp_run_connections;
    END IF;

END getupdatedrunids;

His final version was much faster. See if you can spot the difference:

CREATE OR REPLACE PROCEDURE getupdatedrunids
    ( p_customer_id  IN VARCHAR2 DEFAULT NULL
    , pc_results_out IN OUT SYS_REFCURSOR )
AS
BEGIN
    OPEN pc_results_out FOR
        SELECT DISTINCT e2.run_id, ic.run_id AS connectedids
        FROM   run_status r1
             , run_status r2
             , event_status e1
             , event_status e2
        WHERE  r1.customer_id = p_customer_id
        AND    r2.customer_id = r1.customer_id
        AND    e1.run_id = r2.run_id
        AND    e1.rejected = 0
        AND    e2.run_id = r1.run_id
        AND    e2.event_id = e1.event_id
        AND    e2.run_id > e1.run_id
        AND    e2.rejected = 0
        ORDER BY 1, 2;

END getupdatedrunids;

WREAK_APPLICATION_HAVOC

noreply@blogger.com (William Robertson) — Sun, 10 Feb 2008 11:36:24 PST

Tom Kyte recently blogged about the senseless and yet strangely common practice of coding something like this:

WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:' || SQLERRM);

which fairly obviously achieves nothing except take a standard message like this:

ORA-06501: PL/SQL: program error
ORA-06512: at line 6

and pointlessly scramble it into this:

ORA-20001: Following Error Occured:ORA-06501: PL/SQL: program error
ORA-06512: at line 11

which adds some meaningless text, hides the original line number, and miss-spells "occurred". Not bad for a day's work.

It turned out that some people had been doing this for years because they were simply too stupid to realise that they didn't have to.

Anyway you know all this because you read Tom Kyte's blog. But have a look at this helpful page of advice from Tech On The Net, under "Oracle/PLSQL: SQLERRM Function"

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error
      (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Now that is even better. The first example adds some meaningless text, hides the original line number, and duplicates the error code (unless it's a NO_DATA_FOUND exception, but let's not go there), to produce something like this:

ORA-20001: An error was encountered - -6501 -ERROR- ORA-06501: PL/SQL: program error
ORA-06512: at line 11

The error logging example pointlessly captures SQLCODE (nobody will ever use it), throws away all but the first 200 characters of the error stack, logs nothing about what happened, and fails to re-raise the exception so if you don't check the log you won't know anything went wrong until your customers start asking where their stuff is.

Wouldn't it be great if there were, say, a Boolean third parameter to RAISE_APPLICATION_ERROR that would make it retain the existing error stack, freeing up the message line for you to put something intelligent and helpful, like, I don't know,

BEGIN
    RAISE program_error;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR
        ( -20001
        , 'Biscuits cannot be ordered on a ' || TO_CHAR(SYSDATE,'fmDay') ||
          ' without a hot beverage'
        , TRUE);
END;

to produce something like this:

ORA-20001: Biscuits cannot be ordered on a Sunday without a hot beverage
ORA-06512: at line 5
ORA-06501: PL/SQL: program error

We can but dream.

Frameworkia

noreply@blogger.com (William Robertson) — Fri, 26 Oct 2007 00:36:04 PDT

We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.

I want share a new IDEA to create a new standard PL/SQL developing:
Function ( Standard Buffer) return number
    variable Number;
    variable1 Varchar2;
begin
    variable := get from Standard Buffer;
    variable1 := get from Standard Buffer;

    { make your business }

    put in standard buffer your results
end;
Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".

A lot possibilities are ready.

Do you see the genius of it?

Er, no.

Sensing that there were people who still needed convincing, user601181 posted some sample code developed using the new Frameworkia:

CREATE OR REPLACE FUNCTION iacore
    ( eobufferia IN OUT typeeodata )
    RETURN NUMBER
IS
    CURSOR cur_getroutingcondition
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_condition
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_getroutingcondition cur_getroutingcondition%ROWTYPE;

    CURSOR cur_dobufferiaassign
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_assignement
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_dobufferiaassign cur_dobufferiaassign%ROWTYPE;

    next_node         NUMBER;
    next_node_ck      NUMBER;
    stop_node         NUMBER;
    operation         VARCHAR2(256);
    operation_call    VARCHAR2(256);
    type_node         VARCHAR2(32);
    workflow          VARCHAR2(32);
    line              VARCHAR2(256);
    status_wf_v       VARCHAR2(3);
    pid_chain_node    NUMBER;
    ia_tid            VARCHAR2(64);
    ia_tid_micro      VARCHAR2(64);
    ret_code_default  NUMBER;
    ret_code          NUMBER;
    retval1           NUMBER;
    statementexc      VARCHAR2(256);
    schema_function   VARCHAR2(32);
    package_function  VARCHAR2(32);
    dblink_function   VARCHAR2(32);
    first_node_flag   VARCHAR2(2) := 'NO';
    id_debug_source   NUMBER;
    mapin_keyp        VARCHAR2(1024);

    headerbufferia typebufferia;
    assignbufferia typebufferia;
    checkbufferia  typebufferia;

    rec_wfnode               wf_node%ROWTYPE;
    rec_wffunctionsourcecode wf_function_source_code%ROWTYPE;
    rec_wflogger             wf_logger%ROWTYPE;
    rec_wfbusiness           wf_business%ROWTYPE;
    rec_wffieldmapping       wf_fieldmapping%ROWTYPE;
BEGIN
    headerbufferia := eobufferia(1);

    workflow := frameworkia.getvalue(headerbufferia,'ID_WORKFLOW');

    ---- DETERMINO QUALE NODO INVOCARE
    pid_chain_node := frameworkia.getvalue(headerbufferia,'WF_NODE_ID');

    ----- SE IL NODO E' NULL ALLORA E' IL PRIMO NODO
    IF pid_chain_node IS NULL
    THEN
        -------DETERMINO HANDLER E FILENAME PER IL LOGGER
        SELECT *
        INTO   rec_wflogger
        FROM   wf_logger
        WHERE  id_workflow = workflow;
        -- rec_WfLogger.ID_WORKFLOW
        -- rec_WfLogger.ID_DEBUG_LEVEL
        -- rec_WfLogger.ID_DIRHANDLER
        -- rec_WfLogger.ID_FILENAME

        --------INSERISCO NELL'HEADER
        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DEBUG_WF'
        , rec_wflogger.id_debug_level );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DIRHANDLER'
        , rec_wflogger.id_dirhandler );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_FILENAME'
        , rec_wflogger.id_filename );

        frameworkia.setvalue
        ( headerbufferia
        , 'CHARACTER_EVIDENCE'
        , '§§§§§§§§§§§§§§§§§§§§' );

        -------DETERMINO L'ID NODE
        SELECT wf_node_id
        INTO   pid_chain_node
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        SELECT *
        INTO   rec_wfnode
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        frameworkia.setvalue
        ( headerbufferia
        , 'WF_NODE_ID'
        , rec_wfnode.wf_node_id );

        SELECT b.status
        INTO   status_wf_v
        FROM   wf_node a
             , wf_name b
        WHERE  a.id_workflow = workflow
        AND    a.wf_node_id = rec_wfnode.wf_node_id
        AND    a.id_workflow = b.id_workflow;

        IF status_wf_v = 'OFF'
        THEN
            RETURN -1;
        END IF;

        ia_tid := frameworkia.getvalue(headerbufferia,'IA_TID');
        ret_code_default := 0;
        ret_code         := 0;

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE_DEFAULT'
        , ret_code_default );

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE'
        , ret_code);

        IF ia_tid IS NULL
        THEN
            ia_tid := 'TIA' || dbms_random.STRING('U',1 * 1 + 6) ||
                      TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6');

            frameworkia.setvalue
            ( headerbufferia
            , 'IA_TID'
            , ia_tid );
        END IF;

That's just the first hundred lines, and I've formatted it. The complete iacore function was well over 600 lines long.

I for one welcome the new standard.

What is this "testing" thing?

noreply@blogger.com (William Robertson) — Tue, 14 Aug 2007 10:16:27 PDT

Recently asked on the OTN PL/SQL forum:

I am reading a book: Learning Oracle PL/SQL by Bill Pribyl and Steven Feuerstein.
I am a newbie of PL/SQL and I got no other programming XPs. but in the very first of this book they are introducing something called: testing, to make some test programms /utilites.
I found that is very hard to make, is it neccessary?

Apparently, after you've typed the final semicolon, you can either sit back and open a beer with the satisfaction of a job well done, or you can check whether the thing works. And apparently it's hard, so, do we really have to?

Well, another poster was recently arguing that PL/SQL is not suited to unit testing anyway, or refactoring either for that matter, because it isn't object oriented. I think that will be my excuse from now on. You want me to test stuff, go make PL/SQL more objecty.

Welcome back

noreply@blogger.com (William Robertson) — Fri, 10 Aug 2007 05:47:25 PDT

Our guest administrator "Splogger" has now left the building, along with his page of helpful links to items on Amazon.com and a range of gentlemen's health products.

Suspiciously, a couple of days before he arrived we were taken off air by Blogger's spambots, presumably alerted by the amount of irrelevant, repetitive, and nonsensical text and links to Viagra sites they found here. From what I read, it seems possible that the Blogger automated suspension to prevent blog spam might have actually left the account vulnerable to blog spammers. As ironies go, that is up there with rain on your wedding day and good advice that you just didn't take.

One liner

noreply@blogger.com (3360) — Wed, 02 May 2007 18:05:28 PDT

I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.

nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')

DATE comparisons: the scenic route

noreply@blogger.com (William Robertson) — Sun, 08 Apr 2007 05:39:15 PDT

Need to calculate the number of minutes between two dates? Yes, the dull way is to subtract one from the other and multiply by 1440. But why do that, when you can simply convert each date into Julian format by converting it into a string and then back to a date, and converting the resulting date into a Julian string, and (in a separate step) applying TO_NUMBER to the result to get an integer, so that you can simply subtract one from the other and multiply by 1440.

Except that rounds to the nearest day, so an additional step is to calculate the minutes since midnight for each of the two dates, which you can do by simply converting each date into an 'HH:MI AM' string, then back to a date, then back to an 'SSSSS' string, converting it to a number, dividing by 60 and adding it to the result of the first calculation. I think.

Anyway, consider the following library of handy date functions our Oracle WTF Easter gift to you, the online development community.

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;

Just for fun, let's test it:

CREATE TABLE wtf_test (start_date NOT NULL, end_date NOT NULL) AS
SELECT DATE '2006-12-25' + DBMS_RANDOM.VALUE(1,365)
     , DATE '2007-12-25' + DBMS_RANDOM.VALUE(1,365)
FROM   dual CONNECT BY LEVEL <= 1000;

-- ...several runs here to allow for caching etc, last set of results shown...

SQL> set timing on autotrace traceonly stat

SQL> SELECT dates_pkg.minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:03.96

Statistics
----------------------------------------------------------
  16000  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
   9330  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

SQL> SELECT (end_date - start_date) * 1440 FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.16

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
  25485  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

So the handy package version takes 25 times as long as the 1-line SQL version.

And in the interests of fairness, in case you're thinking perhaps that is just the normal overhead of calling PL/SQL functions in SQL, let's try our own function:

CREATE FUNCTION minutes_elapsed
    ( lowdate DATE
    , highdate DATE )
    RETURN NUMBER
AS
BEGIN
    RETURN (highdate - lowdate) * 1440;
END minutes_elapsed;
/

SQL> SELECT minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.26

Statistics
----------------------------------------------------------
     21  recursive calls
      0  db block gets
    107  consistent gets
      0  physical reads
      0  redo size
  25496  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

Still 15 times faster.

Many thanks to Padders for sharing this one.

Quote For The Day

noreply@blogger.com (William Robertson) — Fri, 23 Mar 2007 01:11:55 PDT

"PL/SQL development can be a tedious and time-consuming job – often monopolizing the valuable time and efforts of Oracle developers." - SQL Navigator page, Quest Software

Spare a thought today for PL/SQL developers, having their precious time tediously monopolised by, umm, PL/SQL development.

423,551 Invalid Indexes

noreply@blogger.com (William Robertson) — Mon, 19 Mar 2007 13:56:36 PDT

A friend received an alarming email from a commendably proactive DBA who was concerned that he had discovered a potentially serious performance issue on the production system:

The following result shows there are many invalid indexes. For example, in the first row we see that there are nearly 423551 invalid indexes for just one row of a table. So if we rebuilt the index it would improve the performance.

Wow, that sure is a lot of invalid indexes for just one row of a table. For the sake of anonymity, I've run his query against the SCOTT/TIGER schema, where fortunately there are only 14 invalid indexes (or perhaps 10, if that's how you count them). Perhaps you can see what the problem might be:

SELECT DISTINCT
       t.table_name
     , i.index_name
     , t.num_rows table_rows
     , i.num_rows index_rows
FROM   dba_tables t
     , dba_indexes i
WHERE  t.table_name = 'DEPT'
AND    t.owner = 'SCOTT'
AND    i.owner = t.owner
AND    i.index_type = 'NORMAL';

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
DEPT                           PK_EMP                                  4         14
DEPT                           EMP_DEPT_IX                             4         14
DEPT                           PK_DEPT                                 4          4
DEPT                           PK_SALGRADE                             4          5

4 rows selected.

There is worse to come. It's not just DEPT that has invalid indexes. He goes on:

The following result shows there are many invalid indexes, so if we rebuild the index, it would improve the performance. At the same time from the first and last rows, indexes are not being used, so we need to look into it.

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
SALGRADE                       PK_DEPT                                 5          4
SALGRADE                       PK_EMP                                  5         14
SALGRADE                       EMP_DEPT_IX                             5         14
SALGRADE                       PK_SALGRADE                             5          5

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
EMP                            PK_DEPT                                14          4
EMP                            PK_EMP                                 14         14
EMP                            EMP_DEPT_IX                            14         14
EMP                            PK_SALGRADE                            14          5

I just can't help wondering what he thinks an index is.

Performance Tuning and The Big O

noreply@blogger.com (William Robertson) — Wed, 17 Jan 2007 08:28:42 PST

Oracle Performance Tuning is a big subject, as anyone will appreciate who has read any of a number of books that set out to help you to understand what problem you are trying to solve, what factors may affect the performance you are seeing, what strategies are available to the query optimizer, and so on.

While Cary Millsap's Optimizing Oracle Performance focusses on finding, tracing and prioritising specific problems in the face of vague reports that the system seems a bit slow this week, and Jonathan Lewis' Cost-Based Oracle Fundamentals takes us on a tour of the CBO to help answer such questions as Why isn't my EXISTS query using an index? (and why isn't it faster than the IN version?) a poster on OraFAQ has an approach we've not seen before:

Hi,

The following is a problem I need help with and I am willing to pay for help if necessary. Any info would be greatly appreciated.

Two tables in a database:

Table1 contains a list of phone numbers
Table2 contains a list of phone numbers as well

I would like to create a Table3, in which Table3 contains all numbers from Table1 that is not in Table2. I am looking for the shortest runtime possible, keeping in mind that you can use whatever method(s) you deem necessary.

Table1 contains 30 Million rows,
Table2 contains 2000 rows.

given a regular SQL expression, it will yield Big O(m*n)

Where m = rowcount of Table1
and n = rowcount of Table2

Generate for me, a method in which, runtime will yield Big O (m log2 n).

I don't need code, I want to hear your logic. Table1 is customers, Table2 is a list of prepaid phone numbers. Table3 is list of people to bill.

As usual no database version is given. The first suggestion, as you might expect, is the quite reasonable:

select phone_no from table1
minus
select phone_no from table2;

accompanied by a comment that it doesn't seem like a great piece of schema design to have two tables in the first place. However, the reply comes back:

The guy who wrote this problem just told me that this answer is incorrect.

His response to me was:

it's not too simplistic, but it is incorrect. This will still yield a big O(m*n).

Give it one more try, you are thinking too much in terms of DB.

Ask yourself, what are the only structures that would yield BigO(nlog2n)? Answer that, and you will get your answer.

Any ideas?

Resident mathematician Ross Leishman tried to explain to me what Big O (m log2n) means, and I can confirm that it is not after all a When Harry Met Sally reference as most of us would probably assume. Apparently the version with an EXISTS subquery was what was wanted, which seemed odd to me on a number of levels, not least that an IN subquery would probably produce the same plan, especially in 10g where the new HASH JOIN RIGHT ANTI allows the database to build its hash table from the 2,000-row table2 rather than the 30 million-row table1. But of course we don't know the database version, do we? Or whether the columns are nullable, unique or indexed, or how values are distributed, or really anything about the actual environment that would help in solving a real-world performance problem. I can see where I'm going wrong though. I am thinking too much in terms of DB.

Short loop

noreply@blogger.com (William Robertson) — Mon, 15 Jan 2007 07:28:43 PST

It's good to see a GOTO every now and then. However, the sender of this one (thanks!) was most impressed by the loop that goes from 1 to v_totalcntr, and indeed the fact that there is a loop and a v_totalctr variable at all, with the variable carefully set from the cursor's %ROWCOUNT, when it can only ever have one value:

DECLARE
  CURSOR cur_pricing IS
      SELECT col1, col2
      FROM   sometable;

  var_pricing cur_pricing%ROWTYPE;

BEGIN
  OPEN cur_pricing;
  FETCH cur_pricing INTO var_pricing;

  IF cur_pricing%NOTFOUND THEN
      GOTO continue;
  END IF;

  v_totalcntr := cur_pricing%ROWCOUNT;

  FOR r IN 1..v_totalcntr
  LOOP
      -- loads of stuff here
      -- but no fetch from cur_pricing
      -- not even for the one time this loop will execute :-)
  END LOOP;

  <<continue>>
  NULL;
END;

Of course you could just fetch the value you want and process it, but where would be the fun in that?

PS The person who sent this in emailed me with a point I must admit hadn't occurred to me:

Saw you posted this one - thanks. But did you pick up on what they probably thought they were doing? I think whoever wrote it thought that %rowcount would have the TOTAL number of rows that the cursor would return - so they thought they would be looping around ALL the records in the cursor. The fact that they also forgot to fetch again in the loop just adds to the problem of course.

Code Re-use

noreply@blogger.com (William Robertson) — Wed, 13 Dec 2006 17:24:09 PST

This code was anonymised by the sender and some of the original logic may be a bit harder to make sense of as a result. Instead of EMP records, you should probably imagine it checking billion-dollar financial deals or nuclear reactor core temperature readings. Anyway, I think I've figured out what it's supposed to do.

You pass in an EMP record containing first name, last name, email address and so on, and it calls OTHER_PROC(email_address, the_emp_record_as_passed_in) - but only if the email address is not null, and it is unique for employees with that name. For some reason. But how to check? Simple:

Use a cursor to load all the rows for that name into an array. (Apparently there were 130+ columns in the original table.)
Check array.COUNT to see how many rows there are.
If there is only one row, use it, taking care to use an NVL expression because we definitely don't want a NULL email address.
If there is more than one row, open the same cursor again, and this time loop through it comparing each row's email address with the previous one. If it's the same, set lv_email_same = 1, otherwise set it to 0. That way, at the end of the loop we'll know whether they were all the same or not, right?

PROCEDURE unleash_havoc (p_emp_rec emp%rowtype)
IS
   lv_email_same  NUMBER(1) :=0; -- 0: no, 1: yes
   lv_email_null  NUMBER(1) :=0; -- 0: no, 1: yes
   lv_email       emp.email%TYPE := NULL;
   ln_row         NUMBER;

   TYPE emp_tab IS TABLE OF emp%ROWTYPE;
   lt_emp_data  emp_tab;

   CURSOR c_emp (p_last_name VARCHAR2, p_first_name VARCHAR2) IS
      SELECT *
      FROM   emp e
      WHERE  e.last_name = p_last_name
      AND    e.first_name = p_first_name;

BEGIN
   OPEN c_emp (p_emp_rec.last_name, p_emp_rec.first_name);
   FETCH c_emp BULK COLLECT INTO lt_emp_data;
   CLOSE c_emp;

   IF lt_emp_data.COUNT = 1 THEN
       ln_row := lt_emp_data.FIRST;
       lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
       other_proc(lv_email,p_emp_rec);

   ELSIF lt_emp_data.COUNT > 1 THEN

       FOR r IN c_emp (p_emp_rec.last_name, p_emp_rec.first_name) LOOP
           IF NVL(r.email,'X') = NVL(lv_email,'X') THEN
               lv_email := r.email;
               lv_email_same := 1;
           ELSE
               lv_email := r.email;
               lv_email_same := 0;
           END IF;

           IF r.email IS NULL THEN
               lv_email_null := 1;
           ELSE
               lv_email_null := 0;
           END IF;
       END LOOP;

       IF  lv_email_same = 1
       AND lv_email_null = 0
       THEN
           lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
           other_proc(lv_email,p_emp_rec);
       ELSE
           ...
       END IF;
   END IF;
END;

Pause For Thought part 2

noreply@blogger.com (William Robertson) — Wed, 18 Oct 2006 23:58:56 PDT

In an AskTom thread about native compilation, a poster mentions that he is seeing some big CPU waits on his system and wonders whether native compilation would help. After some discussion of tracing and profiling and his 32 Tuxedo connections, he reports back that from talking to the programmer (possibly this guy), he might have narrowed the problem down to a procedure called DELAY():

I just happened to talk to the programmer and what i understand is that that specific function when called so many times , it is internally calling a procedure with passing a value n, where n is like 4,5,6 etv. That procedure is called delay and its job is to create a delay in the logic flow. So the delay procedure accepsts values like 10 as seconds and then it
FOR di IN 1..sed LOOP
   NULL;
END LOOP;
A random thought, could this when run so many times accumulate huge cpu?

Tom thinks maybe it might.

Thanks Tom for sending this in.

Death By Furniture

noreply@blogger.com (William Robertson) — Thu, 12 Oct 2006 15:27:24 PDT

According to www.identifiers.org, there are two classes of relational database: "Code Class" and "Identifier Class".

We hadn't heard of those either, but it's all made clear in this presentation (pdf, 1.2MB), in which the limitations of the conventional approach and a novel approach to schema design are explained without the aid of Powerpoint, in a series of pictures like this one:

Still confused? Never mind, you can follow the debate on this OTN thread, which George begins by asking for a simple clarification regarding the capabilities of the Oracle RDBMS. From the answers to this, he should be able to determine whether Oracle is old-hat Code Class or funky new Identifier Class:

I have an interest in establishing how the Oracle System Catalogues cope with particular changes. I have never used Oracle, but I have carried out the same test on another RDBMS. If I had access to Oracle I would have carried out this test myself.

The test goes as follows.

Create a new database.

Create a new simple table, with just a few columns.

Create a form for the table, and add a few rows.

Rename the table or a column – if you can’t, then the RDBMS is Code Class.

If you can rename the table or a column, then do so, and invoke the form that you used before changing the table or column name. If it doesn’t work, the RDBMS is Code Class; if it does then the RDBMS is Identifier Class.

In an Identifier Class RDBMS changes of column or relation/table name will not interfere with the operation of any form already in place based on that table.

I'll be very grateful if anybody can give me a definitive answer on this, either through already having explored the issue or by running the test.

It turns out that "Code Class" covers all existing RDBMS products ever conceived, and "Identifier Class" is an improved model invented by George himself, in which some theoretical 4GL development tool yet to be designed allows you to change table and column names without breaking existing code or having to define a view, and surrogate keys are, well, pretty much the same except that they are now called attribute independent relative position independent identifiers. Perhaps one day Oracle will advance to this point, especially now that they've fixed DBMS_OUTPUT and must be wondering what to do next (perhaps after getting a product to work on Apple Mac and fixing the OTN "change password" facility). We can but dream.

Useful books

noreply@blogger.com (William Robertson) — Sat, 16 Sep 2006 15:27:19 PDT

An enormous spider appears. It's clearly coming for you. Or not. What do you do?

Staying calm, place a glass or similar containment device over the creature.

Now gently slide a card under the glass, taking care not to trap any legs. There should be eight after you have completed this manoeuvre.

Transfer to a suitable platform such as a book, and carry to a window or other suitable release location.

No Oracle experts were harmed in the making of this blog.

Reintroduce the creature into the wild, where it can begin a new life free to roam among its own kind. Or just wander back in of course, but spiders are not known for their homing instinct. Or are they? To be honest we haven't checked. This one said something about "Scotland" and "revenge".

Finally, always sterilise your equipment.

Meanwhile in Edinburgh, police were called to a flat in the Stockbridge area following reports of a violent assault...

Just, Why?

noreply@blogger.com (Thai Rices) — Wed, 13 Sep 2006 04:44:00 PDT

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.


RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
   SELECT *
     FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
   INSTEAD OF UPDATE
   ON emp
   FOR EACH ROW
DECLARE
   new_emp                       emp_table%ROWTYPE;
   old_emp                       emp_table%ROWTYPE;
BEGIN
   new_emp.empno       := :NEW.empno;
   new_emp.ename       := :NEW.ename;
   new_emp.job         := :NEW.job;
   new_emp.mgr         := :NEW.mgr;
   new_emp.hiredate    := :NEW.hiredate;
   new_emp.sal         := :NEW.sal;
   new_emp.comm        := :NEW.comm;
   new_emp.deptno      := :NEW.deptno;
   old_emp.empno       := :OLD.empno;
   old_emp.ename       := :OLD.ename;
   old_emp.job         := :OLD.job;
   old_emp.mgr         := :OLD.mgr;
   old_emp.hiredate    := :OLD.hiredate;
   old_emp.sal         := :OLD.sal;
   old_emp.comm        := :OLD.comm;
   old_emp.deptno      := :OLD.deptno;
   update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
   old_emp                             emp_table%ROWTYPE
  ,new_emp                             emp_table%ROWTYPE
)
IS
BEGIN
   IF old_emp.empno != new_emp.empno
   THEN
      RETURN;   --can't update primary key attribute
   END IF;

   IF     old_emp.empno IS NOT NULL
      AND new_emp.empno IS NULL
   THEN
      DELETE FROM emp_table
            WHERE empno = old_emp.empno;

      RETURN;
   END IF;

   IF    (    old_emp.ename IS NULL
          AND new_emp.ename IS NOT NULL)
      OR (    old_emp.ename IS NOT NULL
          AND new_emp.ename != old_emp.ename)
   THEN
      UPDATE emp_table
         SET ename = new_emp.ename
       WHERE empno = old_emp.empno;
   END IF;
   -- ..
   -- ..
   -- ..
   IF    (    old_emp.deptno IS NULL
          AND new_emp.deptno IS NOT NULL)
      OR (    old_emp.deptno IS NOT NULL
          AND new_emp.deptno != old_emp.deptno)
   THEN
      UPDATE emp_table
         SET deptno = new_emp.deptno
       WHERE empno = old_emp.empno;
   END IF;
END;
/

A Year of WTFs

noreply@blogger.com (William Robertson) — Sat, 09 Sep 2006 08:22:43 PDT

Each year, the Oracle WTF organisation celebrates its anniversary by inviting a fellow Oracle professional to share an evening of fine Eastern European lager and disappointing bar snacks in a pub where you can't hear each other properly, and this year it was the turn of former newbie (but now of course senior expert) DBA Lisa Dobson.

Actually that's not quite true - I realised today it was just over a year ago that we started the blog, and the other evening some of us met up with Lisa who happened to be in town, and if I'd thought of this a bit earlier I could probably have pretended it was all planned. Anyway it was nice meeting Lisa, and we enjoyed the story about the colleague who accidentally deleted the entire production server, as luck would have it a week after the server room air conditioning took out the only other server by dripping a surprisingly large amount of water into it. That rather outdid my story about the day we turned up for work at a client's site and found nobody could log in, because as it happened someone had stolen the servers during the night. Or the other one, if I'd remembered to tell it, about the high street retail chain whose backup system involved a PC and a timer plug. (I shouldn't laugh - it actually worked quite well.) It was also reassuring to hear we are not the only ones who don't understand a single bloody word of those "Oracle" blogs about installing a Java framework in JDeveloper to implement service-oriented BPEL with a right-click and some XML. Umm, neat.

It seems we're also not the only ones to have been shocked and saddened by Doug Burns' recent senseless killing spree. This monster must be stopped. (...is probably what Doug said as he reached for his copy of "Expert One-On-One Oracle").

Anyway, one year, 63 posts, and some lessons learned:

Post one of Tom Kyte's pet hates like WHEN OTHERS THEN NULL (or WHEN OTHERS THEN DBMS_OUTPUT, which isn't much better quite frankly) for a mention on his blog and a surefire thousand hits. We need one about IM Speak now b/c im sure u will C loadsa hits ;-)
Post something involving security for a mention on Pete Finnigan's site. We also get a steady stream of visitors googling for "forgot system password +oracle", "how to get password of a user in oracle" and so on, and being directed to Umm, I Forgot My Password, part 2. (In future just put it on a Post-It under your keyboard like everyone else.)
Titles involving common Oracle error codes seem to do well. A lot of visitors come here from a Google search for SP2-0552 (guys, you have a variable with a colon in front of it somewhere). We need some posts about ORA-0600 or TNSNAMES.
A lot of people seem to be searching for an explanation of joins and end up at our Joins Explained, heaven help them, where a Mr Sanders Kaufman explains things like "Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables." I'm glad we got that cleared up.
Google Analytics Rock. So (while we're at it) do Statcounter, Feedburner and CoComment.

We were recently offered some sponsorship by price comparison website Shopzilla (or rather, they emailed me a week ago and I remembered it in the pub). The consensus among those present was that we should not accept it, although personally I think all that would change if we could get The Daily WTF's Beanbag Girl.

We were also kindly offered a spot on Pythian Group's Logfile Of The Vanities or whatever it's called (or rather, they emailed me a month ago and I seem to have deleted the message, sorry guys, meant to get back to you) but I didn't take them up on it, partly because there didn't seem much point in writing a piece telling people about Oracle blogs they already read (surely?) via blogs.oracle.com, and also because it's explicitly for DBAs, and with the possible exception of Padders we are all developers (even though these days "developer" is often assumed to mean someone who is into aspect-oriented JDeveloper BPEL plug-ins). Our main thing is SQL, PL/SQL and data modelling, and AFAIK none of those have much to do with the DBA role. In fact, thinking about it I'm not sure why they asked. And is that Pie-thian as in Pythagoras and Python, or Pith-ian as in Gryffindor and Slytherin, anyway?