Re: select/delete on IOT becomes slower as more rows get deleted - summary

thank you all who gave me replys, informations and links to other blogs (mentioned at the end)

What I learned:
*) empty Index-blocks are not immediately released from the index' structure. so everything which creates a FULL (INDEX) SCAN or a RANGE SCAN with a too low (or high) value will check even the empty (but not reused) blogs.
*) empty Index blocks are reused when needed to insert rows. *) a 'alter index xxx coalesce;' releases unused blocks immediately.

At last, Jonathan gave me a hint:
> assuming that you always pick the lowest (or highest) value in
> the index, as the starting point of the range scan will always be
> the original
> lowest value (even though it's been deleted) until the block is
> moved out
> of that portion of the index (see 2). This would help if either
> (a) you did a coalesce on the index every thousand rows
> (b) you remembered the last value used and your query was
> select with key_value > {last value}

(this makes sure, no empty blocks are scanned. A ... and ins_date > to_date('01-Jan-1901 01:01:01', 'DD-Mon-YYYY HH24:MI:SS') ... would also lead into a RANGE SCAN from the beginning (with all the empty, not reused blocks).
and in a Test with:

LOOP
    execute immediate 'SELECT /*+ first_rows(1) */ C1, C2 ,C3, ROWID, INS_DATE FROM TEST_IOT WHERE ROWNUM=1 and ins_date >= :old_date FOR UPDATE' into IP, freed, net, rid, prev_date using old_date ;

    old_date := prev_date;
    execute immediate 'DELETE from TEST_IOT where rowid = :rid' using rid;

    commit;
...
END LOOP; brought constant low access times down from 50.000 entries to 10. - with approx 0.0002 sec. per transaction.

I'm not sure, if this is a solution for all kind of FIFO requirenments, but in my case I can give a valid answer WHY it didn't work before and HOW (and WHY) it will work that way.

links:

http://richardfoote.wordpress.com/2008/07/01/deleted-index-entries-part-v-trouble/
http://jonathanlewis.wordpress.com/2008/09/26/index-analysis/
http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

best regards,
  Martin

>
> 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




application/pkcs7-signature attachment: smime.p7s