select/delete on IOT becomes slower as more rows get deleted

Hi List,

again I'm coming with a behaviour I cannot explain to myselve. Maybe someone
can hint my either to an systematic error in my test, my thoughts or how to
explain the behaviour:
I have an index organized table (100000 rows in my testcase)
CREATE TABLE "TEST"."TEST_IOT"
  (    "INS_DATE" DATE,
       "C1" VARCHAR2(40 CHAR),
       "C2" VARCHAR2(30 CHAR),
       "C3" VARCHAR2(32 CHAR),
       "C4" NUMBER(6,0),
       "C5" NUMBER(3,0),
        CONSTRAINT "PK_TEST_IOT" PRIMARY KEY ("INS_DATE", "C1") ENABLE
  ) ORGANIZATION INDEX
...

and run
LOOP
   ...
   execute immediate '*SELECT /*+ first_rows(1) */ C1, C2 ,C3, ROWID FROM
TEST_IOT WHERE ROWNUM=1 FOR UPDATE*' into IP, freed, net, rid;
   execute immediate '*DELETE from TEST_IOT where rowid = :rid*' using rid;
   commit;
   -- ...

the execution plan looks quite simple for the sql:
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |    58 |     2   (0)|
00:00:01 |
|   1 |  FOR UPDATE            |             |       |       |            |
         |
|*  2 |   COUNT STOPKEY        |             |       |       |            |
         |
|   3 |    INDEX FAST FULL SCAN| PK_TEST_IOT |     1 |    58 |     2   (0)|
00:00:01 |
--------------------------------------------------------------------------------------


the goal of these statements is to grab any row out of the table and delete
it (and do something else with the data - not part of hthis striped down
testcase). In the future the developer is hoping for some kind of FIFO
(based on a column INS_DATE which reflects the insert date), which is a
every 1000 rows I grab a timestamp and reportthe difference.

for any reason the performance drops from *00.734* sec per 1000
select/deletes at the beginning to *40.771 *sec per 1000 runs at the end.

The test where made on a test-system, no other load there (neither RDBMS,
nor OS)
System: HP BL860C HP-UX 11.31 - 2 CPUs
Load averages: 0.67, 0.65, 0.59
254 processes: 198 sleeping, 55 running, 1 zombie
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0    1.01  65.2%   0.0%   0.0%  34.8%   0.0%   0.0%   0.0%   0.0%
 2    0.32  36.6%   0.0%   1.0%  62.4%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.67  51.0%   0.0%   0.4%  48.6%   0.0%   0.0%   0.0%   0.0%

System Page Size: 4Kbytes
Memory: 4108172K (3120300K) real, 8973412K (7622616K) virtual, 36320K free
Page# 1/10

CPU TTY    PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU
COMMAND
 0   ?   24646 oracle   178 20  1907M 19212K run     61:28 100.62 100.44
oracleAAAT01

RDBMS:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

Can anyone explain why the performance drops that much as the # of rows in
the IOT decreases?
I would expect quite stable performance as every SELECT has to go from the
root-block down to the first leaf (quite same length every time?) and the
DELETE has the same maintanence-work to do everty time? What is the
component I'm blind to see which increases the response time?

the full test-case is available here:
http://berx.at/files/iot_speed_decreases.sql
(to keep the mail shorter) - any further questions welcome.

thank you all in advance,
 Martin

--
Martin Berger http://berxblog.blogspot.com
           http://www.freelists.org/list/oracle-l

Other related posts: