Deterministic clause

In my previous Fancy Code blog entry, I received a comment from Rick saying:

Maybe the original author specified DETERMINSTIC so that the function wouldn't have to logically derive the return value if/whenever it was called again with the same parameter value. Since it could just return previously calculated results, then maybe he could save a fraction of a second of CPU time within a year or two if it gets called enough.

And since the answer to that question will be lengthy I decided to make it a separate post. According to the documentation this comment is certainly valid. Here is a snippet from the documentation about the deterministic clause:

DETERMINISTIC Clause

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.

The following semantic rules govern the use of the DETERMINISTIC clause:

• You can declare a top-level subprogram DETERMINISTIC.
  
• You can declare a package-level subprogram DETERMINISTIC in the package specification but not in the package body.
  
• You cannot declare DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body).
  
• A DETERMINISTIC subprogram can call another subprogram whether the called program is declared DETERMINISTIC or not.

This part of the documentation hasn't changed from 9iR2 to 11gR1, but there certainly was a difference, concerning this sentence:

When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function.

This has never been true for a long time, as I will show below. Or should I say the "attempts" have all been unsuccessful? To show this, I used the adjusted piece of fancy code and create two variants of the strip_transaction_rolled_back function. The one with the "2" suffix is the deterministic one. The code was executed on a 9.2.0.8 database. Notice that I ran the test at least three times to be able to safely use the simple "set timing on" to measure elapsed times. The elapsed times were consistent in all runs.

SQL> CREATE FUNCTION strip_transaction_rolled_back
  2  ( p_sqlerrm IN VARCHAR2
  3  ) RETURN VARCHAR2
  4  IS
  5  BEGIN
  6    RETURN
  7      CASE
  8      WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
  9      THEN
 10        SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
 11      ELSE
 12        p_sqlerrm
 13      END
 14    ;
 15  END strip_transaction_rolled_back;
 16  /
 
Function created.
 
SQL> CREATE FUNCTION strip_transaction_rolled_back2
  2  ( p_sqlerrm IN VARCHAR2
  3  ) RETURN VARCHAR2 DETERMINISTIC
  4  IS
  5  BEGIN
  6    RETURN
  7      CASE
  8      WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
  9      THEN
 10        SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
 11      ELSE
 12        p_sqlerrm
 13      END
 14    ;
 15  END strip_transaction_rolled_back2;
 16  /
 
Function created.
 
SQL> set timing on
SQL> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back2
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:12.93
SQL> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:12.81

All runs on 9.2.0.8 gave approximately equal elapsed times.

Now the same on 10.2.0.1 on my laptop:

rwijk@ORA10GR2> set timing on
rwijk@ORA10GR2> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back2
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.23
rwijk@ORA10GR2> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.23

Still no difference. Let's try on 11.1.0.6 on my laptop:

rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back2
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:00.14
rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.29

So Oracle11g has implemented the optimization. It now really "attempts to use previously calculated results when possible rather than reexecuting the function".

But in 11g, why not use the PL/SQL function result cache for this?

Let's compare by creating a third function using the PL/SQL function result cache:

rwijk@ORA11G> CREATE FUNCTION strip_transaction_rolled_back3
  2  ( p_sqlerrm IN VARCHAR2
  3  ) RETURN VARCHAR2 RESULT_CACHE
  4  IS
  5  BEGIN
  6    RETURN
  7      CASE
  8      WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
  9      THEN
 10        SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
 11      ELSE
 12        p_sqlerrm
 13      END
 14    ;
 15  END strip_transaction_rolled_back3;
 16  /

Functie is aangemaakt.

The result was this:

rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back3
  7      ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.35

Back at the old elapsed times. Was the result cache indeed used? The "Find Count" in v$result_cache_statistics said so, so yes, the results came out of the result cache. But why is returning the results from a deterministic function so much faster than returning the results from the result cache? First obvious reason I could think of, is that the result cache benefits all sessions, where you can only benefit from the deterministic clause when your session executes the function twice or more. Let's compare the two with Tom Kyte's runstats_pkg:

rwijk@ORA11G> exec runstats_pkg.rs_start

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back2
  7      ( 'ORA-02091: Transaction rolled back ORA:12345 bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> exec runstats_pkg.rs_middle

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    for i in 1..1000000
  5    loop
  6      t := strip_transaction_rolled_back3
  7      ( 'ORA-02091: Transaction rolled back ORA:12345 bla bla'
  8      );
  9    end loop;
 10  end;
 11  /

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> exec runstats_pkg.rs_stop(100)
Run1 draaide in 17 hsecs
Run2 draaide in 249 hsecs
Run1 draaide in 6,83% van de tijd

Naam                                                      Run1        Run2    Verschil
STAT.DB time                                                18         244         226
STAT.CPU used when call started                             17         245         228
STAT.CPU used by this session                               14         245         231
LATCH.Result Cache: Latch                                    0   2,000,002   2,000,002

Run1 latches totaal versus run2 -- verschil en percentage
      Run1      Run2  Verschil     Pct
       441 2,000,638 2,000,197    .02%

PL/SQL-procedure is geslaagd.

Two million result cache latches. Acquiring data from memory, whether it is from SGA, PGA or UGA won't differ much in speed. Getting an extra latch however can make a difference when being executed this many times. So most likely this is the reason why the deterministic clause is faster here.

In short, the deterministic clause in a top-level subprogram should certainly be specified if a function truly is deterministic, because the optimization suggested by the documentation finally got implemented in 11g. If you are on an older version right now, you will benefit in the future when the system is upgraded.

The deterministic clause used in a subprocedure inside a procedure in a package body, still won't have any effect, as documented. So I still believe it only leads to confusion, and is best avoided. A final test to show that the code as it was written in the "Fancy Code" post won't even benefit when upgraded to 11g:

rwijk@ORA11G> create package pck
  2  as
  3    procedure p1 (p_sqlerrm IN VARCHAR2);
  4    procedure p2 (p_sqlerrm IN VARCHAR2);
  5  end pck;
  6  /

Package is aangemaakt.

rwijk@ORA11G> create package body pck
  2  as
  3    procedure p1 (p_sqlerrm IN VARCHAR2)
  4    is
  5      t varchar2(100)
  6      ;
  7      function strip_transaction_rolled_back
  8      ( p_sqlerrm IN VARCHAR2
  9      ) RETURN VARCHAR2
 10      IS
 11      BEGIN
 12        RETURN
 13          CASE
 14          WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
 15          THEN
 16            SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
 17          ELSE
 18            p_sqlerrm
 19          END
 20        ;
 21      END strip_transaction_rolled_back
 22      ;
 23    begin
 24      t := strip_transaction_rolled_back(p_sqlerrm);
 25    end p1
 26    ;
 27    procedure p2 (p_sqlerrm IN VARCHAR2)
 28    is
 29      t varchar2(100)
 30      ;
 31      function strip_transaction_rolled_back2
 32      ( p_sqlerrm IN VARCHAR2
 33      ) RETURN VARCHAR2 DETERMINISTIC
 34      IS
 35      BEGIN
 36        RETURN
 37          CASE
 38          WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
 39          THEN
 40            SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
 41          ELSE
 42            p_sqlerrm
 43          END
 44        ;
 45      END strip_transaction_rolled_back2
 46      ;
 47    begin
 48      t := strip_transaction_rolled_back2(p_sqlerrm);
 49    end p2
 50    ;
 51  end pck;
 52  /

Package-body is aangemaakt.

rwijk@ORA11G> set timing on
rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    -- the deterministic variant
  5    for i in 1..1000000
  6    loop
  7      pck.p2('ORA-02091: Transaction rolled back ORA-12345: bla bla');
  8    end loop;
  9  end;
 10  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.79
rwijk@ORA11G> declare
  2    t varchar2(100);
  3  begin
  4    -- the non-deterministic variant
  5    for i in 1..1000000
  6    loop
  7      pck.p1('ORA-02091: Transaction rolled back ORA-12345: bla bla');
  8    end loop;
  9  end;
 10  /

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:02.79

ORA-01401, ORA-12899 and ORA-01438

My customer recently upgraded some of its databases from Oracle 9.2.0.7 to 10.2.0.3, so when my colleague and me saw error message ORA-12899 after an insert statement, it came as a surprise. But it was a pleasant surprise.

In 9.2.0.7 we were used to see a ORA-01401 in the situation where you'd insert a varchar2 value that doesn't fit into a column because it's length is too big. And to its counterpart, the ORA-01438, in case of a number instead of a varchar2.

The following SQL script was executed on a 9.2.0.8 database:

SQL> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /
 
Table created.
 
SQL> insert into t values (99,'AA')
  2  /
 
1 row created.
 
SQL> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

And on a 10.2.0.3 database:

SQL> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /
 
Table created.

SQL> insert into t values (99,'AA')
  2  /
 
1 row created.
 
SQL> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
ERROR at line 1:
ORA-12899: value too large for column "RWIJK"."T"."NAME" (actual: 3, maximum: 2)


SQL> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

ORA-12899: now that's what I call a small but lovely improvement. You no longer have to wonder what column in your 100-column-table the error message is complaining about. It's all right there in the error message.

The ORA-01401 has been removed from the documentation in version 10, and ORA-12899 became its substitute. Unfortunately, as can be seen above, the ORA-01438 hasn't been given a same facelift.

I also checked 11.1.0.6:

rwijk@ORA11G> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into t values (99,'AA')
  2  /

1 rij is aangemaakt.

rwijk@ORA11G> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
FOUT in regel 1:
.ORA-12899: waarde te groot voor kolom "RWIJK"."T"."NAME" (huidige waarde: 3, maximumwaarde: 2)


rwijk@ORA11G> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
FOUT in regel 1:
.ORA-01438: Waarde is groter dan de voor deze kolom opgegeven maximale precisie.

So no changes from 10 to 11: still ORA-01438 and ORA-12899.

A google search afterwards revealed that this has been "discovered" many times before, but it was new to me.

Fancy code

I was reminded about this blog's motto when I watched a piece of production code last week. It was located as a subfunction at the end of the declaration section of a monster procedure containing 1500 lines. At least some form of modularization has kicked in, was my first thought after seeing the smallish function. This thought was immediately followed by a "and quite a clear name too". Then I looked at what the function was doing. I'll let you experience the same. Here it is:

FUNCTION strip_transaction_rolled_back
( p_sqlerrm  IN  VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC
IS
  SUBTYPE msg_code_type IS VARCHAR2(9);
  cn_ora_  CONSTANT VARCHAR2(4) := 'ORA-';
  cn_transaction_rolled_back CONSTANT msg_code_type := cn_ora_||'02091';
BEGIN
  RETURN
  ( CASE
    WHEN (    (INSTR( str1 => p_sqlerrm
                    , str2 => cn_transaction_rolled_back
                    ) = 1
              )
         AND  (INSTR( str1 => p_sqlerrm
                    , str2 => cn_ora_
                    , pos  => (1 + LENGTH( ch => cn_transaction_rolled_back
                                         )
                              )
                    ) != 0
              )
         )
    THEN
      SUBSTR( str1 => p_sqlerrm
            , pos  => (INSTR( str1 => p_sqlerrm
                            , str2 => cn_ora_
                            , pos  => (1 + LENGTH( ch => cn_transaction_rolled_back
                                                 )
                                      )
                            )
                      )
            )
    ELSE
      p_sqlerrm
    END
  );
END strip_transaction_rolled_back;

The function accepts an error message and returns the same error message, unless the error message starts with ORA-02091 and is followed by another ORA-message. In which case the first ORA-02091 message is stripped off.

You can argue about the layout and all the superfluous brackets, but that's a matter of personal style I want to leave out of the discussion. Three aspects struck me the most:

1. the deterministic clause
2. the subtype
3. the use of named notation for the parameters of the Oracle-functions instr and substr.

1. The deterministic clause says that the outcome of the function will be the same given the same set of inputs. You need this one when you want to define a function based index or a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE (just read about that one) or a function based virtual column. But we are dealing with a subfunction of a packaged procedure ... I did an impact analysis for the function to convince myself that the function was only used inside the procedure, and that the code wasn't copied elsewhere. And I got convinced.

2. Then the subtype. This one allows for tidier coding when you want to use a type throughout your package, and there is a chance the datatype may change some time in the future. Maintenance can then be reduced by only changing the datatype of the subtype instead of all the occurences scattered throughout the package. You can also nicely restrict the subtype to a smaller range. But here in a subfunction, with its only use being two lines below? I don't get the added value of this one either.

3. Using named notation for parameters is generally nice, since you immediately document your code somewhat in the process. Here it is applied to the Oracle functions INSTR and SUBSTR. The formal name of the parameters isn't even documented, as you can see here for INSTR and here for SUBSTR. The names used in the documentation are not the real names as can be seen by describing the sys.standard package:

rwijk@ORA11G> desc sys.standard
...  ...
FUNCTION INSTR RETURNS BINARY_INTEGER
 Argumentnaam                   Type                    In/Out Standaard?
 ------------------------------ ----------------------- ------ --------
 STR1                           VARCHAR2                IN
 STR2                           VARCHAR2                IN
 POS                            BINARY_INTEGER          IN     DEFAULT
 NTH                            BINARY_INTEGER          IN     DEFAULT
...  ...
FUNCTION SUBSTR RETURNS VARCHAR2
 Argumentnaam                   Type                    In/Out Standaard?
 ------------------------------ ----------------------- ------ --------
 STR1                           VARCHAR2                IN
 POS                            BINARY_INTEGER          IN
 LEN                            BINARY_INTEGER          IN     DEFAULT
...  ...

But what's the use of using the undocumented names of the parameters of Oracle functions? I didn't even know them.

So, we have a subfunction created by a developer that has "above average" knowledge of PL/SQL: not every developer uses the deterministic clause, a subtype and the named notation for Oracle-functions. But each use has the effect to confuse the next developers maintaining the code. At least that was the effect it had on me. Add to that the clumsy use of "INSTR(...) = 1" and "INSTR(...) != 0" constructs, which can be rewritten using the LIKE operator for enhanced readability and you end up with the code above.

All of which could have been avoided when you would clearly phrase what it was supposed to do: in case the error message starts with 'ORA-02091' and contains another ORA-message, then strip off the first ORA-02091 message, else leave the error message as is. Or in code:

FUNCTION strip_transaction_rolled_back
( p_sqlerrm IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
  RETURN
    CASE
    WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
    THEN
      SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
    ELSE
      p_sqlerrm
    END
  ;
END strip_transaction_rolled_back;

You can add some comments too about what the function does, but it is almost unnecessary now. And that is exactly what the motto is about. Keep it simple.

Graham Wood customer meeting

Last Thursday, my customer got the chance to have a customer account meeting with Graham Wood at Oracle Netherlands. Normally this would be a DBA affair, but luckily my customer thought it was a good idea to have two developers join the three DBA's. And I was one of the lucky two developers, since I get to solve quite a lot of performance problems, that are spotted by our DBA's.

As a developer I do not have a lot of experience with tools like AWR, ADDM and ASH, so I did some prior reading to know a little more about what the tools do, and I also read the powerpoint of Graham's presentation at last year's Miracle Database Forum, called You Probably Don't Need SQL*Trace. Now that's a title that draws my attention of course. At first glance, the screens from the EM Diagnostic Pack to view ASH data, look very similar to the ones I am familiar with from Indepth for Oracle, which we use.

In the one-and-a-half-hour conversation, Graham answered three of our prepared questions, mostly RAC-related. And since time allowed it, I was able to ask a question myself about the previously mentioned presentation. In Indepth for Oracle it is very easy to spot when and where performance problems occur, since our systems are pretty well instrumented. However, when you drill down to the SQL-level and asking for the plan, you get to see an explain plan instead of the row source operation. This is not useful and therefore the DBA's usually send the SQL statements to me or a colleague to have a closer look. It would be nice if the row sources were displayed together with the time spent on each row source. And this is exactly what ASH will contain. So you are able to drill down from instance to session to module to SQL to row source level. Now that sounds promising. Unfortunately I don't recall in what version it will be available. Maybe it is already available now. If you know, please leave a comment.

Also, Graham was surprised to find out that version 8 of Indepth for Oracle also contains "real execution plans" as can be read here.
According to Graham that is hardly possible, as the information is only available to Oracle. As we will likely upgrade Indepth for Oracle, we will soon find out if it is truly so.

Graham also drew a picture on the whiteboard showing approximately how the first screen of the new interface will look like. Instead of having equally sized pictograms for each instance, it will show all instances as rectangles. The more time they have consumed, the larger the rectangle will be. And all rectangles will be displayed on one screen with the larger ones appearing at the top left corner, and the small - probably developer instance - rectangles at the lower right corner. Instances that show far more worse performance than usual will be displayed red. This sounds like a great interface, especially when the DBA has to monitor LOTS of databases.

I very much enjoyed the session, and although I was feeling a bit overwhelmed by all the information packed in such a short amount of time, I am glad for having had this opportunity. Graham's tour will now lead him to three different cities in Germany and after that he is off to Turkey.

Multiple query read consistency

Nothing new and shiny this time, but a write up about an aspect of one of the cornerstones of Oracle: read consistency. The concepts manual has a very good chapter about this topic called Data Concurrency and Consistency. A project I was auditing had to select data from multiple tables and interface that data to another system. So I adviced the developers to make sure all the queries would query the data from the same timestamp to avoid seeing the related data in an inconsistent state. A simple piece of advice, but I realized I had never implemented it myself, nor did the developers. In fact, I have never seen production code that uses anything different than the default read committed transaction isolation level. Nevertheless I know quite a lot of batches and interfaces that would need "multiple query read consistency" to function correctly.

In the past I had done a few experiments with the serializable transaction isolation level and concluded that this was something especially for banks, and since I never worked for a bank (a 4 hour consultancy job excluded) I did not use that isolation level ever for real. The transaction level read consistency is applicable for "multiple query read consistency" as well, but it looks strange in my opinion: you start out with saying you are beginning a serializable transaction, but you are only querying. Since I like things to be clear, this didn't appeal to me very much. But it works.

Another option I considered were cursor expressions. You can combine all the queries with the use of this feature, but the documentation clearly states:

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor.

So the subqueries will get executed at a different timestamp in PL/SQL. Option dismissed.

Next I investigated the dbms_flashback package. Provided that the undo_retention period is set sufficiently large, this looks like a neat solution. You start out your piece of code with:

dbms_flashback.enable_at_system_change_number(dbms_flashback.get_system_change_number);

This makes sure all subsequent queries will run as of this timestamp. When you are done querying, you end the section with:

dbms_flashback.disable;

And everything is back as normal. Looks good; in fact I adviced this piece of code to the developers. But then I wondered how they did this sort of thing in pre-Oracle9i times, and I returned to the Concepts Manual and realized it was the simple and ancient:

set transaction read only;

or, in PL/SQL:

dbms_transaction.read_only;

This one is the most simple and exactly what is needed in this situation. According to the documentation:

Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

In the test below, you can see how all options work. The setup involves two tables: orders and order_lines, where the orders table contains a denormalized column called "total_price" which is the sum of all total_prices of the order_lines. Also note the on delete cascade foreign key between the two tables.

rwijk@ORA11G> create table orders (id, customer_name, total_price)
  2  as
  3  select 1, 'Customer A', 10 from dual union all
  4  select 2, 'Customer B', 20 from dual union all
  5  select 3, 'Customer C', 30 from dual
  6  /

Tabel is aangemaakt.

rwijk@ORA11G> alter table orders add constraint orders_pk primary key (id)
  2  /

Tabel is gewijzigd.

rwijk@ORA11G> create table order_lines (id, order_id, product, quantity, price_per_unit)
  2  as
  3  select 1, 1, 'product P', 2, 3 from dual union all
  4  select 2, 1, 'product Q', 1, 4 from dual union all
  5  select 3, 2, 'product P', 4, 3 from dual union all
  6  select 4, 2, 'product Q', 2, 4 from dual union all
  7  select 5, 3, 'product P', 1, 3 from dual union all
  8  select 6, 3, 'product Q', 3, 4 from dual union all
  9  select 7, 3, 'product R', 3, 5 from dual
 10  /

Tabel is aangemaakt.

rwijk@ORA11G> create sequence ol_seq start with 8 increment by 1
  2  /

Reeks is aangemaakt.

rwijk@ORA11G> alter table order_lines
  2    add constraint ol_pk
  3    primary key (id)
  4  /

Tabel is gewijzigd.

rwijk@ORA11G> alter table order_lines
  2    add constraint ol_orders_fk
  3    foreign key (order_id)
  4    references orders(id)
  5    on delete cascade
  6  /

Tabel is gewijzigd.

rwijk@ORA11G> create procedure add_order_line
  2  ( p_order_id       in orders.id%type
  3  , p_product        in order_lines.product%type
  4  , p_quantity       in order_lines.quantity%type
  5  , p_price_per_unit in order_lines.price_per_unit%type
  6  )
  7  is
  8  begin
  9    insert into order_lines
 10    ( id
 11    , order_id
 12    , product
 13    , quantity
 14    , price_per_unit
 15    )
 16    values
 17    ( ol_seq.nextval
 18    , p_order_id
 19    , p_product
 20    , p_quantity
 21    , p_price_per_unit
 22    );
 23    update orders
 24       set total_price = total_price + p_quantity * p_price_per_unit
 25     where id = p_order_id
 26    ;
 27  end;
 28  /

Procedure is aangemaakt.

rwijk@ORA11G> create function sleep(p_seconds in number) return number
  2  as
  3  begin
  4    dbms_lock.sleep(p_seconds);
  5    return null;
  6  end;
  7  /

Functie is aangemaakt.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'orders',cascade=>true)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'order_lines',cascade=>true)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> alter session set plsql_optimize_level = 0
  2  /

Sessie is gewijzigd.

The PLSQL_OPTIMIZE_LEVEL is set to 0 to avoid the automatic bulk fetching since version 10. A cursor for loop will now fetch row-by-row. And because of the function sleep it will do so with an interval.

rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:04:38
Order 1 having total price 10 consisting of:
* product P: 6     (23:04:43)
* product Q: 4     (23:04:43)
Order 2 having total price 20 consisting of:
* product P: 12     (23:04:48)
* product Q: 8     (23:04:48)
Order 3 having total price 30 consisting of:
* product P: 3     (23:04:53)
* product Q: 12     (23:04:53)
* product R: 15     (23:04:53)
23:04:53

PL/SQL-procedure is geslaagd.

This piece of code will be used throughout this test. It shows all orders with their order_lines using two queries. You want both the queries to execute at the same time. If you don't enforce this, you may end up with output as can be seen in the next piece of code:

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id = 3;
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:04:54
Order 1 having total price 10 consisting of:
* product P: 6     (23:04:59)
* product Q: 4     (23:04:59)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:04)
* product Q: 8     (23:05:04)
* product R: 40     (23:05:04)
Order 3 having total price 30 consisting of:
23:05:09

