Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Difficulty of making reproducible test case

with 2 comments

Let me show you the difficulty of making reproducible test case.

First of all, take a look at following OTN forum.

The test case authored by Tom Kyte is so famous and natural that it is really hard to find out the flaws. But even this kind of historically proven simple test case fails at being a reproducible test case.

See following test case why it is that difficult to make a reproducible test case.

1. My Oracle

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. Create small undo tablespace to make it easy to hit ORA-01555 error.

alter system set undo_tablespace=UNDOTBS1;

drop table t purge;
create table t as select * from all_objects;  
create index t_idx on t(object_id);

drop tablespace small_undo including contents and datafiles;
create undo tablespace small_undo datafile size 200k;

alter system set undo_tablespace=SMALL_UNDO;
alter system flush buffer_cache;

3. Following is one of the most famous test case that is promised to hit ORA-01555 error, but…

UKJA@ukja102> begin
  2          for x in ( select * from t where object_id > 0 )
  3          loop
  4                  update t set object_name = lower(object_name)
  5                    where object_id = x.object_id and rownum=1;
  6                  commit;
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.  -- Why no ORA-01555???

It does not hit ORA-01555. What is happening here?

4. One of the reasons is that in the recent versions of Oracle, the select statement in the above test case takes table full scan, not index scan.

explain plan for select * from t where object_id > 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |  4541K|   186  (17)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 50000 |  4541K|   186  (17)| 00:00:03 |
--------------------------------------------------------------------------

With full table scan working, Oracle would read the blocks sequentially and do not have repeated visit on the blocks modified by the internal update. So we would have very low chance to hit ORA-01555 error.

5. Okay, now I give INDEX hint to force the index scan, but Oracle still denies to produce ORA-01555.

UKJA@ukja102> create table t as select * from all_objects where rownum <= &1;
old   1: create table t as select * from all_objects where rownum <= &1
new   1: create table t as select * from all_objects where rownum <= 1000

Table created.

UKJA@ukja102> begin
  2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
  3          loop
  4                  update t set object_name = lower(object_name)
  5                    where object_id = x.object_id and rownum=1;
  6                  commit;
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.   -- Again no ORA-01555 error?

6. The reason is that the number of 1000 rows is too small to overwrite the undo which the select statement needs. For 10000 rows, we finally hit ORA-01555.

UKJA@ukja102> create table t as select * from all_objects where rownum <= &1;
old   1: create table t as select * from all_objects where rownum <= &1
new   1: create table t as select * from all_objects where rownum <= 10000

Table created.

UKJA@ukja102> begin
  2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
  3          loop
  4                  update t set object_name = lower(object_name)
  5                    where object_id = x.object_id and rownum=1;
  6                  commit;
  7          end loop;
  8  end;
  9  / 
begin
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 2

When you have small number of objects in your database, the test case would not work at all.

7. There would be many other restrictions when Oracle does not hit ORA-01555.

What makes me shocked(as always) is that making reproducible test case(anywhere, anytime) is really difficult thing even with very simple scenarios. So you need to be very careful when you give someone a test case – it would have many restrictions and flaws than you’ve imagined.

Written by Dion Cho

August 4, 2009 at 10:18 am

Posted in Misc.

2 Responses

Subscribe to comments with RSS.

  1. Also like Jonathan Lewis and probably several others have said, dont believe in any test case unless it is applicable to you and in your situation :)

    Kumar

    August 4, 2009 at 2:41 pm

  2. Great Dion Cho for making test case.
    Good idea to me;) But I don’t like ORA-01555.

    Surachart Opun

    August 4, 2009 at 7:11 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: