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

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 4 Feb 2009 09:47:11 +0100
Message-ID: <cd8f74560902040047yb75dce7y9130ef108e48894e_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 04 2009 - 02:47:11 CST