PL/SQL-procedure is geslaagd.

This query output is clearly wrong, even though the database moved from one consistent state to the other. So here are some of the earlier discussed alternatives. You'll have to trust me that I opened another session to execute the statements mentioned in the remarks.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

Restore the situation to the original state.

rwijk@ORA11G> set transaction isolation level serializable
  2  /

Transactie is ingesteld.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:05:09
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:14)
* product Q: 4     (23:05:14)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:19)
* product Q: 8     (23:05:19)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:24)
* product Q: 12     (23:05:24)
* product R: 15     (23:05:24)
23:05:24

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_flashback.enable_at_system_change_number
  3    ( dbms_flashback.get_system_change_number
  4    );
  5    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  6    for r1 in
  7    ( select id
  8           , customer_name
  9           , total_price
 10           , sleep(5) s
 11        from orders
 12    )
 13    loop
 14      dbms_output.put_line
 15      ( 'Order ' || to_char(r1.id) ||
 16        ' having total price ' || to_char(r1.total_price) ||
 17        ' consisting of:'
 18      );
 19      for r2 in
 20      ( select product
 21             , quantity * price_per_unit total_price_per_order_line
 22          from order_lines
 23         where order_id = r1.id
 24      )
 25      loop
 26        dbms_output.put_line
 27        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 28          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 29        );
 30      end loop;
 31    end loop;
 32    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 33    dbms_flashback.disable;
 34  end;
 35  /
23:05:24
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:29)
* product Q: 4     (23:05:29)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:34)
* product Q: 8     (23:05:34)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:39)
* product Q: 12     (23:05:39)
* product R: 15     (23:05:39)
23:05:39

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_transaction.read_only;
  3    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  4    for r1 in
  5    ( select id
  6           , customer_name
  7           , total_price
  8           , sleep(5) s
  9        from orders
 10    )
 11    loop
 12      dbms_output.put_line
 13      ( 'Order ' || to_char(r1.id) ||
 14        ' having total price ' || to_char(r1.total_price) ||
 15        ' consisting of:'
 16      );
 17      for r2 in
 18      ( select product
 19             , quantity * price_per_unit total_price_per_order_line
 20          from order_lines
 21         where order_id = r1.id
 22      )
 23      loop
 24        dbms_output.put_line
 25        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 26          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 27        );
 28      end loop;
 29    end loop;
 30    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 31  end;
 32  /
23:05:39
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:44)
* product Q: 4     (23:05:44)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:49)
* product Q: 8     (23:05:49)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:54)
* product Q: 12     (23:05:54)
* product R: 15     (23:05:54)
23:05:54

PL/SQL-procedure is geslaagd.